locked
SQL Server Migration RRS feed

  • Question

  • Hi,

    We are planning to move our data-center from one location to another location. As per of this how to move all sql server without/minimal downtime.

    At the same time we are planning to migrate all our sql server from 2005/2008 r2 to 2012 (Which contains Mirroring/Logshipping/Replication)

    Planned to implement Always on Availability instead of mirroring.

    There are 52 SQL servers about to migrate. Database size from 120 GB to 2 TB.


    • Edited by VijayKSQL Tuesday, March 25, 2014 2:53 PM add
    Tuesday, March 25, 2014 2:50 PM

Answers

  • Providing complete and absolute answer to your question would be difficult .Lot will depend on how competent you are with SQL server technology

    Migration of Data Center with SQL Server upgrade and that too having log shipping and mirroring would require a lot of work to be done from scratch.

    Generally data center migration is moving Machines and SAN from one location to other.Now during this period you cannot access SQL server so prepare for downtime first.

    >>from one location to another location. As per of this how to move all sql server without/minimal downtime

    I guess you mean here minimal downtime.

    problem with DC migration is not Sql Server but windows server not coming online.If windows come online SQL Server will do unless you change domain or some files are moved.

    After DC is up.Then prepare for SQL Server migration you can search not net for migration docs you will find lot of them.IMO you should take help of competent DBA (not required if you are one).


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Proposed as answer by Sofiya Li Wednesday, March 26, 2014 9:01 AM
    • Edited by Shanky_621MVP Saturday, March 29, 2014 3:54 PM
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, March 25, 2014 2:57 PM
  • Hi,

    Actually my plan is we are not going to move any existing hardware to new DC. Because it is pretty old.

    We have already purchased new Hardware at New DC and installed all server with Windows 2012 and SQL 2012 with all updates.

    Now the pending task is how to migrate things from sql point of you. like how to migrate logshipping/mirroring/replication.

    Once the migration is success we will decommission old data center.

    Well that's good

    Your best Bet would be migration of databases using Backup and restore method.This will seriously avoid you lot of downtime.

    Migrate logins using script method or you can use below link

    http://support.microsoft.com/kb/918992/en-gb

    Migrate jobs using below script method or you can use SSIS also

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    If you have SSIS packages you need to migrate that as well.

    regarding configuring Always ON that would be good idea and you need to configure from scratch as this was new feature introduced in 2012.

    If you have migrated all your SQL server to 2012 then you need to configure Logshiping again from beginning.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Proposed as answer by Sofiya Li Wednesday, March 26, 2014 9:01 AM
    • Edited by Shanky_621MVP Saturday, March 29, 2014 3:56 PM
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, March 25, 2014 4:03 PM
  • Hi,

    Thank you very much, then how to restore delta data?


    Hi S.Vijay Kumar,

    I’m writing to follow up with you on this post. I will mark Shanky’s post as answer for the original question about migrating SQL Server. For the questions " how to restore delta data", I suggest you post another thread, so that more forum members can involve into the thread and help you out.

    In addition, we need to verify if the delta data means that you do differential backups in SQL Server, then restore these data. If yes, you can refer to the following article about different ways to restore a SQL Server database.
    http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/

    If you use DB2, and restore from delta backups, you can review the following article.
    http://documents.software.dell.com/DOC109384

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, April 1, 2014 4:06 AM

All replies

  • Providing complete and absolute answer to your question would be difficult .Lot will depend on how competent you are with SQL server technology

    Migration of Data Center with SQL Server upgrade and that too having log shipping and mirroring would require a lot of work to be done from scratch.

    Generally data center migration is moving Machines and SAN from one location to other.Now during this period you cannot access SQL server so prepare for downtime first.

    >>from one location to another location. As per of this how to move all sql server without/minimal downtime

    I guess you mean here minimal downtime.

    problem with DC migration is not Sql Server but windows server not coming online.If windows come online SQL Server will do unless you change domain or some files are moved.

    After DC is up.Then prepare for SQL Server migration you can search not net for migration docs you will find lot of them.IMO you should take help of competent DBA (not required if you are one).


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Proposed as answer by Sofiya Li Wednesday, March 26, 2014 9:01 AM
    • Edited by Shanky_621MVP Saturday, March 29, 2014 3:54 PM
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, March 25, 2014 2:57 PM
  • Hi,

    Actually my plan is we are not going to move any existing hardware to new DC. Because it is pretty old.

    We have already purchased new Hardware at New DC and installed all server with Windows 2012 and SQL 2012 with all updates.

    Now the pending task is how to migrate things from sql point of you. like how to migrate logshipping/mirroring/replication.

    Once the migration is success we will decommission old data center.

    Tuesday, March 25, 2014 3:55 PM
  • Hi,

    Actually my plan is we are not going to move any existing hardware to new DC. Because it is pretty old.

    We have already purchased new Hardware at New DC and installed all server with Windows 2012 and SQL 2012 with all updates.

    Now the pending task is how to migrate things from sql point of you. like how to migrate logshipping/mirroring/replication.

    Once the migration is success we will decommission old data center.

    Well that's good

    Your best Bet would be migration of databases using Backup and restore method.This will seriously avoid you lot of downtime.

    Migrate logins using script method or you can use below link

    http://support.microsoft.com/kb/918992/en-gb

    Migrate jobs using below script method or you can use SSIS also

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    If you have SSIS packages you need to migrate that as well.

    regarding configuring Always ON that would be good idea and you need to configure from scratch as this was new feature introduced in 2012.

    If you have migrated all your SQL server to 2012 then you need to configure Logshiping again from beginning.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Proposed as answer by Sofiya Li Wednesday, March 26, 2014 9:01 AM
    • Edited by Shanky_621MVP Saturday, March 29, 2014 3:56 PM
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, March 25, 2014 4:03 PM
  • Hi,

    Thank you very much, then how to restore delta data?

    Thursday, March 27, 2014 2:49 PM
  • Hi,

    Thank you very much, then how to restore delta data?


    Hi S.Vijay Kumar,

    I’m writing to follow up with you on this post. I will mark Shanky’s post as answer for the original question about migrating SQL Server. For the questions " how to restore delta data", I suggest you post another thread, so that more forum members can involve into the thread and help you out.

    In addition, we need to verify if the delta data means that you do differential backups in SQL Server, then restore these data. If yes, you can refer to the following article about different ways to restore a SQL Server database.
    http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/

    If you use DB2, and restore from delta backups, you can review the following article.
    http://documents.software.dell.com/DOC109384

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 1:23 AM
    Tuesday, April 1, 2014 4:06 AM