none
migrate 1 TB database RRS feed

  • Question

  • How to migrate 1 TB database from one server to another with minimal or no downtime

    Kiran

    Monday, August 19, 2019 12:01 PM

Answers

All replies

  • 1. Backup & restore the database and keep the DB on old Server running. Run a diff backup and take database in single user mode; restore the diff backup on new Server and config your apps to use that oen

    2. Log shipping: https://www.mssqltips.com/sqlservertip/2073/migrating-a-vldb-in-sql-server-with-log-shipping/


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 19, 2019 12:59 PM
    Moderator
  • Backing up a 1 TB database and restoring it will take long time. Any other way?

    Kiran

    Monday, August 19, 2019 1:22 PM
  • You may find that you might need to disable log backups after the diff is done, then after you restore the full and the differential on the destination database with no recovery, you do a tail log backup on the source server, copy and restore it to the destination server. 

    You do not necessarily need to use the log shipping wizard for this.

    Monday, August 19, 2019 1:41 PM
    Moderator
  • This is the fastest option available to you. If you are using the enterprise edition of SQL Server 2016 you might want to add the server to an AG and use automatic seeding. This would require an outage as you create your windows cluster, and add the high availability option to your SQL Servers.
    Monday, August 19, 2019 1:42 PM
    Moderator
  • Yes, the full backup will take some time, but that doesn't matter, the final diff backup will be fast done and applied to new SQL Server. 

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 19, 2019 2:43 PM
    Moderator
  • Hello Friend

    The best way is to do this by adding the server to an AG and using automatic seeding.
    If you find it too complicated, make a Full backup, send and keep the other base updated through LogShipping. In this case, you may have something unavailable.

    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    Monday, August 19, 2019 7:52 PM
  • Keep in mind that with automatic seeding you will need to install windows clustering services, add the high availability feature to both of your servers. This will require a restart of both SQL Server services. This may not be acceptable. 
    Monday, August 19, 2019 8:02 PM
    Moderator
  • Keep in mind that with automatic seeding you will need to install windows clustering services, add the high availability feature to both of your servers. This will require a restart of both SQL Server services. This may not be acceptable. 

    Yeah, creating an AG only to migrate a database appears like a big overkill to me.

    No matter how you do, there is 1TB of data that has to be moved. BACKUP/RESTORE is a good option, since the initial operation - which is the one that takes time - can be done will the system is up and running.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 19, 2019 9:54 PM
    Moderator
  • Hi juniorkiran,

     

    Log shipping may be the best solution. Backup and restore may take a lot of time. Use alwayson, as Hilary said, you need to restart sql server when you enable this feature, and you also need to configure a WSFC. Compared with log shipping , it is more difficult to configure it.  Olaf has provided a very good link for your reference. And you can also refer to https://blogs.msdn.microsoft.com/sqlgardner/2011/09/16/minimizing-db-migration-downtime-using-log-shipping/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, August 20, 2019 9:19 AM