Support for High Availability, Disaster Recovery

Article
11/18/2022

This topic discusses Microsoft Drivers for PHP for SQL Server support (added in version 3.0) for high-availability, disaster recovery.

Starting with version 3.0 of the Microsoft Drivers for PHP for SQL Server, you can specify the availability group listener of a high-availability, disaster-recovery availability group or a failover cluster instance as the server in the connection string.

The MultiSubnetFailover connection property indicates that the application is being deployed in an availability group or Failover Cluster Instance and that the driver will try to connect to the database on the primary SQL Server instance by trying to connect to all the IP addresses. Always specify MultiSubnetFailover=True when connecting to a SQL Server availability group listener or SQL Server Failover Cluster Instance. If the application is connected to an Always On database that fails over, the original connection is broken and the application must open a new connection to continue working after the failover.

Full details on Always On availability groups can be found at the High Availability, Disaster Recovery Docs page.

Transparent Network IP Resolution (TNIR)

Transparent Network IP Resolution (TNIR) is a revision of the existing MultiSubnetFailover feature. It affects the connection sequence of the driver when the first resolved IP of the hostname does not respond and there are multiple IPs associated with the hostname. The corresponding connection option is TransparentNetworkIPResolution. Together with MultiSubnetFailover it provides the following four connection sequences:

TNIR Enabled & MultiSubnetFailover Disabled: One IP is attempted, followed by all IPs in parallel
TNIR Enabled & MultiSubnetFailover Enabled: All IPs are attempted in parallel
TNIR Disabled & MultiSubnetFailover Disabled: All IPs are attempted one after another
TNIR Disabled & MultiSubnetFailover Enabled: All IPs are attempted in parallel

TNIR is enabled by default, and MultiSubnetFailover is Disabled by default.

This is an example of enabling both TNIR and MultiSubnetFailover using the PDO_SQLSRV driver:

<?php
$serverName = "yourservername";
$username = "yourusername";
$password = "yourpassword";
$connectionString = "sqlsrv:Server=$serverName; TransparentNetworkIPResolution=Enabled; MultiSubnetFailover=yes";
try {
    $conn = new PDO($connectionString, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    // your code 
    // more of your code
    // when done, close the connection
    unset($conn);
} catch(PDOException $e) {
    print_r($e->errorInfo);
}
?>

Upgrading to Use Multi-Subnet Clusters from Database Mirroring

A connection error will occur if the MultiSubnetFailover and Failover_Partner connection keywords are present in the connection string. An error will also occur if MultiSubnetFailover is used and the SQL Server returns a failover partner response indicating it is part of a database mirroring pair.

When upgrading a PHP application that currently uses database mirroring to a multi-subnet scenario, remove the Failover_Partner connection property and replace it with MultiSubnetFailover set to True and replace the server name in the connection string with an availability group listener. If a connection string uses Failover_Partner and MultiSubnetFailover=true, the driver will generate an error. However, if a connection string uses Failover_Partner and MultiSubnetFailover=false (or ApplicationIntent=ReadWrite), the application will use database mirroring.

The driver will return an error if database mirroring is used on the primary database in the AG, and if MultiSubnetFailover=true is used in the connection string that connects to a primary database instead of to an availability group listener.

Specify application intent

You can specify the keyword ApplicationIntent in your connection string. The assignable values are ReadWrite (the default) or ReadOnly.

When you set ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a USE database statement.

The ApplicationIntent keyword doesn't work with legacy read-only databases.

Targets of ReadOnly

When a connection chooses ReadOnly, the connection is assigned to any of the following special configurations that might exist for the database:

Always On. A database can allow or disallow read workloads on the targeted availability group database. This choice is controlled by using the ALLOW_CONNECTIONS clause of the PRIMARY_ROLE and SECONDARY_ROLE Transact-SQL statements.
Geo-replication
Read scale-out

If none of those special targets are available, the regular database is read from.

The ApplicationIntent keyword enables read-only routing.

Read-only routing

Read-only routing is a feature that can ensure the availability of a read-only replica of a database. To enable read-only routing, all of the following apply:

You must connect to an Always On availability group listener.
The ApplicationIntent connection string keyword must be set to ReadOnly.
The database administrator must configure the availability group to enable read-only routing.

Multiple connections that each use read-only routing might not all connect to the same read-only replica. Changes in database synchronization or changes in the server's routing configuration can result in client connections to different read-only replicas.

You can ensure that all read-only requests connect to the same read-only replica by not passing an availability group listener to the Server connection string keyword. Instead, specify the name of the read-only instance.

Read-only routing might take longer than connecting to the primary. This is because read-only routing first connects to the primary, and then looks for the best available readable secondary. Due to these multiple steps, you should increase your login timeout to at least 30 seconds.