"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

How to setup Geo-Replication in Azure SQL databases ? (youtube.com)

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