Upgrade a replicated database

When upgrading the application, if you are using a replicated database and have replication turned on, you must turn it off, upgrade, and then turn it on again. You must also upgrade the replicated database.

Procedure

  1. Disable any scheduled synchronization jobs for the Reporting Database.
  2. Open the Services window to stop all services: click Start > Control Panel > Administrative Tools > Services.
  3. In Microsoft SQL Server Management Studio, generate the create and drop scripts:
    Note: For use later in this procedure, you must note the location of where you saved the scripts.
    1. In the Subscriber/Distributor instance, right-click the Replication folder, and then click Generate Scripts.
      The Generate SQL Script dialog box appears.
    2. To generate the Create script, in the Script the commands section, select To create or enable the components, and then click Generate Script.
    3. To generate the Drop script, in the Script the commands section, select To drop or disable the components, and then click Generate Script.
  4. To disable replication, execute the Drop script.
  5. Verify that all objects beneath the Microsoft SQL Server Management Studio Replication folder have been deleted.
  6. From the installation directory where your SQL Server is hosted, go to Program Files\Proficy\Proficy Workflow\Program, and then run ProficyInstaller to upgrade the ProficyServer programs and database.
  7. Edit the Create Replication script.
    1. In Microsoft SQL Server Management Studio, from the File menu, click File > Open File.
    2. Navigate to the folder where you saved the scripts generated in step 3.
    3. Select the Create SQL script.
    4. Locate the section denoted by this comment, /****** End: Script to be run at Publisher ******/, and then add the following lines after it.
      Note: You must change the name of the Distribution database and the user names to match your system.
      -- Create users in the Distribution database
USE [DistributionDB];
GO
CREATE USER [QASYSTESTSQLSRV\SqlDistAgent]
  FOR LOGIN [QASYSTESTSQLSRV\SqlDistAgent]
EXEC sp_addrolemember N'db_owner',
  N'QASYSTESTSQLSRV\SqlDistAgent';
CREATE USER [QASYSTESTSQLSRV\SqlLogReaderAgent]
  FOR LOGIN [QASYSTESTSQLSRV\SqlLogReaderAgent];
EXEC sp_addrolemember N'db_owner',
  N'QASYSTESTSQLSRV\SqlLogReaderAgent';
  CREATE USER [QASYSTESTSQLSRV\SqlSnapshotAgent]
  FOR LOGIN [QASYSTESTSQLSRV\SqlSnapshotAgent];
EXEC sp_addrolemember N'db_owner',
  N'QASYSTESTSQLSRV\SqlSnapshotAgent';
  8. Add all of the passwords back into the file. The Create Generation process does not write the user account passwords to the file.
    1. Find all occurrences of @job_password = null.
    2. Replace null with the log on password that precedes it. If the preceding @job_login is also null, do not edit the password so that it remains set to null.
      -- Before: 
exec [SystemTest2v2].sys.sp_addlogreader_agent @job_login = N'QASYSTESTSQLSRV\SqlLogReaderAgent', @job_password = null
-- After:
exec [SystemTest2v2].sys.sp_addlogreader_agent @job_login = N'QASYSTESTSQLSRV\SqlLogReaderAgent', @job_password = 'proficy'
 
-- Do not change passwwords for null job_logins
exec [SystemTest2v2].sys.sp_addqreader_agent @job_login = null, @job_password = null
  9. Execute the Create Replication script.
    Note: This script will have some errors because it will try to publish database objects that were removed from the database as part of the upgrade.
  10. Re-enable the replication, and then add the new database tables and objects to the publication in order to push them to the replicated database:
    1. Expand your database directory and the Replication folder, right-click Local Publications, and then select Properties.
    2. Select Articles, and then select all unchecked items to include them in the publication.
      Note: The subscription must be re-initialized to include the new articles. We recommend re-initializing all articles in the subscription using a full snapshot. For instructions, read the Microsoft Developer Network article, Reinitializing a Subscription
  11. After replication is working against the upgraded database, install the Reporting upgrade, and point it to the replicated database.
  12. Test reporting synchronization by running a manual Sync from the Workflow client.