Applying Microsoft SQL Server patches

Add the following to the Replication Agent Primary Database Guide, Chapter 1, “Replication Agent for Microsoft SQL Server.”

The following is the procedure for applying Microsoft SQL Server patches.

1. Before applying a patch, be sure that all data has been replicated to the replicate site.

   All activities must stop before this step, and all users except the pds_user should log off from the primary database.

   For each existing Replication Agent for Microsoft SQL Server instance, verify that it is in Replicating state and allow replication to finish. To verify that replication has finished, quiesce the Replication Agent instance by issuing the quiesce command.

   It may take a while for the command to return because Replication Agent reads all data from the log file and sends it to the Replication Server.

2. Before applying the service patch disable the database triggers.

   If automark table is enabled, log on to the primary database and disable the automark trigger by issuing:

   DISABLE TRIGGER ra_createtable_trig_ ON DATABASE
   

   where ra_createtable_trig_ is the name of the automark trigger created by Replication Agent.

3. Apply the service patch using the instructions in the Microsoft documentation.

4. Regenerate the objects in the Microsoft SQL Server system resource database.

   • Restart Microsoft SQL Server in single user mode by opening a new command window and executing this command:

     "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -m 
     -sserverName\instanceName
     

     where instanceName is the name of the Microsoft SQL Server instance.

   • Log on to Microsoft SQL Server as the system administrator by using Dedicated Admin Connection and run the mssqlsystemresource_setup script:

     "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" -UuserName 
     -Ppassword -SserverName\instanceName -A -i%RAX-15_2%\scripts\mssql\mssqlsystemresource_setup.sql
     

   • Restart Microsoft SQL Server in multiple user mode.

5. If automark table is enabled before applying the patch:

   • Log on to the primary database and enable the automark trigger by issuing:

     ENABLE TRIGGER ra_createtable_trig_ ON DATABASE
     

     where ra_createtable_trig_ is the name of the automark trigger created by Replication Agent.

   • Log on to the primary database and enable the DDL trigger by issuing:

     ENABLE TRIGGER ra_ddl_trig_ ON DATABASE
     

     where ra_ddl_trig_ is the name of the DDL trigger created by Replication Agent.

6. Zero the LTM locator and move the truncation point to the end of the log:

   • Zero the ltm locator by logging in to RSSD and issuing:

     rs_zeroltm < ra_instance > , < pdb_name > "
     

   • Move the truncation point to the end of log by logging in to Replication Agent and issuing:

     pdb_init move_truncpt
     

7. Resume replication or other operations in Replication Agent. The primary database will be accessible to the users.