3305 Main Street Ste 019 Vancouver, WA 98663

How do I migrate from one SQL Server to another?

Background

This article covers the process associated with relocating your Prolog Manager database from one SQL Server Instance to another. This must be accompanied by the relocation of the Integrator database as well as updates to the Integrator connection file(s).

Considerations

The steps in this article assume the following:

  • Basic understanding of how to use SQL Server Management Studio.
  • The machine(s) on which Integrator is installed is not changing.
  • The location of the Event 1 System Folder used by Integrator to store shared components is not changing.
  • The currently installed version of Integrator is 2.92.0020 or higher.
  • The password for the sa user is not the same for the old SQL Server Instance as the new SQL Server Instance.

Steps

Complete the following steps to migrate your Prolog Manager and Integrator databases from one SQL Server Instance to another and to modify the Integrator connection file(s) so that Integrator is able to connect to the databases on the new SQL Server Instance.

  • Preparation:
    Before relocating the Prolog Manager databases, you should first remove the SDKUser account from the Prolog database(s) while they are still on the old SQL Server Instance using the following steps.
    NOTE: performing these steps will not allow any current Integrator tasks to complete until all steps listed in this article have been completed.
    1. Start SQL Server Management Studio and login to your old Prolog SQL Server Instance using the sa account.
    2. Navigate to the Prolog Manager database and expand it in the Object Explorer tree to show the Security node.
    3. Expand the Security node and then the Users node. If a User named SDKUser is listed, right-click on it and click Delete.
    4. Repeat for all Prolog Manager databases being migrated to a new SQL Server Instance.
  • Migrate Databases:
    These tasks can be accomplished using the Backup and Restore features of SQL Server Management Studio.
    1. Migrate your Prolog Manager database(s) from the old SQL Server Instance to the new SQL Server Instance.
    2. Migrate your Integrator database from the old SQL Server Instance to the new SQL Server Instance.
  • Create SDKUser In New SQL Server Instance
    1. Start SQL Server Management Studio and login to your new Prolog SQL Server Instance using the sa account.
    2. Navigate to the Security node and expand it in the Object Explorer tree to show the Logins node.
    3. Right-click Logins and select New Login.
    4. In the General page, set the following:
      • Login name: SDKUser
      • SQL Server Authentication
      • Password: eV!09290413+
      • Confirm Password: eV!09290413+
      • Uncheck Enforce password policy
    5. On the User Mapping page, check the box for the Prolog Manager database in the User mapped to this login: window.
    6. Select the following checkboxes in the Database role membership for: Prolog Database Name window of the User Mapping page:
      • db_datareader
      • db_datawriter
      • db_ddladmin
    7. Click OK.
  • Determine Hashed Password For The 'sa' User On The New SQL Server Instance:
    These steps will create a test connection to the new SQL Server Instance which will contain the hashed password for the sa user.
    1. Open Integrator.
    2. Dismiss any windows that may pop up.
    3. Create a new Connection using one of the following options:
      • Click File, then New Connection
      • Click the New Connection button on the Integrator toolbar.
      • Press Ctl-n.
    4. Name the new connection Test Connection and click Next.
    5. Select a valid Sage 300 CRE database and click Next.
    6. Select the new SQL Server Instance and click Next.
    7. Enter the correct password for the sa user.
    8. Select the default Prolog Manager database and click Next.
    9. Choose Use default settings and click Next.
    10. Click Finish.
    11. Enter the correct password for the sa user choosing to Save user name and Save password.
    12. Click OK.
    13. Enter valid credentials for Sage 300 CRE choosing to Save user name and Save password.
    14. Click OK.
    15. Locate the Event 1 System Folder used by Integrator.
      1. Start Integrator.
      2. Dismiss any windows that may pop up.
      3. Click Help, then Environment, then View Environment Report.
      4. The Event 1 System Folder is listed under the Event 1 section with the label System Folder.
    16. Open the Event 1 System Folder path determined in the previous step.
    17. Open the Connections directory.
    18. Right-click the file named Test Connection.eic and click Open.
    19. Select Notepad as the program to be used to open the file.
    20. Document the hashed password for the sa listed under the [SQL Server] section.
  • Edit Live Connection File(s):
    1. Open the Event 1 System Folder path.
    2. Open the Connections directory.
    3. For each file ending in extension .eic (except the test connection created in the previous steps:
      1. Right-click the file and click Open.
      2. If prompted, select Notepad as the program to be used to open the file.
      3. Update ServerName and Password under the [SQLServer] section to reference the new SQL Server Instance and hashed password for the sa user documented from the previous steps.
      4. Save changes and close the file.