[Solved]: Replication monitor could not retrieve Publisher information from the distributor (In azure managed instances)

Hi Readers,

I am writing this blog to document a workaround for a connectivity issue I was facing in my Replication monitor (in SSMS).I am hoping those who are facing this issue, would be benefited from this blog.

 I was configuring MI-MI  replication with T-SQL scripts and at the end when I was trying to launch Replication monitor by right clicking on the publication , I have encountered the below error, which was basically telling me that the replication monitor is unable to connect to distributor or cannot retrieve the publisher information from the distributor.

Error message:

Clicking on the show details button above got me the below text

Message details:

===================================

Replication Monitor could not retrieve Publisher information from Distributor 'tcp:xxxxxx.xxxxxxxx.database.windows.net,1433'. (Replication Monitor)

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18131.0&EvtSrc=Microsoft.SqlServer.Management.UI.ReplicationMonitorErrorSR&EvtID=CantGetPublisherInformation&LinkId=20476

===================================

Could not refresh properties of this object from server. Check if this object still exists on the server.  (Microsoft.SqlServer.Rmo)

------------------------------

Program Location:

   at Microsoft.SqlServer.Replication.ReplicationObject.Refresh()

   at Microsoft.SqlServer.Management.UI.ReplMonitorMenu.Invoke()

<End of the error message>

Looking at the error message, I thought this must have something to do with the publisher details maintained in the distributor. I went and right-clicked on the Replicationàselected Distributor propertiesàWent further for the ‘publishers’ page in that window and could see everything was perfect there. And moreover I could successfully test the data inserted at the publication DB being sent successfully to the subscriber too. This cannot be anything to do with the permissions, as I have used SQL accounts with sysadmin access everywhere.

After browsing through some online forums and having found no solutions, I opened a case with Microsoft as I felt that the replication monitor is important for us if we are ever going to use replication in the Prod setup and we didn’t have the luxury of time to keep exploring solutions online.

The gentle man from azure team tried a few options like downloading the latest SSMS and trying to remove the publisher from the replication monitor and adding it back. Unlucky for both of us, nothing really worked.

On the very next day, I stumbled upon a workaround that saved time for both of us which was removing publisher from the replication monitor and adding it back using the third option.

The option that I used was the one in the screenshot

I have supplied the distributor server name and could successfully use the replication monitor to insert tracer tokens, check latency data, look at the agent profiles etc with no issue whatsoever.

I am hoping you would find this blog content useful or Microsoft fixing this issue in their next SSMS release. Thanks for visiting my blog.

Configuring Transactional replication between two Azure managed instances

Hello Readers !

Welcome to another blog on Azure Managed instance and this time I will be walking you through a demo on configuring transactional replication between two managed instances.
Hope you have read my blog on the HA and DR options in Azure managed instance. If you have not, please check it here .In the replication section, i have given some details on the limitations of transactional replication in Managed instances. I have done the below POC taking such into consideration.


We will be using T-SQL scripts for configuration this time as it looks to be too easy compared to the UI steps .

Pre-requisites that are needed to configure replication between two instances

1.A storage account and a SMB Fileshare in it to be used as snapshot folder.
2.Port 445 (TCP outbound) is open in the security rules of NSG for the managed instances to access the Azure file share
3.Also MSDN says the managed instances should not be participating in Active Geo-replication and there has to be a VNET peering done if the instances are in different VNets.

My setup for the POC Test

I have two managed instances sharing the same Vnet/Subnet and I am connected to them in SSMS over a point to site VPN connection that we have setup earlier. (I will write a different blog on configuring this ).I have renamed the private endpoint URL for security reasons, but have followed the same pattern in the scripts for you to identity them as publisher and Subscriber.

Publisher/Distributor : tcp:xxxxxxmi1.xxxxxx.database.windows.net,1433
Subscriber : tcp:xxxxxxgp.xxxxxx.database.windows.net,1433
Publisher Database(Source DB)  : AODB
Article to replicate : Action (Its a table i have in AODB Database)
Login used: AzureAdmin
 (I have used the same account name for the SQL logins in both the instances and gave sysadmin rights earlier for administration use. You can choose different logins as you please).
Storage Account : xxxxxstorageac

Subscriber Database : AODB_Repl_POC

I have created a fileshare in my storage account named 'ReplicationSnapshot' with a decent storage quota of 10 GB.For testing tiny table replications, you don't even need this much. 







I  went to the Network Security Group (NSG) of my managed instance subnet and have added 445 TCP outbound rule for enabling connectivity from the managed instances to the azure storage Fileshare. The service tag that you see below is corresponding to the location of my storage account.  (Basically i have every one of my POC resources in East US2). You should choose the service tag based on your storage account location.

You should also take a note of your storage account connection string that contain the access keys for authenticating Storage account connections from your managed instance  to the Fileshare in your storage account.

Take a note of what is given against ConnectionString as given in the below screenshot which needs to be supplied in "setvar file_storage_key" variable in the script.

Scripts for configuring Replication

All the below scripts are to be executed in SQLCMD mode (You can select it from Query menu in your SQL management studio, when you have a new Query window opened) and need to be executed in the publisher server. Please make sure to change the parameter values corresponding to your setup.


Configuring distribution and distribution DB
EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

Adding the publisher -distributor relationship in the distribution database and adding working directory for distributor

--Please select everything from the below line till the "End of the batch" and execute as a single batch.

--Set Variables
:setvar username AzureAdmin
:setvar password <Password>
:setvar file_storage "\\xxxxxstorageac.file.core.windows.net\replicationsnapshot"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=xxxxxstorageac;AccountKey=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx=;EndpointSuffix=core.windows.net"

USE [master] EXEC sp_adddistpublisher 

@publisher = @@ServerName,
@distribution_db = N'distribution',
@security_mode = 0,
@login = N'$(username)',
@password = N'$(password)',
@working_directory = N'$(file_storage)',
@storage_connection_string = N'$(file_storage_key)';

--End of the batch

Create Publication and Subscriber

--You may execute the below as a single batch against the publication Database. 

-- Set variables 

:setvar username AzureAdmin 

:setvar password <Password>

:setvar source_db AODB 

:setvar publication_name Publish_poc 

:setvar object Action 

:setvar schema dbo 

:setvar target_server "xxxxxxgp.xxxxxx.database.windows.net" 

:setvar target_username AzureAdmin 

:setvar target_password <Password> 

:setvar target_db AODB_Repl_poc 

-- Enable replication for your source database 

USE [$(source_db)] 

EXEC sp_replicationdboption 

  @dbname = N'$(source_db)', 

  @optname = N'publish', 

  @value = N'true'; 

-- Create your publication 

EXEC sp_addpublication 

  @publication = N'$(publication_name)', 

  @status = N'active'; 

-- Configure your log reaer agent 

EXEC sp_changelogreader_agent 

  @publisher_security_mode = 0, 

  @publisher_login = N'$(username)', 

  @publisher_password = N'$(password)', 

  @job_login = N'$(username)', 

  @job_password = N'$(password)'; 

-- Add the publication snapshot 

EXEC sp_addpublication_snapshot 

  @publication = N'$(publication_name)', 

  @frequency_type = 1, 

  @publisher_security_mode = 0, 

  @publisher_login = N'$(username)', 

  @publisher_password = N'$(password)', 

  @job_login = N'$(username)', 

  @job_password = N'$(password)'; 

-- Add the ReplTest table to the publication 

EXEC sp_addarticle  

  @publication = N'$(publication_name)', 

  @type = N'logbased', 

  @article = N'$(object)', 

  @source_object = N'$(object)', 

  @source_owner = N'$(schema)'; 

-- Add the subscriber 

EXEC sp_addsubscription 

  @publication = N'$(publication_name)', 

  @subscriber = N'$(target_server)', 

  @destination_db = N'$(target_db)', 

  @subscription_type = N'Push'; 

-- Create the push subscription agent 

EXEC sp_addpushsubscription_agent 

  @publication = N'$(publication_name)', 

  @subscriber = N'$(target_server)', 

  @subscriber_db = N'$(target_db)', 

  @subscriber_security_mode = 0, 

  @subscriber_login = N'$(target_username)', 

  @subscriber_password = N'$(target_password)', 

  @job_login = N'$(target_username)', 

  @job_password = N'$(target_password)'; 

-- Initialize the snapshot 

EXEC sp_startpublication_snapshot 

  @publication = N'$(publication_name)'; 

 --End of the batch

Its recommended to change the timeout of the agent jobs to workaround a known issue in azure as recommended by MS.

Changing timeout for agent jobs 

-- Increase login timeout to 150s 

update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'  

where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %' 

After executing the scripts , I can see that the table got replicated to the subscriber database and you will get to see files added to your azure storage Fileshare too.

I am not adding the screenshots of sample inserts at the publisher to my article to limit the length of the blog.I am leaving that to you and you may also add more articles to the publications and reinitialize subscription to make it reflect in your subscriber database.

EXEC sp_addarticle

      @publication = Onprem_publish_poc,

      @article = <Tablename>,

      @source_object = <Tablename>

I will write another blog on my experience in fixing a replication monitor error in Azure managed instance.