How to: Configure Data Synchronization to Use SQL Server Change Tracking

  • Article

You can use Visual Studio to enable SQL Server change tracking when you are connected to a SQL Server 2008 database.

Note

The option to enable SQL Server change tracking is available only when the connection to the remote database server is configured to connect to a SQL Server 2008 database.

Enabling SQL Server Change Tracking by Using the Configure Data Synchronization Dialog Box

The following procedures provide the steps that you must follow to configure data synchronization and enable SQL Server change tracking when you configure Synchronization Services by using the Configure Data Synchronization dialog box. For an example that uses real data and configures synchronization by using the Configure Data Synchronization dialog box, see Walkthrough: Creating an Occasionally Connected Application.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To configure data synchronization by using the Configure Data Synchronization dialog box

  1. On the Project menu, click Add New Item.

  2. Click the Local Database Cache template.

  3. Either provide an alternative name or keep the default name of LocalDataCache1.sync.

  4. Click Add.

    A .sync file is added to the project, and the Configure Data Synchronization dialog box opens.

  5. Set the Server connection to the remote SQL Server 2008 database that you want to connect to.

  6. Set the Client connection to the local SQL Server Compact 3.5 database that will store your data locally. If you do not have a local database, you can leave the default setting of DatabaseName.sdf (new) to create a new database in the project. The name of the new database is based on the name of the database on the server.

    Note

    By default, the OK button is disabled and is enabled after you add a table to the Cached Tables area.

  7. Select Use SQL Server change tracking. By default, this option is selected when the Server connection is set to a SQL Server 2008 database.

  8. Click Add to open the Configure Tables for Offline Use dialog box.

  9. Select the tables you want to add to the local database cache, and configure the synchronization mode (the data to download when you synchronize):

    Data to download:

    • New and incremental changes after first synchronization

      This setting retrieves records from the server that have been modified since the last time data was synchronized. The first time synchronization is called, the whole table will be downloaded.

    • Entire table each time

      This setting replaces the local table with the version that is currently on the server.

      Note

      Set the synchronization mode for each table that you are configuring for offline use. The additional disabled choices are available only when SQL Server change tracking is not enabled.

  10. Click OK.

  11. Click Show Code Example to open the Code Example dialog box, which provides a code example that starts the synchronization process. You can copy this code to the Clipboard and then paste it into your program.

  12. Optionally, set the Advanced options.

    The Advanced options in the Configure Data Synchronization dialog box provide settings that enable you to control whether tables are synchronized separately or in a single transaction. The options also enable code separation for n-tier applications.

    • Synchronize tables in a single transaction. By default, this option is cleared and all tables are synchronized individually. If errors are encountered, only tables that have errors have their changes rolled back. If you select this option, all tables are synchronized in a single transaction. If errors are encountered, all changes for all tables are rolled back.

    • Create synchronization components. By default, synchronization components are generated for both the client and the server. You can set this option to Client only or Server only to meet your application requirements.

    • Server project location. By default, the synchronization components for the server will be generated in the current project. Set this option to generate synchronization components for the server into any Visual Basic or Visual C# project in the solution.

    • Client project location. By default, the synchronization components for the client will be generated into the current project. Set this option to generate synchronization components for the client into any Visual Basic or Visual C# project in the solution.

  13. Click OK.

  14. If the synchronization mode for one of the locally cached tables is set to New and incremental changes after first synchronization, updates are required on the SQL Server database, and the Generate SQL Scripts dialog box opens. By default, both options in the dialog box are selected:

    • Update server for incremental changes. Select this option to automatically update the server to enable incremental synchronization immediately after closing this dialog box.

    • Save SQL scripts in the project for later use. Select this option to add the generated SQL scripts to the project so that they can be executed on the SQL Server database later. Undo scripts are also generated and added to the project.

  15. Click OK.

    Note

    SQL Server change tracking will not be enabled until the server is updated to enable change tracking.

    After you click OK, the Data Source Configuration Wizard opens to the Choose Your Database Objects page. (The Data Source Configuration Wizard automatically opens every time that a local database file is added to a project.)

  16. Select the tables to add to the typed dataset.

  17. Click Finish.

After you configure data synchronization by using the Configure Data Synchronization dialog box, you have to add code to the application to initiate the synchronization.

Note

When you synchronize data, the local database is updated, not the table in the dataset or any other object in the 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.

To add code to initiate synchronization

  • If you copied the code from the Code Example dialog box in the previous section, you can just paste it into your application. Otherwise, you can insert the following code anywhere in your application where you want to initiate the synchronization process:

    ' Call SyncAgent.Synchronize() to initiate the synchronization process.
' Synchronization only updates the local database, not your project's data source.
Dim syncAgent As LocalDataCache1SyncAgent = New LocalDataCache1SyncAgent()
Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()

' Add code here to refill your application's data source
' with the updated data from the local database.

    // Call SyncAgent.Synchronize() to initiate the synchronization process.
// Synchronization only updates the local database, not your project's data source.
LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
Microsoft.Synchronization.Data.SyncStatistics syncStats =
syncAgent.Synchronize();

// Add code to refill your application's data source
// with the updated data from the local database.

Enabling SQL Server Change Tracking by Using the Data Source Configuration Wizard

The following procedures provide the steps that you must follow to configure data synchronization and enable SQL Server change tracking when you configure a typed dataset by using the Data Source Configuration Wizard.

For an example that uses real data and shows how to configure synchronization while you create a typed dataset by using the Data Source Configuration Wizard, see Walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard.

To configure data synchronization to use SQL Server change tracking by using the Data Source Configuration Wizard

  1. On the Data menu, click Show Data Sources.

  2. In the Data Sources window, click Add New Data Source to open the Data Source Configuration Wizard.

  3. Leave the default value of Database selected on the Choose a Data Source Type page, and click Next.

  4. On the Choose Your Data Connection page, perform one of the following steps:

    • If a data connection to the SQL Server 2008 version of the database you want is available in the drop-down list, select it.

      -or-

    • Select New Connection to open the Choose Data Source or Add/Modify Connection dialog box and create a new connection to a SQL Server 2008 database. For more information, see Add/Modify Connection Dialog Box (General).

  5. If the database requires a password, select the option to include sensitive data, and then click Next.

  6. Click Next on the Save the Connection String to the Application Configuration File page.

  7. Expand the Tables node on the Choose Your Database Objects page.

  8. Select the tables you want to add to the local database cache.

  9. Select Enable local database caching.

  10. Click Next on the Choose Your Database Objects page.

  11. Select the tables you want to cache on the Choose Tables to Cache page.

  12. Set the Synchronization Mode.

    • New and incremental changes after first synchronization

      This setting retrieves records from the server that have been modified since the last time data was synchronized. The first time synchronization is called, the whole table is downloaded.

    • Entire table each time

      This setting replaces the local table with the version that is currently on the server.

  13. Select Use SQL Server change tracking. By default, this option is selected when the Server connection is set to a SQL Server 2008 database.

  14. Click Finish.

  15. If the synchronization mode for one of the locally cached tables is set to New and incremental changes after first synchronization, updates are required on the SQL Server database, and the Generate SQL Scripts dialog box opens. By default, both options in the dialog box are selected:

    • Update server for incremental changes. Select this option to automatically update the server to enable incremental synchronization immediately after closing this dialog box.

    • Save SQL scripts in the project for later use. Select this option to add the generated SQL scripts to the project so that they can be executed on the SQL Server database later. Undo scripts are also generated and added to the project.

  16. Click OK.

    Note

    SQL Server change tracking will not be enabled until the server is updated to enable change tracking.

After you complete the Data Source Configuration Wizard, you have to add code to the application to initiate the synchronization.

Note

When you synchronize data, the local database is updated, not the table in the dataset or any other object in the 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.

To add code to initiate synchronization

  • Insert the following code anywhere in your application where you want to initiate the synchronization process:

    ' Call SyncAgent.Synchronize() to initiate the synchronization process.
' Synchronization only updates the local database, not your project's data source.
Dim syncAgent As LocalDataCache1SyncAgent = New LocalDataCache1SyncAgent()
Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()

' Add code here to refill your application's data source
' with the updated data from the local database.

    // Call SyncAgent.Synchronize() to initiate the synchronization process.
// Synchronization only updates the local database, not your project's data source.
LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
Microsoft.Synchronization.Data.SyncStatistics syncStats =
syncAgent.Synchronize();

// Add code to refill your application's data source
// with the updated data from the local database.

See Also

Tasks

Walkthrough: Creating an Occasionally Connected Application

Walkthrough: Deploying an Occasionally Connected Client Application with the Local Database

Concepts

Occasionally Connected Applications Overview

Other Resources

Occasionally Connected Applications (Local Database Caching)