Migrating SQL Server content side-by-side

Transfer SQL Server data from your IBM® Connections 5.0 or 5.5 databases to new 5.0 or 5.5 databases as part of migrating to version 6.0.

About this task

Transfer data from your IBM® Connections 5.0 or 5.5 databases, described here as the source databases, to the new 5.0 or 5.5 databases, described here as the target databases. When the data transfer is complete and you validate the new databases, you can update them to version 6.0.
Important: You can continue to use your 5.0 or 5.5 databases until you are ready to move to IBM® Connections 6.0. Any data that you generate after the database update is not migrated to the new environment.
Note: Run the predbxferxx.sql and postdbxferxx.sql commands that are in the 6.0 GA build and not the 5.0 or 5.5 build. Depending on which database version you are migrating from, xx might be:
  • 50
  • 50CRx
  • 55
  • 55CRx

To update the databases, complete the following steps:

Procedure

  1. Create target databases on a separate system from your source databases. The new databases host your data for migration to the 6.0 deployment.
  2. Bring the target databases up to the latest Cumulative Refresh (CR) level for the code stream as described in the IBM® Connections 5.5 Knowledge Center topic: Updating SQL Server databases manually.
  3. Prepare the target databases to accept data from the source databases. Remove constraints from the target databases by running the following SQL scripts from the IBM® Connections 6.0 package:
    Notes:
    • Run these SQL scripts before you transfer data to the target database.
    • Run each script from the same directory that you use to create the target database.
    • IBM® Connections uses the sqljdbc4.jar database libraries, which is used with Java 8. Java 8 is included with the 6.0 database wizard.
    Repeat the following procedures for each application that you are migrating:
    1. Log in as the database administrator.
    2. For each application, change to the directory that contains the relevant SQL file.
    3. Enter the commands that are shown in the following table:

      In these commands, password is the password for the SQL Server user sa.

      Note:

      If your database server has multiple SQL Server instances, add the following line as the first parameter to each command in the table: -S sqlserver_server_name\sqlserver_server_instance_name

      Note: Run the predbxferxx.sql commands that are in the 6.0 GA build and not the 5.0 or 5.5 build, wherexx might be:
      • 50
      • 50CRx
      • 55
      • 55CRx
      Table 1. SQL Server commands for removing constraints
      Application and Directory SQL Server commands
      Activities: /connections.sql/activities/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Blogs: /connections.sql/blogs/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Bookmarks: /connections.sql/dogear/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Communities: /connections.sql/communities/sqlserver If you are at:
      • 5.0 run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.0 CR2 run sqlcmd -U sa -P password -i "predbxfer50CR2.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Communities - calendar: /connections.sql/communities/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "calendar-predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "calendar-predbxfer55.sql"
      Files: /connections.sql/files/sqlserver
      If you are at:
      • 5.5, 5.5 CR1, 5.5 CR2, or 5.5 CR3 run sqlcmd -U sa -P password -i "predbxfer55.sql"
      • 5.5 CR4 or 5.5 CR5 run sqlcmd -U sa -P password -i "predbxfer55CR4.sql"
      Forums: /connections.sql/forum/sqlserver
      If you are at:
      • 5.0 run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.0 CR1 or above run sqlcmd -U sa -P password -i "predbxfer50CR1.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Homepage: /connections.sql/homepage/sqlserver If you are at:
      • 5.0 run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.0 CR1 run sqlcmd -U sa -P password -i "predbxfer50CR1.sql"
      • 5.0 CR2 run sqlcmd -U sa -P password -i "predbxfer50CR2.sql"
      • 5.0 CR3 run sqlcmd -U sa -P password -i "predbxfer50CR3.sql"
      • 5.0 CR4 run sqlcmd -U sa -P password -i "predbxfer50CR4.sql"
      • 5.5 run sqlcmd -U sa -P password -i "predbxfer55.sql"
      • 5.5 CR1 run sqlcmd -U sa -P password -i "predbxfer55CR1.sql"
      • 5.5 CR2 run sqlcmd -U sa -P password -i "predbxfer55CR2.sql"
      Metrics: /connections.sql/metrics/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Mobile: /connections.sql/mobile/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Profiles: /connections.sql/profiles/sqlserver If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
      Wikis: /connections.sql/wikis/sqlserver
      If you are at:
      • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "predbxfer50.sql"
      • 5.5 or 5.5 CR1 run sqlcmd -U sa -P password -i "predbxfer55.sql"
      • 5.5 CR2 or above run sqlcmd -U sa -P password -i "predbxfer55CR2.sql"
      Push notification: /connections.sql/pushnotification/sqlserver If you are at 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "predbxfer55.sql"
  4. Using the IBM® Connections database transfer tool, transfer data to the target databases:
    1. Create the directory DBT_HOME on the target database server. This directory temporarily stores transferred data.
    2. Be sure to use the new version of the dbt.jar file and copy it from the connections_root\ConfigEngine\lib directory to the DBT_HOME directory on the target database server.
      Note: You must ensure the server is installed so that the dbt.jar file is in place before migrating the database.
    3. Create an XML configuration file for each component database to be transferred under the DBT_HOME directory and add the following content:
      <dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database role="source" 
driver="JDBC_driver" 
url="JDBC_url" 
userId="database_admin
schema="application_db_schema_name"
dbType="dbType"/>
<database role="target"
driver="JDBC_driver"
url="JDBC_url"
userId="database_admin" 
schema="application_db_schema_name" 
dbType="dbType"/>
<table sourceName="TABLE_TO_SKIP" exclude="true"/> <!-- optional argument used to have dbtransfer skip a table -->
</dbTransfer>
      Note: Any <table sourceName="TABLE_TO_SKIP" exclude="true entries go directly before </dbTransfer>.

      Where

      JDBC_driver is following type:
      • com.microsoft.sqlserver.jdbc.SQLServerDriver
      JDBC_url is the following type:
      • jdbc:sqlserver://host_IP:port;databaseName=application_database_name
      Note: Where
      • host_IP is the IP address of the database server.
      • port is the port number of the server.
      • application_database_name is one of the following values:
        • Activities: OPNACT
        • Blogs: BLOGS
        • Communities: SNCOMM
        • Bookmarks: DOGEAR
        • Files: FILES
        • Forum: FORUM
        • Home page: HOMEPAGE
        • Metrics: METRICS
        • Mobile: MOBILE
        • Profiles: PEOPLEDB
        • Wikis: WIKIS
        • Push notification: PNS
          Note: For Connections 5 the push notification database name is FILES, for Connections 6 the push notification database name is PNS.

      database_admin is the user ID of the database administrator.

      application_db_schema_name is one of the following values:
      • Activities: ACTIVITIES
      • Blogs: BLOGS
      • Communities: SNCOMM and CALENDAR
        Note: To migrate Communities data, run the dbt command twice; the first time for the SNCOMM schema, and the second time for the CALENDAR schema.
      • Bookmarks: DOGEAR
      • Files: FILES
      • Forum: FORUM
      • Home page: HOMEPAGE
      • Metrics: METRICS
      • Mobile: MOBILE
      • Profiles: EMPINST
        Note: If you use the TDI-related table EMP_UPDATE_TIMESTAMP, it does not need to be transferred. Add the instruction: <table sourceName="EMP_UPDATE_TIMESTAMP" exclude="true"/>.
      • Wikis: WIKIS
      • Push notification: PNS
      dbType is the following value:
      • SQL Server: sqlserver2005
        Note: The JDBC driver, however, is SQL Server 2008 as indicated in the next step.
      The optional entry <table sourceName="TABLE_TO_SKIP" exclude="true"/> instructs dbtransfer to skip a named table.
      Note: take the following into consideration when using "TABLE_TO_SKIP"
      • Skip those tables that transferred fully.
      • If you get an error when transferring a table, then clear the table's contents on the target, do not skip it and run dbTransfer again.
      • If you need to re-run dbTransfer, the tables might execute in a different order.
    4. Prepare the JDBC driver of the target databases:
      • Download the Microsoft JDBC Drivers 6.0 driver from the Microsoft™ website and follow the instructions to extract the driver files.
    5. To transfer the data, run the dbt.jar file. Do not add spaces in a series of file names:
      • Linux™:

        JAVA_HOME/bin/java

        -cp DBT_HOME/dbt.jar:

        SQLSERVER_DRIVER_PATH/sqljdbc4.jar

        com.ibm.wps.config.db.transfer.CmdLineTransfer

        -logDir DBT_HOME/logs

        -xmlfile DBT_HOME/dbt_config_file_name

        -sourcepassword source_db_password

        -targetpassword target_db_password

        Where:
        • JAVA_HOME is the path to the Java™ JDK.
        • dbt_config_file_name is the name of the XML configuration file you created for the dbt.jar file
        • logs is the directory where log files are stored. Create the logs directory before you run this file
        • SQLSERVER_DRIVER_PATH is the path to the sqljdbc4.jar JDBC driver.
        Run the command on a single line; do not add extra spaces. For example, multiple file names are entered as filename.jar:filename.jar without a space between the file names. The command looks similar to the following when run as a single command (edited to your specific configuration):
        JAVA_HOME/bin/java -cp DBT_HOME/dbt.jar:SQLSERVER_DRIVER_PATH/sqljdbc4.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir DBT_HOME/logs -xmlfile DBT_HOME/dbt_config_file_name -sourcepassword password -targetpassword password
      • Windows™:

        JAVA_HOME\bin\java

        -cp DBT_HOME\dbt.jar;

        SQLSERVER_DRIVER_PATH\sqljdbc4.jar

        com.ibm.wps.config.db.transfer.CmdLineTransfer

        -logDir DBT_HOME\logs

        -xmlfile DBT_HOME\dbt_config_file_name

        -sourcepassword source_db_password

        -targetpassword target_db_password

      Run the command on a single line; do not add extra spaces. For example, multiple file names are entered as filename.jar;filename.jar without a space between the file names. The command looks similar to the following when run as a single command (edited to your specific configuration):
      JAVA_HOME\bin\java -cp c:\DBT_HOME\dbt.jar;SQLSERVER_DRIVER_PATH\sqljdbc4.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir c:\DBT_HOME\logs -xmlfile c:\DBT_HOME\dbt_config_file_name -sourcepassword password -targetpassword password

      When the transfer is complete, you can restart your 5.0 or 5.5 deployment to minimize service downtime.

      Note: Data that is generated after you restart the 4.0 or 5.5 environment is not migrated.
  5. Reapply constraints to the target databases:
    1. Log in as the database administrator.
    2. For each application, change to the directory that contains the relevant SQL file.
    3. Enter the commands that are shown in the following table:
    In these commands, password is the password for the SQL Server user sa.
    Note: If your database server has multiple SQL Server instances, add the following line as the first parameter to each command in the table:
    -S sqlserver_server_name\sqlserver_server_instance_name
    Note: Run the postdbxferxx.sql commands that are in the 6.0 GA build and not the 5.0 or 5.5 build, wherexx might be:
    • 50
    • 50CRx
    • 55
    • 55CRx
    Table 2. SQL Server commands for reapplying constraints
    Application and Directory SQL Server commands
    Activities: /connections.sql/activities/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Blogs: /connections.sql/blogs/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    Bookmarks: /connections.sql/dogear/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    Communities: /connections.sql/communities/sqlserver If you are at:
    • 5.0 run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.0 CR2 run sqlcmd -U sa -P password -i "postdbxfer50CR2.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Communities - calendar: /connections.sql/communities/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "calendar-postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "calendar-postdbxfer55.sql"
    Files: /connections.sql/files/sqlserver
    If you are at:
    • 5.5, 5.5 CR1, 5.5 CR2, or 5.5 CR3 run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • 5.5 CR4 or 5.5 CR5 run sqlcmd -U sa -P password -i "postdbxfer55CR4.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Forums: /connections.sql/forum/sqlserver
    If you are at:
    • 5.0 run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.0 CR1 or above run sqlcmd -U sa -P password -i "postdbxfer50CR1.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Homepage: /connections.sql/homepage/sqlserver If you are at:
    • 5.0 run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.0 CR1 run sqlcmd -U sa -P password -i "postdbxfer50CR1.sql"
    • 5.0 CR2 run sqlcmd -U sa -P password -i "postdbxfer50CR2.sql"
    • 5.0 CR3 run sqlcmd -U sa -P password -i "postdbxfer50CR3.sql"
    • 5.0 CR4 run sqlcmd -U sa -P password -i "postdbxfer50CR4.sql"
    • 5.5 run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • 5.5 CR1 run sqlcmd -U sa -P password -i "postdbxfer55CR1.sql"
    • 5.5 CR2 run sqlcmd -U sa -P password -i "postdbxfer55CR2.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Metrics: /connections.sql/metrics/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Mobile: /connections.sql/mobile/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    Profiles: /connections.sql/profiles/sqlserver If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Wikis: /connections.sql/wikis/sqlserver
    If you are at:
    • 5.0 or a 5.0 CR run sqlcmd -U sa -P password -i "postdbxfer50.sql"
    • 5.5 or 5.5 CR1 run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • 5.5 CR2 or above run sqlcmd -U sa -P password -i "postdbxfer55CR2.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
    Push notification: /connections.sql/pushnotification/sqlserver
    • If you are at 5.5 or a 5.5 CR run sqlcmd -U sa -P password -i "postdbxfer55.sql"
    • Run sqlcmd -U sa -P password -i "clearScheduler.sql"
  6. (Metrics only) Run the following commands to update the database sequence for SQL Server target databases:

    • Run the following command on the source database:

      exec METRICS.GETNEWSEQVAL_ID_VALUES;

      Run the following commands on the target database:

      DROP TABLE [METRICS].[ID_VALUES];

      CREATE TABLE [METRICS].[ID_VALUES]

      (

      [SEQID] [BIGINT] IDENTITY(query_result,1) NOT NULL,

      [SEQVAL] [VARCHAR](1) NULL,

      );

      ALTER TABLE [METRICS].[ID_VALUES] ADD CONSTRAINT [ID_VALUES_PK] PRIMARY KEY ([SEQID]);

      GRANT DELETE,INSERT,SELECT,UPDATE ON "METRICS"."ID_VALUES" TO METRICSUSER;

      Where query_result is the result of the corresponding EXEC command that you ran on the 5.0 or 5.5 database.

  7. Manually, or using the DB Wizard, update the new databases to bring them to the IBM® Connections version 6.0 level. For more information, see Migrating 5.0 and 5.5 databases to 6.0 and Updating 5.0 and 5.5 databases with the wizard.

Results

Check that all the databases are working correctly. If you find errors, resolve the problem and repeat this task.