I've faced a problem when my .Net web application is running slow, even when I've optimized all queries. As it turned out another developer, who is making reports runs a bunch of non optimized queries all the time. So I've decided to create a separate server for him. Currently I created Jobs making backup, copying it to another server and restoring it. But it's temporary solution, so in order for second server have up to date data or at least with minimal latency I'm investigating an opportunity to create a read only replica.
As I'm not a DBA I've read a bunch or articles regarding SQL Server replication mechanism. The best option for me is when my PROD web application isn't affected by replication at all, I mean lots of lock on syncronizing tables. I don't need a real time sync as mirroring does it, also I don't need any king of cluster solution, just read only syncing copy of my PROD database. So I've choosed Transactional replication mechanism(not updatable) with asynchronous distributing policy(on schedule).
So I've some question:
-
Does Transactional replication suit my problem the most among SQL Server replication mechanisms?
-
If I have an opportunit to migrate from SQL Server 2008 to SQL Server 2012 would transactional replication have some breaking changes? I've read an article on technet that there might occur errors?
-
Isn't SQL Server 2012
Always Onmechanism better to solve my problem(I'm considering this option as last one, as I'm still using 2008 R2, and yet migration to 2012 is planned by not soon)?
Best Answer
Out of all of the replication options - Transactional sounds like it is what helps you the most here. It gives the minimal latency and it doesn't need to be (but can be) bidirectional. You aren't merging changes and don't need Peer to Peer.
That said - Replication isn't "free" - you need to administer and monitor it and a poor setup could make performance problems worse. You should learn about the best options for performance and the best setup for performance and keep that in mind and keep your replication environment monitored.
Transactional replication is "easier" than Merge replication. But that doesn't mean you will always have a smooth sail and can always get away without someone with replication experience helping out.
Unfortunately this is an "it depends" type of question. We could theoretically give you answers from our own experiences, but you will need to test. If you do bump into issues they will not necessarily be impossible issues that you cannot fix, they will require testing and tweaking.
In SQL Server 2012 AlwaysOn has something for you here that works potentially. Availability Groups are an answer here with the concept of an Active Secondary for reading purposes. But there are licensing concerns - you need to be in Enterprise on both the primary and the read only/active secondary. With the replication approach you still need to be licensed on both ends, but this is a good option for sure. It, too, has overhead associated and some administrative concerns that you should learn about - but it makes a great option.