Database Migration using Migration Wizard: Difference between revisions

From support-works
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 41: Line 41:
# Ensure that all Supportworks clients remain logged off, and that SwServerService is stopped.
# 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.
# 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 SystemDSN for the SwSQL database, for example, would be "Supportworks Data". If you need to create a
# 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:
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.
  1.  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, bear in mind that it may be the live database
  2.  In the next dialogue box, name the data source appropriately, bear in mind that it may be the live database
  # From the Server drop-down list, select the name of the MS SQL server.
  3.  From the Server drop-down list, select the name of the MS SQL server.
  # Click Next.
  4.  Click Next.
  # Select the "With SQL Server authentication..." option ensure that the checkbox option relating to default settings is enabled.
  5.  Select the "With SQL Server authentication..." option ensure that the checkbox option relating to default settings is enabled.
  # Enter a valid login ID and a password (if necessary) for access to the MS SQL database.
  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.
The database administrator should be able to provide this.
  # Click Next.
  7.  Click Next.
  # Select the checkbox option "Change the default database to"
  8.  Select the checkbox option "Change the default database to"
  # Select the name of the MS SQL blank database created earlier.
  9.  Select the name of the MS SQL blank database created earlier.
  # Click Next.
  10. Click Next.
  # Click Finish.
  11. Click Finish.
  # Click the Test Data Source button to confirm successful connectivity to the MS SQL database.
  12. Click the Test Data Source button to confirm successful connectivity to the MS SQL database.
  # Click OK, OK and OK again.
  13. Click OK, OK and OK again.


=== Configure ODBC connectivity ===
=== Performing data migration using the Supportworks ODBC Data Import wizard===
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
# Open the folder whose path on a default Supportworks installation would be C:\Program Files\Hornbill\Supportworks Server\bin
administrator tool from the Windows Control Panel and display the System DSN tab.
# Locate and double-click the file swsqldbmig.exe.
3. Ensure that both the source and the destination databases have a System DSN entry. The System
# On the Source DSN page, identify the source database by specifying its System DSN
DSN for the SwSQL database, for example, would be "Supportworks Data". If you need to create a
# Enter the credentials required to connect to it..
new System DSN, then do so now. For example, the procedure for an MS SQL System DSN would
# Click Next.
be:
# On the Destination DSN page, identify the destination database by specifying its System DSN
1. Click Add, highlight SQL Server (which is the MS SQL driver) on the list and click Finish.
# Enter the credentials required to connect to it.
2. In the next dialogue box, name the data source appropriately, bear in mind that it may be the
# Click Next
live database
# On the Import Process Options page, select the "Drop selected tables..." checkbox.
3. From the Server drop-down list, select the name of the MS SQL server.
# Click Next.
4. Click Next.
# On the Tables Selection page use the Add All button to specify the tables to be migrated.
5. Select the "With SQL Server authentication..." option
# Click Finish.
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.
While the migration is running, a window displays the progress for each table in turn.
The database administrator should be able to provide this.
 
7. Click Next.
Do Not continue until the migration process has completed, this may take some time dependant on
8. Select the checkbox option "Change the default database to"
the size of the database.
9. Select the name of the MS SQL blank database created earlier.
 
10. Click Next.
=== Post Migration Steps ===
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 ===
# Open the Server Configuration Console
# From the Start Window open a command window ('''cmd.exe''')
# Select the Database tab
# Enter the following statements:
# Change the DSN under SQL Database Connection section, to the New DSN created earlier.
## '''cd <<ServerIntsallPath>>\Core Services\swhttpserver\bin\'''
# Enter the User ID and Password required to access the database
## '''php.exe < swrepairall.php'''
# Restart the SwServerService on the live system, which will then begin using the new database.
# For completness the old database DSN should be removed and the database dropped if no longer required.


Once completed all tables will be free from error and you can restart all the remaining services via the Supportworks server configuration tool
The clients can now be logged seamlessly on to the live system.

Latest revision as of 16:57, 5 May 2015



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

Retrieved from ‘https://wiki.support-works.com/index.php?title=Database_Migration_using_Migration_Wizard&oldid=275’