Post reply

migrating principal and mirror on new physical servers

  • May 6, 2011 at 7:35 am

    #389463

    hello

    i have 2 SQL 2005 servers which are mirrored and a witness to provide High Safety With Automatic Failover (synchronous). I am using certificates.

    server A - Principal

    server B - Mirror

    server C - Witness

    All have SQL Server 2005 with Service Pack 4 installed on Win2003 Server Std Ed SP2.

    The current Principal and Mirror servers need to be replaced with new physical ones. Ideally, it should be done without breaking the mirroring because it is a critical setup used 24/7.

    My idea was to configure 2 new servers exactly as the current Principal and Mirror and to swap the new servers with the older ones, one at a time i.e. replace server B with the new server.

    Then promote server B to principal and replace server A with the other new server.

    Hence I configured new physical servers as:

    server D - new Principal

    server E - new Mirror

    I started with server E.

    Server names, SQL Instance name, IP addreses were kept exactly the same as server B. Certificates, logins & endpoints were kept identical.

    IP addresses and hostnames of each server were also added in c:\windows\system32\drivers\etc\hosts.

    The same database and transaction log which were restored with NO RECOVERY on server B was restored with NO RECOVERY on server E.

    When I disconnect the Mirror (server B) from the network, the database on the Principal (server A) goes into 'synchronised/disconnected' mode.

    Database on Server E is 'In Recovery' mode. When I plug in server E, server A stays in 'synchronised/disconnected' and server E 'In Recovery' mode even though I restart services, reboot all 3 servers, try to run the 'GRANT CONNECT ON ENDPOINT...' & '...SET PARTNER...' commands again.

    When I run :

    ALTER DATABASE dbtest SET PARTNER = 'TCP://SERVERB:9999';

    on the Principal and :

    ALTER DATABASE dbtest SET PARTNER = 'TCP://SERVERA:9999';

    on the new Mirror, I get the error 'The database "dbtest" is already enabled for database mirroring.'

    Questions:

    1. Will this method work ?

    2. Is there anything I am doing wrong ?

    3. Is there a better way to do this ?

    Note: if I remove server E and put back server B, it works again.

    Thanks beforehand to anyone who can help me out.

    Michelle

  • May 16, 2011 at 6:40 am

    #1324994

    This is a very good scenario. But, have you tried this.

    Take backup of db in server A - Principal and restore it on server E - new Mirror with no recovery option?

    M&M

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply