Microsoft Sync Framework synchronization database 3: Set Synchronization

Set Synchronization

Before you can use Sync Framework to synchronize databases, You need to configure it through a process called provisioning. The required setting types vary depending on the database type. This article provides background information, operation steps, and complete code examples for setting SQL Server and SQL Server Compact databases.

Provision and Deprovision)

The first step to configure the database for synchronization is to define a scope that identifies the content to be synchronized. After you define the synchronization scope, you can use this synchronization scope to set up a database to create a change tracking and metadata management infrastructure, which consists of metadata tables, triggers, and stored procedures. After a database is set, you can use a certain provider (such as SqlSyncProvider) to indicate the database and use the SyncOrchestrator object to manage synchronization sessions and connect to other synchronization providers for data synchronization. Setting a database for synchronization is a separate task that is different from synchronizing data to other databases. This code is usually located in a separate application.

Provision)

When setting a (Provision) database, some or all of the following elements are usually created in the database:

• The base table contained in the synchronization scope.
• A trace table for each base table in the scope. This tracking table tracks changes to the associated base table.
• Update the trigger of the tracking table when the base table is changed directly.
• Stored procedures used for synchronization operations, such as enumeration changes, insertion changes, data updates, or data deletion. By setting the SetUseBulkProceduresDefault method of the object, you can create a batch process for SQL Server 2008 and SQL Azure databases to execute multiple rows of insert, update, and delete operations at a time using the table value function.
• A special table that contains synchronization scope information, such as the scope_info table. If these tables do not exist, create these tables.

The database setting object provides corresponding methods to control the creation of the above synchronization elements. For example, the creation of a base table is controlled by the SetCreateTableDefault method, and the creation of a trail table is controlled by the SetCreateTrackingTableDefault method. In addition, synchronization elements can be created in a separate Schema of the database, which is implemented through the ObjectSchema attribute of the SqlSyncScopeProvisioning class.

You can call SqlSyncScopeProvisioning. Apply or SqlCeSyncScopeProvisioning. Apply to Apply settings directly. Alternatively, you can create a script for the SQL Server database and run the script later to set the database. To create a setup Script, call the SqlSyncScopeProvisioning. Script method.

Deprovision)

If you no longer need a scope, you can delete the scope and its associated metadata tables, triggers, and stored procedures by calling SqlSyncScopeDeprovisioning. DeprovisionScope or SqlCeSyncScopeDeprovisioning. DeprovisionScope. After a scope is deleted, it is no longer used for synchronization. If a database element (such as a metadata table) is used by Multiple scopes, the element is not deleted before the last scope of the element is deleted. For example, you have a table named MERs and define two scopes named RetailCustomers and WholesaleCustomers respectively. These two scopes include the Customers table. When you delete the WholesaleCustomers scope, the metadata table used for the MERs table is not deleted because it is still used by the RetailCustomers scope.

If you use a parameter-based filter to filter synchronized data in SQL Server or SQL Azure databases, create a filter template and then create a scope for filtering based on the filter template. You can also call DeprovisionTemplate to easily Delete the filter template, all filtered scopes created based on the template, and all associated metadata tables, triggers, and stored procedures. For example, you create a customerstate_template template that filters data by using the state parameter. You can create two filtered scopes customers_WA and customers_OR Based on customerstate_template. When you delete customerstate_template, customers_WA and customers_OR are also deleted.

You can also call SqlSyncScopeDeprovisioning. DeprovisionStore or SqlCeSyncScopeDeprovisioning. DeprovisionStore to delete all scopes and filters, as well as all metadata tables, triggers, and stored procedures from the database. In addition, because deleting a separate scope and filter template will only delete the metadata at the scope level and template level, but not the metadata at the database level, SqlSyncScopeDeprovisioning. deprovisionStore or SqlCeSyncScopeDeprovisioning. deprovisionStore can be used to clear the remaining metadata tables after deleting all scopes and templates.

 

Set Database Synchronization scope and template

We have learned that the 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.

Sometimes, we need to use a parameter-based filter to filter the synchronized data in SQL Server or SQL Azure databases. First, we need to create a filter template and then create a scope for filtering based on the filter template. For example, if each salesperson is only interested in his/her related order data, we need to create a synchronization scope for each salesperson, we can use a synchronization template to simplify the synchronization scope creation process, and even automate this process through subscription. For a detailed introduction to the filter template, see the next article "How to synchronize and filter data for the database ".

Define synchronization Scope

The following code defines a synchronization scope named filtered_customer and adds two tables to it: Customer and CustomerContact. Because these tables already exist in the server database, you can use the GetDescriptionForTable method to retrieve the table architecture. All the columns in the Customer table are included in the synchronization scope, but the CustomerContact table only contains two columns.

            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");

// Definition for Customer.
            DbSyncTableDescription customerDescription =
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);

            scopeDesc.Tables.Add(customerDescription);

// Definition for CustomerContact, including the list of columns to include.
            Collection<string> columnsToInclude = new Collection<string>();
            columnsToInclude.Add("CustomerId");
            columnsToInclude.Add("PhoneType");
            DbSyncTableDescription customerContactDescription =
                SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);

            scopeDesc.Tables.Add(customerContactDescription);

Set Server Database

The following code creates a setting object for the filtered_customer synchronization scope and specifies that all synchronization elements are created in the "Sync" database architecture. The Code also specifies a filter condition for the Customer table. Only data rows that meet this condition are synchronized. Defining a filter condition is divided into two parts: Calling AddFilterColumn to specify the Filter column name as "mermertype", which will be added to the change tracking table to track changes to the Customer table. Call FilterClause to specify the filter condition itself. It is a WHERE clause that does not contain the WHERE keyword. [Side] is the alias of the change tracking table. After the setting options are defined, the Apply method creates the change tracking infrastructure in the server database and writes the setting script to a file.

            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
            serverConfig.ObjectSchema = "Sync";

// Specify which column(s) in the Customer table to use for filtering data, 
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
            serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
            serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";

// Configure the scope and change-tracking infrastructure.
            serverConfig.Apply();

// Write the configuration script to a file. You can modify 
// this script if necessary and run it against the server
// to customize behavior.
            File.WriteAllText("SampleConfigScript.txt", serverConfig.Script());

Set the client database

You can set the client database in two ways:

1. Fully Initialize an SQL Server or SQL Server Compact client database based on the scope/scope information obtained from the Server or another client database. We can use SqlSyncDescriptionBuilder and SqlCeSyncDescriptionBuilder to obtain the Architecture Information of the synchronization scope and create synchronization objects in the client database.

2. Use an existing client database to initialize a snapshot of an SQL Server Compact client database.

The main purpose of snapshot Initialization is to reduce the time required to initialize the client database. For more information about snapshot initialization, refer to the "setting databases with snapshots" section.

The following code example first obtains the synchronization scope information from the Server and uses them to set an SQL Server Compact client database. Then, set another SQL Server client database based on the scope information of the SQL Server Compact client database.

// Create an SQL Server Compact database and set it with the scope information obtained from the Server
// The Compact database does not support separate schemas,
// So we add the prefix "Sync" for all synchronization objects to make them easy to differentiate
Utility. DeleteAndRecreateCompactDatabase (Utility. ConnStr_SqlCeSync1, true );
Utility. DeleteAndRecreateCompactDatabase (Utility. ConnStr_SqlCeSync2, false );
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder. GetDescriptionForScope ("filtered_customer", null, "Sync", serverConn );
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning (clientSqlCe1Conn, clientSqlCe1Desc );
ClientSqlCe1Config. ObjectPrefix = "Sync ";
ClientSqlCe1Config. Apply ();

// Configure an SQL Server client database through the synchronization scope information obtained from the SQL Server Compact database
// Of course, we can also obtain the scope information from the server.
DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder. GetDescriptionForScope ("filtered_customer", "Sync", clientSqlCe1Conn );
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning (clientSqlConn, clientSqlDesc );
ClientSqlConfig. ObjectSchema = "Sync ";
ClientSqlConfig. Apply ();

 

Unset Database

The following code example shows how:

• Cancel setting a synchronization scope in the client database
• Unsets the filter template and related scopes in the server database.
• Cancel setting a client database completely, so that all synchronization objects will be deleted

Delete A Scope

The following example deletes the RetailCustomers scope in an SQL Server client database. At the same time, the delete scope script is saved to a file, which can be used to delete the RetailCustomers scope in other databases. This step is optional for deleting a synchronization scope.

// Delete the RetailCustomers scope in the SQL Server client database
SqlSyncScopeDeprovisioning clientSqlDepro = new SqlSyncScopeDeprovisioning (clientSqlConn );

// First, save the unset SQL script to cancel setting other SQL Server client databases.
// This step is optional
File. WriteAllText ("SampleDeprovisionScript.txt ",
ClientSqlDepro. ScriptDeprovisionScope ("RetailCustomers "));

// Delete the scope
ClientSqlDepro. DeprovisionScope ("RetailCustomers ");

Delete a filter Template

Before deleting a filter template, we usually need to view the synchronization scope created from this filter template, because when this filter template is deleted (Deprovision, all these scopes will also be deleted. The following SQL script can be used to find the filtering scope created by the customertype_template filter template.

-- Find all filtered scopes that were created from the filtered scope template named 'customertype_template'.

select sync_scope_name from scope_info 
where scope_config_id = 
      (select template_config_id from scope_templates 
where template_name = N'customertype_template')

The following example deletes the customertype_template filter template from the SQL Server database. When this template is deleted, the scope RetailCustomers and WholesaleCustomers created by it are also deleted.

// Delete the "mermertype_template" template from the server database
// In this way, all dependencies and the scope of the template will be deleted.
SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning (serverConn );
ServerSqlDepro. DeprovisionTemplate ("customertype_template ");

Delete all synchronization objects

The following example deletes all synchronization objects from an SQL Server Compact client database, including synchronized metadata tables, triggers, and stored procedures.

// Delete all synchronization objects in the SQL Server Compact database.
SqlCeSyncScopeDeprovisioning clientSqlCeDepro = new SqlCeSyncScopeDeprovisioning (clientSqlCe1Conn );
ClientSqlCeDepro. DeprovisionStore ();

 

Use snapshots to set Databases

If we need to configure synchronization for Multiple SQL Server Compact databases, we can use database snapshots to reduce the time required for initialization. A snapshot is specially prepared, including the table architecture, data (optional), and change tracking infrastructure of the SQL Server Compact database. After we fully Initialize an SQL Server Compact database, we can create a snapshot for it, which can be copied to other SQL Server Compact databases to be synchronized. The Snapshot process for database applications is much more efficient than full initialization.

The following example shows how to set the Compact database by obtaining the scope information from the server database, and then use the snapshot of the database to initialize another database.

Static void SynchronizationUsingSnapshot ()
{
// Set the scope information obtained from the server database to SyncCompactDB2.sdf.
SqlCeConnection clientConn = new SqlCeConnection (@ "Data Source = 'd: \ Sync Framework \ CompactDB \ SyncCompactDB2.sdf '");
SqlConnection serverConn = new SqlConnection ("Data Source = localhost; Initial Catalog = SyncDB; Integrated Security = True ");
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder. GetDescriptionForScope ("productssscope", serverConn );
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning (clientConn, scopeDesc );
ClientProvision. Apply ();

// Create a snapshot from the SQL Server Compact database, which will be used to initialize other Compact Databases
// Although we can obtain the scope information from other databases for initialization, the use of snapshots provides a more convenient and convenient Deployment Solution for the Compact database.
// SyncCompactDB3.sdf is a Compact database that does not exist and will be automatically created in the GenerateSnapshot method.
String newCompactDB = @ "D: \ Sync Framework \ CompactDB \ SyncCompactDB3.sdf ";
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization ();
SyncStoreSnapshot. GenerateSnapshot (clientConn, newCompactDB );

// Synchronize SyncCompactDB3.sdf initialized with a snapshot because the snapshot contains data on the server.
// Therefore, no data is synchronized this time until the data of one party changes.
SyncOrchestrator syncOrchestrator = new SyncOrchestrator ();
SyncOrchestrator. LocalProvider = new SqlCeSyncProvider ("ProductsScope", clientConn );
SyncOrchestrator. RemoteProvider = new SqlSyncProvider ("ProductsScope", serverConn );
SyncOrchestrator. Direction = SyncDirectionOrder. UploadAndDownload;
SyncOperationStatistics syncStats = syncOrchestrator. Synchronize ();
DisplayStats (syncStats );
}

Static void DisplayStats (SyncOperationStatistics syncStats)
{
// Output statistics
Console. WriteLine ("Start Time:" + syncStats. SyncStartTime );
Console. WriteLine ("Total Changes Uploaded:" + syncStats. UploadChangesTotal );
Console. WriteLine ("Total Changes Downloaded:" + syncStats. DownloadChangesTotal );
Console. WriteLine ("Complete Time:" + syncStats. SyncEndTime );
Console. WriteLine (String. Empty );
}