Applying Microsoft SQL Server Patches

Apply Microsoft SQL Server patches on a database that is being replicated.

1. Be sure that all data has been replicated to the replicate site.
   Note: All activities must stop before this step, and all users except the pds_username must 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.

   Note: 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. Disable the Replication Agent triggers.
   If the pdb_automark_tables configuration parameter is set to true, 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.

   Note: The trigger name is based on the prefix and suffix setting for database object name.
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:

     "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 in to the Replication Agent instance:

     isql -U username -P password -S instanceName 

   • Reinitialize Microsoft SQL Server :

     server_xlog remove, force
     go
     server_xlog init
     go

   • Restart Microsoft SQL Server in multiuser mode.

5. If the pdb_automark_tables configuration parameter is set to true before applying the patch:
   • Log in 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:

     ra_locator move_truncpt

7. Resume replication or other operations in Replication Agent.