February 26, 2009 at 9:17 am
In order to achieve quick failover (and failback) in the event of the loss of a data centre we are considering using SAN based replication (SRDF in this case). This is on physical SQL servers not virtual.
The OS and therefore some parts of SQL is installed on the local C drive, whilst the rest of the SQL installation is on SAN drives. SRDF only replicates the SAN drives so how do we keep the C drive in synch across the two servers.?
where I see this being a problem is when SQL upgrades are applied as this will make changes to the SAN drives and the C drives and so not all changes made will be replicated. This even applies to minor changes like how many error logs are kept, or default database directories, as these values are reflected in the registry.
Does anyone else out there do DR in this way and what procedures do you use to handle this situation?
thanks in advance
george
---------------------------------------------------------------------
February 27, 2009 at 7:19 am
the disaster recovery forum is obviously hardly visited, so I am bumping this thread..................
any takers?
---------------------------------------------------------------------
March 24, 2009 at 7:22 pm
we've been doing it on a cluster for years, one of EMC's first customers to do this. works great, downside is it's only on disk level.
if you're doing physical nodes and not clustered, why not just do the db files? just set up a recovery server at your DR site, recreate all logins and just attach the db's as a test every so often
March 24, 2009 at 9:48 pm
Have you considered boot from SAN? Windows 200 and 2003 support it, so it is really just a matter of finding a SAN vendor that supports it at the hardware level.
Boot from SAN in Windows Server 2003 and Windows 2000 Server
http://www.microsoft.com/windowsserversystem/wss2003/techinfo/plandeploy/bootfromsaninwindows.mspx
March 25, 2009 at 10:18 am
thanks for replying guys!
Mr(?) Noob, your way certainly gets round the upgrade problem.
Trouble is I am greedy and was really hoping to leverage SRDF to keep the failover in synch for me by replicating system databases as well. Because there are not just logins, but DTS\SSIS, agent jobs, linked servers as well. If they could just be kept in synch and just be there when we failover that would be so good and justify SRDF as more than a faster way to failover and back. Quite keen that the failover server ends up with the same name as the primary as well.
Michael, I like the boot from SAN idea, do you do that?. Our intel guys don't seem keen, saying there are performance problems. Seems to me it would give the slickest failover though.
I will be setting up a proof of concept and try all options I can think of, see how they compare.
Other option appears to be replicate system databases but not C, and split the disks being replicated when we do upgrades then re-establish SRDF.
---------------------------------------------------------------------
March 25, 2009 at 10:52 am
Here are a couple of possibilities.
1. Set up DB mirroring from primary to DR server. If there is a primary server failure, make the DR server the principal and rename it. This should only take a few minutes. As far as I can tell, SQL 2005 does not store the server name inside and will allow renaming of the server OS.
2. Make the server itself a VM, even if it is the only guest OS on that box, and stored the VM virtual disk files on the SAN, replicating them the same way as the SAN volumes the VM server uses for the data. What I am saying here is that the VM virtual disk is only for the C: drive. The SQL DB and transaction files will exist on their own SAN volumes, as you see fit.
This method should allow the VM to use the underlying host server resources as if their were no host in the middle, if the VM software will perform. I understand they have worked out the performance issues they had with SQL Server a few years ago.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 25, 2009 at 11:09 am
This is just like buses, nothing for a month, then 3 at once:-)
Stamey (3/25/2009)
Here are a couple of possibilities.1. Set up DB mirroring from primary to DR server. If there is a primary server failure, make the DR server the principal and rename it. This should only take a few minutes. As far as I can tell, SQL 2005 does not store the server name inside and will allow renaming of the server OS.
Got to be SRDF I am afraid, for quick failover AND failback. No time for reinitialising from full database backup\restores
2. Make the server itself a VM, even if it is the only guest OS on that box, and stored the VM virtual disk files on the SAN, replicating them the same way as the SAN volumes the VM server uses for the data. What I am saying here is that the VM virtual disk is only for the C: drive. The SQL DB and transaction files will exist on their own SAN volumes, as you see fit.
This method should allow the VM to use the underlying host server resources as if their were no host in the middle, if the VM software will perform. I understand they have worked out the performance issues they had with SQL Server a few years ago.
Chris
We have some SQL on vmware, and SRDF in place for these, they are fine. Most of our apps don't perform on vmware though and this requirement is to get SRDF up and running on the physical boxes, some of which support multiple applications.
---------------------------------------------------------------------
March 25, 2009 at 12:48 pm
george sibbald (3/25/2009)
thanks for replying guys!Mr(?) Noob, your way certainly gets round the upgrade problem.
Trouble is I am greedy and was really hoping to leverage SRDF to keep the failover in synch for me by replicating system databases as well. Because there are not just logins, but DTS\SSIS, agent jobs, linked servers as well. If they could just be kept in synch and just be there when we failover that would be so good and justify SRDF as more than a faster way to failover and back. Quite keen that the failover server ends up with the same name as the primary as well.
Michael, I like the boot from SAN idea, do you do that?. Our intel guys don't seem keen, saying there are performance problems. Seems to me it would give the slickest failover though.
I will be setting up a proof of concept and try all options I can think of, see how they compare.
Other option appears to be replicate system databases but not C, and split the disks being replicated when we do upgrades then re-establish SRDF.
you can do it. somewhere in BOL i think there are instructions to move system databases to another drive. for the domain name the failover server is different unless you drop the old one and rename the DR server.
if you can wait 6-12 months Intel is about to ship it's new CPU's in a week or so and the hardware will be so powerful you can run SQL in VMWare and just ship the virtual machine file to your DR site.
next week Intel releases their new Nehalem CPU's that Apple and Cisco already announced a few weeks ago. this one is a dead end since they skipping this current generation and will release a new generation in less than 12 months unlike 2-3 year time as before. I think next week's release is 45nm and they are going to 32nm later this year. from what i read a 2U server with this CPU should support up to 192GB of RAM. and these machines will come with PCI Express v2 which is twice as fast as the current I/O system
we used to run log shipping here years ago before srdf and before i became a dba. from what i heard there were always problems and they had to always reinitialize it with a new full backup/restore. mirroring is almost the same thing and subject to the same problems
March 25, 2009 at 10:25 pm
george sibbald (3/25/2009)
...Michael, I like the boot from SAN idea, do you do that?. Our intel guys don't seem keen, saying there are performance problems. Seems to me it would give the slickest failover though...
Never used it myself.
I would ask whoever said there are performance problems for more info about why they are saying that. Did they actually try it themselves? Or did they just hear it somewhere, and are repeating an old wives tale based on some problems with early versions, etc.? Also, even if they did try it themselves, did they really know what they were doing?
Doesn't hurt to be a little skeptical. Of course, that also includes the promises made by SAN vendors with visions of of multimillion dollar sales.
March 25, 2009 at 10:48 pm
We've had plenty of servers booting off SANs since 2001 with no issues. It gets a little harder to configure for some hardware to get the boot, but it should be no performance issue.
What is on the C drive you're worried about? Should be a base instance, which should be minimal to recover for you.
March 26, 2009 at 7:53 am
Michael Valentine Jones (3/25/2009)
george sibbald (3/25/2009)
...Michael, I like the boot from SAN idea, do you do that?. Our intel guys don't seem keen, saying there are performance problems. Seems to me it would give the slickest failover though...
Never used it myself.
I would ask whoever said there are performance problems for more info about why they are saying that. Did they actually try it themselves? Or did they just hear it somewhere, and are repeating an old wives tale based on some problems with early versions, etc.? Also, even if they did try it themselves, did they really know what they were doing?
Doesn't hurt to be a little skeptical. Of course, that also includes the promises made by SAN vendors with visions of of multimillion dollar sales.
We already have the SAN and SRDF with it, both are already used for fileservers and app servers etc, so perhaps the experience with boot from SAN comes from that. Whatever I will include that setup in the POC. The powers that be have said they want SRDF used for the SQL servers and if we are going to go that route (and I'm quite happy to) I'm just really smitten by the idea of being able to replicate the whole server so failover becomes flip the drives and boot the server up, no worrying about hardcoded server names or file locations in databases, SSIS packages etc. I maybe being too optimistic.
---------------------------------------------------------------------
March 26, 2009 at 7:56 am
never did boot from san myself, but i think it's pretty easy to set up
just set the HBA BIOS on to boot from and have the SAN admin provision the disk before you boot and just install the OS or boot from HP Smartstart or other vendor OS install scripting CD
March 26, 2009 at 8:04 am
Steve Jones - Editor (3/25/2009)
What is on the C drive you're worried about? Should be a base instance, which should be minimal to recover for you.
cheers for the insight steve.
Its those things on the C drive that a SQL install places on the C no matter where you actually ask for the install to go to. i.e the things that go into program files\microsoft SQL server, such as binaries for the tools I believe go there, and of course information held in the registry.
Our system databases are placed on the SAN so have the potential to be replicated by SRDF. If we do replicate them I was wondering how others who do that handle SQL upgrades to the SRDF pair to keep the binaries on the C in line with the system databases.
---------------------------------------------------------------------
March 26, 2009 at 8:23 am
we have DR servers at a remote location. most apps are java and use sql logins and we have a db/tables that we use to point apps to servers and db's. the app server has a local text file that tells it which alias db to go to. each alias db has a table with the info. just edit the file to point it to a new server
on the remote server we already have the sql logins set up. for windows authenication if you use groups instead of creating a separate login for everyone it's a lot easier to manage
March 26, 2009 at 9:03 am
George,
I think most people handle it like other DR stuff. They reinstall SQL (have to know the version + SP + hotfix) on the new server and then point things to the SAN. Or they keep a warm server handy and apply patches to both at once.
It's sad that SQL still requires a C drive for some things.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply