Migrating to a New Microsoft SQL Server

To migrate your ActivID CMS data to a new Microsoft SQL Server, complete the following steps.

1. Stop the ActivID CMS server.

2. On the source system where the older Microsoft SQL Server is installed, open Microsoft SQL Server Management Studio to open the new Microsoft SQL Server database.

3. Click to expand the Server node to reveal all databases (as illustrated next).

   

4. Right-click the AIMSAUDIT database, click Tasks, and then click Detach to display the Detach Database window.

   

5. Check the Drop Connections check box and click OK.

6. Repeat steps 4 and 5 to detach each of your remaining ActivID CMS databases (AIMSCTI, AIMSEE, AIMSLGI, AIMSRQI, and AIMSUSER).

7. On the source system where the older Microsoft SQL Server is installed, go to:

   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

8. Copy all of the .mdf and .ldf files in your source ActivID CMS databases. For example, the files:

   AIMSAUDIT.mdf, AIMSAUDIT_log.ldf or AIMSCTI.mdf

9. On the destination system where the new Microsoft SQL Server is installed, go to:

   C:\ Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

10. Paste all of the .mdf and .ldf files that you previously copied in step 8.

11. On the destination system where the new Microsoft SQL Server is installed, open Microsoft SQL Server Management Studio to open the new Microsoft SQL Server databases.

    

12. Right-click the Databases node, and then click Attach to display the Attach Databases window.

    

13. Click Add to display the Locate Databases window.

14. Expand the nodes as needed to locate the Data subdirectory. For example, the directory might be:

    C:\Program Files\Microsoft SQL Server\ MSSQL10.MSSQLSERVER\MSSQL\DATA

15. Expand the Data subdirectory where the .mdf files of the ActivID CMS databases are located.

    

16. Select AIMSAUDIT.mdf and then click OK to attach this database to the new Microsoft SQL Server.

    

17. Click OK.

    

18. Click OK to the confirmation message to attach the database to the new Microsoft SQL Server.

19. Repeat step 11 through step 18 for the five remaining ActivID CMS databases (AIMSCTI, AIMSEE, AIMSLGI, AIMSRQI and AIMSUSER).

    The following image illustrates all ActivID CMS databases attached to the new Microsoft SQL Server.

    

20. Create a login for each of the ActivID CMS databases by completing the following steps.

    • Select the AIMSAUDIT database, and then click New Query to display the New Query window.

    The following list contains the guidelines you must observe when using database logins.

    • Each of the six ActivID CMS databases must be linked to a login that is composed of a Database Owner username and password.

    • The username is always the name of the database (for example, for the AIMSAUDIT database, the username is AIMSAUDIT).

    • The password is the Database Owner password that was created during the ActivID CMS installation. 

21. In the New Query window, enter the following query:

    Copy

    exec sp_change_users_login ‘Auto_Fix’, ‘AIMSAUDIT’, NULL, ‘your_db_password’;

    This query creates a database login with the username AIMSAUDIT and the password <your_db_password>, and linked to the database.

    Note: <your_db_password> must match the Database Owner password that was defined when ActivID CMS was installed. If you use a different password, you must also change the Database Owner password that was defined during your ActivID CMS installation.

22. Click Execute. This displays the Query executed successfully message shown in the following illustration.

    

    You can expand the Security node to verify that the AIMSAUDIT database login has been successfully created under Logins (illustrated above).

23. To verify the state of the login configuration, under Logins, right-click AIMSAUDIT, and then click Properties.

    

24. Repeat steps 20 through step 23 for the five remaining ActivID CMS databases (using the corresponding database names).

    When you have run all queries, all ActivID CMS logins should be displayed under Logins as illustrated next.

    

25. Recreate the AIMSEE user in the AIMSLGI database and map the AIMSEE login to the AIMSLGI_ACCESS role by completing the following steps.

    • On the destination system where new Microsoft SQL Server is installed, open the SQL Server Management Studio and click New Query.

    • Execute the following script as shown in the below screenshot.

      Copy

      USE [AIMSLGI
      GO
      DROP SCHEMA [AIMSEE]
      GO
      USE [AIMSLGI]
      GO
      DROP USER [AIMSEE]
      GO
      USE [AIMSLGI]
      GO
      CREATE USER [AIMSEE] FOR LOGIN [AIMSEE]
      GO
      USE [AIMSLGI]
      GO
      ALTER USER [AIMSEE] WITH DEFAULT_SCHEMA=[AIMSEE]
      GO
      USE [AIMSLGI]
      GO
      EXEC sp_addrolemember N'AIMSLGI_ACCESS', N'AIMSEE'
      GO

      

To reconnect ActivID CMS to the new database on the new Microsoft SQL Server, complete the following steps:

1. Stop the ActivID CMS server.

2. On the system hosting ActivID CMS, click Start, point to All Programs, HID Global, Credential Management System, and then click Modify Credential Management System.

   

3. Click Next.

   

4. Click Repair and then click Next.

   

   This illustration shows the SQL Server authentication option selected under the Connect using panel. The other option that can be selected is Windows authentication.

5. Adjacent to the Database Server drop-down list, click Browse and go to your new Microsoft SQL Server database server.

6. In the Connect using panel, select the appropriate authentication option: Windows authentication or SQL Server authentication.

   Note: The text boxes in the Database Administrator Credentials panels are disabled if you initially created the ActivID CMS databases using the database creation scripts instead of using ActivID CMS Setup.

   • If the Windows authentication option is selected, then complete the following tasks:

     • In the User Account and Password text boxes, enter the login name and password of the Windows user account being used to connect to the database.

   • If the SQL Server authentication option is selected, then complete the following tasks:

     • In the Database Administrator Credentials panel, enter the login name and password for the SQL Database Administrator.

     • In the Database Owner Password panel, enter and confirm the password.

7. Click Next.

   

8. Click Install.

9. When you see a message that the installation has been successful, click Finish.

10. Restart the ActivID CMS server.

11. Login to the ActivID CMS Operator Portal and perform a connectivity check.