locked
Migrate Mirror Database to New Server RRS feed

  • Question

  • Hi,

    I need to migrate my SQL server Mirror Database to a new server. my current setup is as below

    1. server A principal (192.168.1.100)

    2. server B Mirror     (192.168.1.200)

    now i have a new server (Server C) to replace server B as below

    1. server A principal (192.168.1.100)

    2. server C Mirror     (192.168.1.300)

     

    my question is how to migrate mirror db to new server without any affect or downtime on principal server, please share idea/experience for above.

    note: SQL SERVER 2008R2 EE(64BIT), Win2008R2 EE 64bit.

    Regard

    Faisal Wahid

    Tuesday, April 14, 2015 6:15 AM

Answers

All replies

  • IMHO you have options, no downtime to principle on any case:

    1) Break current db mirroring setup and create mirror to Server C. As mirroring works 1:1 relationship you will have to break current mirroring.

    2) Setup log shipping between Server A and Server C and once you are ready, break mirror(A-B) and configure ServerC as new mirror.

    https://simondrichards.wordpress.com/2012/07/05/switch-disaster-recovery-from-log-shipping-to-mirroring/

    I prefer option 2.
    Tuesday, April 14, 2015 6:26 AM
  • Thanks for your quick response.

    Is it possible something like in oracle (pardon as i am oracle DBA not sql server guy), 

    (Oracle Steps to migrate standby(mirror) db to new server)

    1. Pause replication on primary(principal) 

    2. Move standby(mirror) database files to new server(using copy/rman/etc).

    3. Update connection information (TNS in oracle) on primary(principal) to point new server.

    4. Start replication on primary(principal).

    the above steps will simply migrate the standby(mirror) database to new server,

    can we do something like above in sql server?

     

    Tuesday, April 14, 2015 6:48 AM
  • Oracle we can change in tns.ora and change parameters in primary and standby.

    SQL server we store instance information inside database. I dont think we have any option without drop and recreate mirroring.


    Tuesday, April 14, 2015 7:39 AM
  • As dave_gona has said, for mirroring in SQL Server there is no other option for moving mirror database to another server.

    You have to break mirroring first and then recreate mirroring with server c as mirror.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, April 14, 2015 8:23 AM
  • Hi  Faisal,

    There is no way in SQL SERVER to migrate mirroring like as Oracle.

    @ If your DB size is larger and takes long time to configure mirroring , you can configure log shipping as a  stand by high Availability until mirroring gets  configured.

    If you are using Clustering then without configuring Log shipping  you can also move forward  to configure mirroring.

    All Depends on your RPO and RTO.


    Regards Naveen MSSQL DBA


    • Edited by Naveen-DBA Tuesday, April 21, 2015 7:37 AM Added Imp.
    Tuesday, April 21, 2015 7:36 AM