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