Problem: Automatic failover is supported in high-safety database mirroring mode but clients not utilizing SQL Native Client OLEDB Provider / ADO.NET 2.0 cannot connect to the new principal database automatically upon role mirroring role change.
- Create new Alias in SQL Client configuration and use Alias name instead of server name in client’s connection settings
- Write a utility program to update the Alias’s upon mirroring role switch
- The Alias update can be done by modifying the corresponding string value under registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
- Create new DNS CNAME / A record pointing to active database server
- Use the “virtual” name in client’s connection settings
- Write a utility program to 1) update DNS record and 2) flush DNS cache on client machine, upon mirroring role switch
[Remarks: I only implemented Solution 1 to avoid all the security considerations and the potential need to have separated processes to update DNS on server and flush the cache on client. See DNS APIs at http://msdn.microsoft.com/en-us/library/ms682058(VS.85).aspx ]
To detect the mirroring role switch:
Method 1: By monitoring SQL Server WMI Events
- Create WMI event Alert in SQL Server Agent. See “Alerting on Database Mirroring Events” – http://msdn.microsoft.com/en-us/library/cc966392.aspx
- WMI Namespace: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
- Query: SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8
- State 7 = Manual failover, 8 = Automatic failover
- Automatic failover event occurs on the mirror server instance (i.e. the loser)
- See “Database Mirroring State Change Event Class” in SQL Books Online – http://msdn.microsoft.com/en-us/library/ms191502(SQL.90).aspx
[Remarks: Things are a bit tricky as the “loser” has to register the SQL Alias / DNS for the new principal database. Also the SQL Agent Alerts is usually installed on the SQL Server, making it hard to do something on the client machine.]
Method 2: By polling the principal database
- SQL Native Client / ADO.NET 2.0 can utilize the “Failover Partner=” parameter in connection string.
- e.g. Server=SQL01; Failover Partner=SQL02; Initial Catalog=AdventureWorks;Integrated Security=True
- With .NET 2.0, the active principal database instance can be get in SqlConnection.DataSource property.
SqlConnection conn = new SqlConnection(connstr);
string principalServer = conn.DataSource;
[Remarks: I developed a .NET 2.0 Console Application and scheduled to run periodically to poll for principal database instance, update SQL Alias if necessary. Certain applications might require restart to reinitiate database connections, which could be part of the actions done by the utility program.]