January 17, 2010 at 4:19 am
I have a production sql server instance (say Prod1). Now I bring down the services, copy the instance root directory and its contents (say d:\..\MSSQL10.InstanceId) to a file location. Then bring down the phyical server itself.
Now I build a new phyical server with the same name as the old server and then do a Sql intallation extactly the same as the old server.
If I then bring down the services, rename the instance root directory, copy over the previous instance root directory with the contents. Then start the Sql server intance, will the services come up. If it does come up, would automatically recognize the databases & service pack etc.
Any advise on this would be very helpful coz I'm going to carry out a PoC soon.
Thanks...
January 17, 2010 at 5:04 am
do you mean the \data directory, i.e the system databases? I see no point in copying anything else over, the install will put the rest in place for you.
Can you clarify thats what you mean?
---------------------------------------------------------------------
January 17, 2010 at 5:10 am
MSSQL directory to be precise which contains folders such as FTData, BackUp, Data, Logs etc. This includes system dbs as well as user dbs.
Reason is there is storage level replication happening between the old server and the new server. I would just need to attach the disks containing the MSSQL directory. Would this work?
January 17, 2010 at 5:46 am
yes I would say it is workable as a one off task, but the SQL installation would need to be identical.
If this is your DR process, when you do upgrades you would have to do both boxes. If you are doing SAN replication this means splitting the replication temporarily.
As you are moving to different physical hardware there will be issues:
Probable decryption errors (there will be an error to this effect on the errorlog), so make sure you have a backup of the service master key, you will need to restore this.
the local accounts created by the install will be orphaned and lose their permissions to the directories. quick fix for this is to give account SQL is running under local admin, else you will have to readd directory permissions and drop and readd accounts in SQL.
If you have reporting services you will need to follow this process
http://support.microsoft.com/kb/842425
If this is to be your DR process I suggest separating user databases to a different drive, replicating only that and using standard DR processes (scripting out and copying) to copy system info. the simpler your installation is the more true that advice is.
Most certainly do a POC to prove it works in your environment, I may not have fully understood your request.
---------------------------------------------------------------------
January 17, 2010 at 6:16 am
Thank you George. Yes..this indeed our DR site setup.
We are not using encryption..but I will make sure that the service master is backed up.
We dont have reporting services intalled, but we do have Analysis services. Any special steps to follow for that?
To make things more interesting, this is acutally a Sql 2008 cluster (both production & DR). Let me explain the entire scenario:
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 (EMC) based replication. This is the standard DR model followed for Oracle & Unix and we have been instructed to do a PoC for the same model using Sql & Windows cluster. 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 apps team wants the remote DR site to have the same name as the production instance. So achieve all this I plan to do the following:
1. The replication will happen between the shared disk resouces (which Sql Server uses in Production) to the DR SAN.
2. The DR SAN will be made available in the DR site after breaking the replication.
3. The sql Server related folders (instance root directory say MSSQL10.InstanceID) will be renamed
4. I will install a 2nd intance in the DR site with the same name, disk configuration and patch levels as in production.
5. Then bring down the sql server services in DR.
6. Rename the Sql Server folders (of the newly installed instance) to a dummy name.
7. Rename back the folders (modified in Step 3) to the actual names
8. Bring up the Sql Server services.
9. I believe the Sql Server will be automatically mapped to the production data.
10. By keeping the DR Instance with the same name as production I hope to avoid any registry errors or any other dll missing issues.
11. Test the DR sql server intance.
12. Now bring the DR sql server instace down.
13. Change the IP of the Sql Server network name to point to the production IP.
14. Bring up the Sql Server resources on production.
The entire method is kind of complex. I want to take this up as a PoC and check if it works.
Do you see any problems in the steps mentioned. Much appreciate your help.
January 17, 2010 at 6:41 am
being clustered certainly makes it more 'interesting'
the work I have done in this area was not on a cluster unfortunately. I can foresee problems with IP addresses, other than that (BIG caveat)the advice should basically hold true. Analysis services should be fine if the olap\data directories are replicated.
Two things
- expecting it to just work in SQL because 'thats how we do it in Oracle' is wishful thinking by people who should know better. The architectures are too different and its a mistake to try and force them to work the same. Main issues are SQL's close linking to the server name and the fact the install always puts some part of the installation to the C drive, no matter where you point the installation at.
- the apps people are shifting a lot of responsibility to you by expecting the same server name. with proper change and control procedures servername changes should be possible and connection strings should not have server names harcoded in them, DNS could be used for instance.
Try it in your POC, but allow time to also investigate only replicating drives with the user database and analysis services data on, the process of failing over during a DR will be a lot less complicated and quicker that way.
another option that would work very well for you is to upgrade OS to windows 2008 and use a geo-cluster. This is a setup where each node is connected to a different physical SAN and you replicate with SRDF in your instance between the SANS.
---------------------------------------------------------------------
January 17, 2010 at 6:55 am
Thanks a lot for your advice. The POC is going to happen sometime next week. Will get back with the results.
Windows 2008 sure looks promising. I will try to put it as a recommendation to our Server team. Anyways keeping my fingers crossed...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply