SQL Server Replication and DR

Published On: 2012-03-29By:

Every once and a while people ask me if they should use SQL Server Replication to get data to a DR site.  And typically to them my answer is “probably not”.  The reason that I say that is for a couple of reasons.

1. If there are triggers on your tables, replication doesn’t have a way to ensure that the triggers will be there on the remote site.

2. If you need to add tables, procedures, views, etc. you have to reinitialize the subscription to add the new articles to the subscriber.

3. The failback story is pretty much a mess.  Assuming that you do have to fail over to your DR server failing back isn’t exactly the easiest thing to do.  Basically you have to take another outage while you move the database back.  That or you have to resetup replication in the other direction.

Needless to say that these are some pretty good reasons to not use SQL Server Replication to get data to your DR site.  Especially as there are so many better options such as Database Mirroring, Log Shipping, storage replication, third party storage replication and soon enough AlwaysOn Availability Groups.

If you are using SQL Server Replication to replicate data from your production site to your DR site I urge you to look at the other options which are available to you and you should strongly consider moving to one of the other technology options.

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via