IFI Techsolutions - Microsoft Gold Partner

Author- Ashfaq Pathan (Associate Data Engineer)

Transactional Replication Setup

We will set up a transactional replication for migrating the database from Managed Instance to SQL Server 2017 hosted on Azure SQL-VM.

Managed Instance will be both Publisher and Distributor.
SQL Server will be Subscriber.

Prerequisites:

1. Azure Subscription.
2. Storage account with one file-share in it (Create using the azure portal).
3. VNet Peering (You need this only if your SQL-MI & SQL-VM are on different networks)
4. One Azure Managed Instance (Create using the azure portal).
5. One SQL VM (Create using the azure portal).

Requirements

A. VNet peering:

Note:(You can Skip VNet Peering if you have both(SQL-MI & SQL-VM) resources in the same Network)

There must be network connectivity between SQL-MI and SQL Server. Ensure that both servers are on the same VNet. If that is not the case then a VNet peering setup would be required.

In my case, I do not have SQL-MI & SQL-VM on the same network. I have done a VNet Peering to get them on the same network. Below are steps with Screenshots :-

1: VNet peering. form SQL-VM to SQL-MI.

Step 1: Open VNet of SQL-VM (You can get this from the Resource group where you have your SQL-VM Click on the Peerings option in the left bar)

Step 2: Click on add button.

Step 3: Add name and select subscription and SQL-MI VNet from the dropdown.

Step 4: Check peering status.

TRSQL-vnet = I have SQL-VM in this VNet, VNet-sqlmitest21 = I have SQL-MI in this VNet.

2: VNet Peering from SQL-MI to SQL-VM.

Step 1: Open VNet of SQL-MI (You can get this from the Resource group where you have your SQL-MI) Click on the Peerings option in the left bar.

Step 2: Click on add button.

Step 3: Add name and select subscription and SQL-VM VNet from the dropdown.

Step 4: Check Peering status

VNet-sqlmitest21 = I have SQL-MI in this VNet, TRSQL-vent = I have SQL-VM in this VNet.

B. ADD Outbound Port Rule:

Outbound TCP port 445 needs to be open for transactional replication, so in case you have NSG make sure this port is allowed. Allowing this port will enable Managed Instance to access the Azure Storage account, which will be covered a bit later. To allow the port, go to the virtual network of your Managed Instance and identify the security group name.
Step 1: Get the Security group name.

Step 2: Open NSG search with NSG name in the portal and open it.

Step 3: Add Port 455 in outbound security rules.

C. Storage Account:

Transactional replication will require a storage account, Within the Storage account, create a File Share with a quota of 10 GiB.
Go to the File share Properties and copy the storage URL because this value will be needed in the following T-SQL script.

Step 1: Open Storage account add new File share and open it.

Step 2: Copy URL from File share properties. Eg: https://Storage-name/file-name

Step 3: Copy key for that. Go to Storage account > Access key > Connection string.

Important: From the Storage account, We need 2 things :- URL Path & Connection String. Copy this and save.

D. Configure replication:

Step 1 Start VM and open SSMS and connect SQL-MI with Private endpoint & SQL-VM with Private IP.

Step 2: Configure distribution
Connect to your SQL-MI managed instance using SQL Server Management Studio and run the following T-SQL code to configure your distribution database.

USE [master]

EXEC sp_adddistributor @distributor =’Sql-mi.database.windows.net’, @password = ‘xyz123456789′

EXEC sp_adddistributiondb @database = N’distribution02’

Step 3: Configure publisher to use a distributor.
On your publisher, SQL Managed Instance SQL-MI, run the following code to register the new distributor with your publisher.

EXEC sp_adddistpublisher @publisher = ‘sql-mi.database.windows.net’, — primary publisher

@distribution_db = N’distribution02′,

@security_mode = 0,

@login = N’user123′,

@password = N’pasword@123456789′,

@working_directory = N’\\storage-account-name.file.core.windows.net\file_share_name’,

@storage_connection_string = N’Connection-string’

— example: @storage_connection_string =N’DefaultEndpointsProtocol=https;AccountName=replstorage;Ac

Step 4: Create a publication.

Step 4.1 SQL-MI > Replication > Local Publications > Right Click Create New Publication.

Step 4.2 Click on the Next button.

Step 4.3 Select the database you want to publish and click Next.

Step 4.4 As shown in the screenshot below, for Publication Type, choose Transactional publication and click Next.

Step 4.5 In the ‘Articles’ step, choose objects you want to replicate. In this example, we will choose all Tables and click the Next button.

Step 4.6 In the ‘Filter Table Rows’ step, we don’t add any filters, just click Next.

Step 4.7 In the ‘Snapshot Agent’ step, check “Create a snapshot immediately” and click Next.

Step 4.8 In the ‘Agent Security’ step, click on Security Settings to provide credentials for both Snapshot Agent and connection to the Publisher.

Step 4.9 Add user & password of SQL-MI and click on ok.

Step 4.10 In the ‘Complete the Wizard’ step, Provide the name for the publication and click Finish.

Step 4.11 This Wizard will show progress and once the process is done you should see a Success sign with 0 Errors and 0 Warnings. Click the Close button. With this, the publication is successfully created.

Step 5: Configure replication subscription.

When distribution and publication are configured, we need to create a subscription as the last piece of transactional replication setup. In the context menu of the Publication, Follow the below steps: –

Step 5.1 From publication right-click [New Subscription].

Step 5.2 New Subscription Wizard will open click on the Next button.

Step 5.3 In the ‘Publication’ Step, select the Publisher, database, and the publication and click Next.

Step 5.4 In the ‘Distribution Agent Location’ step, select the option to Run all agents at the Distributor and click Next.

Step 5.5 In the ‘Subscriber’ step, click on Add Subscriber and then Add SQL Server Subscriber.

Step 5.6 Connect to the SQL Server that will be the Subscriber. This SQL Server will be the target for the database migration. Use the server’s local IP address for this connection.

Step 5.7 For the newly added Subscriber, for the Subscription Database, choose the new database option from the drop-down menu, and in the New Database window, provide the Database name. This will be the name of the target database for the migration. Then click OK and then Next.

Step 5.8 In the Distribution Agent Security step, click on the button with three dots.

Step 5.9 In the ‘Distribution Agent Security’ step provides login and password for Distributor and Subscriber.

Step 5.10 Click on the Next button.

Step 5.11 In the ‘Synchronization Schedule’ step, set the Agent Schedule to ‘Run continuously’. This will enable continuous data flow from the source database to the destination and help with the online migration.

Step 5.12 In the ‘Initialize Subscription’ step, set ‘Initialize When’ to ‘Immediately’. Click on Next.

Step 5.13 In the Wizard Actions step, check the ‘Create the subscription(s)’ option and click Next.

Step 5.14 Complete the wizard click finish.

Step 5.15 Wait for Success status on ‘Creating Subscription(s)’ step.Click on close.

Step 6: Once this is done, from the context menu of the newly created subscription, open the ‘View Synchronization Status. You will see its progress and eventually all transactions will be replicated.

Step 6.1 Right-click on subscription, click on View Synchronization Status.