Blog Post

Azure Database Support Blog
2 MIN READ

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

Dandan_Zhang's avatar
Dandan_Zhang
Icon for Microsoft rankMicrosoft
Jul 11, 2019

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

Published Jul 11, 2019
Version 1.0
  • Bill-eh's avatar
    Bill-eh
    Copper Contributor

    Thanks! This worked well for IP defined SQL VM where DNS of named instance was not available. Otherwise loop_back_detection seemed to fail....

     

  • Could you clarify which topology is used here?

    Was it  Azure SQL Managed Instance Publisher and Distributor, Azure SQL VM Subscriber 

    If so, on the SQL VM side should we set it up with the publisher again? 
    I tried this out and was facing the following error. 

    Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.


     

    I'm trying out transactional replication for the first time, so pardon me if I'm making some basic mistakes here.