top of page

"Step-by-Step Guide to Setting Up Geo-Replication in Azure SQL Databases"

This is prepared based on the implementation I did using my Azure account, following a very good youtube video by Nirav Gandhi. There are few portal interface changes like – it is now called ‘replicas’ vs previously ‘geo-replication’.

I showed this SQL replication capability how it works to students while teaching them a session on Business Continuity.

The youtube video link is given below

If anyone is interested in implementing the MS-SQL geo-replication on Azure cloud, they can either follow the video or the step-action table given below.




Step 1

Login to Azure portal and click SQL server DB


Step 2

Click +Add


Step 3

Create a Resource Group called ‘Active GeoReplication1


Step 4

Click OK


Step 5



Step 6

Create New server with name as ‘sqltce1primary’

Also choose the authentication method as

Use SQL Authentication

Some menu options could differ from what is mentioned here

Step 7

Give server admin login as ‘sqltce1’

Elastic pool should be No

Workload is Development

Step 8

Give password as ****



Step 9

Reconfirm the password ****


Step 10

Select the location as South India

Compute is selected as

General Purpose – serverless

Select optn. Locally Redundant

Step 11

Click OK


Step 11a

Want to use SQL elastic pool? Select No


Step 12

Configure Compute + Storage

Check whether this step is really required

Step 13

Click on Configure DB


Step 14

Goto Basic, click Basic

If you had chosen Server less, this step can be skipped

Step 15

Reduce DB size to 1 GB

If you had chosen Serverless option previously, this step noe reqd. can be skipped.

If you had chosen serverless, DB size is 25 GB

Step 16

Click on Apply


Step 17

Don’t change any other settings


Step 18

Click Review & Create


Step 19

Click Create

… Your Deployment underway, it will take 2 minutes

Step 20

Click Go to Resource


Step 21

Click set server Firewall

Firewall Settings Window Opens

Public Network is Selected


Step 22

Add Client IP

… Leave other Settings untouched

Step 23

Click Save

You see a message

updating Server Firewall Rules

Step 24

You get to see a Message Success

Successfully updated Server FW Rules


Step 25

You need to check from your local system’s SSMS s/w that you are able to connect to this SQL server from your system


Step 26

Click SQL Databases and you will see a list of SQL DBs

DBS – databases

Step 27

Click the DB link and check for the connection string


Step 28

Copy the server name

connection string

Step 29

Copy the server name which appears as

This will appear as part of the long string

Step 30

Open SSMS on your local system

SSMS - SQL Server Management System

Step 31

Type the

Server name

Login = sqltce1



Step 32

Click Connect. You can see Primary Database on Azure management under SSMS control on local system

Next we will create secondary DB server

Step 33

On SSMS, select the DB with name



Step 34

Click Create a Query to open the Query window


Step 35

Create and run the following query

Create table Employee


  Id int identity(1,1),

  Name varchar(50)



Step 36

Also, create and run the following query

Insert into Employee

Select ‘steve’ union

Select ‘sundar’ union

Select ‘Bill’


Step 37

When run the following query,

Select * from Employee

     You get the following output,

          Id        Name

1         1         Bill

2         2         Sundar

3         3         Steve

Inside the table you will have only zeros


Step 38

Go back to Azure dashboard


Step 39



Step 40

In the left panel, under Data management section, you find Replicas. Click that

Previously Replicas was geo-replication

Step 41

Click on + Create


Step 42

Select Target Secondary Region as

(US) East US

& click

Do not select

[] Allow Azure services to access server

Step 43

Click Target servers

Configure Required settings


Step 44

Under New Server fill the following Boxes

Server Name = sqltce1secondary


Server Admin login = sqltce1

Password = ****

Confirm password = ****

Refer This as Secondary

Local & remote server names are different. But local & remote DB names are same

Step 45

Click Select


Step 46

Go to SQL DBs


Step 47

Click TCE1_PRIMARY_DATABASE(sqltce1secondary)


Step 48

Click ‘Set Server Firewall’ option from top row options


Step 49

Public access

[.] Selected networks


Step 50


‘+ Add your client IPv4 address(’

You will get a message “Successfully updated Firewall Rules”

Step 51

Copy Server Name string Copy the server name appears as



Step 52

Go to SSMS on local system & connect to Secondary server sqltce1secondary


Step 53

On the secondary server, in TCE1_PRIMARY_DATABASE.

Run the following query.

select * from Employee


Step 54

You can now see the DB Table you created in the Primary server


Step 54a

If you try to insert any rows in the Secondary DB, you will get an Error message saying, it is READ ONLY

You can check this

Step 55

After FAIL-OVER from Primary to Secondary,  Role reversal will happen. Primary DB will become Secondary DB and vice-versa


Step 56

Go to SQL Servers


Step 57

Go to SQL Databases


Step 58

Select the Primary DB



Step 59

Click on the Replicas in the left panel


Step 60

Go to Secondary server. Right click and you will get a drop-down menu. Select Forced Fail-Over option

After this step Role-Reversal will happen

Step 61

Click YES

On the Right-Top-Corner of the screen, A Message will appear as

Request Submitted …

Step 62

Go to SQL Databases


Step 63

Check the Replica State column and verify & confirm Primary/Writable or Secondary/Read-only status of respective servers


Step 64

Go to SSMS & run some query on the secondary server to insert data into DB 

Insert into Employee

Values (‘Satya’),(‘Ramu’)

The above query will succeed.

Remember previously this query failed giving READ-ONLY Error

Step 65

On SSMS, also run some insert query on earlier-designated Primary. This will fail


Step 66

In the Secondary Server click  in the last column. A drop down will appear. Select Stop Replication





1 Comment

Rated 0 out of 5 stars.
No ratings yet

Add a rating
Apr 07
Rated 4 out of 5 stars.

Good way to learn by following the steps.

You may try to group them into a few discrete events from a DR perspective.

bottom of page