August 27, 2009 at 3:36 pm
Prod Server
server name: PRDS1
IP: Differs from DR
Data/System Files On SAN
SQL is installed on Local drive
DR Server
server name: DRS1
IP: Differs from PROD
Data/System Files: Using SAN based replication: Will have the same data/system files from PROD
SQL server is pre-Installed on local drive with a different server name and IP Address.
To build a DR solution, In case of a DR, i was thinking of just changing the server name/IP on the DR server and will tackle maintenance jobs/startup parameters/sp_dropserver etc etc my self.
Do you think this should work.
thank you
hello
August 27, 2009 at 3:41 pm
It can but I propose a different solution..
Using DNS create a name for the server and point it at the Prod server. You will need to be using a default instance for this.. You probably want to have a fairly short TTL.
When you cut over you change the IP in DNS to point at the other server.
Are you doing full SAN replication, if you are you will have master and all the system databases. It would be easiest if the paths on both were exactly the same. Also if you aren't replicating the system databases yo have a new problem.. The logins and other system ids and jobs will drift out of sync. So you may have all the data just almost nobody can login..
And depending on the SQL Version you may have to restore the server master key, not sure..
CEWII
August 27, 2009 at 5:35 pm
thanks for the quick reply.
Yes it is going to be the default instance.
We are replicating everything. system/user database files.
i am not sure of what your solution is. What changes do i do in the DNS? can you elaborate please?
DNS somehow route all the traffic to the DR server and also i will make the change of the server name(ip as well) on the DR server to that of PRD.
My biggest concern is registry settings. Should i be replicating registry settings or it is not a big deal?
sever keys,sp_dropserver etc etc i think can be manageable.
thank you
hello
August 27, 2009 at 5:39 pm
yes, the volume lables and the file system layout is going to be exactly the same.
hello
August 27, 2009 at 8:37 pm
DNS isn't so much routing as resolution.. If prod ip address is 192.168.1.100 and the DR server's ip address is 192.168.2.50. You would configure DNS for some arbitrary name MySQLServer to point at 192.168.1.100. When you cut over to the DR server you would chnage that to 192.168.2.50. Your client machines would use the name MySQLServer instead of the real server names. This would provide more transparency to your end-users, they don't need to know (or care) exactly what server is which..
As far as registry, probably not, if you install them similarly then you probably don't need to worry.
You may want to keep a copy of your prod server master key handy in case you need it. Trust me, you gotta have it..
Is that any clearer?
CEWII
August 27, 2009 at 9:29 pm
ok, here it goes again.
prod server name is example: PRODSQL1 with IP 10.20.30.40
dr server name is example: DRSQL1 with ip 10.20.30.50
Both exist and the sql server services are running on it.
in case of a DR,
i will just change the DR info from DRSQL1 with ip 10.20.30.50 to
PRODSQL1 with ip 10.20.30.40 on the DR server on the my computer tab and also let the dns team to know about this change and have them make what ever the changes they have to make on their end.
Server Masterkey also gets replicated using SAN, no issues there.
See any issues?
hello
August 28, 2009 at 6:22 am
Actually I do..
1. Both SQL servers cannot be running, only one.
2. Changing the ip address on the one can make it difficult to get the other one working again because of an IP address conflict. Do this with DNS and leave the machines IP alone. I'm getting the idea that you want to do it this way because you understand it and don't want to do it the way I'm stating because it isn't clear. Let each keep their own IP addresses and use DNS as an abstraction layer.
3. Server master key is not replicated in the DBs, you can copy every file in the data directory but that doesn't give you the key. I experienced this before, to duplicate a server we stopped SQL and copied all of the data files, master and everything. But it then complained about not being able to decrypt credentials, because the key on the machine wasn't right to do the decryption. You have to restore the Server Master Key at least once, but I'd keep a copy around just in case.
CEWII
April 5, 2010 at 7:09 pm
Here is the way I have done it with Block-Level SAN Replication
1) Create a DNS Alias for the Server
2) The DR server is always up
3) the system DB's are *not* replicated
4) All user DB's *are* replicated (same drive paths)
5) All users are copied over nightly, with a default DB of master
6) All jobs and DTS packages are saved nightly and copied to DR
In DR,
1) Change DNS pointers
2) Flush DNS for everything connecting to the server (app servers/clients, etc...)
3) A script to attach all DB's
4) A script for update all users with necessary permissions/roles/defaults
5) A script to import jobs/DTS
In situations where we had to fail-over to DR, it took less than 10 minutes to bring everything online.
However, the best solution, provided your DC's meet the requirements, would be a geographically-dispersed cluster, then you would have a near instant failover
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply