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 the database. 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 SQL Server setting up the Transactional Replication again did put us in a deep trouble. In this article, I am going to discuss in detail the method which I adopted to set up the Transactional Replication.

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.

One important point to mention here is that on the Suscriber 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) on the Reporting DB Server,

On the Reporting server, the indexes were kept on a separate dedicated SAN whose size was 110 GB and RAID 10. The indexes were allocated to the Secondary File Group just to ensure excellent performance.

The replication Snapshot Agent files was configured to reside under the following path on the OLTP DB server,

D:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\ABC\20111113144542

The drive D was a SAN drive of RAID 10 which was holding the SQL Server Data Files as well. Before I started setting up the Replication process again, the free space available on the D drive of the server was 50 GB.

I created the Publisher as well as the Subscriber using the wizard. Once completed, I Started the Snapshot Agent. Once the Snapshot got created, I was surprised to see that the free space available on the D drive was just a mere 8 GB. This being a Production OLTP database server I was in some sort of worry at this stage.

Once the Snapshot got created, I started the Log Reader Agent so that the system starts generating the Replicated Commands which worked well. I then started the Synchronisation status so that the corresponding database on the subscriber starts receiving the Replicated Commands. 5-6 hours passed by but still the subscriber wasn’t able to complete the process of receiving the Replicated commands. When I viewed the synchronization status of the subscriber, I noticed the following error message:

The process is running and is waiting for a response from the server. 

Since I have already lost a huge amount of time and with just 8-9 hours remaining for my users to come ONLINE, I decided that there is a need to change the strategy to set up the Replication process. I stopped the Log Reader Agent as well as the Synchronization Agent manually.I then manually deleted all the files which was present under the path,

D:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\ABC\20111113144542

This was done to free up the space on the D drive of the OLTP DB Server.

I then took the Full Backup of the database which was acting as a publisher using the below T-SQL

Once the Full Backup got completed, I started transferring it onto the Reporting Server which was acting as a subscriber. Since both the OLTP as well as the Reporting Server were located in the same Data Center with a very good Bandwidth as well as the N/W speed, it took around 5 hrs and 47 minutes for me to copy the backup onto the Reporting Server.

Once copied successfully, I started Restoring it on the Reporting Server using the below T-SQL.

RESTORE of Full Backup was done with NORECOVERY option so that I can restore additional backups over it; this will ensure that I have latest data on the subscriber while setting up the Replication procedure.

While the Full Backup was getting Restored on the subscriber, I started taking the Differential Backup of the same database i.e ABC on the Publisher itself i.e OLTP Database Server using the below T-SQL,

The size of the differential backup was very small, it was a mere 6 GB and by the time the Full Backup got completed, the differential backup got copied onto the subscriber successfully.

I then started restoring the Differential Backup over the database named ABC present on the subscriber using the below T-SQL.

Once the differential backup got restored, I created the Publisher using the wizard itself but one important point to consider here is that I never set up any sort of settings for the Snapshot Agent at all, please refer the screen capture below:

1_Setting_up_Transactional_Replication_for_Huge_Databases

The next step was to enable the publication to allow initialization from a backup file. This was done by executing the below T-SQL against the distribution database on the Publisher itself i.e OLTP Database Server.

I then took the latest Transactional Log Backup of the database named ABC which was present on the OLTP Database Server and then restored the same onto the database named ABC which was present on the subscriber with RECOVERY option. RECOVERY was used so that the database comes ONLINE. Restoration was done using the below T-SQL.

The next step was to add the subscription using the below TSQL which was executed against the database named ABC on the OLTP DB Server i.e. the publisher.

I then manually executed all the indexes scripts which were generated as discussed in the beginning of the article itself against the database named ABC on the Reporting server i.e. the subscriber.

I then started the LogReader agent and noticed that the Replicated Commands were getting generated properly.

I then viewed the synchronization status of the subscriber and noticed that the Replicated commands were getting delivered properly onto the subscriber, please refer the screen capture below:

2_Setting_up_Transactional_Replication_for_Huge_Databases

This ensured that the entire process worked well. It also ensures that the subscriber will have the same indexes as well as statistics as the publisher itself. In general, I would normally recommend using the above approach for setting up the Transactional Replication for databases which are huge in size. It saves a lot of time and even gives a lot of ease to the DBA as well.

My sincere thanks to all the readers for giving their valuable time in reading the article. Any feedback, alternative approach to the same will be Highly Appreciated. My sincere thanks to the editor Amit Bansal and the entire Support team at Larsen&Toubro Infotech for providing their valuable guidance which helps me to present some interesting Real World scenarios which I encounter in my Daily Work to the Community.

 

Regards

Satnam Singh

Like us on FaceBook Follow us on Twitter

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