Occasionally Connected Applications Overview
An occasionally connected application is an application that uses data from a remote database but that might not always have access to the remote database. Occasionally connected applications are also used when access is continuously available but it might not be efficient to continuously query the remote database for data that is rarely changed, such as a list of states or product categories. In these situations, applications can use data from a local database that is located on the client and periodically synchronize the data with the remote database on the server.
In other words, if your application requires data from a remote database that does not have to be continuously retrieved from the database, you can use a local database cache to store the data on the client computer or directly inside your application. For example, consider an inventory application that uses data from several tables in a database. The number of items in stock for any individual part is an important piece of data that continually changes, so the application should always reflect the current values in the database if possible. However, your application also displays a list of valid shipping companies that rarely changes. These valid shipping companies are stored in the shippers table and do not have to be retrieved every time data is queried from the database. By storing this shippers table in a local database cache, you can decrease the number of unnecessary roundtrips that your application has to make to the remote database. So, in addition to using a local database cache for applications that have occasional connectivity, consider storing data that changes infrequently, or that changes on a known schedule, in a local database cache.
You can configure caching to store individual tables, sets of tables, or specific records queried from a table (for example, only customers who have a specific sales representative).
The synchronization of data between client and server databases is facilitated by Microsoft Synchronization Services for ADO.NET (the Microsoft.Synchronization.Data namespace). You can configure synchronization visually in Visual Studio by using the Configure Data Synchronization dialog box, or by using the Data Source Configuration Wizard when you create a typed dataset. For more information, see How to: Configure Data Synchronization in an Application and Walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard.
In addition, Visual Studio supports the change tracking feature in SQL Server 2008. You can enable SQL Server 2008 change tracking when you configure synchronization from the Configure Data Synchronization dialog box or the Data Source Configuration Wizard. For more information, see How to: Configure Data Synchronization to Use SQL Server Change Tracking.
Local Database Cache Limitation in SQL Azure
You can use the local database cache to synchronize data found in SQL Azure with a local SQL Compact database. However, Cloud based services such as SQL Azure leverage a multi-tenant environment where customers share the same hardware but existing data providers are not streamlined for a multi-tenant environment. To shield one customer from the impact of a workload initiated by another customer, SQL Azure uses a throttling mechanism to limit the amount of resources granted to a single request. Specifically, SQL Azure limits the time a transaction can execute and the amount of log growth that can occur for a single transaction.
You should use these providers for basic scenarios and small amounts of data. If you attempt to sync a large amount of data, the sync operation may fail because of throttling problems. If throttling problems occur, there is no way to reduce the number of changes to avoid sync failures. Furthermore, these providers are not ideal for more advanced scenarios that require bi-directional synchronization.
For detailed information about components that are streamlined for scenarios involving SQL Azure, see https://go.microsoft.com/fwlink/?LinkId=180745.
Synchronization Services API (Microsoft.Synchronization.Data)
Microsoft Synchronization Services for ADO.NET is designed for occasionally connected applications. It lets you synchronize data from disparate sources over two-tier, n-tier, and service-based architectures. Instead of just replicating a database and its schema, the Synchronization Services API provides a set of components to synchronize data between data services and a local store. Applications are increasingly used on mobile clients, such as portable computers and devices, that do not have a consistent or reliable network connection to a central server. It is important for these applications to work with a local copy of data on the client. Equally important is the requirement to synchronize the local copy of the data with data on a central server when a network connection is available. The Synchronization Services API, which is modeled after the ADO.NET data access APIs, gives you an intuitive way to synchronize data. It makes creating applications for occasionally connected environments a logical extension of creating applications for which you can depend on a consistent network connection.
Installing the Synchronization Services API and Documentation
The Synchronization Services API is not part of the .NET Framework. It is installed as part of the SQL Server Compact 3.5 installation. (By default, SQL Server Compact 3.5 is installed when you install Visual Studio.) By default, the Synchronization Services documentation is not installed. Therefore, it will be available only if Help is configured for online use. To install the Synchronization Services documentation for local use, you must first download the Help collection from the Microsoft Synchronization Services for ADO.NET Books Online Web site.
Incorporating Data Synchronization into Applications by Using Visual Studio
To incorporate data synchronization into your application, Visual Studio provides a Local Database Cache template. Local Database Cache is available as a new project item template. (On the Project menu, click Add New Item.) For more information, see How to: Configure Data Synchronization in an Application.
In addition to using the Local Database Cache template, you can also use the Data Source Configuration Wizard to configure synchronization. During configuration of a typed dataset, select the option to Enable local database caching on the Choose Your Database Objects page of the wizard. For more information, see Walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard.
Adding a Local Database Cache opens the Configure Data Synchronization dialog box. You use this dialog box to provide specific information about the client and server in order to set up the required synchronization components. The local database cache uses a SQL Server Compact 3.5 database on the client to store data locally. You can use an existing SQL Server Compact 3.5 database as the local database cache. If you do not yet have a local database, you can use the Configure Data Synchronization dialog box to create a new local database. To create a new local SQL Server Compact 3.5 database, you can set the Client connection (in the Configure Data Synchronization dialog box) to create a new SQL Server Compact 3.5 database based on the tables selected from the Server connection.
Note
The Configure Data Synchronization dialog box enables you to configure Microsoft Synchronization Services for ADO.NET for download scenarios only. This means that after you configure data synchronization by using this dialog box, calling Microsoft.Synchronization.Data.SyncAgent.Synchronize will only update the local database with changes found on the remote database. Changes made to the data on the local database will not be uploaded to the remote database. After you configure data synchronization by using the Configure Data Synchronization dialog box, you can programmatically enable uploads (bidirectional synchronization) during synchronization. For more information, see How to: Configure a Local and Remote Database for Bidirectional Synchronization.
Configuring the Remote Database for Synchronization
For data synchronization to work successfully, there are some additions that are required to each table on the remote database with which you want to synchronize. The Configure Data Synchronization dialog box creates SQL scripts to run against the remote database to create the necessary objects (the objects listed in the table below). All SQL scripts that are created by the synchronization designer are saved to a SQLScripts folder in your project.
Note
The SQL scripts generated by the Configure Data Synchronization dialog box are run by default. You can choose whether the scripts are generated and executed by setting the Script Generation options in the Configure Tables for Offline Use dialog box.
The default behavior of the Configure Data Synchronization dialog box is to automatically run the scripts and update the remote database when the dialog box is closed. Clear the Execute scripts when this dialog is closed check box if you do not want to automatically run the scripts. Additionally, if the remote database already has the required tracking columns, triggers, and deleted-items table, no scripts will be generated. In other words, if no changes are required to the remote database, no scripts are created.
The following table lists the items required on the remote database and provides an explanation for each:
Additions to each synchronized table on the remote database |
Explanation |
|---|---|
LastEditDate column |
This column should be a DateTime or TimeStamp on each table being synchronized. It is compared to the LastEditDate column on the client to identify records that have been modified on the server since the last synchronization call. |
CreationDate column |
This column should be a DateTime or TimeStamp on each table being synchronized. It is compared to the CreationDate column on the client to identify records that have been added to the server since the last synchronization call. |
deleted items table (TableName_Deleted) |
Items are moved to this table when they are deleted from the table on the database server. This is done to identify records deleted from the server since the last synchronization call. Each table being synchronized requires a deleted items table to track records deleted from the remote table. |
DeletionTrigger (TableName_DeletionTrigger) |
This trigger runs every time that a record is deleted from the server database table. Deleted records are moved to the deleted items table. They are moved to the deleted items table because records in the client database that are not in the server database might be treated as new records and added back to the server. Synchronization Services checks the deleted items table to determine that it should delete a deleted record from the client database instead of adding it back to the server database. |
InsertTrigger (TableName_InsertTrigger) |
This trigger populates the CreationDate column with the current date and time when new records are added. |
UpdateTrigger (TableName_UpdateTrigger) |
This trigger populates the LastEditDate column with the current date and time when existing records are modified. |
Starting the Synchronization Process from an Application
After providing the required information in the Configure Data Synchronization dialog box, add code to your application to initiate synchronization. It is important to understand that synchronizing data updates the local database, not the table in the dataset or any other object in your application. Remember to reload your application data source with the updated data from the local database. For example, call the TableAdapter.Fill method to load your dataset's data table with the updated data from the local database.
See Also
Tasks
How to: Configure Data Synchronization in an Application
Walkthrough: Creating an Occasionally Connected Application
How to: Configure a Local and Remote Database for Bidirectional Synchronization
Concepts
SQL Server Compact 3.5 and Visual Studio