Azure SQL Database - GEO Replication across subscription with private endpoints

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.

 

• Two subscriptions for primary and secondary environments,
  1. Primary Environment: Azure SQL Server, Azure SQL database, and Virtual Network.
  2. Secondary Environment: Azure SQL Server, Azure SQL database, and Virtual Network.

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

• Public access should be enabled during the GEO replication configuration.
• Both your virtual network's subnet should not overlap IP addresses. You can refer to this blog for more information.

 

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

• Creating a geo-replica on a logical server in a different Azure tenant is not supported 
• Cross-subscription geo-replication operations including setup and failover are only supported through T-SQL commands. 
• Creating a geo-replica on a logical server in a different Azure tenant is not supported when Azure Active Directory only authentication for Azure SQL is active (enabled) on either primary or secondary logical server.

 

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.

 

and you can initiate a failover using the below T-SQl command: 

 

ALTER DATABASE [DatabaseName] FAILOVER

 

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.

 

3- Another error, can be due to insufficient permission as follows:

 

Msg 45137, Level 16, State 1, Line 1

Insufficient permission to create a database copy on server 'secservertest1'.

 

Possible solution: make sure the user permission as mentioned in Active geo-replication - Azure SQL Database | Microsoft Docs 

 

Another workaround for many issue, is to create geo-secondary through ARM template, Microsoft.Sql/servers/databases - Bicep & ARM template reference | Microsoft Docs

For example:- How to deploy a sql database with geo replication using azure resource manager templates - Stack Overflow

 

Sample Template:

 

 

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.