Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

  1. Does Transactional replication suit my problem the most among SQL Server replication mechanisms?

  2. 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?

  3. Isn't SQL Server 2012 Always On mechanism 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)?

share|improve this question
up vote 8 down vote accepted

Does Transactional replication suit my problem the most among SQL Server replication mechanisms?

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.

If I have an opportunity 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?

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.

Isn't SQL Server 2012 Always On mechanism 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)?

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.

share|improve this answer

Mike as a good point on Transaction Replication. We have similar situation and I am trying to get Transaction Replication to work at the moment, it's working as is, but if I add an article which I know is generating lots of Delete and Insert causing Distribution DB to bog down at the Publisher side (that is one of the caveat with transaction replication). This is my question:

SQL Server Transactional Replication Distribution Database not keeping up

I am still trying to figure out why Distribution DB is not keeping up at the publisher. Hopefully your environment won't be generating these many changes and you should be okay with the transaction replication, as that what seems to be the best for your approach.

share|improve this answer

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.

As Mike mentioned, T-Rep is not free. You have to maintain distributor database and there are dependencies on log-reader agent. If the amount of changes occurring on Publication are large and depending on the network latency, there will be commands accumulating in distribution database waiting for the log-reader agent to scan and send them to subscriber. Schema changes will require a new snapshot - which causes locking on publication database until the snapshot finishes. Also, replication of the entire database as opposed to a subset of data (selected tables, SPs, etc) often leads to problems related to its maintenance.

Based on your situation, I would recommend logshipping. Its easy to setup and there is minimal dependency (network share - should have appropriate permissions).

The good thing about logshipping is that you can have the secondary database as read-only in STANDBY mode.

You can delay the restore of log backups on secondary.

Note: that if you use logshipping with STANDBY option, the secondary database cannot be on a higher version of sql server than a primary.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.