{"id":6481,"date":"2016-01-04T17:32:03","date_gmt":"2016-01-04T16:32:03","guid":{"rendered":"https:\/\/blog.redbaronofazure.com\/?p=6481"},"modified":"2016-01-04T18:32:01","modified_gmt":"2016-01-04T17:32:01","slug":"dr-part-3-sql-azure-geo-replication","status":"publish","type":"post","link":"https:\/\/blog.redbaronofazure.com\/?p=6481","title":{"rendered":"DR part 3 – SQL Azure Geo-Replication"},"content":{"rendered":"

This is the third post on how to make sure your application can survive a datacenter outage. The first post just set the basics on how to get your company’s website on to Azure. The second post focused on how to use Traffic Manager infront of a dual datacenter website deployment. This third post in the series will be on the topic on how the SQL Azure database can be geo-replicated across two datacenters.<\/p>\n

If you haven’t read the DR2 post, I suggest you do this before reading this post.<\/p>\n

Geo-replication is built in functionality in SQL Azure Database<\/strong><\/p>\n

First of all, there is an excellent article by Tony Petrossian in the SQL Azure product group (see references) that discusses the geo-replication features in SQL Azure and the difference between Active and Standard. I encourage you to read that if you haven’t.<\/p>\n

In short, how geo-replication works in SQL Azure is that you have a primary database that continously sends commited transactions asynchronously to its secondary databases so that they are up to date. You can have multiple secondary databases but they can only be SQL Azure databases, so you can not user a SQL Server VM as the secondary.<\/p>\n

\"DR3-enable-geo-repl-portal-1\"<\/a><\/p>\n

Configuring the Geo-Replication is very easy in the portal. You just go to the primary database and select Geo-Replication in the Settings list. If you can’t find the menu item, you have the wrong Pricing Tier for the database. It needs to be Standard, so S0 is the lowest you can have to set this up.<\/p>\n

\"DR3-enable-geo-repl-portal-2\"<\/a>If the Pricing Tier for the database is Standard,\u00a0you can only do what is called Standard Geo-Replication, and the portal tells\u00a0you that twice. In order to have a readable secondary database, the Pricing Tier must be Premium.<\/p>\n

What is the Non-readable secondary type, then? It is basically an offline database that receives and applies the asynchronous transactions and keeps the database up to date, but it can not be used by applications in any way until you terminate the continous copy relationship.<\/p>\n

\"DR3-non-readble\"<\/a><\/p>\n

Terminating the relationship is a manual procedure in the portal or something you do with Powershell. Either way you do it, it is not something Azure does automatically.<\/p>\n

So the question is – should you switch to Premium or stay with Standard? Well, that depends on what you need the secondary site for and also how you plan to use Traffic Managers routing option.<\/p>\n

The combinations of Traffic Manager’s Routing options and SQL Azure’s Geo-Repliction options<\/strong><\/p>\n

Traffic Manager has three options for Routing Method between the endpoints (websites) it has in its profile. The Routing Methods are Performance, Weighted(Round Robin)\u00a0and Priority(Failover). The first two options will mean that all endpoints (websites) will be actively in use and serve web content to clients. This means\u00a0that an offline, non-readable database is\u00a0of little use with Performance or Weighted Traffic Manager routing and we must\u00a0have Active Geo-Replication to get a readable database.\u00a0But if the Traffic Manager routing is Priority(Failover), this means that we can\u00a0get by with\u00a0either the offline or the read-only as the secondary database. It becomes a question on how the failover should happen.<\/p>\n

Time and decision to failover the database<\/strong><\/p>\n

If we assume we are using Routing Method Priority (Failover) for the Traffic Manager Profile, the switch from the primary to the secondary endpoints will happen automatically when TM’s monitoring\u00a0probing fails on http(s). At that point monitoring status will become “Degraded” as I explained in my DR2 post and it will start to direct traffic to the secondary endpoint (ie the website in alternate Azure datacenter).<\/p>\n

Since the continous copy relationship needs to be terminated regardless if we use an offline or read-only secondary database, this means that the actual database failover involves a decision point to execute the failover\u00a0action. The choice of using offline or read-only as a secondary database is\u00a0a choice of what should happen between the time of the TM failover and until the decision is made to terminate the continous copy relationship (which\u00a0puts the secondary database in read-write mode).<\/p>\n