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

Action

Comments

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

Give DB name as ‘TCE1_PRIMARY_DATABASE’

 

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 sqltce1primary.database.windows.net

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

password

 

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

‘TCE1_PRIMARY_DATABASE’

 

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

Select ‘TCE1_PRIMARY_DATABASE’

 

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

Select

‘+ Add your client IPv4 address(223.xxx.xx.xx)’

You will get a message “Successfully updated Firewall Rules”

Step 51

Copy Server Name string Copy the server name appears as sqltce1secondary.database.windows.net

 

 

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
Guest
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.

Like
bottom of page