Azure SQL Geo-replication

I assume that you ever wondered what will happen in case of permanent loss of Microsoft Azure datacenter as a result of natural disaster or catastrophic human error. What about your data stored in data bases there ? I want to show you in few steps how to configure your Azure SQL database and secure your priceless production data.

If you don’t have your SQL database yet, let’s create it. Select SQL Database in Databases category.

and fill the following form.

In newly created resource select Geo-replication in Settings section.

Then select a region to which you want to replicate your database. As you can see primary database is located on server in South Central US so let it be West Europe.

Fill the following form:

and wait. Azure will do everything for you.

Replicated database is ready for use a dozen or so seconds later.

What replicated/secondary database can do ?

Secondary databases are available for querying and for failover in the case of a primary data center outage.
All committed transactions from primary database are asynchronously replicated to copied databases even to different servers in different region so every database is always up-to-date.
Secondary database can be promoted to primary database by application or administrator at any moment. During a real outage it happens automatically.

Check it! Connect to both servers using SQL Server Management Studio.

and execute the following script on primary database which creates a sample table with one row od data.

CREATE TABLE TestTable
( Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Data nvarchar(max) NOT NULL
);

INSERT INTO TestTable (Data) VALUES ('testData')

Next run this script on both servers.

SELECT [Id]
      ,[Data]
  FROM [dbo].[TestTable]

SELECT
 @@SERVERNAME AS 'Server Name'
,DB_NAME() AS 'Database Name'

And here is the proof.

Azure Geo-replication can be used also for load-balancing. More here. Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *