Recentlywe need to perform a HotFix promotion on our Production environment. In HotFix promotion, the vendor who has developed the application has done some major enhancements to fix the bugs on the Application as well as on the database level. As a pre-requisite, before the HotFix promotion starts, we need to break the LogShipping as well as Transactional Replication. The database on which the HotFix was going to be applied was around 90 GB in size. Setting up the LogShipping again was not a difficult ask but setting up the Transactional Replication again did put us in a deep trouble. In this article, Satnam Singh a Senior SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses in depth the method which he adopted after doing a consecutive research for 4 Consecutive NonStop working days which finally made him successful.

SQL Server Versions:

Publisher: OLTP DB Server, SQL Server 2005, Service Pack 3,32 GB of RAM,8 CPU’s

Subscriber: Reporting DB Server,SQL Server 2005, Service Pack3, 16 GB of RAM, 12 CPU’s

Both the above mentioned servers were located in the same DataCenter in the United States of America. Just FYI, the subscriber is the reporting server which receives data from the OLTP server every 15 minutes via Transactional Replication. Also while setting up the Transactional Replication, it was decided that the distribution database will be hosted on the OLTP Server itself.

One important point to mention here is that on the Subscriber i.e. the Reporting Server we have created a lot of indexes for better performance of the Select statements.Before I started with the HotFix promotion, I ensured that I have the backup of all the indexes. In order to take the backup of the indexes, I executed the below query against the database named ABC (Database which was involved in Replication) and resided on the subscriber (Reporting DB Server).Before starting the HotFix promotion, I decided to take the backup of all the SQL Server Indexes present in the existing database on the Reporting Server. In order to do so, the below T-SQL was executed against the database named ABC on the subscriber i.e Reporting Server.

Once the above T-SQL was executed, I got the Index Generation script of all the Indexes which I saved onto a particular location on the server itself. Please note that on the Reporting Server we have allocated a seperate drive if RAID 10 and size 200 GB just to hold the SQL Server Indexes itself for better performance.Also note that the Index generation script will not script the Clustered Index present in the tables, this is because we will be moving only the Non Clustered Indexes in the database.If we try to move the Clustered Indexes the table associated with that Index will also get moved because the leaf of the Clustered Index is the data page itself.


After the HotFix got promoted, I started reverting back with the changes. I decided to first setup the Transactional Replication because that was important as most of the Business users heavily relied upon the Reports. Since the database OLTP database
size was around 90 GB, I decided to go with “Replication without a Snapshot“. 

On the OLTP Server, I first created the publisher using GUI.Once the publisher was created, I directed the system that it needs to Initialise the backup from the backup set. 

I then took the Full Backup of the database on the OLTP Server using below T-SQL.

The IP Address of the OLTP Server was 

Once the Full Backup got completed, I started restoring the same on the Reporting Server using the below T-SQL.

Full Backup was Restored using NORECOVERY mode so that additional backups could be applied over it. 

By the time the Full backup was getting Restored onto the Reporting Server, I started taking the Differential Backup of the same on the OLTP Server itself using the below T-SQL.

By the time the Differential Backup got restored I started taking the Transactional Log Backup of the database named ABC on the OLTP Server itself using the below T-SQL.

I then Restored the Transactional Log Backup onto the database named ABC on the Reporting Server using the below T-SQL. 

Once the database named ABC on the subscriber was brought ONLINE, i then decided to create the subscriber on the OLTP Server using the below T-SQL.

Once the above query was executed, I received the below error message:

The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. 

At this stage, I realised that instead of Restoring the Differential and Transactional Log backup over the Full Backup on the Reporting Server, if I create the publisher and then just restore the Full Backup on the Reporting Server, the Log Reader Agent itself would take care of all the DML changes which had happened on the OLTP Server itself. I then took the decision of creating the Publisher on the OLTP Server, instructing it to initialise the backup from the Backup set, Restore the Full Backup on the Reporting Server and then create the subscriber.

Once the above logic was implemented, I was successfully able to create the subscriber but when I viewed the synchronisation status, the system threwed the below error message:

“Violation of Primary Key constraint in the table named abc” 

Now at this stage, I was pretty much confused with the unusual behaviour of the technology. Hours passed by but even after doing all the troubleshooting, I couldn’t find the culprit.After spending around 12-16 hours investigating the case, I decided to check the T-SQL
involved for the triggers in the database and I was very surprised to see the code. The T-SQL for triggers didn’t had “Not For Replication” clause at the top of it. I thought that this might be the case with few of the triggers but I was very surprised that none of the triggers in the entire database had “Not For Replication” clause at the top of its code. Due to the presence of triggers duplicate
entires were generated for a particular table, one entry was generated by the Log Reader Agent whereas the other entry was generated by the Trigger and hence there was a Violation of Primary Key constraint.

I then took the decision that against the database named ABC on the Reporting Server, all the triggers shall be disabled for the replication process to work properly.

I then changed the strategy for Setting up the Transactional Replication between the OLTP and Reporting Database Servers which is as follows:

  • Create the publisher on the OLTP server and directed it to initialize the backup from the backup set.
  • On the OLTP Server, take the Full Backup of the database named ABC.
  • On the Reporting Server, Restore the Full Backup.
  • Generate a script to disable all the triggers in the database named ABC on the Reporting Server using the below T-SQL.

  • The output of the above query was executed against the database named ABC on the reporting server and all the triggers got disabled.
  • Created the subscription by executing the below T-SQL query against the database named ABC on the OLTP Server by using the below T-SQL query,

Once the above query was executed, Data started flowing properly between the OLTP and the Reporting Server.

This was one such approach which I used to set up Transactional Replication between the OLTP and the Reporting environment, Indeed it was a very unique case which i have faced till date. My sincere thanks to all the viewers for providing their valuable time in reading the article. If you have any suggestions then please do let us know.



Satnam Singh

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook