What objective factors indicate it's time to implement SQL Server replication?

Question

I am trying to strike a balance between high performance of our database and ease of maintenance. We are considering using replication to improve performance, by replicating our SSRS reports to a physically separate database from our transactional database. However, enabling replication has a number of drawbacks from a developer point of view:

• It makes schema changes more difficult
• It interferes with our automated integration/build server
• It seems to make it difficult to implement SQL source control

My question is: When do you know it's time to go with replication in light of these drawbacks? How do you decide whether the additional complexity justifies the gains?

We've used it before so setting it up is not a problem. This is more about making the decision to, or not to, enable it. I'm looking for some object performance metrics that others have observed with replication.

Of course the best thing would be to do some simulated load testing on our own servers and figure it out ourselves, but I am hoping there are some general guidelines out there.

Answer 1

Replication should be considered during the design phase of the application. If you have a geographically distributed workforce/userbase, having regional databases and central databases may make sense. Disconnected databases on laptops can "synchronize" when they finally reconnect with the network (think sales staff on the road).

As for reporting purposes, replication is NOT the answer. Most of the performance problems in a reporting environment stem from the fact that the reports are being written against a system that is configured for online transaction processing (OLTP).

Replication for reporting purposes is simply moving your OLTP data to another server, but keeping the same report-unfriendly structure. In essence, you are throwing more hardware at the problem and increasing your maintenance costs, but for marginal gains.

What you should be looking at is how to get the specific data that your reports need, and transform them into a more useful format. Create a feed that grabs new transactions from your production database and stores it in a reporting database, preferably on a separate server. Every time the feed runs, it should grab all the data that is newer than the last time it ran, transform that data, and store it. The reports that you currently are running will give you a good idea of the types of transformations required.

By following this approach, you are going to reap huge performance benefits.

Answer 2

For what it's worth, we've found replication to be helpful in a similar situation because the report writers can "own" the indexes on the replicated database. This has given us the ability to improve query performance by adding indexes that the application developers would have never approved in production because of their negative impact on INSERTS and UPDATES.

Maybe part of your use case is to copy some transactional tables, change some indexing, and see if it's worth it?

Hope this helps!