Things You Need To Know About SQL Data Sync Service. The following is a collection of the things I’m usually asked, things that I usually encounter and clarify in forums and some other little known facts.
So, I thought I’d post it here and just update it from time to time. 1. Provisioning Before a member database can participate in synchronization, the Data Sync Service needs to create database objects to track, enumerate and apply changes. How to: Deliver Changes in Batches (SQL Server) By default, Sync Framework delivers changes to each node in a single DataSet object.
This object is held in memory as changes are applied to a node. The default behavior works well if there is sufficient memory on the computer where changes are applied and the connection to the computer is reliable. Some applications, however, can benefit from having changes divided into batches. Consider the following scenario for a synchronization application: A large number of clients that use SqlCeSyncProvider synchronize periodically with a server that uses SqlSyncProvider.Each client has a limited amount of memory and disk space.The connections between the server and clients are low bandwidth and intermittent, often resulting in long synchronization times and dropped connections.The size of the changes (in KB) for a typical synchronization session is large.
Batching changes is ideal for this type of scenario because it provides the following capabilities: How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server) This topic describes how to provision a database that is synchronized by DbSyncProvider, so that incremental data changes can be tracked in that database.
Changes are tracked so that they can be applied to other nodes during a synchronization session. To provision a database for Sync Framework, follow these steps: Enable snapshot isolation for the databaseIdentify tables to synchronizeCreate tracking tables to store per-table metadata; and create indexes on those tablesCreate triggers on each base table to populate and update the tracking tables(Optional) Handle existing data in the databaseCreate a tracking table to store per-scope metadata; and create an index on this tableDefine scopes to synchronize, which specifies which tables are synchronized as a unitCreate stored procedures to select and update data and metadata These steps are not required for databases that are synchronized by SqlCeSyncProvider; provisioning is handled by Sync Framework when the database is initialized.
Tuning and Monitoring Performance. We recommend the following approach to synchronization performance: Configuring each server and database, and the application code to achieve best performanceDeveloping performance baselinesMonitoring and tuning to meet or exceed baselines The first step in performance tuning is to ensure that hardware and software are configured appropriately.
Server and Network Considerations Ensure that each computer has an adequate IO subsystem, and that database files are properly allocated.The speed of reading and writing changes to disk is typically more important than network speed, so an adequate IO subsystem is essential. It is recommended to use multiple RAID disk arrays, and to have one dedicated array each on the server for tempdb, user databases, and transaction logs. tempdb, user databases, and transaction logs should be created in separate file groups.
Database and Application Design Depending on your application, some of these performance measures might be more important than others. Sync Framework Tips and Troubleshooting - TechNet Articles - United States (English) (5) Microsoft Sync Framework. Things You Need To Know About SQL Data Sync Service. Modifying Sync Framework Scope Definition – Part 1 – Introduction. Introduction Changing Sync Framework scope definition is probably one of the most frequently asked question in the Sync Framework forums.
Changes in scope definition may be due to schema changes such as addition or removal of columns, changes in filter columns or filter clause, or maybe even addition or removal of tables in the scope. Unfortunately, modifying scope definition is not supported by Sync Framework out-of-the-box (. (which makes this probably rank as among the most requested feature). I don’t think i can cover the workarounds in a single blog post so am splitting this into multiple post instead. Before we dive into some of the workarounds on how to effect scope changes, let’s review first what gets provisioned when we define a new scope. So let’s review the objects created by Sync Framework during provisioning. Sync Framework Objects 1. Scope Modifications 1. If a column needs to be added, the _selectchanges stored procedure needs to be altered to include the new column. 2. 3. 4. Provisioning additional scope using Sync Framework’s sync scope provisioning. One of the common questions asked about new scope provisioning in Sync Framework is on how to provision a new or subsequent scope in addition to an existing one.
However, while the Sync Framework documenation mentions provisioning multiples scopes is possible, it doesn’t explicitly mention how to go about it. Here’s the example from the documentation for provisioning a new scope : Code Snippet So let’s say you want to add a new scope for the customer type “Wholesale”. Most people would simply duplicate the code above by simply modifying the filter and setting it to “Wholesale” as follows: The problem is when you run the above code, you’ll encounter an error indicating that the tracking tables already exists for the table.