MS SQL – Failover a SQL Mirrored Database
Following is a short step by step procedure for failing over a MS SQL Mirrored Database:
We are assuming the 1st SQL server is no longer responding and flipping the databases to be active on the 2nd SQL server.
1.) Obviously, log in with admin rights to the SQL Enterprise Management Studio
2.) We should see that the databases are in a “Mirror” state and synchronised while trying to restore a connection to the “Principal” databases
3.) The mirroring happens at a database level and by selecting the “Database Properties” from a right click we can then initiate a failover from the “Mirroring” Property
NB: It is important to take note of the current mirrored database state before failing over. As shown in the screenshot the mirrored database status should show as fully synchronised to avoid any data inconsistencies when restoring the service.
4.) The clicking the Failover action button from the database properties the mirrored database should now show as the “Principal” and disconnected since the SQL 1 server is no longer available.
5.) When the SQL 1 server returns online then we should see this status change to “Synchronised” on the SQL 2 server as shown.
6.) The foregoing process needs repeated for each database you wish to failover.
7.) Finally, you can repeated the steps above to fail back to SQL 1 server when service has been restored. This time you would use the SQL Enterprise Management Studio on SQL 1 server.
Tags: database, DR, ECM, HA, MSSQL
You can comment below, or link to this permanent URL from your own site.
