How to Synchronize a Reporting Server

Have you ever had to build a reporting system where you wanted to off-load the reporting function from the main production server?  It's a problem that can be solved with a variety of methods, for example:

- Database Mirroring: Works very nicely in an asynchronous environment but has the downside of only duplicating the data to a single server (so, no high-availability options) plus Enterprise Edition is needed.

- Replication: Has the benefit of duplicating data to multiple servers (plus, other benefits like filtering some transactions while letting others go through) and runs well on Standard Edition, but is harder to setup and maintain.

- Log Shipping: Has the benefit of duplicating data to multiple servers for high availability and runs on Standard Edition, but it has big drawbacks due to needing to apply the logs to the reporting server (such as difficulties with operations like rebuilding indexes). 

However, SQL Server MVP Simon Sabin, has some good recommendations.  He recommends that you stick with replication over log shipping.  Why? 

In Simon's own words, "Because with log shipping you need down time to restore logs in a log ship model to manage the restore i.e kicking users off.  Also, impacted greatly by any operations like index rebuilds on the primary server. There are also orphaned login issues (as with all restored based mechanisms)." 

Simon went on, "With transactional replication, you don't have to replicate all your data. You can have additional tables in your reporting database You can include extra indexes in your reporting system to optimise for reporting while you cannot with log shipping.  You can include extra stored procedures and views that aren't in your main database. You don't have to have all the SPs, views etc that are in the main database.  You can amend the replication SPs to provide an TL (Transform Load) mechanism, i.e. add history, timestamps not in the main database."

On the other hand, "Transactional replication requires careful management with schema changes. Bulk DML statements need to be managed on the main database to avoid excessive replication transactions.  For us having the DB always available and being able to make reporting specific optimisations are the key points. We already have a transactional replication model and so this is knowledge we already possess."

Excellent information, Simon.  Read more of his thoughts at http://sqlblogcasts.com/blogs/simons.

Best regards,

-Kevin