Switch roles in DB mirroring | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Switch roles in DB mirroring

I am testing SQL 2K5 sp1 and testing the DB mirroring without a witness server. I have done the testing if the primary failed, then the mirror will become primary. I didn’t find any instructions on how to do if the original primary is back on line, how do I switch the mirror server(new primary) back to the health original primary server. Please advice. wingman
We are running mirroring with no witness, in high-performace mode. This is how we do it: When you bring the primary back on line, it should return to mirroring in the mirror role, in a suspended state. This is done to allow you to restore backups or stop mirroring if need be.
What we do is bring the primary back on line – wait for it to connect and change itself to the mirror. Then we resume mirroring: ALTER DATABASE <database_name> SET PARTNER RESUME This allows any changes made on the backup server (which has now become the primary) to move over to the production server(the old primary). Wait until the databases are synchronized, and then switch roles to return to normal mirroring operation. To switch roles: Stop the SQL server service on the recovery server to disconnect it from the mirroring partner. Run the following command on the production server to force it back in to the principal role:
ALTER DATABASE <database name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS Bring the recovery server#%92s SQL service back online. This will automatically change the role of the recovery server to the mirror. Next, resume mirroring with the following command: ALTER DATABASE <database_name> SET PARTNER RESUME
Note that forcing service has the potential for some data loss – you should investigate and see if this possibility is acceptable. I hope this helps. -Greg
One more thing when there is not witness you can’t do auto failover. Mohammed.
When you use database mirroring in high-availability mode, the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement. Although mirroring has acted correctly, not having access may surprise customers, because with an ordinary stand-alone computer, the database would still be served. To understand how the system works without the option of automatically failing over, try using database mirroring without a witness for a time.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.