Azure-Managed Instance Data Load and Replication

Overview

This article will show you how to use SymmetricDS to load data into a managed instance of SQL Server running in Azure. Once this setup is complete you will have a target Azure-managed instance database that is ready for an initial load of data with change capture support to keep it in sync.

Prerequisites

Here is what is expected to be in place and outside the scope of this article before starting.

• A managed instance of SQL Server setup and running in Azure
• A source database either on-premise or possibly another database in the cloud (does not have to be SQL Server).
• An installed and running version of SymmetricDS Professional edition 3.14.1 or higher

Managed Storage in Azure 

Setting up a managed storage space in Azure will allow for faster loading of data into Azure initially through the use of the bulk insert commands SQL Server provides.

1. First create a new managed storage area if you did not already.

2. Once the storage account is created, record the account name and the access key that will be needed by SymmetricDS to connect to it later.

3. Next create a blob storage container within the managed storage account just created above.

4. Once the blob container is created you will need to generate and recored the shared access (SAS) token that will be needed to connect to the container as well by SymmetricDS for bulk loading. These have an expiration date so create one with an appropriate expiration date so you know when it expires and will need to regenerate a new one and update SymmetricDS properties.

SymmetricDS Setup

1. Assuming SymmetricDS is installed and running per the prerequisites above, the next step is to set up a SymmetricDS node (database connection) to the Azure-managed instance. On the dashboard, there is an add database connection shortcut that will open the connection wizard to begin this setup.

2. Next select SQL Server Azure as the database type and provide the JDBC connection URL to the Azure instance. You can obtain the Azure JDBC URL on the “connection strings” menu in Azure (see second screenshot below)

3. When prompted in the wizard about setting up bulk loading, select the managed storage option. Then provide the account name to the storage created above along with the account key for the storage container. Then you will need to provide the container name and SAS token to connect to the specific blob storage.

4.  Finally finish the connection setup wizard and you have an Azure-managed instance SymmetricDS node that is ready for a bulk data load.   Future steps beyond the scope of this article would be to run through the “Add Tables” wizard (also a shortcut on the dashboard).   Once tables have been selected for replication with the new Azure node as the target an initial load can be requested by going through the “Load Data” wizard (also a shortcut on the dashboard) to seed the new Azure-managed instance with its initial data set.