It seems a recurrent topic this week in the SQL Azure forums, how do I remove an Azure Data Sync installation so I stop my syncing process and could delete all the Data Sync stuff (tables, stored procedures and so on) from my local databases?

SQL Azure Data Sync is a cloud service for scheduling bi-directional synchronizations between on-premise (SQL Server) and cloud (SQL Azure) databases in a myriad of possibilities (on-prem/Azure, Azure/Azure…). This synchronization service is based on Microsoft Sync Framework and the scenarios where it could be helpful comprises all kind of data synchronization for provide high availability and data proximity where they are used by applications, generally on the cloud.

SQL Azure Data Sync supports synchronizations between multiple databases: SQL Azure databases and SQL Server and SQL Azure databases so you can create custom synchronization groups that fit your business requirements and maintains your data where they are needed for whoever that could need them.

The Syncing Players

When thinking in using the service is important to understand the synchronization scenario and its players. There are two environments (generally), the on-premise databases (you can use Azure Data Sync only for SQL Azure database syncing but usually you will provide different data sources including some local SQL Server databases). With these two main scenarios in mind, the players would be

  • Local SQL Server Databases (databases, tables and columns to sync)
  • Local Syncing Agent (generated during  the Synchronization group definition)
  • Cloud SQL Azure Databases (databases, tables and columns to sync)
  • SQL Azure Data Sync Synchronization Group (synchronization model and flow)

The Syncing Model (Sync Topology)

Once that you have clear knowledge of what you want to synchronize and that you have all the players ready is time  to define the Synchronization model or de Synchronization group, a collection of databases, tables, columns and conditions you want to define (synchronization rules) and a synchronization schedule starting from 5 minutes or more (hours, days, months… according with your business needs).

Once you have all defined (using Windows Azure Platform Management Portal and Data Sync configurations) you have to deploy your model. During the definition you are provided with a Sync Agent you have to download, install and configure to expose those databases you want to be visible for the synchronization service.

When you deploy, Windows Azure establishes all the needed communication and initiate logging collection and creates the data structures and objects needed in every database to allow the process of synchronization (it’s a lot of stuff if you take a look to your local exposed database).

The problem of deleting a Synchronization group

When all the synchronization process up and running, agents make their job checking for changes and initiating synchronization processes in the specified direction (as defined in your syncing model).

So at the time of stopping the service and trying to delete if you don’t have a clear understanding of the process you may find yourself with lots of error trying to delete a database subscription to the service that doesn’t allow you to delete because there are pendent jobs or logging errors, and on and on, so we will take a look at  the process of de-provisioning, stopping and deleting a SQL Azure Data Sync configuration.

The process of de-provisioning a Data Sync Service

the process starts just at the opposite way than the process of installation.

  • First you have to have your local servers, databases and agent up and running, and connected to the Data Sync service with the appropriate keys (generated from inside Windows Azure Platform Management Portal). The first process is to select local databases using this agent and click Unregister option. This will inform to the Azure service to stop synchronization agents and will restore your database to its initial state deleting Data Sync data structures and objects (tables, stored procedures, etc.). I have found many people trying to start the process directly from Windows Azure, and trying to delete the servers and the data sync groups or the databases participating in the synchronization, all of these actions means problems due the first step is stopping the local databases.

  • When your local databases have been unregistered, next step is to delete databases and agents from Windows Azure using Windows Azure Platform Management Portal. Select data sync databases node and delete data sync agents and databases. There should be no problem as agents are stopped and no local databases are involved in syncing now.
  • Last step is deleting the syncing model, edit the topology and delete databases. If you can’t delete the databases you will see there is an option under the database icons in the topology for Force deletion, you can use it for deleting the databases from the model and finally you are ready to delete de Data Sync group.
  • Finally, you should have a Data Sync server without Data Sync groups and you can use it for hosting other Sync Groups or to delete if you don’t need the server. Remember you can provision another new one whenever you needed.

With these easy steps you could de-provision your Data Sync configuration. I hope you find this process useful if you are experiencing problems when stopping or deleting Data Sync objects now.

Happy cloud experience!