Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Standard Geo-Replication for Azure SQL Database

Updated: July 10, 2015

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 non-readable and does not accept client connections. It is visible on the list of secondaries and the continuous copy status can be monitored using DMVs. The non-readable secondary can be switched to active if there is a datacenter issue resulting in unavailability of the primary database. This allows you to terminate the relationship between the primary and the secondary, thus activating the non-readable database and making it an independent database ready to accept client connections.

The target region for the non-readable secondary is predetermined based on the location of your primary database. The management portal will display the target region.

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 non-readable secondary. In addition, the collation setting is replicated from the primary to the non-readable secondary.

  • The non-readable secondary counts towards the maximum number of databases on a server.

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

In the Azure Management Portal, follow these steps to set up a non-readable 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 non-readable secondary, click Add Secondary. This launches the Add Replica dialog box which allows you to specify the non-readable (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 non-readable secondary information and the status is set to Pending. The Databases List page is also updated with a new row for the non-readable secondary. Next, the Status is set to Creating and Replication is set to Pending. When the process is complete, the status of the non-readable 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

  • Use the Start-AzureSqlDatabaseCopy cmdlet and specify the –OfflineSecondary parameter to initiate a non-readable (offline) secondary database.

Because 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.

For information about this cmdlet, see Start-AzureSqlDatabaseCopy.

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 non-readable secondary.

See Also

© 2015 Microsoft