SQL Server Driver for PHP Connection Options: Failover_Partner
One of the best things about writing this series on connection options for the SQL Server Driver for PHP is that I get to learn about lots of interesting SQL Server features. This time, the Failover_Partner connection option has led me to do a bit of homework on database mirroring. The short story is that as an application developer you don’t really need to understand much about database mirroring…it’s a DBA concern. Of course, if you are both app dev and DBA and have the need for a failover database, then you might want to invest some time in learning more about mirroring than I'll go into (the MSDN documentation starts here). In this post, I'll provide an introduction to mirroring and hopefully give you enough information about the Failover_Partner option for you to send your DBA an intelligent email in case something goes wrong.
Database mirroring is primarily a software solution for increasing database availability. Essentially, you set up two servers with one acting as the primary server for a given database and the other acting as a "mirror" in case the primary server fails. When a PHP application connects to the primary server, the Failover_Partner connection option specifies the name of the server to which the application should connect if the primary server is not available. If I connect as shown here…
$serverName = "Server_A";
$connectionInfo = array( "Database"=>"ExampleDB"
, "UID"=>"UserName"
, "PWD"=>"Password"
, "Failover_Partner" => "Server_B");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
…then my PHP client will first attempt to connect to Server_A. If that server is not available, then it will attempt to connect to Server_B with the assumption that a backup of ExampleDB (on Server_A) exists on Server_B. If Server_B is not available, it will then try Server_A again…and so goes the alternating until either one of the servers responds or the connection timeout limit is reached. Of course, for this scenario to work, you need to have Server_A and Server_B configured for mirroring.
Note: The Express versions of SQL Server 2005, 2008, and 2008 R2 do not support mirroring. For complete lists of which features are supported in which versions, see the following pages: Features Supported by the Editions of SQL Server 2005, Features Supported by the Editions of SQL Server 2008, and Features Supported by the Editions of SQL Server 2008 R2.
As you can see, there isn’t much for the application developer to worry about except *a* failed connection. From the developer point-of-view, it doesn’t matter which server is failing to respond, you just have to plan for the possibility of a failed connection (which would mean the unlikely case of both servers being unavailable).
From the DBA point-of-view (or the application-as-a-whole point-of-view), there are several things to consider. The first is whether your application needs mirroring at all. This essentially boils down to deciding how important availability is for your application: setting up mirroring adds insurance to your application being available when it’s needed. If you decide that this insurance is necessary, then you have a decision to make in balancing performance against data integrity. There are basically two options for setting up mirroring: asynchronous mode and synchronous mode:
- Setting up mirroring in asynchronous mode provides better performance, but doesn’t guarantee data integrity. In this mode, Server_A sends transaction logs to Server_B but doesn’t wait for confirmation of receipt of those logs before responding to the client. This allows Server_A to respond to the client quickly, but in the case of a failover, Server_B might not have all the transactions logs to create a up-to-the-last-second copy of the database, so some data could be lost. (Note that I’m oversimplifying somewhat. Complete details are here: Asynchronous Database Mirroring (High-Performance Mode)).
- Setting up mirroring in synchronous mode provides a very high degree of data integrity, but at the cost of some performance. In this mode, Server_A sends transaction logs to Server_B and then waits for Server_B to confirm that the logs have been written to disk before responding to the client. This allows Server_B to create a up-to-the-last-second copy of the database in the case of a failover, but performance is impacted since Server_A has to wait for Server_B. (Again, I’m oversimplifying. Complete details are here: Synchronous Database Mirroring (High-Safety Mode)).
Clearly, once you decide on your mirroring mode, there are many other questions to be answered (including the nuts-and-bolts questions of getting your mirrored servers set up). The SQL Server documentation should answer those questions in detail: Database Mirroring. If you find that those docs don’t answer your questions, let me know and I’ll do some more homework.
Thanks.
-Brian