Migrating Replication Agent for Microsoft SQL Server When Upgrading SQL Server Database

Migrate Replication Agent for Microsoft SQL Server when you upgrade SQL Server database from 2008 to 2008R2 version.

1. To prevent loss of any replicated data, deny users—other than existing Replication Agent pds_username users—access to the primary database.
   See the Microsoft SQL Server documentation.
2. Log in to the Replication Agent for Microsoft SQL Server 15.7.1 SP120 instance, verify that it is in the Replicating state, and allow replication to finish.

   To verify that replication has completed:

   1. Periodically issue the ra_statistics command, until all of these statistics are 0 (zero):

      • Input queue size

      • Output queue size

   2. Note the Last QID Sent from the last set of statistics.
   3. Issue the ra_locator update command so that Replication Agent retrieves the truncation point from Replication Server.
   4. Issue the ra_locator command again and compare the displayed locator with that of the Last QID Sent. If they are different, wait and repeat this step.
   5. Quiesce the Replication Agent instance by issuing the quiesce command.
   6. Shut down the Replication instance by issuing the shutdown command.
3. Upgrade Microsoft SQL Server database from 2008 to 2008R2 version, following the steps in the Microsoft SQL Server documentation.
4. Start the primary database in single-user mode:

   "C:\Program Files\Microsoft SQL Server\MSSQL<version>\MSSQL\Binn\sqlservr.exe” -m -s instanceName

   where:

   • version – is the version number of the database.
   • instanceName – is the name of the Microsoft SQL Server instance.
5. Start the Replication Agent instance in one of these ways:
   • Invoke the RUN script for the instance that you want to start.
   • Invoke ra, and specify the instance that you want to start.
   • If Replication Agent is installed on a Windows system, start a Windows service for the instance that you want to start.

   The RUN script and the ra utilities are batch files on Microsoft Windows.

   See Replication Agent Start-Up in the Replication Server Administration Guide to start the instance on your platform.

6. Initialize Replication Agent system database objects by issuing server_admin init.
7. Restart the primary Microsoft SQL Server database in multiuser mode:
   1. From Windows Control Panel, go to Administrative Tools > Services, and find the service named SQL Server (SERVER).

      where SERVER is the name of your Microsoft SQL Server data server.

   2. Start this service.
8. Migrate the Replication Agent metadata by issuing ra_migrate.
9. In the Replication Agent instance, resume replication by issuing resume.
10. Allow all users to access the primary database.