Transactional replication allows you to replicate data from one database to another hosted on either SQL Server or Azure SQL Managed Instance. SQL Managed Instance can be a publisher, distributor or subscriber in the replication topology. See transactional replication configurations for available configurations.
In this tutorial, you learn how to:
Configure a managed instance as a replication publisher and distributor.
Configure a managed instance as a replication subscriber.
This tutorial is intended for an experienced audience and assumes that the user is familiar with deploying and connecting to both managed instances and SQL Server VMs within Azure.
Configuring SQL Managed Instance to function as a publisher and/or a distributor requires:
That the publisher managed instance is on the same virtual network as the distributor and the subscriber, or VNet peering or VPN gateways have been configured between the virtual networks of all three entities.
Connectivity uses SQL Authentication between replication participants.
An Azure storage account share for the replication working directory.
Port 445 (TCP outbound) is open in the security rules of NSG for the managed instances to access the Azure file share. If you encounter the error failed to connect to azure storage <storage account name> with os error 53, you will need to add an outbound rule to the NSG of the appropriate SQL Managed Instance subnet.
1 - Create a resource group
Use the Azure portal to create a resource group with the name SQLMI-Repl.
2 - Create managed instances
Use the Azure portal to create two SQL Managed Instances on the same virtual network and subnet. For example, name the two managed instances:
sql-mi-pub (along with some characters for randomization)
sql-mi-sub (along with some characters for randomization)
Copy the storage access keys in the format of:
DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net
Connect to your sql-mi-pub managed instance using SQL Server Management Studio and run the following Transact-SQL (T-SQL) code to create your publisher database:
Connect to your sql-mi-sub managed instance using SQL Server Management Studio and run the following T-SQL code to create your empty subscriber database:
SQL
USE [master]
GOCREATEDATABASE [ReplTran_SUB]
GOUSE [ReplTran_SUB]
GOCREATETABLE ReplTest (
IDINTNOTNULL PRIMARY KEY,
c1 VARCHAR(100) NOTNULL,
dt1 DATETIME NOTNULLDEFAULTgetdate()
)
GO
6 - Configure distribution
Connect to your sql-mi-pub managed instance using SQL Server Management Studio and run the following T-SQL code to configure your distribution database.
SQL
USE [master]
GO
EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO
7 - Configure publisher to use distributor
On your publisher SQL Managed Instance sql-mi-pub, change the query execution to SQLCMD mode and run the following code to register the new distributor with your publisher.
Be sure to use only backslashes (\) for the file_storage parameter. Using a forward slash (/) can cause an error when connecting to the file share.
This script configures a local publisher on the managed instance, adds a linked server, and creates a set of jobs for the SQL Server agent.
8 - Create publication and subscriber
Using SQLCMD mode, run the following T-SQL script to enable replication for your database, and configure replication between your publisher, distributor, and subscriber.
Azure SQL Managed Instance is currently experiencing some backend issues with connectivity with the replication agents. While this issue is being addressed, the workaround is to increase the login timeout value for the replication agents.
Run the following T-SQL command on the publisher to increase the login timeout:
SQL
-- Increase login timeout to 150supdate msdb..sysjobsteps set command = command + N' -LoginTimeout 150'where subsystem in ('Distribution','LogReader','Snapshot') and command notlike'%-LoginTimeout %'
Run the following T-SQL command again to set the login timeout back to the default value, should you need to do so:
SQL
-- Increase login timeout to 30update msdb..sysjobsteps set command = command + N' -LoginTimeout 30'where subsystem in ('Distribution','LogReader','Snapshot') and command notlike'%-LoginTimeout %'
Restart all three agents to apply these changes.
10 - Test replication
Once replication has been configured, you can test it by inserting new items on the publisher and watching the changes propagate to the subscriber.
Run the following T-SQL snippet to view the rows on the subscriber:
SQL
select * from dbo.ReplTest
Run the following T-SQL snippet to insert additional rows on the publisher, and then check the rows again on the subscriber.
SQL
INSERTINTO ReplTest (ID, c1) VALUES (15, 'pub')
Clean up resources
To drop the publication, run the following T-SQL command:
SQL
-- Drops the publicationUSE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'GO
To remove the replication option from the database, run the following T-SQL command:
SQL
-- Disables publishing of the databaseUSE [ReplTran_PUB]
EXEC sp_removedbreplication
GO
To disable publishing and distribution, run the following T-SQL command:
SQL
-- Drops the distributorUSE [master]
EXEC sp_dropdistributor @no_checks = 1GO
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.