How to configure bi-directional transaction replication between SQL MI and SQL VM

Recently I got an advisory request on how to configure bi-directional replication in MI and I realize we don't have much documentation on this so hopefully following example script that I tested will be helpful:

 

Goal:  Replicate the same table from MI to SQL VM as well as from SQL VM to MI. 

 

On SQL MI side:

USE [master]

GO

 

EXEC sp_adddistributor @distributor = @@ServerName;

EXEC sp_adddistributiondb @database = N'distribution';

GO

 

:setvar username xxxx

:setvar password xxxxxxxxx

:setvar file_storage "//xxxxx.file.core.windows.net/xxxxx"

:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==;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)';

 

   -- Set variables

:setvar username xxxxx

:setvar password xxxxxxxxxxxxxxxxxx

:setvar source_db xxxxx

:setvar publication_name xxxxxx

:setvar object XXXXXX

:setvar schema dbo

:setvar target_server "VM IP"

:setvar target_username XXXX(I used sql account here)

:setvar target_password xxxxxx

:setvar target_db xxxxxx

 

-- 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)',

@schema_option = 0x80030F3; //I need to use this schema option otherwise the primary key got lost upon replication.

 

-- Add the subscriber

EXEC sp_addsubscription

@publication = N'$(publication_name)',

@subscriber = N'$(target_server)',

@destination_db = N'$(target_db)',

@subscription_type = N'Push',

@sync_type = N'replication support only',  //This option is needed thus we don’t need to run snapshot. Snapshot will cause the replication fail with error “Cannot drop the table 'dbo.ReplicateTable' because it is being used for replication”. We will need to have the schema and data in sync on both sides before this replication.

@loopback_detection ='TRUE'; //This option is also needed for bidirectional replication

 

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

 

On SQL VM side,

1. I used the GUI to create the publication as I got login failed error for some reason when using the TSQL command.
2. I used following script to add subscription

use [dbname]

exec sp_addsubscription @publication = N'publicationname', @subscriber = N'mi.xxxxx.database.windows.net', @destination_db = N'targetdb', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0, @loopback_detection =N'TRUE';

 

exec sp_addpushsubscription_agent @publication = N'publicationname', @subscriber = N'mi.xxxxxxx.database.windows.net', @subscriber_db = N'targetdb', @job_login = N'VM\danzhang', @job_password = 'xxxxxxx', @subscriber_security_mode = 0, @subscriber_login = N'xxxx', @subscriber_password = 'xxxxx', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20190528, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

GO