SQL Server Performance Forum – Threads Archive

Using Replication as disaster recovery solution

We#%92ve been using SQL Server log shipping as part of my disaster recovery strategy for over a year now. A couple of weeks ago we decided to move the fail-over server (the server that the transaction logs are being shipped to) to a remote location (Disaster recovery site). The problem with the DR site is the bandwidth is only 1MB. The bandwidth of our local network is 1Gig. Due to the 1MB bandwidth problem Log shipping started having problems (restore and copy jobs started failing). After our normal weekend re-indexing, one of the transaction logs grows to about 40Gig. Log shipping isn#%92t able to copy and restore a 40Gig log file across a 1MB network to the DR site. We#%92ve decided to discontinue Log shipping and go with SQL Server transaction Replication for disaster recovery. I understand log shipping is the most appropriate solution in this situation besides clustering. Is there anyone out there who is using replication as a solution for disaster recovery? Does it really work? My main concern is with the recovery process since replication adds extra objects in a database. Please help. Regards

Replication is limited to objects level where after the Publisher and Distributor are configured, you can create publications based on data, subsets of data, and/or database objects. Whereas the log shipping is on database based and cluster is based on the whole server.
In your case I suggest to promote the upgrade of network on the DR site in order to sustain the Disaster Recovery process. With replication the scope of DR is limited by the objects on database and you must think about system databases as well in order to perform the pure failover in case of any issues. I’ve not performed or not inclined to use REplication as a pure DR process, may be you can wait up for others responses. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for responding this posting. Is there anyone out there using replication as a disaster recovery solution? I would really like them to share their experience using replication as a solution for disaster recovery. For example, replication doesn’t copy constraint to the DR server. What is their strategy to ensure that objects in both databases stay in sync? How do they handle login id transfer to the DR database? Any information would be greatly appreciated. Thanks

The first step in developing a solid disaster recovery plan is to develop an idea of what constitutes an acceptable loss for your organization. First, consider the impact of losing data stored in your database. Would you be able to recover from the loss of an hour’s worth of data? Second, consider the loss of access to the database itself. What would be the ultimate result if your end users were not able to access information for an extended period of time. The I haven’t seen such implementations using Replication as a prime disaster recovery solution, for warm standby Log shipping and hot standby cluster are the best managed and well followed in the market. Outthere, may be third party tools will provide such facility using replication topology. May be search under Google will fetch you the results. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>