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
