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
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
@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)';
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.
