migrating principal and mirror on new physical ser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

migrating principal and mirror on new physical ser

Author

Topic

zoobilizoo
Starting Member

3 Posts

Posted - 2011-05-06 : 09:15:17

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 10:01:35

I've followed the procedure listed below to move mirrored databases to new servers with no downtime:

SQL Server References & Abbreviations
CP - Current Principal  CM - Current Mirror   CW - Current Witness
NP - New Principal      NM - New Mirror       NW - New Witness

- Make full database backup on CP 
- Restore full backup to NP with NORECOVERY option
- Restore full backup to NM with NORECOVERY option
- Make log backup on CP
- Restore log backup to NP with NORECOVERY option
- Restore log backup to NM with NORECOVERY option 
- On CP, stop mirroring from CP to CM (ALTER DATABASE ? SET PARTNER OFF)
- On NP, start mirroring from NP to CP (ALTER DATABASE ? SET PARTNER='TCP://CP:9999')
- On CP, start mirroring from CP to NP (ALTER DATABASE ? SET PARTNER='TCP://NP:9999')
- Wait for mirror to synchronize
- On CP, fail over mirror from CP to NP (ALTER DATABASE ? SET PARTNER FAILOVER)
- Wait for mirror to complete failover & synchronize
- On NP, stop mirroring from NP to CP (ALTER DATABASE ? SET PARTNER OFF)
- Make log backup on NP
- Restore log backup on NM with NORECOVERY option
- On NM, start mirroring from NM to NP (ALTER DATABASE ? SET PARTNER='TCP://NP:9999')
- On NP, start mirroring from NP to NM (ALTER DATABASE ? SET PARTNER='TCP://NM:9999')
- On NP, add witness between NP and NW (ALTER DATABASE ? SET WITNESS='TCP://NW:9999')

You'd have to replace "?" with the name of your database(s), and CP, CM, NP, NM with your actual server names, but the outline is sound. I believe the error you received is due to the fact that mirroring was still enabled on the existing principal even though it was not connected. You'll have to run ALTER DATABASE...SET PARTNER OFF on the principal before you can engage the new mirror.

zoobilizoo
Starting Member

3 Posts

Posted - 2011-05-14 : 07:33:00

hi

Thanks for your reply. In the end, I installed a seperate set of mirrored servers/databases and restored the db there. I then copied whatever was not inserted during the db migration into the new db manually. While your suggestion was interesting, my deadline and the complexity the proposed solution involved (when the live applications using the sytem were also included) became forbidding.

Lesson: it is easier to install a new system than to migrate an existing one, as far as SQL server mirroring is concerned.

Thanks again.

Michelle.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-14 : 08:20:03

quote:
Lesson: it is easier to install a new system than to migrate an existing one, as far as SQL server mirroring is concerned.

I wouldn't agree with that, in fact my experience has been that mirroring makes migrating to new hardware incredibly easy compared to the alternatives. Don't give up on it, it's worth the effort.

zoobilizoo
Starting Member

3 Posts

Posted - 2011-05-16 : 10:23:30

Thanks for encouragement. I'll perhaps try it in my spare time ;-) Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources