March 8, 2011 at 6:20 am
Hi all
We have a scenario like we have 6 physical boxes on which sql server is running and no of dbs are approx 200.Now we are planning a DR for all these dbs.Can you pls suggest me best solution for this condition?
We are planing to build a single high end vmware servers and make it a DR server for all through Metro clustering,is it possible?or just create a consolidation server and move all 6 boxes into one box and create a single DR box in separate location ?
March 8, 2011 at 6:38 am
To a very large extent, DR planning will depend on specific business needs, hardware availability, skillset availability, resources available/obtainable, and SLAs/SLRs.
Virtual machines can be part of a DR plan, but more often, they're part of a money-saving plan. They can help if a motherboard crashes, but they aren't really part of handling database corruption, I/O failure, power outages, facility damage/destruction, data loss due to developer/DBA incompetence/accident, et al.
I seriously recommend hiring a contractor who specializes in disaster prep and recovery and going over business needs, etc., with that person. It'll take some time and some (a lot of) work, but you'll end up with a better plan, more suited to your specific needs, than you will by asking a few questions on an online forum and getting a few short, generic answers.
If you can't go that route, then start with the basics. Tested backups, some sort of co-location in case of fire/earthquake/hurricane/civil unrest/etc., redundant power sources (tested, checked, fueled regularly), multi-channel SAN with appropriate RAID arrangements, logs on different I/O systems than data files, server redundancy (mirroring, log shipping, replication, clustering, VM, or some combination thereof), spare hardware (hot-swap drives and spare power supplies at the very least), and, most important, a plan that has been drilled and tested to see how long it takes to recover from various levels of server/database issue.
Those are all general. Hiring an expert will get you specifics applicable to your situation and your business, your budget and your geography, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 8, 2011 at 6:44 am
GSquared (3/8/2011)
To a very large extent, DR planning will depend on specific business needs, hardware availability, skillset availability, resources available/obtainable, and SLAs/SLRs.Virtual machines can be part of a DR plan, but more often, they're part of a money-saving plan. They can help if a motherboard crashes, but they aren't really part of handling database corruption, I/O failure, power outages, facility damage/destruction, data loss due to developer/DBA incompetence/accident, et al.
I seriously recommend hiring a contractor who specializes in disaster prep and recovery and going over business needs, etc., with that person. It'll take some time and some (a lot of) work, but you'll end up with a better plan, more suited to your specific needs, than you will by asking a few questions on an online forum and getting a few short, generic answers.
If you can't go that route, then start with the basics. Tested backups, some sort of co-location in case of fire/earthquake/hurricane/civil unrest/etc., redundant power sources (tested, checked, fueled regularly), multi-channel SAN with appropriate RAID arrangements, logs on different I/O systems than data files, server redundancy (mirroring, log shipping, replication, clustering, VM, or some combination thereof), spare hardware (hot-swap drives and spare power supplies at the very least), and, most important, a plan that has been drilled and tested to see how long it takes to recover from various levels of server/database issue.
Those are all general. Hiring an expert will get you specifics applicable to your situation and your business, your budget and your geography, etc.
very good advice... DR is so dependent on your environment, your systems, your budget, etc etc that it is nearly impossible to answer here.
The above is a good starting point though
March 8, 2011 at 6:56 am
Ok suppose i have to ceate a DR solution for a instance whihc has more than 100 dbs and b sizes is approx upto 30-40 gb.cost is not a constraint for this.Can u pls advise the better solution.Ideally we can go with Logshipping only because mirroing is not possible here......but dont u think its very complex to manage tht logshipping setup?so in tht case wht sud we do for DR? and also whts is proc and cons of Vm boes in tersm of sql server?
March 8, 2011 at 7:01 am
GSquared (3/8/2011)
I seriously recommend hiring a contractor who specializes in disaster prep and recovery and going over business needs, etc., with that person. It'll take some time and some (a lot of) work, but you'll end up with a better plan, more suited to your specific needs, than you will by asking a few questions on an online forum and getting a few short, generic answers.
Seconded. This isn't trivial to do and getting it wrong is costly.
Manvendra, you say cost isn't a constraint. So is geo-dispersed clusters, multiple SANs, multiple servers, multiple data centres and top-end fibre connections costing tens of millions of dollars feasible? DR at the top end is not cheap. I've never seen a client with an unlimited HA/DR budget, not even the banks where downtime is measured in millions of dollars a minute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 6:40 am
Manvendra (3/8/2011)
Ok suppose i have to ceate a DR solution for a instance whihc has more than 100 dbs and b sizes is approx upto 30-40 gb.cost is not a constraint for this.Can u pls advise the better solution.Ideally we can go with Logshipping only because mirroing is not possible here......but dont u think its very complex to manage tht logshipping setup?so in tht case wht sud we do for DR? and also whts is proc and cons of Vm boes in tersm of sql server?
How transactional are the databases? Does it vary from database to database or are they all the same? What kind of connection are you log shipping through and how much bandwidth does it have? How fast does your co-location restore the shipped logs and how reliable is their uptime? How reliable is your uptime? Are you backing up to a separate I/O subsystem than your data and log files? Are your databases on a SAN? If so, how is that configured? How about your log files? The backups?
There are too many questions that affect this to just start giving answers blind like this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 6:46 am
GSquared (3/9/2011)
How transactional are the databases? Does it vary from database to database or are they all the same? What kind of connection are you log shipping through and how much bandwidth does it have? How fast does your co-location restore the shipped logs and how reliable is their uptime? How reliable is your uptime? Are you backing up to a separate I/O subsystem than your data and log files? Are your databases on a SAN? If so, how is that configured? How about your log files? The backups?
And...
Why do you say mirroring isn't possible?
What's the downtime SLAs for these databases? Same for all or different for each?
What are the data loss SLAs for these databases? Same for all or different for each?
How far away is the co-location? Is it far enough for the potential disasters whereever you are?
Do you need automated failover?
etc...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 8:40 am
the pro's of vmware is that you ship the entire instance to DR and just mount it on your DR vmware host and change the IP. no need to mess with logins or anything else. assuming you have dynamic DNS everything should work in 15-30 minutes since the instance is just a bunch of bits on your network that can be moved between any vmware server
March 9, 2011 at 8:46 am
alen teplitsky (3/9/2011)
the pro's of vmware is that you ship the entire instance to DR and just mount it on your DR vmware host and change the IP. no need to mess with logins or anything else. assuming you have dynamic DNS everything should work in 15-30 minutes since the instance is just a bunch of bits on your network that can be moved between any vmware server
You're assuming you have (a) the bandwidth to ship it, (b) the server(s) it's hosted on are still up and available to ship from, (c) you have the time to ship it.
None of those will be applicable if you are dealing with, for example, a fire in the server room, or a power outage you weren't actually prepared for, or your SAN dies because of a 2-disk failure in a RAID 5 array.
VMotion and other virtual machine movement solutions are really most applicable for moving virtual machines around in the same data center if one of a set of blade servers (or something similar) fails. That helps with a very specific type of DR, but it's pretty much useless in other types.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 9:00 am
DR for 100 databases, 30GB.
Solution1 : Copy the backups onto DVDs/tape, have someone drive them to a remote location every afternoon. Have standby serverse ready in the second location.
Solution 2 : log ship all databases to a second servers somewhere.
Solution 3 : use geoclustering with six servers at each location and SAN replication to make it work.
Which one is appropriate for you? It depends. What is your Recovery Time Objective (RTO) and your Recovery Point Objective(RPO)? IF you don't know what SLA you have to meet for recovery in a disaster, how can you decide which is appropriate for you.
You might want to read up on what is involved with DR first, and understand what things you need to get clarified before you start with any technology.
http://en.wikipedia.org/wiki/Recovery_time_objective
March 9, 2011 at 9:14 am
GSquared (3/9/2011)
alen teplitsky (3/9/2011)
the pro's of vmware is that you ship the entire instance to DR and just mount it on your DR vmware host and change the IP. no need to mess with logins or anything else. assuming you have dynamic DNS everything should work in 15-30 minutes since the instance is just a bunch of bits on your network that can be moved between any vmware serverYou're assuming you have (a) the bandwidth to ship it, (b) the server(s) it's hosted on are still up and available to ship from, (c) you have the time to ship it.
None of those will be applicable if you are dealing with, for example, a fire in the server room, or a power outage you weren't actually prepared for, or your SAN dies because of a 2-disk failure in a RAID 5 array.
VMotion and other virtual machine movement solutions are really most applicable for moving virtual machines around in the same data center if one of a set of blade servers (or something similar) fails. That helps with a very specific type of DR, but it's pretty much useless in other types.
all the big SAN vendors have DR solutions where you keep a second SAN at a remote data center and they will ship your data there automatically. EMC's is called SRDF. you ship the data and it can be set to run automatically so your DR site is always up to date. at the DR site you have to have vmware servers and you just mount the instances and change IP's. it's so easy you can do it from starbucks.
vmware just came out with a new DR product that runs on top of srdf and other vendors' DR solutions but i don't remember all the details. vmotion is not DR, it's only high availability
vmware and the MS hyper-v is so fast now that if you want good DR and have a SAN, just virtualize SQL and ship the entire instance to your DR site. it's not even worth it messing with mirroring or log shipping anymore
your SAN DR may not always be up to the minute data but if you have a real emergency like a fire in the data center your first priority is to get up an running as fast as you can with whatever you have. you can try to get your up to the minute data later.
March 10, 2011 at 6:48 am
alen teplitsky (3/9/2011)
GSquared (3/9/2011)
alen teplitsky (3/9/2011)
the pro's of vmware is that you ship the entire instance to DR and just mount it on your DR vmware host and change the IP. no need to mess with logins or anything else. assuming you have dynamic DNS everything should work in 15-30 minutes since the instance is just a bunch of bits on your network that can be moved between any vmware serverYou're assuming you have (a) the bandwidth to ship it, (b) the server(s) it's hosted on are still up and available to ship from, (c) you have the time to ship it.
None of those will be applicable if you are dealing with, for example, a fire in the server room, or a power outage you weren't actually prepared for, or your SAN dies because of a 2-disk failure in a RAID 5 array.
VMotion and other virtual machine movement solutions are really most applicable for moving virtual machines around in the same data center if one of a set of blade servers (or something similar) fails. That helps with a very specific type of DR, but it's pretty much useless in other types.
all the big SAN vendors have DR solutions where you keep a second SAN at a remote data center and they will ship your data there automatically. EMC's is called SRDF. you ship the data and it can be set to run automatically so your DR site is always up to date. at the DR site you have to have vmware servers and you just mount the instances and change IP's. it's so easy you can do it from starbucks.
vmware just came out with a new DR product that runs on top of srdf and other vendors' DR solutions but i don't remember all the details. vmotion is not DR, it's only high availability
vmware and the MS hyper-v is so fast now that if you want good DR and have a SAN, just virtualize SQL and ship the entire instance to your DR site. it's not even worth it messing with mirroring or log shipping anymore
your SAN DR may not always be up to the minute data but if you have a real emergency like a fire in the data center your first priority is to get up an running as fast as you can with whatever you have. you can try to get your up to the minute data later.
Really? It's changed that much since last April?
That's the last time I tested a VMWare DR solution to a remote location. Across a high bandwidth connection, it took 6 hours to get the databases up and running at the co-lo. Replication took under a minute, to the same co-lo. Maybe my system engineers just didn't know enough about VMWare, but that was a real result of a realistic test. (Untested DR is just a waste of time and money.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 4:47 pm
Why mirroring is not possible????
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply