Unanswered: migrating principal and mirror on new physical servers
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.'
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.
Sounds like you may have to do this the old fashioned way. Basically rebuild the mirror on the new servers. Is your company accepting of the following?
1) Build first new (standby) server with SQL 2005
2) Break mirror to the old standby server
3) Rest names of standby servers
4) Recreate mirror to the new SQL 2005 (standby) server
5) Fail over to the new standby server
6) repeat for the primary server.
I am not sure I would look forward to that if the database was particularly large, as it involves copying backup files around twice. If everything is small enough, then it may not be as big a deal.
The other option would be something like brain transplant. you could set up the new server, shut down SQL Server on both the standby and the new server. rename the standby servers, then copy all of the database files (including master and msdb) to the new standby server (save the old ones of course), and see if that works better. Personally, I would not be altogether comfortable with this approach without a lot of testing.
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.