Terminating an Azure SQL Database Replication


In my last post, I talked about setting up geo-replication for Azure SQL databases. There might be situations where you need to terminate your replication between your replicas. This could be a need for various reasons. You want to move your replica to a different region or you want to remove replication temporarily or you want to bring your secondary replica online and allow DML operations on it etc.

To remove replication, Azure provides two options: planned and forced termination. Again, if you have worked with on-premise database mirroring or availability groups, then this will seem familiar to you. Planned termination incurs ZERO data loss for the replica. The forced termination has chances of data loss.

Planned Termination is intended for use in planned operations where data loss is unacceptable. Termination can only be performed on the primary database, after the active secondary has been seeded.

Forced termination is intended for when the primary database or one of its active secondary databases is lost or is inaccessible. A forced termination can be performed on either the primary database or the active secondary. Every forced termination results in the irreversible loss of connectivity between the primary database and at least one active secondary. In addition, forced termination on an active secondary causes the loss of any transactions that have not been replicated from the primary database. If the primary database has only one continuous copy relationship, after termination, updates to the primary database will no longer be protected.

You will have to setup the replication again in case you want a synchronized copy of the database.

image

The steps to accomplish this is mentioned below.

Select the Geo-Replication tab for the database. This tab is only enabled for databases in subscriptions that are enrolled in the Premium preview program. Active Geo-Replication is currently only supported for Premium databases. You should see that the Replication Role for the database is displayed as source.

  1. Select the desired active secondary from the REPLICAS list.
  2. To terminate the continuous copy relationship, click Stop Replica. This launches the Stop Active Geo-Replication dialog which allows you to select the type of termination you want to perform.
  3. The Stop Active Geo-Replication dialog box presents two options when launched from the primary database:Stop replication after synchronization completes: This option ensures that the termination happens after the committed transactions on the primary have been replicated to the active secondary.Stop replication immediately: This option terminates the continuous copy relationship between the primary and the selected active secondary immediately. You should expect some data loss for the active secondary in this scenario.

    Select the Stop replication after synchronization completes option and click to confirm.

Reference:

Terminate a Continuous Copy Relationship
http://msdn.microsoft.com/en-us/library/azure/dn741323.aspx

Advertisements

Geo Replication meets Azure SQL Database


Active Geo-Replication enables continuous copy of your Premium database across geographic regions worldwide or within the same region. This is akin to setting up an availability group for SQL Server 2012 and above. For this you will need the following:

  • At least two Azure SQL Database Servers will available Premium database quota. The quota available is visible under the SERVERS tab in the Azure Management Portal for SQL Databases. You can alternatively retrieve the available quota by querying the server_quotas view (also in PREVIEW).
  • One or more Azure SQL Database configured on the Premium tier.

When downgrading from a Premium service tier, you must first terminate all Active-Geo Replication relationships. You can follow the steps described in the Terminate a Continuous Copy Relationship topic to stop the replication process between the primary and the active secondary databases.

The performance level of the secondary replica should be the same or higher than the primary replica.

Setting it up

In screenshot 1, you will see that the GEO-REPLICATION tab shown for the database optimus (Yes, another transformer character!) This will be available only if you have signed up for the PREVIEW feature for the new database tiers offered for Azure SQL Database. And if you have a Premium database configured, then you can setup a replica using the ADD REPLICA button which will be available at the bottom of the page.

image

On clicking the ADD REPLICA button, you will be provided with an ADD ACTIVE GEO-REPLICA screen. This allows you to pick another target server which will host the replica copy of your Azure SQL Database. See Screenshot 2.

image

Select the target server where you want your active geo-replica created. Only servers enabled for geo-replication in the same subscription as the source with available Premium database quota are shown. Additionally, only servers enabled for the current reservation size of the source database are shown. Replicas are created with the same database name, edition, maximum size, and reservation size as the source.

In the above screenshot, you can see that I have asked Azure to create a replica of optimus in the North Central US region and I have the primary copy hosted in the South Central US region. Once you proceed with your selection, you will be asked to accept terms and conditions as Premium database hosting attracts a cost!

Once the replication is configured, the Geo Replication tab will show you the status and the configured replicas as shown in Screenshot 3.

image

Reference:

SQL Database Pricing Details
http://azure.microsoft.com/en-us/pricing/details/sql-database/#service-premium

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Active Geo-Replication for Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx