Sicronizando Framework para SQL Azure
Author: Dinakar Nethi
Contributors: Sean Kelley
Technical Reviewers: Michael Thomassy, Liam Cavanaugh
SQL Azure Database is a cloud database service from Microsoft. SQL Azure provides web-facing database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective
scalability, high availability, and reduced management overhead. This document is not intended to provide comprehensive information on SQL Azure Data Sync. The intent is to provide best practices guidelines on synchronizing SQL Azure with SQL Server and to
supplement the information available at the links in the References section.
Microsoft Sync Framework is a data synchronization platform that can be used to synchronize data across multiple data platforms. There is a provider for each different type of database. A key aspect of Sync Framework is the ability to create custom providers.
Providers enable any data sources to participate in the Sync Framework synchronization process, allowing peer-to-peer synchronization to occur. To include support for SQL Azure, the Microsoft Sync team released the
Microsoft Sync Framework Power Pack for SQL Azure which includes a provider for SQL Azure and a Data Sync tool for SQL Azure.
The SqlAzureSyncProvider is an optimized SQL Azure provider that handles some of the complexities involved in handling transactions with SQL Azure by using Table-valued parameters to reduce round trips to the server and also by using an intelligent “back-off-algorithm”
to scale down the batch size of BCP during synchronization when it hits the throttling levels.
The Data Sync tool is a wizard that helps connect between SQL Server and SQL Azure and automate the synchronization process.
Following are the steps in setting up a Synchronization between an on-premise SQL Server and SQL Azure.
For larger databases you may see faster synchronization times if you create multiple scopes. Since each scope has one thread allocated to it, creating multiple scopes allows for parallel data migrations.
A scope is the combination of tables and filters. For example, you could define a filtered scope named
Sales-WA that contains only the sales data for the state of Washington from the
customer sales table. If you define another filter on the same table, such as
Sales-OR, this is a different scope. If you define filters, Sync Framework does not automatically handle the deletion of rows that no longer satisfy a filter condition. For example, if a user or application updates
a value in a column that is used for filtering, a row moves from one scope to another. The row is sent to the new scope that the row now belongs to, but the row is not deleted from the old scope. Your application must handle this situation.
Initialize via snapshots vs. full initialization wherever possible to improve initial sync time by an order of magnitude
SQL Azure Data Sync uses a custom data provider for the Microsoft Sync Frame synchronization engine written especially for SQL Azure, named "SqlAzureSync Provider." This new provider performs efficiently, lowers the barrier to entry, and ensures reliability
when synchronizing with SQL Azure by intelligently handling some SQL Azure-specific complexities that occur on multi-tenant systems. Specifically, the provider decreases the number of round-trips to the server by using table-valued parameters (TVPs) to apply
changes. In addition, When SQL Azure uses its throttling mechanism to minimize the impact of run-away operations, SqlAzureSyncProvider responds by using a “back-off algorithm” which automatically reduces batch sizes from the default of 5,000 rows during synchronization.
A helpful side-effect of the use of this algorithm is that changes are viewable before synchronization is complete, and synchronization progress can be displayed in real time.
This article is also available in other languages, including Russian.
Wiki: Sync Framework для SQL Azure