Site icon TheWindowsUpdate.com

Azure SQL Database – GEO Replication across subscription with private endpoints

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Active geo-replication is an Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a server in the same or different data center (region).

 

We have received few cases where customers would like to have this setup across subscriptions with private endpoints. This article describes how to achieve it and set up GEO replication between two Azure SQL servers across subscriptions using private endpoints while public access is disallowed.

 

To start with this setup, kindly make sure the below are available in your environment.

 

               Note: Use paired region for this setup, and you can have more information about paired regions by accessing this link.

 

For this article , the primary and secondary environments will be as below:

 

Primary Environment

 

Subscription ID: Primary-Subscription

Server Name: primaryservertest.database.windows.net

Database Name: DBprim

Region: West Europe

Virtual Network: VnetPrimary

Subnet: PrimarySubnet - 10.0.0.0/24

 

Secondary Environment

 

Subscription ID: Secondary-Subscription

Server Name: secservertest1.database.windows.net

Region: North Europe

Virtual Network: VnetSec

Subnet: SecondarySubnet - 10.2.0.0/24

 

Limitations

 

GEO Replication Configuration

Follow the below steps to configure GEO replication (make sure the public access is enabled while executing the below steps)

1) Create a privileged login/user on both primary and secondary to be used for this setup:

    a. Connect to your primary Azure SQL Server and create a login and a user on your master database using the below script:

 

--Primary Master Database
create login GeoReplicationUser with password = 'P@$$word123'

create user GeoReplicationUser for login GeoReplicationUser
alter role dbmanager add member GeoReplicationUser

 

Get the created user SID and save it:

 

select sid from sys.sql_logins where name = 'GeoReplicationUser'

 

 b. On the primary database create the required user as below:

 

-- primary user database
create user GeoReplicationUser for login GeoReplicationUser
alter role db_owner add member GeoReplicationUser

 

   c. Connect to your secondary server and create the same login and user while using the same SID you got from point A:

 

--Secondary Master Database
create login GeoReplicationUser with password = 'P@$$word123', sid=0x010600000000006400000000000000001C98F52B95D9C84BBBA8578FACE37C3E
create user GeoReplicationUser for login GeoReplicationUser;
alter role dbmanager add member GeoReplicationUser

 

 

2) Make sure that both primary and secondary Azure SQL servers firewall rules are configured to allow the connection (such as the IP address of the host running SQL Server Management Studio).

 

3) Log in with the created user to your primary Azure SQL server to add the secondary server and configure GEO replication, by running the below script on the primary master database:

 

 

-- Primary Master database
alter database DBprim add secondary on server [secservertest1]

 

4) To verify the setup, access your Azure portal, go to your primary Azure SQL database, and access Replicas blade as below:

 

 

You will notice that the secondary database has been added and configured.

 

Note: before moving to the next step make sure your replica has completed the seeding and is marked as "readable" under replica status (as highlighted below):  

 

 

Configuring private endpoints for both servers

 

Now, we will start preparing the private endpoints setup for both primary and secondary servers.

 1) From Azure Portal > Access Primary Server > private endpoints connections blade > add new private endpins as below:

 

we will select the primary subscription to host the primary server private endpoints, 

 

 

 

Next, the primary private endpoint will be linked to the primary virtual network and make sure the private DNS zone is linked to the primary subscription as below: 

 

 

2. Create secondary server private endpoint, from Azure Portal > Access Secondary Server > private endpoints connections blade > add a new private endpoint as below:

 

in the below steps, we will select the secondary server virtual network and subscription,

 

 

 

In the next step, will link the secondary server private endpoint with the primary private DNS Zone, as Both primary and secondary private endpoints should be linked to the same private DNS zone (as below),

 

 

3) Once both private endpoints are created, make sure that they are accepted as mentioned in this document.

 

4) Access your private DNS zone from Azure portal, and verify that both are linked to the same one. This can be checked by accessing Azure portal >  go to private DNS zone > select your primary subscription and check it as below, 

 

 

Note: this step has been discussed in detail in this blog article.

 

Virtual Network setup

You need to make sure your Azure Virtual networks have Vnet peering between primary and secondary, in order to allow communication once the public access is disabled. For more information, you can access this document

 

Disabling public access 

Once the setup is ready you can disallow public access on your Azure SQL servers,  

 

Next

once the public access is disabled, the GEO replication will be running under private endpoints between your Azure SQL server across subscriptions.

 

Troubleshooting

1- You may encounter below error when adding the secondary using T-SQL

alter database DBprim add secondary on server [secservertest1]

 

Msg 42019, Level 16, State 1, Line 1
ALTER DATABASE SECONDARY/FAILOVER operation failed. Operation timed out.
 
Possible solution: Set "deny public access" to off while setting up the geo replication via the T-SQL commands , once the geo replication is set up "deny public access" can be turned back on and the secondary will be able to sync and get the data from primary, public access only needs to be on for setting up the geo replication.

 

2-  Also, You may encounter below error when adding the secondary using T-SQL

alter database DBprim add secondary on server [secservertest1]

 

Msg 40647, Level 16, State 1, Line 1
Subscription 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx' does not have the server 'secservertest1'.

 

Possible solution: Make sure that both private links use the same private DNS zone that was used for the primary.  Refer to blog  for more information.

 

References

Active geo-replication - Azure SQL Database | Microsoft Docs

Using Failover Groups with Private Link for Azure SQL Database - Microsoft Tech Community

 

Disclaimer

Please note that products and options presented in this article are subject to change. This article reflects the Geo Replication across different subscriptions with private endpoints option available for Azure SQL Database in October, 2021.

Closing remarks
 

I hope this article was helpful for you, please like it on this page and share through social media. please feel free to share your feedback in the comments section below. 

Exit mobile version