Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Setting Up Geo-Replication in SQL Azure


By:   |   Read Comments   |   Related Tips: More > Azure

Problem

We've seen that Microsoft SQL Azure offers geo-replication for even Basic tier priced databases and we've considered using a secondary for a read-only copy of the data set. How can we do this and what are some benefits of this design?

Solution

Geo-replication offers a high availability and disaster recovery strategy in Azure and one of many benefits is that it can help offload reads traffic. In this tip, we'll look at setting up a geo-replicated secondary and offloading a report to this secondary.

Geo-Replication in Azure SQL

1. Click on Geo-Replication. Once we select the database name in Azure, we see a list of options on the left with one option being Geo-Replication. When we click this option, we should see a globe with the pointers of where our primary is and where we can set up our secondary. In general, it's recommended to stay within the same region, like North East and South East. Microsoft does provide a Recommendation in the interface and this is what they recommend for the secondary.

Create Secondary in Azure SQL

2. Click on the Secondary's Target Region. Once you select the secondary's target region, you will see options to create the name of the database, set the pricing tier, the secondary type you want, and the target server. Remember that at the time of writing this tip, Microsoft does not charge for SQL Azure servers, but charges at the database level. You will want to make sure that you set up the database tier you wish - and with offloading reads, this might not be as high as the write tier.

Secondary's Target Region in Azure SQL

3. Start deploying the secondary. Once you start deploying the secondary, it may take some time relative to how large your database is among other factors. When this is complete, we can then log into the server using the credentials that we created to make sure that all objects are present. Keep in mind that in this example we're setting up a read-only secondary - these data should match our primary and we're not writing to this, or intending to. Once the secondary is set up, any firewall rules or access rules we had on the primary must be applied to the secondary, otherwise, we'll receive a failure to connect.

Start deploying the secondary database

Some additional considerations:

  1. For your secondary server, consider the naming convention for its purpose, so that it's intuitive when you share it with the teams.
  2. After setting up the secondary, and creating the appropriate access rules, verify the objects on the database, using a validation script. Keep in mind, this isn't just for tables or procedures; if I create a role on the primary, I should also see it on the secondary.
  3. You will receive errors if you try to set up a secondary on a pricing tier that's incompatible - like trying to set up a 100GB database secondary on Basic.
  4. The secondary will take on properties, like transparent data encryption, of the primary. This does not apply to server level properties, like the firewall.

SQL Azure's geo-replication offers environments both a high availability and disaster recovery tool. For some reports and applications, this may offer a way to re-route traffic to reduce contention from heavy write loads. From a few tests on medium and large sized data sets, the performance of writes improved on the primary when I routed the test reports' traffic on the secondary.

Next Steps
  • Is disaster recovery a concern in your environment? If so and if you cannot handle any downtime, I would recommend looking at the Geo-Replication feature, which is available even in the basic tier.
  • Consider your read traffic and whether it will require the same tier as the primary; this can be changed after you set it up, and relative to your needs, it might be something that can be at a much lower tier.
  • Test the performance, especially test the routing of your read traffic if you're in an OLTP environment.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools