StarQuest Technical Documents

Migrating SQDR control database from SQL Server to DB2 LUW

Last Update: 21 November 2016
Product: SQDR
Version: 4.50 and later
Article ID: SQV00DR037

Abstract

SQDR 4.1x and earlier required the use of Microsoft SQL Server for the control database used by SQDR. SQDR 4.5 introduced the option to use IBM DB2 LUW (DB2 for Linux, UNIX, and Windows). Because SQDR Plus uses DB2 for its staging database, StarQuest now recommends the use of DB2 rather than SQL Server for typical scenarios, especially when SQDR and SQDR Plus are installed on the same machine.

This technical document describes the process of migrating an existing SQDR control database from SQL Server to DB2 by using SQDR snapshot replication. This examples below assumes that both SQL Server and DB2 are installed locally, but the same technique can be used when working with remote database instances.

It also assumes that the SQL Server control database has already been updated to the current SQDR version level (4.50 or later) - the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC\version should be 4.50 or later. If you are updating from an earlier version of SQDR, update SQDR and the SQL Server control database first. Similarly, if you are updating SQDR from 32-bit to 64-bit, do this upgrade first; see Upgrading to 64-bit SQDR for details.

Prerequisites:

If you are performing this migration on a system where SQDR Plus is also installed, skip this section, as these items have already been handled by the SQDR Plus installer. Otherwise:

• Install and license DB2 for LUW.
• Create a local Windows user sqdr. This user will be used for connecting to the local DB2 LUW database.
• Create a database named SQDRC; specify separate disks for the data and logs if desired for best performance:

DB2 CREATE DATABASE SQDRC ON D: DBPATH ON L:

• Use StarAdmin to bind StarSQL SQL packages in the STARSQL package collection in the SQDRC database.

Procedure:

• In Data Replictor Manager, pause all incremental groups.
• Run Data Replicator Configuration to create empty control tables (schema SQDR) in SQDRC. These tables will be the target of the snapshot replication from SQL Server.
• Create a local Windows user sqdrtemp (or some name other than sqdr).
• Use Data Replicator Configuration a second time to create empty control tables (schema SQDRTEMP) in SQDRC. We will use this controlDB for replicating the actual controlDB. Use the StarSQL (64-bit) connection string Server=SQDRC;HostName=127.0.0.1;Port=50000, connect with the userID sqdrtemp, and select the schema SQDRTEMP.
• Set up a snapshot replication from SQL Server to the DB2 LUW database SQDRC.
  • Source: SQL Server Native Client 11 -
    Server=sys/instance (e.g. ./SQLEXPRESS)
    user=sa or some other ID
    Destination: StarSQL (64-bit) - Server=SQDRC;HostName=127.0.0.1;Port=50000
    user=sqdr
  • Use the script ControlDB.txt to import the list of tables and the script ControlDBMaps.tsv to import the column definitions. The subscriptions should be designed to use existing tables, Append replicated rows to existing data, and be placed into a snapshot group with concurrency=1 and Halt on Errors Only.
  • Run the snapshot replication.
• After replicating the controlDB, create a backup of SQDRC and use Data Replicator Configuration to switch to the DB2 LUW control database (schema SQDR) that has just been replicated. Specify sqdr as the user.
• You should now see all the subscriptions and groups, and all the groups are paused. Resume the paused I/R groups..
• If you are satisfied with the results, you may now delete the local Windows user sqdrtemp, drop the sqdrtemp schema and its tables from the SQDRC database, drop the ControlDB database in SQL Server, and (if not needed for any other purposes) uninstall SQL Server.