locked
SQL Server Database Migration RRS feed

  • Question

  • We have a project where we have to migrate our SQL Server to a new, remote SQL Server. We have been purchased by another organization and we need to move our SQL Server and data and objects from our site to the new site. And it is across the country.

    Has anyone ever done anything like this before? Perhaps there are some central tools and repository available that would allow us to turn off at our site, load the SQL Server on the West Coast with our data and objects, and over a weekend of time. This is a large migration...millions and millions of rows. So we need quick access speed in order to extract and load. It would have to be done over a weekend in order not to disrupt the business and be as transparent as possible. And we deal with dozens of third parties that rely on our data extracts for data.

    I'm sorry if this is not in the correct forum. And I appreciate your review and am hopeful for a reply.

    Wednesday, September 21, 2016 8:41 PM

Answers

  • You need to plan it well in advance to have smooth migration without disrupting business. part of plan (Very High Level), you want to ship full backup to target location first and finish successful restore of full backup with "No Recovery". Once that is done, then start pushing incremental backup and continue restoring that with "No Recovery", Finally on cutover weekend, you will have only latest changes (using either transaction backup or combination with incremental and transaction) to transfer which should not take much long and you will be able to finish it and then point your application accordingly that way you should be able to achieve it within your maintenance window. 

    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.


    Friday, September 23, 2016 5:18 PM
  • Hi ITBobbyP,

    Without knowing database size and your schedule, it’s hard to give specific suggestions. But since you say it’s a large migration and it’s across the country, it’s unlikely you could do a regular backup/restore within your maintenance window(weekend). In this case, I would suggest you first establish log shipping between local SQL Server and remote SQL Server so you could have a copy of your database on remote site, then migrate other database objects such as Logins and SQL Agent Jobs. With that, you will have enough time to run tests on remote site. After you finished all the test and migration, you could redirect your clients to the remote SQL Server at anytime.

    Here’re some articles for your reference.(1, 2)

    If you have any other questions, please let me know.

    Regards,
    Lin

    Thursday, September 22, 2016 6:09 AM
  • If you are going offline for the weekend a backup and restore should work. You might be able to bcp the data into the file system and then bcp it back in on the destination server.

    This would involve moving files using a copy over your wan, or using fedex  - which depending on the size of the database, could be faster.

    Log shipping today (which will require you to send a backup) will allow you to do this more leisurely, so if you start to log ship today, and then next week you cutover - you will find your cutover will take minutes.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Saturday, September 24, 2016 9:05 PM
    Answerer

All replies

  • Hi ITBobbyP,

    Without knowing database size and your schedule, it’s hard to give specific suggestions. But since you say it’s a large migration and it’s across the country, it’s unlikely you could do a regular backup/restore within your maintenance window(weekend). In this case, I would suggest you first establish log shipping between local SQL Server and remote SQL Server so you could have a copy of your database on remote site, then migrate other database objects such as Logins and SQL Agent Jobs. With that, you will have enough time to run tests on remote site. After you finished all the test and migration, you could redirect your clients to the remote SQL Server at anytime.

    Here’re some articles for your reference.(1, 2)

    If you have any other questions, please let me know.

    Regards,
    Lin

    Thursday, September 22, 2016 6:09 AM
  • You need to plan it well in advance to have smooth migration without disrupting business. part of plan (Very High Level), you want to ship full backup to target location first and finish successful restore of full backup with "No Recovery". Once that is done, then start pushing incremental backup and continue restoring that with "No Recovery", Finally on cutover weekend, you will have only latest changes (using either transaction backup or combination with incremental and transaction) to transfer which should not take much long and you will be able to finish it and then point your application accordingly that way you should be able to achieve it within your maintenance window. 

    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.


    Friday, September 23, 2016 5:18 PM
  • If you are going offline for the weekend a backup and restore should work. You might be able to bcp the data into the file system and then bcp it back in on the destination server.

    This would involve moving files using a copy over your wan, or using fedex  - which depending on the size of the database, could be faster.

    Log shipping today (which will require you to send a backup) will allow you to do this more leisurely, so if you start to log ship today, and then next week you cutover - you will find your cutover will take minutes.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Saturday, September 24, 2016 9:05 PM
    Answerer