Migrating Supportworks Data from One Database Server to Another

Status:	Published
Version:	2.0
Authors:	HTL QA
Applies to:	All Supportworks ESP versions

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, MS SQL). If you now wish to change to another database server (for example, SwSQL), the easiest way to migrate all your managed data from the old database server to the new one is by using an appropriate migration tool. For migration from a third-party database to SwSQL, this would be the Supportworks ODBC Data Import wizard (not to be confused with the Data Import Manager). For migration from SwSQL to a third-party database, it would be the migration utility that comes with that database. 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.

Warning: Do not attempt to use the Supportworks ODBC Data Import wizard for migrating data from SwSQL to a third-party database, as in that case, the primary keys may not be copied over.

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-migration 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:

1. On the computer(s) where your current Supportworks server and database server reside, perform a backup of both the system and the data.
2. Ensure that all Supportworks clients remain logged off, and that SwServerService is stopped.
3. 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.
4. 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, bearing in mind whether or not this points to what will ultimately be the live database.
   3. From the drop-down list in the Server field, select the name of the computer where the MS SQL server is located.
   4. Click Next.
   5. Select the "With SQL Server authentication..." option and ensure that the checkbox option relating to default settings is enabled.
   6. Enter a valid login ID and a password (if necessary) for connecting to the MS SQL database. You will have obtained these from the database administrator.
   7. Click Next.
   8. Select the checkbox option "Change the default database to" and, from the associated drop-down list, select the name of the MS SQL database that Supportworks has been using (or will be using) to hold managed data.
   9. Click Next.
   10. Click Finish.
   11. In the ODBC Microsoft SQL Server Setup dialogue box, click the Test Data Source button to confirm successful connectivity to the MS SQL database.
   12. Click OK and OK again.
5. You will now perform the actual data migration using the relevant tool. If you are migrating to a third-party database, then follow the instructions provided with that database's migration tool. If you are migrating to SwSQL, then follow the instructions given here:
   1. Firstly, open the folder whose path on a default Supportworks installation would be C:\Program Files\Hornbill\Supportworks Server\bin and double-click the file swsqldbmig.exe. This displays the Supportworks ODBC Data Import wizard.
   2. On the Source DSN Information page of the wizard, identify the source database by specifying its System DSN and the credentials required to log into it.
   3. Click Next.
   4. On the Destination DSN Information page of the wizard, identify the destination database by specifying its System DSN and the credentials required to log into it.
   5. On the Import Process Options page of the wizard, if you wish to discard, before migration, the records that may currently exist in the destination database tables into which data is to be migrated, select the "Drop selected tables..." checkbox.
   6. Click Next.
   7. On the Tables Selection page of the wizard, use the Add or Add All button to specify the tables, in the source database, from which data is to be migrated.
   8. Click Finish.

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

6. Once the migration process has completed, run the Supportworks Server Configuration utility and select the Database tab.
7. Ensure that the DSN field setting now points to the destination database.
8. Restart SwServerService on the live system, which will then begin using the new database.

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