How to: Use Synchronization Scopes
This topic shows you how to use different kinds of scopes to describe what you want to synchronize between databases, including which situations benefit from the use of multiple scopes.
For more information about provisioning, see How to: Provision and Deprovision Synchronization Scopes and Templates (SQL Server).
For more information about how to run the sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Scopes
Synchronization scopes describe what you want to synchronize between two databases. Only items that fit within the scope description are synchronized; all other items outside the scope are not synchronized. A scope contains a description of the tables that are synchronized. For each table in the scope, you can specify that only a subset of the columns is included in the scope, and you can define a filter that limits the rows that are included in the scope. A database can contain multiple scopes, but it is important to design your scopes correctly so that synchronization happens in the most efficient way possible. To understand the way that scopes can be used, it is important to know what a scope contains and what database elements are created when a scope is created. The following sections describe the elements of the various kinds of scopes that can be created. The elements described are created when the default settings are used. Configuration options, such as SetCreateTableDefault can be used to control which elements are created when a scope is provisioned. For more information about configuration options, see How to: Provision and Deprovision Synchronization Scopes and Templates (SQL Server).
Database Elements Created for a Simple Scope
The simplest scope contains one table and no filters, such as the one created in a SQL Server database by the following code:
When this scope is provisioned to the database, the following elements are created for the scope:
A tracking table, named Sales.Customer_tracking. This table contains synchronization metadata for the items in the Sales.Customer table, such as the timestamp of the last change, and which peer made the change. There is a one-to-one relationship between the rows in the tracking table and the rows in the table that it tracks.
An entry in the scope_info table that contains metadata for the scope, such as the name of the scope and the synchronization knowledge of the scope.
An entry in the scope_config table that contains configuration information for the scope. Configuration information is stored in an XML format and contains adapter information for the table. The adapter information names the tracking table, stored procedures, and triggers that are used to maintain the synchronization metadata for the table, as well as a description of the columns that are contained in the table. This XML description is used to configure the adapter object that represents this table during synchronization.
Stored procedures prefixed with the table name, such as Sales.Customer_insert. These stored procedures are used to make updates to the table and to the tracking table, as well as to enumerate changes for synchronization.
Triggers are created and added to the Sales.Customer table, prefixed with the unqualified name of the table, such as Customer_insert_trigger. These triggers are used to update the metadata in the tracking table when a local change is made to the table.
Database Elements Created for a Filtered Scope
The tables in a scope can be filtered by column, by row with a static filter, and by row with a parameter-based filter.
Scope with a Column Filter
For column filtering, you define which columns in each table are to be included in the scope. During synchronization, data is sent only for the columns in the scope. For example, a scope that contains one table and only two columns in that table can be created in a SQL Server database by using the following code:
A scope that contains a column filter results in the same database elements as a simple scope, with the following differences:
The configuration information for this table in the scope_config table contains descriptions only for the columns that are included as part of the scope. When the adapter for this table is configured, it contains only the columns that are included in the scope, so only the specified columns are represented during synchronization, and only the specified columns can be processed by the adapter.
Some of the stored procedures used for updating the table during synchronization differ from the unfiltered case, such as the Sales.Customer_insert and Sales.Customer_update procedures. These procedures contain parameters only for the columns that are included in the scope, so only the columns in scope can be changed during synchronization.
Scope with a Static Row Filter
For static row filtering, you define a filter clause that defines the criteria a row must meet to be included in synchronization. For example, a scope that contains one table and a static row filter can be created in a SQL Server database by using the following code:
A scope that contains a static row filter results in the same database elements as a simple scope, with the following differences:
The configuration information for this table in the scope_config table contains filter clause and filter column information.
The tracking table for this table contains an additional column for each filter column. This column contains the entries from the base table and is used by Sync Framework to filter the tracking table.
The Sales.Customer_selectchanges stored procedure contains the filter clause as a WHERE clause that is used to filter the rows returned by this procedure.
The triggers for the Sales.Customer table include code to copy the filter column information from the base table to the tracking table when changes are made to the base table.
Scope with a Parameter-based Row Filter
For parameter-based row filtering, you define a template for the filter and then create filtered scopes by specifying values for the template parameters. For example, a scope that contains one table and a parameter-based filter can be created in a SQL Server database by using the following code:
For more information on parameter-based filters, see How to: Filter Data for Database Synchronization (SQL Server).
The elements created for parameter-based row filtering are similar to those used for static row filtering with the following differences:
The configuration information for this table in the scope_config table includes descriptions of the filter parameters.
An entry is added to the scope_parameters table for the filter. The entries in this table contain XML that describes the filter parameters and their associated values. The parameters and values in this XML description are used by the adapter to specify the filter values during synchronization.
The WHERE clause in the Sales.Customer_selectchanges stored procedure contains the parameterized filter clause defined for the FilterClause property in the provisioning code.
Database Elements Created for Additional Scopes
A database can contain multiple scopes, and the scopes do not all have to be of the same kind. Each additional scope, no matter the kind, will result in the following elements being added to the database:
An entry in the scope_info table.
An entry in the scope_config table.
The per-table elements, such as the tracking table and stored procedures, are added for a new scope only when the table is not yet contained in any other scopes. When a table has already been provisioned as part of another scope, the per-table elements are not changed. The following code example creates a scope that contains a Sales.Customer table, and creates a second scope that contains the Sales.Customer table and a Sales.CustomerContact table. Because the Sales.Customer table was already provisioned by the first scope, including it in the second scope does not change or add any new per-table elements for the Sales.Contact table. However, all per-table elements are added for the Sales.CustomerContact table because it has not yet been provisioned as part of another scope.
Because per-table elements are not changed when a new scope is added, all scopes that share a table must use the table in the same way. For example, a table is provisioned to include only two columns for scope A. Later, scope B is provisioned. Scope B contains the same table but specifies that all columns are included in the scope. Provisioning succeeds, but synchronization of scope B fails because the stored procedures used for synchronization of scope A, such as the selectchanges procedure, handle only the two columns specified by scope A, which does not match the expected set of columns synchronized by scope B.
Configuration options for per-table elements are not applied when the element already exists in the database. For example, scope A is provisioned to use bulk procedures for the table it contains. Scope B is provisioned and specifies that bulk procedures should not be used for the same table. Because the table has already been provisioned, bulk procedures are used for this table for both scope A and scope B.
You can specify that Sync Framework create a new selectchanges procedure to be used for a new scope that contains a table that has already been provisioned. This is useful when the second scope uses a different static row filter than the first. For example, scope A contains one table that is not filtered. Scope B contains the same table, and filters rows based on a filter clause of [side].[CustomerType] = ‘Retail’. When scope B is provisioned, specify Create for SqlSyncScopeProvisioningSetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption). This causes Sync Framework to create a second selectchanges procedure for enumerating changes in scope B. The filter column is also automatically added to the tracking table.
Parameter-based filters accommodate multiple scopes by using extra tables that contain parameter and filter information. When an additional scope is provisioned with new filter parameters, the new scope reuses all of the existing elements for a table, adding only a row to the scope_parameters table that contains the parameter values for the scope. For more information on parameter-based filters, see How to: Filter Data for Database Synchronization (SQL Server).
Considerations when Using Scopes
A scope is a 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, be aware that 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.
Scopes can be distinct or they can overlap with each other. Two scopes overlap if they share common data between them. For example, the table products could be included in a sales scope and an inventory scope. Scopes can be both overlapping and filtered. The following scenarios demonstrate ways in which filtering and overlap can occur:
Scenario 1:
Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
Scope 2 is sales-OR. This scope includes: products; orders, with a filter of state=OR; and order_details, with a filter of state=OR.
In this scenario, the entire products table is shared by both scopes. The orders and order_details tables are in both scopes, but the filters do not overlap; therefore the scopes do not share rows from these tables.
Scenario 2:
Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
Scope 2 is sales-Northwest. This scope includes: products; orders, with a filter of state=WA OR state=ID; and shippers.
In this scenario, the entire products table is again shared by both scopes. The orders table is in both scopes and the filters overlap: both scopes share the rows that satisfy the filter state=WA. The shippers and order_details tables are not shared between the scopes.
There are many different ways in which scopes can be defined, but the following principle must be followed: any data that is synchronized between a pair of databases in the synchronization topology can belong to only one scope. For example, in Scenario 2 above, Database A and Database B could synchronize Scope 1; and Database A and Database C could synchronize Scope 2. Database A and Database B cannot also synchronize Scope 2 because of the products and orders rows that belong to both scopes. This is because different scopes are treated as different replicas, so a change by Scope 1 is seen as a new change when Scope 2 is synchronized, and the change synchronized by Scope 2 is then seen as a new change when Scope 1 is synchronized, resulting in an infinite loop of changes.
Improving Performance by Using Multiple Scopes
In certain situations, multiple scopes can improve performance. One such situation is when your synchronization community uses a hub-spoke topology, synchronizing many clients to a single central server. If you use a single scope, the scope_info table on the server contains a single row to represent that scope for all clients. Because this row in the scope_info table contains synchronization metadata that must be updated on the server each time a client is synchronized, frequent synchronizations by many clients can lead to contention over the row in the server database, which can slow down synchronization for the entire community. The solution is to create a new scope for each client. This creates a separate row in the scope_info table for each client, removing contention over the table. Because each scope treats the tables in the same way, all per-table elements are reused, so the scope_info entry and a scope_config entry are the only additional elements created in the database.
Be aware that, because the same data is contained in multiple scopes, the scopes must only be used between the client and the server, and not among clients, otherwise synchronization between clients will never converge.
The following code example provisions two scopes to a server database, and provisions each scope to a different client database. The two scopes contain the same table. Each client is then synchronized with the server.