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

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. Stop all the Supportworks and related services via Supportworks server configuration tool
2. Stop the SwSqlServer service:

   Using (Control panel->Administrative tools->Services

3. Back-up the files from the following location:

   <<ServerInstallPath>>\Core Services\swsqlserver\data\ (Where <<ServerInstallPath>> is the default installation folder)

4. Start the SwSqlServer service

Install Script

1. Ensure you have received the following .zip: swrepairall.zip
2. Unzip the script file to the following location:

   <<ServerInstallPath>>\Core Services\swhttpserver\bin\ (Where <<ServerInstallPath>> is the default installation folder)

Configure ODBC connectivity

1. Open the MS Windows ODBC32 Setup
2. Choose System DSN
3. Click New
4. Choose the Supportworks SQL driver
5. Enter the Following:

   Data Source Name - Supportworks Messagestore

   Host = IP Address of the SupprotWorks Server

   Database Name = sw_messagestore

   User = root

   Password =

   Port = 5002

6. Test Data Source

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