January 3, 2010 at 9:45 am
We are using Sql Server 2008 Enterprise x64 edition on Windows Server 2003 Ent platform. The production environment is configured in a 2 node Active/Passive cluster. Our requirement is to setup a remote DR site using SRDF R1-R2 SAN based replication. The remote site already has a setup similar to production (Sql 2008 Ent x64 + 2 node cluster) and is currently used as a Staging Environment. The steps which I have suggested to achieve DR is as follows:
1. Install a 2nd instance of Sql 2008 so that it becomes an Active/Active configuration.
2. Store the system db files and other installation data in a seperated shared disk.
3. Replicate the User databases from Prod SAN to DR SAN using SRDF r1-r2 replication.
4. Create a job in prod to script out the logins and store it a seperate sql file which will also be replicated.
5. Create a job in prod to backup the msdb database and replicate it to DR site.
6. At the time of DR break the mirroring and attach the shared disks as clustered resource.
7. Manually attach the User dbs.
8. Fire the login script which creates the corresponding logins
9. Restore the msdb backup onto the DR sql instance so all jobs, operators and alerts are created in DR instance.
10. Declare the DR site is now available to accept application/client connections.
Pls can someone advice, if the steps are right or am I missing something here. The DR site is going to be setup soon. Also the SAN replication works perfectly for Oracle dbs and Im assuming that it should work fine for Sql too. Please let know if this would work.
January 8, 2010 at 8:48 am
well, you might be killing yourself for no reason.
here is what we do in my shop...
we san mirror everything but C: (the boot device)
upon failover, we issue this command
sp_dropserver 'old_server_name'
go
sp_addserver 'new_server_name', 'local'
go
i assume you are spanning datacenters, as are we, so the host name changes.
we found after months and months of testing, it is better and easier to just mirror the system databases. If you are worried about bandwidth on the SAN devices, move temp_db to a local partition.
July 5, 2010 at 12:59 am
Hi,
As far as i know MS does not support restoring system databases (msdb in your case) from your production sql instance to the DR sql instance.
In the past i've had to manually script out job, operators, ssis, dts packages etc and apply them manually to the DR sql instance.
I'd like to know what others think about restoring msdb db from one clustered instance to another.
Cheers
July 7, 2010 at 4:47 pm
Geoff, are you by any chance on SQL 2000, or maybe not using SSIS? (not to mention SSRS or SSAS)
If on SQL2005 you may be getting away with murder just because your service account has local admin rights.
pdchandran, because of the split of user databases\system databases and the greater link to the server name that SQL has, its not as clear cut as with ORACLE.
I don't see why you need a second instance of SQL, but other than that you are on the right lines. Scripting out msdb jobs and having a process to save SSIs packages across would likely be cleaner than restoring msdb.
Having said that if you really want to turn clustering from an HA solution to a DR solution upgrade the OS to windows 2008 and set up a multi site cluster (two geographically separate nodes connected to separate SANS which are being SAN replicated)
---------------------------------------------------------------------
July 8, 2010 at 7:42 am
SQL 2005. and yes, the sql service account is a local admin.
i guess i also left out that ALL sql bits are on drive E: (which is mirrored)
we are currently migrating to SQL 2008 on W2k8 and we are just going to boot from SAN and mirror everything on the SAN. then all we have to do is turn on the failover server after we break the SAN mirror. tested and working great so far in the lab. 😉
July 8, 2010 at 8:31 am
yep, boot from SAN solves all the issues when SAN replicating with SQL. wish my sysadmins would have allowed us to go that route.
---------------------------------------------------------------------
February 11, 2012 at 11:06 am
We are planning to implement SQL DR solution using SAN replication. In our case we needed to implement this DR solution for close to 30 to 40 2-node SQL clusters, each cluster handling about 4 TB of SQL data. What is the maximum network latency that can realistically work between the two data-centers? Can async SAN replication be used for such a solution? I guess synchronous SAN replication will have severe performance impact. I have very little idea on how this solution works. Can you tell me what all drives (data/log/tempDB) are enough to replicate between the two data centers? Do we need to replicate the C: (boot device) too? Is it better to implement SAN replication on all drives of system? If I have a SQL cluster in primary site, can the whole cluster be replicated to DR site using such mechanism? If, so, what are the steps I need to take care? Your help is very much appreciated.
February 11, 2012 at 11:40 am
Please post new questions in a new thread. Thank you.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply