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
- 50
- 50CRx
- 55
- 55CRx
To update the databases, complete the following steps:
Procedure
- Create target databases on a separate system from your source databases. The new databases host your data for migration to the 6.0 deployment.
- 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.
-
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:- Log in as the database administrator.
- For each application, change to the directory that contains the relevant SQL file.
- 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"
- Using the IBM® Connections
database transfer tool, transfer data to the target databases:
- Reapply constraints to the target databases:
- Log in as the database administrator.
- For each application, change to the directory that contains the relevant SQL file.
- Enter the commands that are shown in the following table:
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"
-
(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.
-
- 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.