June 18, 2009 at 12:25 pm
Version: SQL Standard 2005 SP2 x64
Question: What is the best failover method for a DR environment? 2 geographically separated datacenters.
Background: Iām currently using SQL litespeed log shipping to ship logs on an hourly basis to the DR site. Database is 400GB and is 50/50 read vs. write. Compressed hourly logs average size is 500mb. Iām wondering if a different failover method should be used? Could transactional replication be used? What are the pros and cons of TR in my scenario?
Thanks for any input
June 18, 2009 at 12:50 pm
By the time someone provides a solution, you may also want to go through this article
http://www.sqlservercentral.com/articles/Replication/logshippingvsreplication/1399/%5B/url%5D
Managing replication might not be easy and the volume of transaction ur system is producing, unless entire transactions is written to the distribution database, you cannot prevent ur log file from growing. If u keep distribution db on the same server, it'd consume more resources of the server. if you opt for remote distributor, network traffic consumption will be higher coz ur data moves from pub to dist and from dist to sub.
Replication for this volume of transactions will require thicker pipe between primary and secondary...
In log shipping everything is propagated to the secondary without any trouble. In replication, every addition/deletion of tables/other objects require rebuilding of snapshot. I'm assuming ur database of fairly huge size, in which case this would be a tedious operation.
June 18, 2009 at 2:28 pm
Hi Jacob
Here the alternatives you have
1. MS-SQL cluster on top of Veritas.
If your company or job has the money, you should go for this one. It allows you to have MS-SQL cluster aware instances which are geographically dispersed. It works pretty well, but learning curve is high and cost maybe too much for you.
2. Databse Mirroring;
If I recall well, you have that feature on SQL standard ed. Latency is minimum and if you use a witness, will do an automatically failover; only drawback if that during initial setup, you'll have to migrate User SIDs from old to new one, so during failover, connectivity won't be an issue.
3. Log shipping
4. Mirroring with Log shipping combo
June 18, 2009 at 2:32 pm
The only cause of concern here is the network bandwidth as the DR site is geographically seperated. remember size of zipped file is 500mb. if you were to apply all those transactions(obviously, without zipping them) over wan, it'd be huge ask. Cost of maintainence of wider pipes will be very high.
June 18, 2009 at 2:47 pm
ps (6/18/2009)
The only cause of concern here is the network bandwidth as the DR site is geographically seperated. remember size of zipped file is 500mb. if you were to apply all those transactions(obviously, without zipping them) over wan, it'd be huge ask. Cost of maintainence of wider pipes will be very high.
FedEx. UPS? š ... send Full Tape Backup via regular mail... restore that on the "Mirror", and continue the Mirroring setup. Yeah, old fashion way, but one you restore the initial file, the rest is easier. You can also run the full backup and split the BAK file in small chunks using winrar or similar tools. Then send each mini file via WAN to the remote server; once you got everything there, use winrar to assembly and restore. Robocopy is good also: http://en.wikipedia.org/wiki/Robocopy
June 18, 2009 at 3:30 pm
You can also run the full backup and split the BAK file in small chunks using winrar or similar tools. Then send each mini file via WAN to the remote server; once you got everything there, use winrar to assembly and restore.
Having done this method before, it works quite nicely. Just requires a little patience. I would recommend the mirroring myself.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply