As a Database Administrator, we all are normally involved in Setting up SQL Server Transactional Replication on our environment.Apart from setting up Transactional Replication, performing a proper maintainence of the same is also very important. In this exclusive article, Satnam Singh, a Senior SQL Server DBA from Larsen&Toubro Infotech,Mumbai,India discusses one such Real World scenario which he used on his Production Servers to ensure that the performance of the database on the Reporting Server which was receiving data via Transactional Replication from the OLTP Server is excellent even after the Replication is re-configured.


Every year, we perform an activity named HotFix promotion wherein the vendor who has developed the Application performs some enhancement on the database. In order to perform this HotFix promotion, the Pre-Requisite is to break the Transactional Replication between the OLTP and the Reporting Server and once everything is promoted well, the same needs to be re-configured again. On the Reporting Server, we have build plenty of Indexes for better performance of the SSRS reports. Just FYI, the OLTP and the Reporting Server were in the same Data Centre in the United States of America.On the Reporting Server, we have kept SQL Server Indexes On a Secondary Data File kept on a Seperate Dedicated Drive of 200 GB, RAID 10 for better performance. The size of the Production OLTP Database is around 91 GB whereas the size of the Reporting Database is more than that i.e. around 130 GB, this is due to indexes contributing to an increase in size.

Since the Database Size is huge, therefore I would never recommend using the GUI for setting up the Transactional Replication, instead I would go for Replication without a Snapshot method wherein I would use the Database Backups to configure the Replication.Once the Transactional Replication is configured using the OLTP Database backup, I would loose all the indexes which were created manually against the Reporting Database for better performance. In order to ensure that I have all the indexes and statistics on the Reporting Server exactly the same as before the HotFix promotion, I performed the following steps:

  • A day before the HotFix promotion started, I took the backup of all the SQL Server Indexes. In order to take the backup of all the SQL Server Indexes, I used the below T-SQL script

Once the above T-SQL is executed, we will get a T-SQL to create all the indexes in the output. Also on the Reporting Server, since the Clustered Index resides on the Primary Data File itself therefore the above T-SQL has been written in such a manner that only the Non Clustered Indexes will be scripted out.

  • Once the indexes has been scripted out,the next step is to generate a script of all the statistics present in the database on the Reporting Server. In order to take the backup of all the SQL Server Statistics, I use the below T-SQL.

Once the above T-SQL is executed, you will get the script of all the SQL Server statistics present in the database.

  • Set up the Transactional Replication between the OLTP and Reporting Servers using the concept of Replication Without a Snapshot. I have already discussed the same concept in one of my articles published a couple of days back on the below link;
  • On the Reporting SQL Server, please create a Secondary FILEGROUP which will host the SQL Server Indexes.
  • Drop all the SQL Server Non Clustered Indexes on the Reporting Server using the below T-SQL:

  • Run the T-SQL script for Index Creation on the Reporting Server which we have discussed in Point No 1 above.Please note that once the Index Creation script is executed, all the indexes will reside on a Secondary FileGroup hosted on a Seperate Drive of RAID 10 as discussed in the beginning of the article.
  • Run the T-SQL script for Statistics Creation which we have just used above.
  • Execute the job to perform the Re-Indexing and Update Statistics of the database. The scripts for the same is as follows:

Script to Re-index all the tables of the Database:

Script to perform the Update Statistics of all the tables in the Database:

  • Once Re-indexing and Update Statistics is completed,please check the performance of the queries involved in the SSRS Reports.There might be few cases wherein a query might perform poor during the First Iteration but the performance of the same improves drastically when executed twice or thrice.

This was one such approach which I used in my Infrastructure to ensure that even though at any Point of Time, I am required to re-configure the Transactional Replication between the OLTP and the Reporting Server, it’s performance remains the same to the state as it was before.

Many Thanks to all the readers for providing their valuable time in reading the blog post. My Sincere Thanks to my Team Members at L&T Infotech with whom I have spend considerable amount of time in developing this solution.If you have any valuable suggestions which can help us to improve this article, 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