May 10, 2013 at 9:54 am
Hi folks,
I imagine transactional replication is most often implemented between servers but I am trying to ascertain what benefits I can expect(if any) if I replicate to a different database on the same server.
The source database would be a medium busy, transactional, vendor provided database. Currently I have custom read-only code that is running against this database, basically reporting. The plan would be to redirect my code to the replicated database.
The issues I hope to improve are performance and some occasional deadlocking.
Can select statements contribute to deadlocking or is this only caused by inserts/updates?
Can I expect and performance improvements?
Thanks if you would like to share your thoughts.
May 10, 2013 at 1:18 pm
Select statements can absolutely affect performance.
Can you expect improvements ? That's hard to say without know the cause of the problems. If the problems are built into the application, or coming from queries and reports that need to use production, then they will still cause problems.
May 10, 2013 at 1:34 pm
Ok let me refine my question. If I redirect select statements to the replicated database would the performance of the primary possibly be be improved. Sure at the server level CPU, memory and I/O are still being used.
I don't know much about the internals. If I issue two select statements against the same table does the second one have to wait for the first?
May 10, 2013 at 1:56 pm
Yes, you could quite possibly have improvements by directing queries to a replicated database. Less chance of blocking and generally less demand on the primary database.
Many people set up reporting databases for that reason. You can also replicate a subset of the data if that is acceptable so the reporting queries run faster. For instance maybe you only need previous 12 mionths of data, but production has 10 years. Just refresh reporting databas with previous 12 months.
Another possibility is to aggregate or otherwise manipulate data for reporting. You might replicate to reporting database, then run some jobs early morning to combine data into summary tables in the reporting db, then run reports off them, and ready for users when they get to the office. All kinds of options based on your business needs.
May 10, 2013 at 2:14 pm
Ok, thank you sir, I'll continue to explore this. My primary goal is to minimize the potential impacts to the application using the primary database from my reporting and ad hoc querying. Replication latency of under a minute would be acceptable. I'm sure the ideal scenario is moving this to a different server or perhaps instance but I might not have that luxury.
I'd almost like to allocate server resources on a database by database level but to the best of my knowledge that is not possible. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply