none
best way to migrate

    Question

  • Hi All,

    Assuming my database server is 24*7 database, what would be the best strategy to migrate my database server to new server with new hardware with minimal downtime?

    Thanks in advance.

    Wednesday, February 13, 2013 12:46 PM

Answers

  • For an absolute minimum of downtime, use Database Mirroring and the Rolling Upgrade procedure.

    If you can tolerate a little more downtime, just use backups instead Database Mirroring.  That procedure would look something like:  

    1) Take a Full backup on the old server

    2) Restore it WITH NORECOVERY on the new server

    3) Start Downtime

    4) Take a Differential backup or final log backup

    5) Restore the log backups or the differential backup on the new server and finally RECOVER the databse

    6) End downtime

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, February 13, 2013 1:06 PM

All replies

  • Hi All,

    Assuming my database server is 24*7 database, what would be the best strategy to migrate my database server to new server with new hardware with minimal downtime?

    I posted the same question under Database engine section without my knowledge. I apoligize.

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e53abc2a-c3fd-482a-bc9b-648c5311cc24


    Thanks in advance.

    Wednesday, February 13, 2013 12:49 PM
  • For an absolute minimum of downtime, use Database Mirroring and the Rolling Upgrade procedure.

    If you can tolerate a little more downtime, just use backups instead Database Mirroring.  That procedure would look something like:  

    1) Take a Full backup on the old server

    2) Restore it WITH NORECOVERY on the new server

    3) Start Downtime

    4) Take a Differential backup or final log backup

    5) Restore the log backups or the differential backup on the new server and finally RECOVER the databse

    6) End downtime

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, February 13, 2013 1:06 PM
  • Hello,

    This is a huge, broad, and very detail heavy topic! I will give you a few places to start looking to give you a start.

    1. Mirroring

    If you want to have a very quick migration with the assistance of your networking and windows teams, it would be possible to use mirroring. Setup a new server, restore the database from a backup, bring it up to speed with log backups and then setup mirroring to keep the mirror up to date. When the cutover time comes, change dns entries, application connection strings, use aliases, etc, break the mirror and recover on the new insstance. If everything goes well you're good if not, repoint dns, aliases, etc back to the original instance.

    The downfall to this is would be that synchronous mirroring would need to be used which will cause an amount of overhead that will affect your performanced to a degree.

    2. Replication

    Replication scenarios such as using transactional replication as a subscriber could work. honestly I don't use replication all that often so I am not as savvy here but it could work to keep a subscriber up to date and then once again, cut over and cleanup the replication.

    3. Log shipping

    While log shipping is somewhat slower, you can force jobs to run and basically have the same approach as mirroring but through the log shipping mechanism.

    There are more ways out there, but here are some of the main ones that I use or see others use.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, February 13, 2013 1:23 PM
    Answerer
  • Hello,

    The database mirroring procedure with rolling upgrade explained by David seems a good option.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Wednesday, February 13, 2013 1:51 PM
    Moderator
  • 3) Start Downtime

    Hallo David,

    I'm wondering why you would like to have a downtime? You don't need it with mirroring but - as Sean has written - it is a better approach to use dns-alias or sql alias. Than you can directly make the mirror the principal and change the sql / dns-alias.

    @Manu:

    Some important issues need to be taken into consideration BEFORE the move over:

    1. Before you decide for mirroring as moveing strategy implement a connection typ by using dns-alias or sql-alias
    A sql alias is the only usable solution if your sql server is a named instance!
    A dns alias is a better approach because you don't have to change the connection string at every machine which access the database

    If you need to use sql alias distribute the settings by a policy to all machines which access the database

    2. Implement mirroring on the second machine (see the step by step from David (without downtime :) ) or see how it is working here:
    http://msdn.microsoft.com/en-us/library/ms190941.aspx

    Please note the following IMPORTANT notes for mirroring:

    3. when mirroring is established you can failover and have to change the dns / sqlalias

    4. After the failover has occured and ALL machines connect to the new server you can remove the mirroring by using SSMS and the following command (on the "new" server):

    ALTER DATABASE yourdb SET PARTNER OFF;

    That's it and the downtime is max 5 - 10 min in a very large environment but a few seconds if only ONE dns-server is available.

    If you are using sql alias you have to distribute the registry settings again to each computer and than they can connect to the database. This could take more time as dns-alias distribution.


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Wednesday, February 13, 2013 2:23 PM
  • >>I'm wondering why you would like to have a downtime?

    If you use a Mirroring Failover you wouldn't have to.  If you are using backup/restore to move the database you need to start downtime before you take the final backup (log,differential, or full) that will bring the new database up to date.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, February 13, 2013 2:53 PM
  • Hi All,

    Thanks for prompt responses.

    Just wanted to know if incase if i multiple database's say 30 odd databases, is it essential to have mirroring all databases ? Please correct if i am wrong.

    What about detaching my Storage from old server and attach to new server with same name and IP address.. Does it work?? 

    Best Regards,

    Manu

    Wednesday, February 13, 2013 3:00 PM
  • >>I'm wondering why you would like to have a downtime?

    If you use a Mirroring Failover you wouldn't have to.  If you are using backup/restore to move the database you need to start downtime before you take the final backup (log,differential, or full) that will bring the new database up to date.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sorry David - that was a missunderstanding from me.

    I thought the downtime was in conjunction with mirroring :)


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, February 13, 2013 3:15 PM
  • Hi All,

    Thanks for prompt responses.

    Just wanted to know if incase if i multiple database's say 30 odd databases, is it essential to have mirroring all databases ? Please correct if i am wrong.

    What about detaching my Storage from old server and attach to new server with same name and IP address.. Does it work?? 

    Best Regards,

    Manu

    Hi Manu,

    you can mirror 30+ databases. No problem and the process is the same.
    If you would like to detach the storage you first have to detach the databases than add the storage to the new server and attach the databases. But this means a downtime for all clients.

    Have a look concerning attach and detach here:
    http://msdn.microsoft.com/en-us/library/ms187858.aspx


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, February 13, 2013 3:17 PM
  • Thanks Uwe. I will go through the mirroring document suggested by david and in case of any queries , i will get back.

    Thanks all very much for your valuable inputs.

    Wednesday, February 13, 2013 4:04 PM
  • Hello,

    If you are using 32-bit SQL Server I would NOT mirror 30 databases as there could be worker thread starvation or VAS issues. Max I would do on 32-bit is 8-10.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, February 13, 2013 4:34 PM
    Answerer
  • In opposite of the the statement of Sean I would do it with 30 database (if you have 32 bit systems) in a multi step process.

    Run 10 databases first and do a clean failover until the applications will run with the new environment.
    After 10 databases have been successfully moved to the new location break the mirror and move the next 10 databases  and so on.

    Another - for me - important issue is the recommendation from microsoft with less than 10 databases in case of a full HA environment which is not the situation you have...

    In case of mirroring more than 30 databases it "could" lead to a problem with "suspended" mirroring connections. But this is not critical because of the following reason:

    Let's say you have 30 databases in a mirror and for 20 of them you see the status "suspended"

    That only means that the data will not be transferred to the mirror - nothing else.
    If you move the 10 databases and than break the mirror the next databases will start transferring their data  - the status is "synchronizing". Wait with the failover until the status is "synchronized". Than you do the failover with these databases and break the mirror after successfull migration to the new system. Than the process will be repeated with the next databases.

    In case of a real HA-scenario I agree with Sean - than it can not be used!

    I had the same situation with a 64 bit system and 120 databases to move - the only thing you have to take care is the log because it will not be released until the status is "synchronized". Another option will be the increase of numbers of the worker threads but this will force a restart of sql server services. Get more details about mirroring and its limitations here:

    http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

    This article explains the number of worker threads required for database mirroring for each database and illustrates the observed performance of an application with many databases.

    From my personal point of view mirroring is the one and only possible solution which may provide a database migration without the minimal impact!


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, February 13, 2013 5:28 PM
  • Uwe,

    Exactly. I was advocating more of a stepped approach of a few (8-10) at a time rather than all at once. Per usual it depends on your scenario and situation.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, February 13, 2013 6:12 PM
    Answerer
  • Hi Rick and Sean thanks a lot for giving more insight to mirroring.

    In there is no database mirrorning implemented and i am using Standard SKU , i such a scenario what would be the best approach/steps to perform the migration to new server with same server name as in if i had to move the storage to new server which has master database which contains the server name , linked servers , job originating server , service master key and so on...  with minimal downtime ?

    Any stream lined steps performing smooth and fast migration with minimal downtime?

    Thursday, February 14, 2013 4:53 PM
  • You will need to prioritize.  Do you want a simple procedure, or do you want to minimize downtime. How much downtime can you get? 

    This article explains different approaches and tradeoffs pretty well.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 14, 2013 5:11 PM