+

Search Tips   |   Advanced Search

Migrate SQL Server-based content side-by-side

Transfer SQL Server-based data from the Connections 4.0/4.5 databases to the new 4.0/4.5 databases as part of migrating to v5.0.

Transfer data from the Connections 4.0/4.5 databases, described here as the source databases, to the new 4.0/4.5 databases, described here as the target databases. When the data transfer is complete and you validate the new databases, we can update them to v5.0. References to 4.5 throughout this topic also include 4.0 since Connections 5.0 supports migrating any version from 4.0 or 4.5 to v5.0 directly.

We can continue to use the 4.0 or 4.5 databases until we are ready to move to Connections 5.0.

Important: Although we can continue to use the 4.0/4.5 databases until we are ready to move to Connections 5.0, any data that you generate after the database update is not migrated to the new environment.

Run the predbxferxx.sql and postdbxferxx.sql commands in the 5.0 GA build and not the 4.5 build. Depending on which database version we are migrating from, xx might be:

To update the databases:

  1. Use the Connections 4.5 database wizard, create target 4.5 databases on a separate system from the 4.5 source databases. The new databases host the data for migration to the 5.0 deployment.

    If the 4.5 database wizard is not on the system hosting the target databases, copy it from the system hosting IBM Connections 4.5.

    The Connections 4.5 Database wizard cannot be used to create target databases for Microsoft SQL 2012 EE since Microsoft SQL 2012 is not supported until Connections 5.0. Instead, use sql commands to create the 4.5 target databases using the steps described in Create SQL Server databases manually.

  2. Bring the 4.5 target databases up to the level of the Cumulative Refresh (CR) updates that have been made to the 4.5 source server as described in Updating 4.0 and 4.5 SQL Server databases manually.

  3. Prepare the target 4.5 databases to accept data from the source 4.5 databases. Remove constraints from the target databases by executing the following SQL scripts:

    • Run these SQL scripts before transferring 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 are already on the target database server:

    Repeat the following procedures for each application that we are migrating:

    1. Log in as the database administrator.

    2. For each application, change to the directory containing the relevant SQL file.

    3. Enter the commands shown in the following table:

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

      If the 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

      Run the predbxferxx.sql commands in the 5.0 GA build and not the 4.5 build, wherexx might be:

      • 4.0

      • 4.5

      • 4.5CR4

      Application Directory SQL Server commands
      Activities /connections.sql/activities/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Blogs /connections.sql/blogs/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Bookmarks /connections.sql/dogear/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Communities /connections.sql/communities/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"

      sqlcmd -U sa -P password -i "calendar-predbxferxx.sql"

      Files /connections.sql/files/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Forum /connections.sql/forum/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Home page /connections.sql/homepage/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Metrics /connections.sql/metrics/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Mobile /connections.sql/mobile/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Profiles /connections.sql/profiles/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"
      Wikis /connections.sql/wikis/sqlserver sqlcmd -U sa -P password -i "predbxferxx.sql"

  4. Use the Connections database transfer tool, transfer data to the target databases:

    1. Create a directory called DBT_HOME on the target database server.

      This directory temporarily stores transferred data.

    2. Use the new version of dbt.jar, and copy it from the connections_root\ConfigEngine\lib directory to the DBT_HOME directory on the target database server.

    3. Create an XML configuration file 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"/>
</dbTransfer>

      where

      JDBC_driver is following type:

      • SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver

      JDBC_url is the following type:

      • SQL Server: jdbc:sqlserver://host_IP:port;databaseName=application_database_name

      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
        • Dogear: DOGEAR
        • Files: FILES

        • Forum: FORUM
        • Home page: HOMEPAGE
        • Metrics: METRICS
        • Mobile: MOBILE
        • Profiles: PEOPLEDB
        • Wikis: WIKIS

      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

        To migrate Communities data, the dbt command needs to be run twice; once for the SNCOMM schema, and the second time for the CALENDAR schema.

      • Dogear: DOGEAR
      • Files: FILES

      • Forum: FORUM
      • Home page: HOMEPAGE
      • Metrics: METRICS
      • Mobile: MOBILE
      • Profiles: EMPINST
      • Wikis: WIKIS

      dbType is the following value:

      • SQL Server: sqlserver2005

        The JDBC driver, however, is SQL Server 2008 as indicated in the next step.

    4. Prepare the JDBC driver of the target databases:

      • Download the SQL Server 2008 JDBC 2.0 driver from the Microsoft website and follow the instructions to extract the driver files.

    5. To perform the data transfer, run the dbt.jar file:

      • Linux:

        "JAVA_HOME/bin/java"

        -cp DBT_HOME/dbt.jar:

        SQLSERVER_DRIVER_PATH

        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 running this file

        • SQLSERVER_DRIVER_PATH is the path to the sqljdbc4.jar JDBC driver.

      • Windows:

          "JAVA_HOME/bin/java"
          -cp DBT_HOME/dbt.jar;
          SQLSERVER_DRIVER_PATH
          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

      When the transfer is complete, we can restart the 4.5 deployment to minimize service downtime.

      Data generated after restarting the 4.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 containing the relevant SQL file.

    3. Enter the commands shown in the following table:

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

    If the 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

    Run the postdbxferxx.sql commands in the 5.0 GA build and not the 4.5 build, wherexx might be:

    • 4.0

    • 4.5

    • 4.5CR4

    Application Directory SQL Server commands
    Activities /connections.sql/activities/sqlserver

    sqlcmd -U sa -P password

    -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Blogs /connections.sql/blogs/sqlserver sqlcmd -U sa -P password -i "postdbxferxx.sql"
    Bookmarks /connections.sql/dogear/sqlserver sqlcmd -U sa -P password -i "postdbxferxx.sql"
    Communities /connections.sql/communities/sqlserver

    sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    sqlcmd -U sa -P password -i "calendar-postdbxfer45.sql"

    Files /connections.sql/files/sqlserver sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Forum /connections.sql/forum/sqlserver

    sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Home page /connections.sql/homepage/sqlserver

    sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Metrics /connections.sql/metrics/SQL Server

    sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Mobile Wizards/connections.sql/mobile/sqlserver

    sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Profiles /connections.sql/profiles/sqlserver sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

    Wikis /connections.sql/wikis/sqlserver sqlcmd -U sa -P password -i "postdbxferxx.sql"

    sqlcmd -U sa -P password -i "clearScheduler.sql"

  6. (Metrics only.) Update the database sequence for SQL Server target databases:

    • Run on the 4.5 source database:

      exec METRICS.GETNEWSEQVAL_ID_VALUES;

      Runs on the 4.5 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 4.5 database.


What to do next

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

Update the new databases to IBM Connections v5.0. See Update 5.0 databases topic.


Parent topic:
Migrate data side-by-side