Database Migration using Migration Wizard

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	Supportworks Version 7.5.0 onwards

Migrating Supportworks Data from One Database Server to Another

When you installed your Supportworks system, you will have configured it to use a particular database server (for example, SwSQL). If you now wish to change to another database server (for example, MS SQL), the easiest way to migrate all your managed data from the old database server to the new one is by using the Supportworks ODBC Data Import wizard (not to be confused with the Data Import Manager). Note that "managed" data would not include data held in the Supportworks system databases (whose names begin with "sw_"), since these can only belong to an SwSQL server. It should also be noted that no index's will be migrated along with the loss of primary key information, of which will need to be applied manually.

Before carrying out a data migration, you must ensure that an appropriate ODBC System data source (DSN) exists in Windows for each of the two database servers, so that the data-import tool can connect to them. Each DSN would have the managed database defined as the default database to which to connect. It is convenient if the data source pointing at the destination database server will also be able subsequently to be used by Supportworks (if it is not already).

If you intend to migrate your entire Supportworks system to another computer at the same time, you will initially have to carry out a full (clean) installation of Supportworks on that machine, with the new database specified as the one to use. You would then need to stop the services and copy all the relevant folders except \data across to the new machine. See the FAQ entitled "Migrating or Cloning the Supportworks Server" for details. The procedure for migrating just your data is as follows:

Pre-migration steps

1. Ensure that a blank database has been created on the destination database server (recommended name is swdata)
2. On the computer(s) where your current Supportworks server and database server reside, perform a backup of both the system and the data.

Configuring database DSNs

1. Ensure that all Supportworks clients remain logged off, and that SwServerService is stopped. 2. On the computer that has the Supportworks system installed, run the ODBC data source administrator tool from the Windows Control Panel and display the System DSN tab. 3. Ensure that both the source and the destination databases have a System DSN entry. The System DSN for the SwSQL database, for example, would be "Supportworks Data". If you need to create a new System DSN, then do so now. For example, the procedure for an MS SQL System DSN would be: 1. Click Add, highlight SQL Server (which is the MS SQL driver) on the list and click Finish. 2. In the next dialogue box, name the data source appropriately, bear in mind that it may be the live database 3. From the Server drop-down list, select the name of the MS SQL server. 4. Click Next. 5. Select the "With SQL Server authentication..." option ensure that the checkbox option relating to default settings is enabled. 6. Enter a valid login ID and a password (if necessary) for access to the MS SQL database. The database administrator should be able to provide this. 7. Click Next. 8. Select the checkbox option "Change the default database to" 9. Select the name of the MS SQL blank database created earlier. 10. Click Next. 11. Click Finish. 12. Click the Test Data Source button to confirm successful connectivity to the MS SQL database. 13. Click OK, OK and OK again.

Configure ODBC connectivity

1. Ensure that all Supportworks clients remain logged off, and that SwServerService is stopped. 2. On the computer that has the Supportworks system installed, run the ODBC data source administrator tool from the Windows Control Panel and display the System DSN tab. 3. Ensure that both the source and the destination databases have a System DSN entry. The System DSN for the SwSQL database, for example, would be "Supportworks Data". If you need to create a new System DSN, then do so now. For example, the procedure for an MS SQL System DSN would be: 1. Click Add, highlight SQL Server (which is the MS SQL driver) on the list and click Finish. 2. In the next dialogue box, name the data source appropriately, bear in mind that it may be the live database 3. From the Server drop-down list, select the name of the MS SQL server. 4. Click Next. 5. Select the "With SQL Server authentication..." option ensure that the checkbox option relating to default settings is enabled. 6. Enter a valid login ID and a password (if necessary) for access to the MS SQL database. The database administrator should be able to provide this. 7. Click Next. 8. Select the checkbox option "Change the default database to" 9. Select the name of the MS SQL blank database created earlier. 10. Click Next. 11. Click Finish. 12. Click the Test Data Source button to confirm successful connectivity to the MS SQL database. 13. Click OK, OK and OK again.

Executing the Script

1. From the Start Window open a command window (cmd.exe)
2. Enter the following statements:
   1. cd <<ServerIntsallPath>>\Core Services\swhttpserver\bin\
   2. php.exe < swrepairall.php

Once completed all tables will be free from error and you can restart all the remaining services via the Supportworks server configuration tool