Geo-replication is a powerful feature in SQL Server that allows you to replicate your database to a secondary location for disaster recovery purposes. In this article, we will discuss the concept of geo-replication and the steps required to set it up in Azure SQL database.

What is Geo-Replication?

Geo-replication is the process of replicating a database to a secondary location, typically in a different geographical region. This provides an additional layer of protection against natural disasters or physical outages that may affect the primary data center. With geo-replication, you can ensure that your data is always available, even in the event of a catastrophic failure.

Setting up Geo-Replication in Azure SQL Database

To set up geo-replication in Azure SQL database, you will need an Azure subscription and an Azure SQL database that you want to replicate. You can use the Azure portal or T-SQL to configure and monitor geo-replication.

Using the Azure Portal

1. Navigate to your Azure SQL database and click on the “Not Configured” option under the geo-replication role.

2. Select the data center where you want to replicate your database.

3. Create the secondary database on a server located in the chosen region.

4. The secondary database will be populated with data from the primary database through a process called seeding.

5. Once seeding is complete, replication is established, and the secondary database becomes readable.

Using T-SQL

If you prefer using T-SQL to set up geo-replication, you can follow these steps:

1. Install the latest version of SQL Server Management Studio (SSMS).

2. Connect to your primary server in SSMS.

3. Navigate to the master database and click the “New Query” button.

4. Execute the following command to add a secondary database:

ALTER DATABASE YourDatabase
ADD SECONDARY ON SERVER YourSecondaryServer WITH (ALLOW_CONNECTIONS = ALL);

5. Verify the setup by executing the following command:

SELECT
start_date,
link_guid,
partner_server,
partner_database,
replication_state_desc,
role_desc
FROM sys.geo_replication_links
WHERE database_id = YourDatabaseId;

Monitoring and Managing Geo-Replication

Once geo-replication is set up, you can monitor and manage it using the Azure portal or T-SQL.

1. In the Azure portal, you can failover the replication by clicking the “Failover” button. This switches the roles of the primary and secondary databases.

2. To remove replication, go to the secondary database and click “Stop Replication”. This will remove the secondary database from the replication relationship.

3. In T-SQL, you can use the following queries to monitor the replication status:

SELECT
link_guid,
partner_server,
last_replication,
replication_lag_sec
FROM sys.dm_geo_replication_link_status;

SELECT *
FROM sys.dm_operation_status
WHERE major_resource_id = 'YourDatabaseId'
ORDER BY start_time DESC;

Conclusion

Geo-replication is a valuable feature in SQL Server that provides an extra layer of protection for your data. By replicating your database to a secondary location, you can ensure high availability and disaster recovery. Whether you choose to set up geo-replication through the Azure portal or using T-SQL, it is important to regularly monitor and manage the replication to ensure the integrity of your data.