Configuring transactional replication with Azure SQL Managed Instance there are some limitations that we need to take into consideration. One of them is that both the publisher and distributor need to be placed either on Managed Instance or on the on-premises server. The other requirements can be found here.
In the docs there are already covered two types of topologies:
- Azure SQL Managed Instance Publisher, Distributor and Subscriber
- Azure SQL Managed Instance Publisher and Distributor, Azure SQL VM Subscriber
In this article we will focus more on the scenario in which instead of a Azure SQL VM Subscriber, we will have an on-premises Subscriber. Being similar to the second above topology type the steps will also be alike, after configuring the on-premises and Managed Instance servers we need to do the following:
After that comes the create subscription step where the difference will be in the @subscriber parameter. For an on-premises subscriber we will not need a private DNS zone so the syntax will be similar to this:
use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'OnPremServerName',
@destination_db = N'ReplSub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
exec sp_addpushsubscription_agent
@publication = N'ReplTest',
@subscriber = N'OnPremServerName',
@subscriber_db = N'ReplSub',
@job_login = N'azureuser',
@job_password = '<Complex Password>',
@subscriber_security_mode = 0,
@subscriber_login = N'azureuser',
@subscriber_password = '<Complex Password>',
@dts_package_location = N'Distributor'
GO
Common issues
- Failed to connect to Azure Storage
2019-11-19 02:21:05.07 Obtained Azure Storage Connection String for replstorage 2019-11-19 02:21:05.07 Connecting to Azure Files Storage '\replstorage.file.core.windows.net\replshare' 2019-11-19 02:21:31.21 Failed to connect to Azure Storage '' with OS error: 53.
Recommendations:
- Ensure that the port 445 is open in the outbound security rules of the Network Security Group (NSG) that your Managed Instance is using
- Check Distributor properties as below:
Right-click on Local Publications and access Distributor properties:
After that access the Publishers page and press on the below highlighted area:
After that you should have a window similar to this:
In here please check that the Default Snapshot Folder is in the correct format and for the Storage Account Connection String you can also re-enter it here from here:
More details on this error are accessible here Failed to connect to Azure Storage.
- Could not connect to Subscriber
Error messages:
The process could not connect to Subscriber 'REPLMITOVM.REPLDNS.COM'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
Get help: http://help/MSSQL_REPL0
Named Pipes Provider: Could not open a connection to SQL Server [53]. (Source: MSSQLServer, Error number: 53)
Get help: http://help/53
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 53)
Get help: http://help/53
Query timeout expired, Failed Command: (Source: MSSQLServer, Error number: HYT00)
Get help: http://help/HYT00
Recommendations:
- Ensure that the port 1433 is open in the outbound security rules of the Managed Instance NSG
- As an alternative to T-SQL script mentioned here you can also add the subscription directly from the GUI of SSMS:
Right-click on the publication and click New Subscriptions
Click Next, select the publication name and click Next, Run all agents at the Distributor, Add SQL Server Subscriber:
Here we will need to connect to the on-premises server with SQL Authentication as mentioned in the first part of the requirements.
After the subscription was added click on the highlighted area below:
On the top part use login that you are using for the distributor (SQL login that you are accessing the Azure SQL Managed Instance) and on the bottom part of the window enter the SQL login used previously to access the on-premises server.
After that you can leave the Synchronization Schedule together with the Initialize Subscription steps as default and Finish the New Subscription Wizard.
As a short summary the recommendations for the Could not Connect to Subscriber error would be:
- Ensure that the port 1433 is open and that the other requirements are met
- Try as an alternative to add the subscription with SSMS
- Provide the IP address of the on-premises server in the sp_addsubscription stored procedure
- Check if the @@SERVERNAME is the same with SERVERPROPERTY(N'servername')
SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME
If the results are different this can be fixed by using this script:
DECLARE @actualname NVARCHAR(100)
DECLARE @currentname NVARCHAR(100)
SELECT @actualname = CONVERT(NVARCHAR(100), SERVERPROPERTY(N'servername'))
SELECT @currentname = @@SERVERNAME
EXEC sp_dropserver @currentname
EXEC sp_addserver @actualname, local
Reference article: SQL SERVER – FIX – Replication Error: SQL Server replication requires the actual server name to make...
More details about the Could not connect to Subscriber error can also be found here.