How to FAILOVER databases in MIRRORING?

When our requirement is high data availability & Automatic failover is required in case of any disaster then we can opt for Mirroring of databases. In mirroring we need to maintain a separate WITNESS server if we want to AUTO-FAILOVER databases in case of any disaster. Without a WITNESS server also mirroring can be configured but in such case mirrored databases will not auto failover during disaster, DBA’s need to manually fail over the databases.

Below picture shows mirroring configured between Server A & Server B, Server C is configured as WITNESS server. So this witness server continuously monitors the health/availability of the Server A (i.e.., Main Server) and whenever the database of Server A becomes un-available the witness server will make the database of server B (secondary server) as the principal server.

In mirroring the database of main server is called PRINCIPAL Database & the secondary server database is called Mirrored database. This mirrored database will be in restoring state until principal database is available. Whatever the changes made to the Principal database will automatically move to the mirror database.

If we configured mirroring without a WITNESS server when PRINCIPAL database becomes un-available then in order to FAILOVER we can user below command:

ALTER DATABASE <Database Name>

SET PARTNER FAILOVER;

Or

we can failover through GUI by clicking on ‘Failover’ tab which is in properties of database -> click on Mirroring -> click on Failover.

The below tables are important for getting details of Mirroring.

select * from sys.database_mirroring_witnesses;

Go

select * from sys.database_mirroring_endpoints;

Go

select * from sys.database_mirroring;

‘mirroring_role_desc’ column in sys.database_mirroring table will give the type of the server either Witness, Mirror or Principal.

‘mirroring_state_desc’ column of sys.database_mirroring table will have details weather Mirroring is synchronized or disconnected.

Note: While considering any high data availability features we need to take into consideration many things like space requirements, network traffic, cost of maintenance & so on. Based on our server set up we need to select the best suitable one.

Thanks!!!