Microsoft SQL Azure offers geo-replication for even Basic tier priced databases, providing a high availability and disaster recovery strategy in Azure. One of the many benefits of geo-replication is the ability to offload read traffic. In this article, we will explore how to set up a geo-replicated secondary and offload a report to this secondary.

Step 1: Click on Geo-Replication

Once you have selected the database name in Azure, you will see a list of options on the left. Click on “Geo-Replication” to access the geo-replication settings. Here, you will see a globe with pointers indicating the location of your primary database and where you can set up your secondary. It is recommended to choose a secondary location within the same region, such as North East and South East.

Step 2: Select the Secondary’s Target Region

After clicking on the secondary’s target region, you will be prompted to provide a name for the database, set the pricing tier, choose the secondary type, and select the target server. Keep in mind that Microsoft does not charge for SQL Azure servers, but charges at the database level. Consider setting up a lower tier for the secondary database, as it will primarily handle read operations.

Step 3: Deploy the Secondary

Once you initiate the deployment of the secondary, it may take some time depending on the size of your database and other factors. After the deployment is complete, log into the server using the credentials you created to ensure that all objects are present. It is important to note that the secondary database is read-only and should mirror the data in the primary database. Any firewall rules or access rules applied to the primary database must also be applied to the secondary to avoid connection failures.

Additional Considerations

When setting up the secondary server, consider using a naming convention that clearly indicates its purpose, making it intuitive for teams to identify. After setting up the secondary and creating the appropriate access rules, it is recommended to verify the objects in the database using a validation script. This includes not only tables and procedures but also roles and other objects created on the primary database.

It is important to ensure that the secondary database is set up on a compatible pricing tier. Attempting to set up a secondary database with incompatible specifications, such as a 100GB database on a Basic tier, will result in errors.

Properties such as transparent data encryption on the primary database will be inherited by the secondary. However, server-level properties like the firewall settings do not apply to the secondary.

SQL Azure’s geo-replication provides both high availability and disaster recovery capabilities. By offloading read traffic to the secondary database, it is possible to reduce contention from heavy write loads and improve the performance of write operations on the primary database.

Setting up geo-replication in SQL Server Azure offers a reliable and efficient way to ensure data availability and minimize downtime. By following the steps outlined in this article, you can leverage geo-replication to offload read traffic and improve the overall performance of your SQL Server Azure environment.