With Azure products gaining popularity, it’s a common scenario these days to find a part of data being held in the cloud (Windows Azure database) environment. This makes it essential to keep the reference data synchronized between on-premise as well as cloud databases. This post will provide you with couple of ways in which you can maintain data synchronization between your cloud and on-premise databases.
Method 1: Using the SSIS Export/Import Wizard
You can create an SSIS package using the Export/Import Wizard, which will export all of the required data from your on-premise SQL Server database to the cloud database. The connection manager to be used for Azure DB is .NET Framework data provider for SQL Server. Once you provide the server URL, the database name (Initial Catalog), and give the SQL authentication details that you use for connecting to the cloud database; you can start mapping the required objects for exporting the data to your cloud database. Once you create this package, you can save it as a .DTSX file and create a SQL agent job to execute it. This can then be scheduled to automate data synchronization.
Method 2: Provisioning Data Synchronization Using the Data Sync Application
The data sync application can be used for synchronizing data between Azure and the on-premise database. It uses a hub which would be an Azure database for this synchronization. The starting point is to create a data sync instance from the Azure management portal.
After the data sync service is created, click on the “Create Azure SQL Database” link and register the SQL Azure database through the below screen:
The Sync Direction is set to be bi-directional to enable data transfer to and from the Azure database. The next step is to add the on-premise SQL Server to the data sync application. For this, click on the on-premise SQL database icon and launch the register SQL Server screen, like below:
You need to install a client synchronization agent on your on-premise server and register the instance before you link it to data sync application. To do this, see the screen below:
Next, it will launch the “Install a New Agent” screen:
You need to generate a key and use it for completing the registration of the installed agent client on your on-premise SQL Server.
Once the agent is registered, you can proceed in registering the on-premise SQL Server instance(s) to the synchronization agent on your server.
After finishing this step, you can come back and include your on-premise SQL Server to sync the group through the below screen:
The next step is to add a hub database for the SQL Azure database using similar steps to the ones outlined above. Once all the databases are added to the sync group, you need to define the data set which is to be involved in the data synchronization process. This involves identification of tables with column names, which are to be included in synchronizing process. We may also define a filter, if needed, over the table data in the data set definition. We can also define a schedule for data synchronization.
The following step is to deploy the data sync group using the deploy option from the top:
This will start the synchronization process and does the data synchronization based on a set schedule.
The data sync application maintains few tables, like below, in the database to store the details on the synchronization process.