Migrating Supportworks Data from One Database Server to Another

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

This FAQ should only be followed by a competent Supportworks Administrator.

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 SystemDSN 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.

Performing data migration using the Supportworks ODBC Data Import wizard

1. Open the folder whose path on a default Supportworks installation would be C:\Program Files\Hornbill\Supportworks Server\bin
2. Locate and double-click the file swsqldbmig.exe.
3. On the Source DSN page, identify the source database by specifying its System DSN
4. Enter the credentials required to connect to it..
5. Click Next.
6. On the Destination DSN page, identify the destination database by specifying its System DSN
7. Enter the credentials required to connect to it.
8. Click Next
9. On the Import Process Options page, select the "Drop selected tables..." checkbox.
10. Click Next.
11. On the Tables Selection page use the Add All button to specify the tables to be migrated.
12. Click Finish.

While the migration is running, a window displays the progress for each table in turn.

Do Not continue until the migration process has completed, this may take some time dependant on the size of the database.

Post Migration Steps

1. Open the Server Configuration Console
2. Select the Database tab
3. Change the DSN under SQL Database Connection section, to the New DSN created earlier.
4. Enter the User ID and Password required to access the database
5. Restart the SwServerService on the live system, which will then begin using the new database.
6. For completness the old database DSN should be removed and the database dropped if no longer required.

The clients can now be logged seamlessly on to the live system.