View source for Migrating Supportworks Data from One Database Server to Another

{{Template:Basic Cover
|title=Migrating Supportworks Data from One Database Server to Another
|type=FAQ
|htl=Y
}}

{{Template:Basic Status
|status=Published
|version=2.0
|authors=HTL QA
|applicableto=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:

# On the computer(s) where your current Supportworks server and database  server reside, perform a backup of both the system and the data.
# Ensure that all Supportworks clients remain logged off, and that SwServerService is stopped.
# 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.
# 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:
## Click Add, highlight SQL Server (which is the MS SQL driver) on the list and click Finish.
## 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.
## From the drop-down list in the Server field, select the name of the computer where the MS SQL server is located.
## Click Next.
## Select the "With SQL Server authentication..." option and ensure that  the checkbox option relating to default settings is enabled.
## 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.
## Click Next.
## 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.
## Click Next.
## Click Finish.
## In the ODBC Microsoft SQL Server Setup dialogue box, click the Test  Data Source button to confirm successful connectivity to the MS SQL  database.
## Click OK and OK again.
# 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:
## 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.
## 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.
## Click Next.
## 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.
## 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.
## Click Next.
## 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.
## Click Finish.
##:While the migration is running, a window is displayed showing the progress for each table in turn.
# Once the migration process has completed, run the Supportworks Server Configuration utility and select the Database tab.
# Ensure that the DSN field setting now points to the destination database.
# 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.