Creating the Microsoft SQL Database

This section includes the procedure to create Microsoft SQL Server databases from Sybase databases. It covers both Hq and Schema databases.

ARCHIBUS will de-support SQL Server 2000 from 17.2. Use SQL Server 2005 to build the databases.

To create the MS SQL database:

  1. Create a new database through SQL Server Management Studio with‘sa’ as the owner.
  2. If the server does not have AFM and AFM_SECURE logins, add them by going to: Server name -> Security->Logins. For AFM, you need the "DBCREATOR" server roles.

    For AFM:

    1. Add AFM as a user to the new database, user_name= afm, login_name=afm, default schema will be set as dbo by the server, add db_owner as a database role.
    2. Create an afm schema by going to database_name-> security->schema. Set schema owner as afm.
    3. From the MSSQL Query Analyzer, run "ALTER USER afm WITH DEFAULT_SCHEMA = afm;" to set schema afm as the default schema for user afm.

    For afm_secure:

    1. Add afm_secure as a user to the new database; user_name= afm_secure, login_name=afm_secure. The default schema will be set as dbo by the server.
    2. Create an afm_secure schema; set schema owner as afm_secure.
    3. From the MSSQL Query Analyzer, run "ALTER USER afm_secure WITH DEFAULT_SCHEMA = afm_secure" to set schema afm_secure as the default for user afm_secure.
  3. Create a new project in ARCHIBUS.

    Note: If you are creating a MS SQL database for double-byte languages, complete the steps in Creating Databases for Double-Byte Languages for MS SQL before running the Database Update Wizard as described in the next steps.

  4. Run the ARCHIBUS Database Update Wizard by using all default options. Source: HQ Sybase project; Destination: new sql server project.
  5. After running the Database Update Wizard, open ARCHIBUS. Using the Domain Navigator, go to System Management->Schema->Define->. Then execute all of the tasks from "Update Space Management SQL Views" to the end.

  6. In ARCHIBUS, go to File->Run Basic Script-> Execute the file: Schema->Common->hotlschg.abs.
  7. Run the sql statements in updateCalendarViewFileName.txt through Query Analyzer.
  8. Optionally, you can verify the correctness of your database: In ARCHIBUS, go to File->Run Basic Script->Schema->System->Comparator->Comparator50.abs to compare the ARCHIBUS data dictionary to the actual physical database.

    Three versions of the .abs ship with ARCHIBUS in the folder: …\Afm18\Schema\System\

  9. Run comparator.abs; there will not be many differences (if any) for "allow null"; there are a number of expected differences for "default value".
  10. Then run the reccount.abs. The number of records in Sybase and SQL server 2005 should be the same.

    Note: Because there is no 8k field size limit on SQL server 2005, the Database Update Wizard does not remove the double-byte fields from the single-byte database or the single-byte fields from the double-byte database.

  11. To detach the database, from the MSQL Query Analyzer, type in:

    "exec sp_detach_db 'hq'"