Microsoft Sync Framework Database Synchronization 1: Database Synchronization Overview

Database Synchronization Overview

Overview

Sync Framework supports synchronization between databases. It provides an intuitive and flexible API that allows you to generate applications for offline and collaborative solutions. With this API, you can use all or part of the provided components to complete synchronization based on the application architecture and requirements. Sync Framework supports client-server, client-to-client, and hybrid topology. In the client-server topology, all clients are synchronized with a central server. In the client-to-client topology, each client can be synchronized with other clients, and changes do not need to be transmitted through the central server. The hybrid topology is composed of the client-to-client topology and the client-server topology.

Traditional Distributed System

With the rapid development of computer network technology, especially the application and popularization of Internet technology, distributed computer software has become increasingly important. After all, distributed technology brings us data sharing, making interaction and collaboration possible. It is becoming increasingly important for an enterprise to support mobile and remote staff. It is important that organizations ensure that these staff members access the same information as they do in their offices. In most cases, these users will use portable computers, office desktops, smartphone or PDA, and remote users will use these devices to access the data warehouse over the network. However, this traditional distributed solution has the following serious defects and cannot achieve satisfactory results:

• Network requirements

To allow users to access information, remote devices must be able to continuously connect to the company network when accessing data. This may not be a problem for some staff (such as those working at home. However, it may be difficult for other users (such as sales staff who often travel. For example, if a salesperson is visiting a customer and cannot access inventory data because there is no network connection, the salesperson cannot complete the work effectively.

• Data access speed

In a typical client-server company environment, users have a high-speed network with fast access to information. However, remote staff often connect through a slow network that is not reliable. When using a typical solution, each time a user needs a copy of data, the download is required because the data cannot be stored on the device for a long time. For example, each time a salesperson opens an application, he/she must download its product list. However, it may take a long time to fill the application with information, which makes the user very worried.

• Single fault point

When using this solution, users often rely on a single server database. If the database is unavailable due to a planned server downtime or unexpected Server failure, the connection between all remote staff and their data will be interrupted.

• Server scalability

As the number of remote staff increases, the performance of the company's servers will be affected, and more hardware may need to be added.

Occasionally Connected applications

An alternative to traditional distributed system solutions is to implement occasionally connected applications. Occasionally Connected applications allow remote staff to access data, but the information they need is stored locally.Occasionally Connected applications usually use data synchronization to populate the local database.

Data Synchronization includes the following functions: regularly obtain information stored in a database (such as a client database) and synchronize changes with other databases (such as server databases. The advantage of the synchronization-based solution is that users no longer need to use uninterrupted network connections to access information. Because the data is stored locally, users can access the data at any time and reduce the processing load of the central database. In addition, because the network speed is no longer a limiting factor, users can now access data at the speed of local computers.

Client-to-client synchronization can avoid single point of failure, because it makes synchronization independent of all changes need to go through the server. This function pushes a large number of applications to each client and helps solve the scalability of servers.

Yes, as we can see, occasionally connected applications are very suitable for programs that cannot stay connected due to network problems, and can improve system performance and enhance reliability. What we need to do is to synchronize data between the local database and the remote database. The Sync Framework provides excellent and complete support for Database Synchronization, saving us from implementing complex synchronization logic and Exception Handling for each system.

Sync Framework supports synchronization between databases. It provides an intuitive and flexible API that allows you to generate applications for offline and collaborative solutions. With this API, you can use all or part of the provided components according to the application architecture and requirements. Sync Framework supports client-server, client-to-client, and hybrid topology.

The Sync Framework uses Synchronization Providers to communicate with each synchronized database. The Sync Framework database provider (similar to the ADO. NET data provider) protects other Sync Framework components from the specific implementation methods of each database. The Sync Framework includes five database providers that support two solutions: offline and collaboration. ShowOffline solution: On the client-In the server topology, multiple clients connect to a central server to synchronize data when the connection is available.

A common extension of this Occasionally Connected Application is the ability to support collaborative application solutions.In the collaboration solution, the two databases can be synchronized in a peering manner without using a central hub.Shows a hybrid topology where a central server synchronizes with multiple clients and these clients can synchronize with each other. This topology supports applications that allow users to share project remarks. Project Team members often require local copies of data they can process. When they make changes, they can synchronize the above-mentioned changes with the server, or exchange changes with other team members.

 

Database Synchronization provider

The Sync Framework contains three major Database Synchronization providers, which are applicable to both offline and collaborative solutions. They support client-server, client-client, and hybrid topology. We can combine these providers in various ways to synchronize the topology solution we selected. Because of the use of the Sync Framework metadata, they can even be synchronized with other Sync Framework providers. The three synchronization providers are as follows:

SqlSyncProvider

• Synchronize SQL Server, SQL Server Express, and SQL Azure databases.
• Provides rich and configurable preparation tools to create necessary synchronization structures for databases, such as metadata tables and stored procedures.
• Flexible filtering options are provided to filter the tables to be synchronized by row or column. The parameter-based filtering mechanism supports maximum reuse of database elements.
• The corresponding components are provided to help us manage synchronization metadata, including clearing metadata and recovering element data (when we recover a database from a backup ).

SqlCeSyncProvider

• Synchronize SQL Server Compact databases
• Provides simplified preparation tools to create necessary synchronization structures for databases, such as metadata tables and stored procedures.
• You can efficiently create new SQL Server Compact databases based on the prepared SQL Server Compact database snapshot.
• Corresponding components are provided to help us manage synchronization metadata.

DbSyncProvider

• It is usually used to synchronize non-SQL Server databases compatible with ADO. NET.
• A fully functional API is provided to allow any database compatible with ADO. NET to synchronize data.

Offline-Only Providers

The other two Database Synchronization providers only support the offline solution. These providers have been replaced by those that support both offline and collaborative solutions. For newly developed applications, use the provided above. Offline solution providers include DbServerSyncProvider and SqlCeClientSyncProvider.

These two providers have the following features:

• Suitable for client-server topology.
• Always Used in pairs. For example, you cannot use two instances of ServerSyncProvider to synchronize two Server databases (Instead, use SqlSyncProvider or DbSyncProvider ).
• The collaboration provider may not be complex, but cannot participate in the topology together with other Sync Framework providers.

 

Database Synchronization architecture and database-like synchronization Architecture

In offline and collaborative scenarios, the Sync Framework can be used in Two-layer and N-layer architectures. In any architecture, the following activities occur in a synchronization session: a synchronization orchestrator communicates with two synchronization providers to obtain the database and change the application. The synchronization provider uses a synchronization adapter that contains related SQL Commands to synchronize each table. Some providers expose these adapters to allow us to customize these SQL commands, while others automatically generate adapters to reduce the complexity of synchronization.

Shows a two-tier architecture: All components in a synchronization session are located on the Local Computer and contain a connection directly connected to the remote database. In a synchronization session, a local computer is the computer that initiates the synchronization. If we want to synchronize multiple computers, each computer must contain all the components shown in the figure.

Shows an N-layer architecture: This architecture requires additional components, including components and services on the remote computer. The proxy on the local computer will process the connection to the remote database. Developers must implement proxy and service by themselves.

The database involved in synchronization can be SQL Server 2005 SP2 or any later version, including SQL Server Compact 3.5 SP2 and later versions, SQL Azure, or ADO.. NET provider. Sync Framework automatically configures change tracking and metadata storage for SQL Server and SQL Server Compact databases.

Sync Framework SyncOrchestrator (synchronization agent)

The synchronization agent drives the synchronization process in the following ways:

• The order and Direction of application changes are determined based on the Direction attribute.
• Call the server synchronization provider to retrieve the server database and change the application.
• Call the client synchronization provider to retrieve the client database and change the application.

In addition, the synchronization agent maintains session-level information for synchronization operations and provides successful messages, errors, and statistics to applications on the client.

SyncProvider (SqlSyncProvider, SqlCeSyncProvider, and DbSyncProvider)

The synchronization provider communicates with the corresponding database and shields the specific implementation of the synchronization proxy and database. For SQL Server Compact databases, use SqlCeSyncProvider; for other versions of SQL Server, including SQL Azure, use SqlSyncProvider; for non-SQL database, use DbSyncProvider. All three Providers are inherited from RelationalSyncProvider. The main activities of the synchronization provider are as follows:

• Retrieve the changes that have occurred in the (local) database since the last synchronization.
• Incremental changes to (remote) database applications.
• Detects conflicting changes.

SyncAdapter (DbSyncAdapter and SqlCeSyncAdapter)

The synchronization adapter imitates the data adapter in ADO. NET and defines the synchronization adapter for each table to be synchronized. The synchronization adapter provides specific SQL commands required for the synchronization provider to interact with the database.

For the synchronization adapters used by SqlSyncProvider and SqlCeSyncProvider, Sync Framework automatically generates related SQL commands, which can reduce the synchronization complexity. For DbSyncAdapter, we need to create the adapter and specify related SQL commands, such as InsertCommand, which can be inserted into the server database application from the client database. Because the synchronization adapter uses the ADO. NET DbCommand object, you can use any command structure supported by ADO. NET. This includes inline Transact-SQL, stored procedures, views, and functions. These commands only need to define a single result of the structure and data to be transmitted and applied.

Other APIs

Shows the main classes in the API. However, there are still many classes not shown in the figure. To obtain information about all available classes, see Microsoft. synchronization, Microsoft. synchronization. data, Microsoft. synchronization. data. sqlServerCe and Microsoft. synchronization. data. server is a detailed API definition in dll. The following section describes other important classes that you should be familiar.

DbSyncScope(Synchronization scope)

Synchronization scope is a logical grouping of objects to be synchronized. For Database Synchronization, a synchronization group is usually a set of data tables, and the data tables can be filtered. A data table can be contained in one or more synchronization groups.

Database Provisioning Objects(Database preparation object)

For SQL Server and SQL Server Compact databases, the Sync Framework provides a set of classes to describe the synchronization group and the data tables contained in each synchronization group. After these tables and synchronization groups are defined, we can use the Sync Framework object to prepare a script (provisioning scripts) for each node application ). These scripts create an infrastructure for change tracking and change applications, including metadata tables, triggers, and stored procedures. The following table lists the classes used to prepare databases and providers:

SQL Server, SQL Azure	SQL Server Compact	Description
DbSyncScopeDescription	DbSyncScopeDescription	Indicates the synchronization scope. The synchronization scope is a logical group of tables that can be synchronized by a unit (which can be filtered.
SqlSyncScopeProvisioning	SqlCeSyncScopeProvisioning	Specifies the settings of SQL Server, SQL Azure, or SQL Server Compact databases in a specific scope.
SqlSyncProviderScopeConfiguration	SqlCeSyncProviderScopeConfiguration	Indicates the configuration information used by SqlSyncProvider or SqlCeSyncProvider for a specific scope.
DbSyncTableDescription	DbSyncTableDescription	Indicates the architecture of the table included in the synchronization scope.
DbSyncColumnDescription	DbSyncColumnDescription	Indicates the structure of the columns of the table included in the synchronization scope.
SqlSyncDescriptionBuilder	SqlCeSyncDescriptionBuilder	Indicates the scope and table information of the SQL Server, SQL Azure, or SQL Server Compact database involved in synchronization.
SqlSyncTableProvisioning	SqlSyncTableProvisioning	Sets the SQL Server, SQL Azure, or SQL Server Compact database represented by the DbSyncTableDescription object.
SqlSyncProviderAdapterConfiguration	SqlSyncProviderAdapterConfiguration	Indicates the configuration of the synchronization adapter for a database table.

 

SyncOperationStatistics

Session statistics are a set of statistics provided by the synchronization agent for each synchronization session. Statistics include information related to the synchronization time, the number of changes processed, and any conflicts or exceptions.

DbSyncSession

The DbSyncSession object provides access to the synchronized session variables. Session variables are some variables provided for developers. They are used as parameters for selecting, inserting, updating, and deleting commands on the server.

Sync Framework DLLs

The Database Synchronization classes in the Sync Framework are included in the following DLLs:

Microsoft. Synchronization. dll, including SyncOrchestrator.

Microsoft. Synchronization. SqlServer. dll, including SqlSyncProvider.

Microsoft. Synchronization. SqlServerCe. dll, including SqlCeSyncProvider.

Microsoft. Synchronization. Data. dll, including RelationalSyncProvider, DbSyncProvider, and DbSyncAdapter.

 

This article describes the background and advantages of Database Synchronization and the specific Database Synchronization provider, as well as its architecture and important synchronization classes, in the next article, we will use a complete instance to demonstrate how to use these providers and related classes to complete our synchronization tasks.