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 on SQL Server to another.  This must be accompanied by the relocation of the Integrator database as well as updates to the Integrator connection file(s). 

The steps in this article assume a basic understanding of how to use SQL Enterprise Manager or SQL Management Studio.

Steps
Complete the following steps to migrate your Prolog Manager and Integrator databases from one SQL Server to another and to modify the Integrator connection files so that Integrator is able to connect to the databases on the new server.

Step 1 - Preparation

Before relocating the Prolog Manager databases, you should first remove the SDKUser account from the databases while they are still on the old server using the following steps.

1.  Start SQL Enterprise Manager or SQL Management Studio Express.

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 Schemas node.  If a Schema named SDKUser is listed, right-click on it and click Delete.

4.  Below the Security node, expand the Users node.  Right-click on the SDKUser item and click Delete.

Step 2 - Relocate Databases

1.  Relocate your Prolog Manager database from old server to new server.

2.  Relocate your Integrator database from old server to new server by making a backup of your database on the old server and then restoring it onto the new server.  These tasks can be accomplished using the Backup and Restore features of SQL Enterprise Manager or SQL Management Studio.

Step 3 - Edit Connection Files

1.  Locate the folder containing your Integrator connection files.  The location of these files will be dependent on the version of Integrator you are running.  For versions 2.82.0015 and prior, the connections folder resides under “C:Program FilesEvent 1Integrator” (assuming your Program Files folder is on drive C:).  For versions beyond 2.82.0015, the location of the Connections folder will be below the Event 1 System folder.  To identify the path of the Event 1 System folder, follow these steps:
  a)  Start Integrator
  b)  Dismiss any windows that come up
  c)  Click the Help menu and then click Environment and then View System Information
  d)  The system folder is listed under the Event 1 section with the label System Folder.  The connections folder will reside in the Integrator folder that is within the path shown here.

2.  For each connection file shown in the Connections folder, follow these steps
  a)  Right-click on the connection file and click Open.
  b)  Select Notepad as the program to be used to open the file.
  c)  Edit the entry for ServerName below the SQLServer section so that it reflects the new SQL Server name.
  d)  Save the file.

Step 4 - Test Connections

1.  Start Integrator
2.  For each connection that was affected, open the connection to confirm that Integrator is able to connect to the Prolog Manager and Sage 300 Construction and Real Estate databases as expected.