July 31, 2014 at 12:04 pm
We have a SQL Server located in India; We need to move all the DB's on that server to a server residing locally for various legal reasons. The Server in India is a 2008 running 24 databases. 3 of these DB's are over 100GB and all 3 of them are being replicated from a third server in India. I am looking for high-level steps I will need to perform this so as to make sure I don't miss anything. Please advise, especially on problems I can expect to run into.
thanks!
July 31, 2014 at 12:10 pm
Back up databases
Script logins (with SIDs and passwords)
Script any linked servers
Script any jobs
Copy backups, restore on new server, run the scripts you created.
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
July 31, 2014 at 2:16 pm
Thanks for the response Gila. Any comments or experience on moving the files of VLDB's over the net? I am particularly concerned about the time it might take to copy the files over.
August 1, 2014 at 2:03 am
Use robocopy for transferring the backup files... Cant really comments on time taken to transfer... depends on your network speed
If you can use a compressed backup , even better...
Try breaking backup into multiple files instead of one big file..
Cheers
August 1, 2014 at 3:57 am
Like2SQL (7/31/2014)
We have a SQL Server located in India; We need to move all the DB's on that server to a server residing locally for various legal reasons. The Server in India is a 2008 running 24 databases. 3 of these DB's are over 100GB and all 3 of them are being replicated from a third server in India. I am looking for high-level steps I will need to perform this so as to make sure I don't miss anything. Please advise, especially on problems I can expect to run into.thanks!
What network connection facilities do you have between sites if any?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 1, 2014 at 6:21 am
Excellent. I was about to ask the same basic question.
Do I need to copy the system databases to the new location?:ermm:
August 1, 2014 at 7:07 am
Like2SQL (7/31/2014)
Any comments or experience on moving the files of VLDB's over the net?
Be patient, take a good book.
Or copy the files to an external drive and courier/drive it to the new location.
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
August 1, 2014 at 7:44 am
Or copy the files to an external drive and courier/drive it to the new location.
😀
It genuinely can be quicker to get on a plane with a hard drive yourself.
August 1, 2014 at 8:15 am
Beatrix Kiddo (8/1/2014)
Or copy the files to an external drive and courier/drive it to the new location.
😀
It genuinely can be quicker to get on a plane with a hard drive yourself.
I'm about to copy 25 terabytes worth of databases from DAS to SAN.
Gonna be a lot of pencils in the ceiling.
A lot of pencils.
:hehe:
August 1, 2014 at 11:07 am
you mentioned that 3 of databases are part of replication. then there may be a publisher and distributer.
when you move these databases then you need to generate new publications and then subscribe to these database.
restore MSDB backup ,all jobs will be restored.
August 3, 2014 at 1:19 am
Do a test transfer of a large file in advance to gauge how long the 100gb databases will take to transfer across the network.
If the speed is slow or you can't have much of an outage, you could transfer a full backup across to the new server a few days in advance and then transfer daily differentials and finally transaction logs backups to break up the process into smaller files.
August 6, 2014 at 11:36 am
Thanks for the responses guys. So far we have moved 8 of the smaller databases via simple backup and restore with no issues. However needed further guidance on the 3 VLDB's which are publishers in a replicated setup. Any advice on duplicating the replication will be appreciated. Best way to do this, gotchas etc. All 3 are publishers, 1 of them is using snapshot replication and the other 2 are using merge.
Thanks!
August 8, 2014 at 5:06 pm
Like2SQL (7/31/2014)
We have a SQL Server located in India; We need to move all the DB's on that server to a server residing locally for various legal reasons. The Server in India is a 2008 running 24 databases. 3 of these DB's are over 100GB and all 3 of them are being replicated from a third server in India. I am looking for high-level steps I will need to perform this so as to make sure I don't miss anything. Please advise, especially on problems I can expect to run into.thanks!
I did something similar when I was working for HP few years ago. I moved the backup file(s) over the network using robocopy from a server in China to one in USA. If I recall well, robocopy has a retry flag or command so it also helps during the process.
The whole process took almost a week though and I split the backup on several files. Using a single backup file won't work over a WAN.
Also, the speed and reliability of the WAN link is key. Otherwise, it may be better using old fashion methods like sending a backup tape via FedEx or similar services.
August 12, 2014 at 1:53 pm
Assuming its not a complicated database with any kind of encryption
1. Back up the database. Use native or third party compression tool
2. Transmit and apply the back up , any related login scripts, linked server, jobs et al.
3. Test the restored back up functionality et al
4. Daily or hourly restore of differential back up . (Log shipping can be set up also if you can make changes to ur server).
5. On the day of cut over,
a. Stop the Prod server.
b. Make sure there is no untransmitted records on the publisher to distributor/ distributor to subscriber
c. Stop all the replication jobs - Publisher/Distributor/Subscriber
e. Transmit and apply the final compressed backup on the ToBe server. Apply any changed logins/ et al.
f. Restore MSDB for all jobs
g. DNS changes.
h. Start all the replication jobs
The pain will be if replication errors out, the snaphot will take longer for the 100GB.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply