locked
DB mirroring migration to a new SQL server hardware.. RRS feed

  • Question

  • I need to migrate my current SQL server running DB mirror to a new SQL hardware.

    My plan is not to have any down time and at the same time since most application is connecting to both the principal and mirror, i intend to use the same server name and IP.

    Current setup
    Principal: ServerA (10.10.10.10)

    Mirror: ServerB (10.10.10.11)

    Withness: None

    My proposal:

    Build a new serverA naming it ServerC (10.10.10.12)

    Build a new serverB naming it ServerD (10.10.10.13)

    Step 2:

    Pause the original serverA

    Disconnect the SAN attached to serverB and reattached to ServerD and rename the serverD to ServerB and change the IP to (10.10.10.11).

    Resume the mirror.

    Step 3:

    Fail over the mirror to serverB (making the new mirrored server the principal)

    Pause the original serverB which is now the pricipal

    Disconnect the SAN attached to serverA and reattached to ServerC and rename the serverC to ServerA and change the IP to (10.10.10.10).

    Resume the mirror.

    Fail over back to server A

    Question:

    I know this is a very unique case. has anyone done this before and if yes, what are the recommended soluton?

    Thanks

    OSac


    Datawarehouse lead Architect

    Friday, May 25, 2012 2:17 PM

Answers

  • Hi Sonnie Avenbuan,

    >> Disconnect the SAN attached to serverB and reattached to ServerD and rename the serverD to ServerB and change the IP to (10.10.10.11).

    Regarding to your description, you may not allow to rename the Server name D to B and its IP address because the original Server B is still online. You need to remove the original mirror Server B first and then you can rename the Server D and change IP address.

    >> Fail over the mirror to serverB (making the new mirrored server the principal)

    Regarding to your description, seems you tried to failover the mirror to new Server B (original Server D) based on the server name. If so, I am afraid it would fail, you need to create the mirror database Server B again and then you can failover to new server B.
    For how to Setting Up Database Mirroring (SQL Server), please refer to here.

    According to your purpose, my suggestion is:
    1. Remove the mirror database Server B first, and then you can create the new Server B and use the set IP address (10.10.10.11)
    2. Create the mirror database Server C for Principal Server A. After data Sync finish, you can failover to Server C.
    3. Pause Server C (usually around midnight for minimize downtime) to rename Server C to Server A and change IP.  For how to minimize downtime with DB mirroring please refer to here
    4. Last you need to create new Server B as mirror database for new Server A.

    Hope it would be useful for you.


    Regards,
    Amber zhang
    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    • Edited by amber zhang Monday, May 28, 2012 6:57 AM
    • Marked as answer by amber zhang Friday, June 1, 2012 9:00 AM
    Monday, May 28, 2012 6:57 AM
  • Hi!

    I can't follow Amber completely with this...

    Yes, of course you need to shutodwn serverB before you rename serverD, otherwise you will end up with a naming conflict. (I assume you know that...) The question that comes in mind for me is: Do you run your system DBs (especially master and msdb) also on the SAN? If so your stunt might actually work. If your system DBs are on a different storage you will have problems in many ways. (e.g. How to attach the mirrored DBs without forcing them into recovery. Also you would then need to take care of all the permissions, logins, jobs, endpoints, etc. that you have on your old system.)

    In general the idea you have is doable, although I wouldn't do it... clean way would be to kill ServerB, rename/reinstall ServerD, restore the DBs and recreate the mirror. Slight hint on that: DO NOT remove the mirror until right before you recreate it. Otherwise your applications will have problems with the connections.

    Lucifer

    • Marked as answer by amber zhang Friday, June 1, 2012 9:00 AM
    Tuesday, May 29, 2012 6:45 AM

All replies

  • Hi Sonnie Avenbuan,

    >> Disconnect the SAN attached to serverB and reattached to ServerD and rename the serverD to ServerB and change the IP to (10.10.10.11).

    Regarding to your description, you may not allow to rename the Server name D to B and its IP address because the original Server B is still online. You need to remove the original mirror Server B first and then you can rename the Server D and change IP address.

    >> Fail over the mirror to serverB (making the new mirrored server the principal)

    Regarding to your description, seems you tried to failover the mirror to new Server B (original Server D) based on the server name. If so, I am afraid it would fail, you need to create the mirror database Server B again and then you can failover to new server B.
    For how to Setting Up Database Mirroring (SQL Server), please refer to here.

    According to your purpose, my suggestion is:
    1. Remove the mirror database Server B first, and then you can create the new Server B and use the set IP address (10.10.10.11)
    2. Create the mirror database Server C for Principal Server A. After data Sync finish, you can failover to Server C.
    3. Pause Server C (usually around midnight for minimize downtime) to rename Server C to Server A and change IP.  For how to minimize downtime with DB mirroring please refer to here
    4. Last you need to create new Server B as mirror database for new Server A.

    Hope it would be useful for you.


    Regards,
    Amber zhang
    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    • Edited by amber zhang Monday, May 28, 2012 6:57 AM
    • Marked as answer by amber zhang Friday, June 1, 2012 9:00 AM
    Monday, May 28, 2012 6:57 AM
  • Hi!

    I can't follow Amber completely with this...

    Yes, of course you need to shutodwn serverB before you rename serverD, otherwise you will end up with a naming conflict. (I assume you know that...) The question that comes in mind for me is: Do you run your system DBs (especially master and msdb) also on the SAN? If so your stunt might actually work. If your system DBs are on a different storage you will have problems in many ways. (e.g. How to attach the mirrored DBs without forcing them into recovery. Also you would then need to take care of all the permissions, logins, jobs, endpoints, etc. that you have on your old system.)

    In general the idea you have is doable, although I wouldn't do it... clean way would be to kill ServerB, rename/reinstall ServerD, restore the DBs and recreate the mirror. Slight hint on that: DO NOT remove the mirror until right before you recreate it. Otherwise your applications will have problems with the connections.

    Lucifer

    • Marked as answer by amber zhang Friday, June 1, 2012 9:00 AM
    Tuesday, May 29, 2012 6:45 AM
  • Thank you Amber and Prince for your inputs and contributions.

    During the course of my evaluation, I have come up with the following new proposed approach due to the current server settings:

    Current setup
    Principal: ServerA (10.10.10.10) ==>SQL 2005 Ent. Build 3282 (SP2 Cumulative Update 9)

    System Db on C:\ drive

    userDb on S:\ drive (SAN drive)

    Mirror: ServerB (10.10.10.11) ==>SQL 2005 Ent. Build 3282 (SP2 Cumulative Update 9)

    System Db on C:\ drive

    userDb on S:\ drive (SAN drive)

    Withness: None

    proposal setup:

    Build a new serverA naming it ServerC (10.10.10.12) èSQL 2008 R2

    Build a new serverB naming it ServerD (10.10.10.13) èSQL 2008 R2

    Step1: ( Upgrade current setup to SQL 2008 R2)

    1. Upgrade mirror server  ServerB (10.10.10.11) to SQL 2008R2
    2. Manually failover to Upgraded mirror server  ( B becomes principal and A becomes Mirror)
    3. Run DBCC CheckDb on ServerB (10.10.10.11)- Now principal
    4. Resume Mirroring

    Step2: Migrate the mirror to a new hardware  è (Goal is to retain the old name and IP for global application already connecting)

    1. Build a new serverA naming it ServerC (10.10.10.12) èSQL 2008 R2
    2.  Build a new serverB naming it ServerD (10.10.10.13) èSQL 2008 R2
    The proposed solution is to  use part of amber and prince  suggestions as follows: 1. Remove the mirror database Server B first, and then you can create the new Server B and use the set IP address (10.10.10.11)
    2. Create the mirror database Server B for Principal Server A. After data Sync finish, you can failover to Server B.
    3. Remove the mirror database Server A and then create the new Server A and use the set IP address (10.10.10.10)

    4. Create the mirror database Server A for Principal Server B. After data Sync finish, you can failover to Server A.

    This way, II won’t have to worry about taking a copy of the master and try to restore to the new server.

    Question:?

    Problem #1: When I ran SQL server 2008 Upgrade Advisor on ServerB (10.10.10.11), I get error     read-only database cannot be upgraded”

    Question: Will this be an issue during upgrade?

    Problem #2: Is this a viable solution based on experience?

    Thanks for your help.

    Sonn


    Datawarehouse lead Architect

    Tuesday, June 12, 2012 2:35 PM
  • I have a solution :

    1. backup the principal database (server A)  including full, differential (optional) and transaction logs backup. 

    2. copy the backup to the Server C and the Server D.

    3.restore all the backup using "with norecovery" at the Server C and Server D.

    4.shutdown all the application which are connected to Server A, and backup the last transaction log.

    5.copy the last transaction log to Server C and Server D.

    6.restore the last transaction log with recovery at the Server C

    7.restore the last transaction log with norecovery at the Server D

    8.Shutdown the Server A and the Server B, rename the Server C to the Server A, the Server D to the Server B.

    9.set the partner to the new Server A at the new Server B, and set the partner to the new Server B at the Server A. Now they are new mirror databases.

    What do you think?


    Sunday, July 22, 2012 8:23 AM