Export (0) Print
Expand All

Standard Geo-Replication for Azure SQL Database

Updated: September 10, 2014

This topic describes the Standard Geo-Replication feature available in Microsoft Azure SQL Database. Built on the same continuous copy mechanism as Active Geo-Replication, Standard Geo-Replication asynchronously replicates committed transactions from the primary database to one secondary database. The secondary, unlike Active Geo-Replication, is offline and does not accept client connections. It is visible on the list of secondaries and the continuous copy status can be monitored using the DMVs. The offline secondary can be switched to active if there is a datacenter issue resulting in unavailability of the primary database. In such an event, the failover property of the database is enabled. This allows you to terminate the relationship between the primary and the secondary, thus activating the offline database and making it an independent database ready to accept client connections.

The target region for the offline secondary is predetermined based on the location of your primary database. The following table lists the fixed paired regions:

 

Primary Secondary

North Central US

South Central US

South Central US

North Central US

East US

West US

West US

East US

North Europe

West Europe

West Europe

North Europe

South East Asia

East Asia

East Asia

South East Asia

East China

North China

North China

East China

When planning to use Standard Geo-Replication, consider the following:

  • The secondary database has the same service tier and performance level as the primary database. Ensure you have enough capacity on the server for your offline secondary. In addition, the collation setting is replicated from the primary to the offline secondary.

  • The offline secondary counts towards the maximum number of databases on a server.

  • The offline secondary counts toward the maximum allowed replicas in Active Geo-Replication. This means if you decide to use an offline secondary, you will be limited to three active secondary replicas. The source and target servers must belong to the same subscription.

Configure an Offline Secondary Database

Azure Management Portal

In the Azure Management Portal, follow these steps to set up an offline secondary database:

  1. Sign in to the Management Portal using your Microsoft account.

  2. Select a database from the Databases list. This opens the Database Dashboard or the Quick Start page.

  3. Select the Geo-Replication tab for the database. This tab is only enabled for databases in the Standard and Premium service tiers.

  4. To create a new offline secondary, click Add Secondary. This launches the Add Replica dialog box which allows you to specify the Offline secondary.

  5. The list of servers shown in the Target Server drop down list box displays servers that have available quota that are enabled on the paired region in the same subscription as the primary.

Once the confirmation is complete, the Geo-Replication page is updated with the status information. A new row is added to the Secondaries list with the offline secondary information and the status is set to Pending. The Databases List page is also updated with a new row for the offline secondary. Next, the Status is set to Creating and Replication is set to Pending. When the process is complete, the status of the offline secondary is set to Offline and Replication is set to Active. For information on monitoring the continuous copy status, see Monitor a Continuous Copy Relationship

PowerShell

  1. Start PowerShell and sign in to your Microsoft Azure subscription.

  2. Use the Start-AzureSqlDatabaseCopy cmdlet and specify the –OfflineSecondary parameter to initiate an offline secondary database.

The following example creates an offline secondary on the “xyz” server for the Orders database on the “abc” server. The –ContinuousCopy parameter specifies that the copy is to be set up as a continuous replication. The –OfflineSecondary parameter specifies that the secondary is offline.

ImportantImportant
Since the Start-AzureSqlDatabaseCopy cmdlet is used for both database copy and Geo-Replication, you must specify the –ContinuousCopy parameter to configure Geo-Replication. Running the Start-AzureSqlDatabaseCopy without the –ContinuousCopy parameter will result in a one-time copy of the database. If you do not specify the –OfflineSecondary parameter when configuring continuous replication for databases in the Premium service tier, an active secondary database is created when the –ContinuousCopy parameter is specified.

Start-AzureSqlDatabaseCopy -ServerName "abc" -DatabaseName "Orders" -PartnerServer "xyz" –ContinuousCopy -OfflineSecondary

For information about this cmdlet, see Start-AzureSqlDatabaseCopy.

Disaster Recovery

In the event of a datacenter failure, you will see a notification in the Management Portal informing you that the primary is unavailable. The Failover Enabled property of the secondary is set to true which allows you to begin the process of activating the offline secondary. To programmatically monitor this property, use PowerShell or REST. Both PowerShell and REST expose this property through the termination flag IsTerminationAllowed.

For more information, see Failover in a Standard Geo-Replication Configuration

Upgrade/Downgrade

Upgrading or downgrading service tiers or performance levels requires that you terminate the continuous copy relationship. However, since the ability to explicitly stop replication is normally not enabled, you must first drop the secondary database, change the service tier or performance level of the primary database, and then reconfigure the offline secondary.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft