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.