Setting up Replication in a SQL Managed Instance

Question

Post reply

Setting up Replication in a SQL Managed Instance

DanielP

SSCrazy

Points: 2302
More actions
May 9, 2022 at 9:51 pm

#4029700

I am running into the error 53, failed to connect o azure storage when setting up replication in SQL Managed Instance. The replication is within the same SQL Instance.
Port 445 (TCP outbound) is open.
Could there be another cause for this error?
When setting it up the @Workign_Directory param and @Storage_Connection_String seem to have the correct values when running sp_adddistpublisher.

Any ideas on how best to troubleshoot?

Thanks,

Daniel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

DanielP SSCrazy Points: 2302 More actions · Answer 2

Just following backup on this. I still don't have any resolutions on it. Again, trying to setup Transactional Replication in SQL Managed Instance from DatabaseA to DatabaseB in the same Instance. There seems to be all the necessary permissions on the File Share in Azure verified. Copying the @Storage_connecton_string from Azure Access Key for the FileShare. The @Working_directory is using '\\####.file.core.widnows.net\####.

When I create the Publication and it creates the initial Snapshot Job in the Job step error log it shows the following:

022-05-26 19:22:25.27 Obtained Azure Storage Connection String for kruxdev1usw2file01

2022-05-26 19:22:25.27 Connecting to Azure Files Storage '\\######.file.core.windows.net\#######'

2022-05-26 19:22:51.05 Failed to connect to Azure Storage '' with OS error: 53.

I have reviewed some of the very few existing articles on it and no luck.

Transactional replication - Azure SQL Managed Instance | Microsoft Docs

Requirements

Use SQL Authentication for connectivity between replication participants.

Use an Azure Storage Account share for the working directory used by replication.

Open TCP outbound port 445 in the subnet security rules to access the Azure file share.

Open TCP outbound port 1433 when the SQL Managed Instance is the Publisher/Distributor, and the Subscriber is not. You may also need to change the SQL Managed Instance NSG outbound security rule for allow_linkedserver_outbound for the port 1433 Destination Service tag from virtualnetwork to internet.

Place both the publisher and distributor in the cloud, or both on-premises.

Configure VPN peering between the virtual networks of replication participants if the virtual networks are different.

Thanks,

Daniel

DanielP SSCrazy Points: 2302 More actions · Answer 3

This was resolved. It was an open port issue and the order of it. All good.