In this article, I would like to instruct you how we use Geo-Replication to set High Availability up for Microsoft SQL Azure Database.

Prerequisite condition

The following article gives some basic steps to create a SQL Database in Microsoft Azure

Introduction

Active Geo-Replication is Azure SQL Database feature that allows you to create readable replicas of your database in the same or different data center (region).

This feature is applied to Database level, not Server level.

Configuration

1- Login to Microsoft Azure Portal and connect to the database create. In my example, I created the database named as ddazuredemo


2- On Settings panel, click on Geo-Replication. I planned to replicated data from East US region to South Central US region

3- Click on South Central US region. Create Secondary panel appears ==> Click on Target Server. In this step, we create a new secondary database server

Server Name: southcentralusdemo.database.window.net

Server admin login: adminacc

Check on Allow Azure Services access server

Click on Select button

Pricing Tier: Basic, 1GB

Click OK and wait for a few of minutes for initializing deployment

4- Create a new table and insert data into Primary Database Server and then observing how data is replicated to the Secondary server

4.1 Connect to the Primary Server ddazuredemo.database.window.net with SQL Server Management Studio

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StgSales](
[Date] [int] NULL,
[CustomerCode] nvarchar NULL,
[ProductCode] nvarchar NULL,
[SalesAmount] [float] NULL
) ON [PRIMARY]
GO

4.2 Insert data into StgSales table

INSERT INTO [dbo].[StgSales]
([Date]
,[CustomerCode]
,[ProductCode]
,[SalesAmount])
VALUES(20170101,’AW00011000′,’FR-R92B-58′,100),(20170101,’AW00011001′,’HL-U509′,200)

4.2 Connect to the Secondary Server southcentralus.database.window.net and query data.

4.3 The secondary server is only Read-Only mode so we cannot insert data

Fail-Over Configuration

With Geo-Replication, we must manually configure Fail-Over through our application or we do it manually in case the Primary Server is crashed or need to be upgraded.

Auto-failover groups is an extension of active Geo-Replication. It is designed to manage the failover of multiple Geo-replicated databases simultaneously using an application initiated failover or by delegating failover to be done by the SQL Database service based on a user defined criteria.

Auto-failover is applied at Server level, different with Geo-Replication is at Database level

1- Connect to the database server ddazuredemo

2- Click on Failover groups —> Add group

Failover group appears

Failover group name: ddfailoverdemo

Secondary serveer: southcentralus

Read/Write failover policy: Automation

Database within this group: ddazuredemo. This is the place where allow us to select what databases we want to auto-failover

Click Create

After finishing the Auto-failover, we can see there are two connection end-point

Read/write listener endpoint: ddfailoverdemo.database.windows.net

Read-only listener endpoint: ddfailoverdemo.secondary.database.windows.net

When you connect to ddfailoverdemo.database.windows.net, it means we connect to the Primary database server, it can be either in East US database server or South Central US database server.  In case, East US database is down, South Central US will be the Primary.

In case, we want to use data for reporting purpose or for load balancing of read-only workloads, we can use Read-only listener endpoint.

3- Connect to ddfailoverdemo.database.windows.net

4- Connect to ddfailoverdemo.secondary.database.windows.net

Testing Auto-FailOver

Because Microsoft Database Azure does not support start/stop server so it’s quite difficult for use to verify Auto-Failover.

Instead, we use Forced Failover on Azure Portal. After forcing Failover, the Primary database server is South Central US

Obviously, the Read/Write listener endpoint and Read-Only listener endpoint connection have no changes. We still work with these endpoint and don’t care which database server is PRIMARY.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.