KB: Microsoft SQL Server mirroring database automatic failover for non-ADO.NET 2.0 clients

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.

Solution 1:

  • 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

Solution 2:

  • 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

[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);
    conn.Open();
    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.]

References:

Database Mirroring in SQL Server (ADO.NET)

Database Mirroring in SQL Server 2005 Books Online

Advertisements