A DBA's Approach to Setup Transactional Replication - 4 NonStop Days at Office

Recently, we 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

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.

//
backupdatabase ABC to disk='E:\Microsoft SQL Server\MSSQL\Backup\User\ABCt_Full_Backup_After_HotFix.bak'
//
The IP Address of the OLTP Server was 10.1.1.1
Once the FullBackup got completed, I started restoring the same on the Reporting Server using the below T-SQL.
//
RESTOREDATABASE ABC
FROM DISK='\\10.1.1.1\User\ABC_Full_Backup_After_HotFix.bak'WITH MOVE 'ABC'TO'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log'TO'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10,NORECOVERY
//

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.

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.

This was one such approach which I used to set up Transactional Replication between the OLTP and the Reporting environment.
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.

Share

About the Author

My name is Satnam Singh and I am working as a Senior SQL Server DBA with Larsen and Toubro Infotech in Mumbai,India. I have 6 years of experience as a Database Administrator. I specialise mainly in HA Solutions, Query and Server Performance Tuning, Capacity Planning. I am also the guest columnist for various websites like www.sqlservercentral.com, www.sql-server-performance.com,www.sqlservergeeks.com wherein I have various articles posted covering various aspects of the technology. During my free time, I love reading Paul Randal, Gail Shaw's and Amit Bansal's Blogs. If you have any question then you can reach me on singhsatnam84@yahoo.com, I would be glad to assist you.