October 10, 2008 at 8:32 am
I've got 3 servers that I need to move to new "boxes". They just figured out that I know a little bit (seriously... just a little bit) about Systems DBA work but this one is all new to me. Replication is present (it's insane :alien: how many databases and tables within each database are being replicated). Servers "S1" and "S2" have the source databases and tables. Server "D" is the destination where all this data get's replicated to. Server "D" does "pulls" from the "S1" and S2" servers using replication.
There's a terabyte of information on each the servers. The "move" is from two local physical servers and 1 VM server located in Michigan to VM servers in NY. We not only have the capability of doing normal backups, but we also have RedGate compressed backups. Either way, I have no idea how to restore a server with a shedload of replicated databases. Obviously, full backups and the ensuing restores, never mind the transmission of the files, are going to take a huge amount of time, as well.
There's a couple of kickers to all of this... we can schedule only one 24 hour outage, the replication being done is a legacy system, no one here in Michigan or New York has actually done such a move nor even used replication previously, and, up to a couple of weeks ago, I've never even seen a server with replication active. Further all attempts by the folks in NY have been dismal failures but they won't tell me what they've tried (intra company politcs... yeeach!).
And, get this... we have to pull this off on Wednesday of next week. Nothing like planning ahead... Of course, the cheap buggers won't actually spend the $ to hire a company that has done such things. :Whistling: :PI think we're in deep Kimchee! :sick:
Anyway, I've read several articles on moving replicated servers, including some by Andy Warren, but none of them seem complete by themselves...:blink:
Does anyone know of an article/link that explains the whole process womb-to-tomb? If not, if you've actually been successful in making such a move, is there any chance you could write up some step by step instructions?
I realize this is no average request and that the help I've asked for is huge, but I sure could use some help. Thanks folks.
I'm gonna go pull some nose hairs so I feel better about this... 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 8:45 am
My apologies... I forgot to say that they're all SQL Server 2000 Enterprise Edition servers which also means that I just posted to the wrong bloody forum... :blush: I don't want to "cross post", so I'll just leave it here.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 9:01 am
The servers are physically different, but can you convince them to retain the original server names and IP addresses?
If so, keep everything simple. Stop the services on the servers and simply copy the files to the new servers and have everything installed in the same directories.
Your real issue is going to be the time to copy the files. I would seriously consider having someone with a firewire drive in Michigan get the databases and hop on a plane. http://www.ibeast.com/content/tools/band-calc.asp
You would need a dedicated OC3 to copy 1TB of data in 15 hours. Even with a redgate backup, you are probably talking about your 1Tb ending up 300GB - assuming a dedicated T3 you have a lot of copy time - that plus the actual backup time is going to be your #1 problem.
October 10, 2008 at 9:18 am
I would do it this way:
1. Take a full backup of each DB, Compress it and start log shipping it. You can d that "NOW".
2. After you are sure that the Logshipping process has caught up. Stop Activity on the Old Primary (make DBs Read-only)
3. Bring the Logshipped DB online with the latest Tlog backup
4. Shutdown OLD Server and Create DNS Aliases with The Old Server Name pointing to the New Server
5. Setup replication on the "New Server" from scratch.
Good Luck
* Noel
October 10, 2008 at 10:59 am
Michael Earl (10/10/2008)
The servers are physically different, but can you convince them to retain the original server names and IP addresses?If so, keep everything simple. Stop the services on the servers and simply copy the files to the new servers and have everything installed in the same directories.
Your real issue is going to be the time to copy the files. I would seriously consider having someone with a firewire drive in Michigan get the databases and hop on a plane. http://www.ibeast.com/content/tools/band-calc.asp
You would need a dedicated OC3 to copy 1TB of data in 15 hours. Even with a redgate backup, you are probably talking about your 1Tb ending up 300GB - assuming a dedicated T3 you have a lot of copy time - that plus the actual backup time is going to be your #1 problem.
Outstanding... thanks, Michael. Pardon the absolutey stupid questions that follow, but I'm totally ignorant of replication other than it's been a real PITA on this move.
1. I think I can make it all happen as above, but will I need to setup replication again, or will it be a 100% plug'n'play exercise?
2. What about the 3m databases, those being Master, Model, and MSDB? Will those be OK as well?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 11:12 am
noeld (10/10/2008)
I would do it this way:1. Take a full backup of each DB, Compress it and start log shipping it. You can d that "NOW".
2. After you are sure that the Logshipping process has caught up. Stop Activity on the Old Primary (make DBs Read-only)
3. Bring the Logshipped DB online with the latest Tlog backup
4. Shutdown OLD Server and Create DNS Aliases with The Old Server Name pointing to the New Server
5. Setup replication on the "New Server" from scratch.
Good Luck
Very cool... This also sounds very doable. Not looking forward to setting up replication for as many tables as are being replicated... is there any way to generate a script for the whole replication shootin' match from the "old" servers?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 12:04 pm
... is there any way to generate a script for the whole replication shootin' match from the "old" servers?
Yes replication settings can be scripted out but either you will have to doit one DB at a time from EM or cook your own scripts to do it.
(FIND+REPLACE) is your friend in terms of PUBLISHER/DISTRIBUTOR/SUBSCRIBER names. You must use "actual" server names not DNS ALIASES.
There are a few changes that may want to apply/consider with 2005 though but for the most part the scripts just work.
At my workplace we had time to prepare (upgrade) our replication setup scripts so it was a quick job but given your time constraints script-out and apply to the destination is just as doable.
Once again, good luck!
* Noel
October 10, 2008 at 12:07 pm
My suggestion is to stop the services on the original servers, copy the databases (including the system databases) to the new servers, and start the services on the new servers. Assuming the names and IP addresses are the same, MSSQL should not know the difference and replication will keep going as it would if you simply rebooted the servers.
If you have to do restores and bring replication back up I think you are going to run out of time.
October 10, 2008 at 1:17 pm
noeld (10/10/2008)
or cook your own scripts to do it.
Cool... I can do that. Thanks for the heads up on that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 1:33 pm
Michael Earl (10/10/2008)
My suggestion is to stop the services on the original servers, copy the databases (including the system databases) to the new servers, and start the services on the new servers. Assuming the names and IP addresses are the same, MSSQL should not know the difference and replication will keep going as it would if you simply rebooted the servers.If you have to do restores and bring replication back up I think you are going to run out of time.
Yeah... that's were I was going with this whole thing but thought . 🙂 They've made my life real difficult by thinking they know how to do it and issuing "orders" on how they wanted it done instead of taking the time to find out from folks who've actually done it.
Thanks, Michael.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 1:58 pm
Micheal, Noeld... thank you very much for the help. You guys are the best. 🙂 I'll let you know how it all turns out if they actually follow the plan I just gave them.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 3:36 pm
Good Luck..
It is really not that hard to put together srcripts to addpublications/subscriptions based on some metadata. We spent some time ironing out the details and we have so much flexibility that we don't use wizards anymore. Replication is allover the place where I work and once you put the time into building the scripts manageability becomes a breathe !
* Noel
October 11, 2008 at 10:38 pm
Hi guys .. even i m having a merge replication implemented on the server
now i want to migrate it to the brand new server how do i go for his migration this is my 1st migration for replication environment ....
October 12, 2008 at 9:17 am
I would imagine that it would be done the same way as they told me above.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2010 at 5:27 am
Hi jeff i know that is forum is too old but i want to know ur how did u migrated it to the new server
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply