Since the release of SQL Database geo-replication last year we have been listening to customer feedback and working on improving the feature. Today we are announcing a set of new geo-replication enhancements that both address customer pain points and enable new capabilities for customers. These features are available for V12 databases and include:

• T-SQL support for geo-replication
• Failover and failback
• Ability to synchronize security credentials and firewall rules
• Full support of geo-replication for databases in elastic pools
• Configurable performance level of the secondary database
• Azure Resource Manager API and support of role-based security
• Synchronous PowerShell cmdlets

T-SQL support for geo-replication

You can now use T-SQL to configure geo-replication and initiate failover. These commands require SQL authentication and therefore do not depend on sharing the subscription certificate as is the case with the existing PowerShell cmdlets. In particular, it allows you to have a secondary logical server created in a different Azure subscription than the primary server. For more information about the new T-SQL syntax refer Geo-Replication for Azure SQL Database using Transact-SQL.  Each T-SQL command has a PowerShell or REST API equivalent.

Failover and Failback

The new failover command allows you to easily switch the database role while maintaining the relationship. During a real outage you can invoke the command with the “unplanned” option to immediately promote a secondary to primary. When the failed primary recovers and is available again, the system will automatically mark it as a secondary and bring it up-to-date with the new primary. Due to the asynchronous nature of replication a small amount of data can be lost during unplanned failovers if a primary fails before it replicates the most recent changes to the secondary.

Another enhancement is the automatic management of multiple secondary databases. When a primary with multiple secondaries fails over the system will automatically reconfigure the replication relationships and link the remaining secondary to the newly promoted primary without requiring any user intervention.

The following illustration shows how you can failover your geo-replicated database in case of an outage using Azure Portal. Refer to this article for more details of using Azure Portal for geo-replication .

After the outage is mitigated it may be desirable to return the application to the primary region. To do that you can invoke the failover command with the  “planned” option. For information how to activate “planned” failover refer to Geo-Replication for Azure SQL Database using Transact-SQL and Geo-Replication for Azure SQL Database using PowerShell.

DR drills

As mentioned earlier, when you invoke the failover command in “planned” mode it switches the database roles between secondary and primary after data is fully synchronized. Because the planned failover does not result in data loss and the primary database remains protected at all times you can also use this command to perform disaster recovery drills in production. 

Keeping credentials and firewall rules in sync

We recommend the use of database firewall rules for geo-replicated databases so that these rules can be replicated with the database to ensure all secondary databases have the same firewall rules as the primary. This eliminates the need for customers to manually configure and maintain firewall rules on servers hosting both the primary and secondary databases. 

Similarly, using contained database users for data access ensures both primary and secondary databases always have the same user credentials so that in case of failovers there will be no disruptions due to mismatch in logins and passwords.

With the addition of Azure Active Directory (AAD) customers can manage user access to both primary and secondary databases and eliminating the need for managing credentials in databases all together.

Geo-replication of elastic pool databases

You can configure geo-replication for any database in a Standard or Premium elastic database pool. The secondary database can be in another elastic database pool as long as the service tier is the same. For regular databases, the secondary can be an elastic database pool and vice versa. Again, as long as the service tiers are the same. You can find good examples of configuring geo-replication in elastic database pool refer to Geo-Replication for Azure SQL Database using Transact-SQL and Geo-Replication for Azure SQL Database using PowerShell. 

Configurable performance level of the secondary database

You can now create a secondary database with lower performance level than the primary. Both primary and secondary databases are required to have the same service tier. This option is not recommended for applications with high database write activity as it may result  in increased replication lag and therefore, has high risk of substantial data loss after failover. In addition, after failover the application’s performance will be impacted until the new primary is upgraded to a higher performance level. You can find examples of configuring performance level of the secondary in Geo-Replication for Azure SQL Database using Transact-SQL and Geo-Replication for Azure SQL Database using PowerShell. 

Azure Resource Manager API and role-based security

Geo-replication now includes a set of new Azure Resource Manager (ARM) APIs for management, including new ARM-based PowerShell cmdlets. These APIs require the use of resource groups and support role based security (RBAC). Here’s a RBAC primer with great instructions on implementing the access roles.

The existing Azure SQL Service Management (classic) REST API and Azure SQL Database (classic) cmdlets are supported for backward compatibility. However, the new features are only supported in ARM based Azure SQL REST API and the ARM based Azure SQL Database PowerShell cmdlets.

Synchronous PowerShell cmdlets

Scripting is simplified through the introduction of the new ARM-based PowerShell cmdlets that support synchronous execution by default. When using geo-replication and failover commands synchronously, you no longer a need to monitor the progress of these long-running operations. Scripts can be written to simply wait for the command to return.