My previous blog, Caching in Entity Framework using Query Notifications, is about how to implement caching based on Query Notification using Entity Framework and LinqToCache. I mentioned that a query notification SqlDependency was not able to detect changes after a database mirroring failover. To resolve this problem, SqlDependency need to recreated on the new principal database. To achieve this, we need to know when a failover occurs. In this blog, I'll provide three approaches to detect database mirroring failover.
Approach 1 (Recommended) - Get and Compare the DataSource from an Opened Database Connection
When connect to a mirrored database, we specify failover partner in the connection string like this:
"Data Source=MyDBServer1;Failover Partner=MyDBServer2;Initial Catalog=MyDatabase;Integrated Security=True;MultipleActiveResultSets=True"
The current server name can be retrieved via the DataSource property of an opened SqlConnection object (more details can be found in this article: Database Mirroring in SQL Server (ADO.NET)). By storing the value in a static variable and comparing with new value, we can determine if the current server is changed (in another word, if there is a failover).
When and how to get an opened connection depends on the application itself. If Entity Framework (EF) is used, the SqlConnection can be retrieved via the Connection property of an ObjectContext object. By default, EF control when to create, open and close database connections. You may need to open a connection manually as described in this article: Managing Connections and Transactions.
Approach 2 - Event Notification and Service Broker
We expect to get an event when there is a database mirroring failover. SQL Server does provide these events. Here is the details: Database Mirroring State Change Event Class. We can create event notification for states: 7 - Manual Failover and 8 - Automatic Failover:
create event notification failover
on server
for database_mirroring_state_change
to service 'myservicename', 'current database';
Then use WAITFOR(RECEIVE...) command to periodically check the notifications. More details can be found at here.
This approach need Service Broker programming skills. If the principal database/server crashes, WAITFOR(RECEIVE...) command needs to be executed on the new principal database to get the event - there should be an event message for the failover after the mirror database becomes principal.
Approach 3 - System Stored Procedures
We can execute sp_dbmmonitorresults periodically on msdb database on one or both database servers to get the status.
The drawback of this approach is that the stored procedure requires membership in the sysadmin fixed server role or in the dbm_monitor fixed database role in the msdb.
Posts
