Tuesday, 23 June 2020

Configuring Geo Replication and Fail-over in Azure SQL Database

                    Azure's Active Geo Replication is one of the best features for maintaining resiliency of your Azure SQL Databases either in the same region or at a geographically distant region. Another good thing is that the secondary replicas are readable so you can leverage your secondary replicas for reporting purpose. Doing this will benefit you in two ways- first- your primary replica can be isolated for important production transactions only and -second- you will be utilizing your secondary replica as well by running reports on the same.The databases configured for Geo-Replication can automatically fail-over to its secondary partner (with a grace period, which is the time sql services will wait for before failing over at the time of Disaster.Grace Period can be anything between 1 hour to 24 hours ) if there is an issue with the Primary Replica. At the time of writing this post, you can create total 4 secondary readable replicas.

Now, Lets see a quick demo on how to configure Active Geo Replication-

1) Login to Azure Portal- www.portal.azure.com.

2) Search for SQL Databases in the search box-
Add caption
3) Click on SQL Databases and you will see option to create new SQL Database. If you already have sql databases created in your subscription then they will be shown on this blade. Click Create SQL Database. You will be presented with a form where you need to provide all the mandatory details to proceed further-

4) You need to fill mandatory details like Resource Group- you will have an option to create a new Resource Group or using the already existing Resource Group from your subscription. Next you should give your database a name. Then you need to select Server, here you can use an already existing SQL Server or create a new one right now.

5) Since I do not have an existing SQL Server, I will quickly go ahead and create a new one by filling the mandatory details below like Server Name which should be unique, Server Admin Login, a strong Password and Location of the SQL Server.

6) Then comes the option of whether we want to use Elastic Pools, for now we will select No for this option .I will discuss about Elastic Pools in the future blogs.

7) Next comes the option of configuring database. There are two types of models available here- DTU based and vCore model. Here in our example we are using DTU based configurations. Click on configure database and you will be presented with a screen below-

8) Since we want to go with DTU based model, click on "Looking for basic, standard, premium?" option. We can select standard from the next blade and select DTU and Data size from there. We have selected S2 tier and at the right side it shows estimated monthly cost for using this size. The cost summary is based on the size you select and cost is shown in the local currency.

9) After clicking on Apply button, click Review and create to provision your SQL Database and SQL Server. In a few moments you will have your SQL Database and SQL Server ready.

10) Go to SQL Databases blade and you will see the newly created database. You can see the details like name of the Server, Replication Role, Pricing tier, Location etc.

11) Now we have to add the newly created database in Active Geo Replication. Go to your database and click on Geo Replication from the left side. You will see the below screen

12) After that, you need to select the region to create your secondary database. Please note that our primary database is in East US, we can choose any available location for our secondary database. In our example I am selecting West US as the secondary database location. This will depend on the requirements basically.

13) Upon clicking on Secondary location you will be presented with the below blade to furnish all the required details to create your secondary replica. Also you can see a note above which talks about pricing implication, usually each replica is charged separately, so of you have 3 replicas then the cost would be 3X.

14) Now, you can either use an already existing Secondary Server or you can create a new one at this point of time only. We will create the new target Server. Click on Target server and fill all the details like Server Name, Credential details etc and click Select.

15) You can see the newly created secondary server selected.

16) Click OK and you will have your Secondary Server configured in a few minutes.

17) Once the set up is complete you can verify the Replication by going to the SQL Databases blade. You can verify the names of the servers with their specific Replication roles.

18) Now that Replication is configured , we are still pending with creating fail-over group so that fail-over can happen in an event of Disaster. Fail-over can be configured as either automatic or manual . In our demo we are configuring it Automatic with Grace Period of 60 minutes. For configuration, we need to go to the primary server and click on Fail-over Groups Option under settings.

19) Below screen will appear-

20) We need to click on Add group to create a Fail-over Group. Fill out the details like Fail-over Group name, select Secondary Server, Fail-over Policy, Grace-Period, add databases etc and click create.

21) In a few moments you will have your Fail-over group created. You can verify the same from Fail-over groups blade only. Click on the same and you will be presented with the below dashboard. you can see that the Name is the Fail-over group name that we gave, primary and secondary servers and other details are available for our verification.

If you click on the Fail-over group name you will see a pictorial representation of your database replicating geographically. From here you can manage Fail-over, removing or adding more databases in the fail-over groups etc

This finishes are demo of configuring Active-Geo Replication. If you have any questions please feel free to ask in comment section.