By: Tim Smith | Comments | Related: > 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.
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.
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.
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.
Some additional considerations:
- For your secondary server, consider the naming convention for its purpose, so that it's intuitive when you share it with the teams.
- 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.
- 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.
- 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.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
