Walkthrough: Provisioning another SQL Compact Client using Snapshot Initialization

The snapshot initialization is designed to reduce the time required to initialize a client database. After one client database has been initialized by using full initialization, subsequent databases can be initialized by using a snapshot of this first client database. A snapshot is a specially-prepared SQL Server Compact database that contains table schema, data (optional), and change-tracking infrastructure. During the first synchronization session for the new client with the server, client-specific metadata is updated, and any changes that occurred on server since the snapshot was created are downloaded to the client database.

Important

Snapshots should be generated only when there is no activity in the SQL Server Compact database. Concurrent operations of any type are not supported during snapshot generation.

In Walkthrough: Provisioning a SQL Compact Client walkthrough, you created a SQL Server compact database SyncCompactDB and synchronized it with the server database SyncDB. In this walkthrough you will create a console application that creates and initializes a second SQL Server compact database by using the snapshot of SyncCompactDB database.

To generate the snapshot of first client to create second client

1. In Visual Studio, In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLCompact’, point to Add, and click New Project.

2. Select Visual C# from Project Types, and select Console Application from Templates.

3. Type ProvisionSecondCompactClient for project name.

4. Click OK to close the New Project dialog box.

5. In Solution Explorer window, right-click ProvisionSecondCompactClient, and then click Add Reference.

6. Select Microsoft.Synchronization.Data.SqlServerCe and click OK to close the Add Reference dialog box.

7. Repeat the previous two steps to add a reference to the System.Data.SqlServerCe assembly.

8. Add the following using statements to the beginning of the Program.cs file after the existing using statements.

   using System.Data.SqlServerCe;
   using Microsoft.Synchronization.Data.SqlServerCe;
   

9. Add the following code to the Main method to create a SQL connection to the compact database.

10. Add the following code to the Main method to generate a snapshot of this database. This code creates a SqlCeSyncStoreSnapshotInitialization object and invokes the GenerateSnapshot(SqlCeConnection, String) method on it to use a snapshot of the SyncCompactDB database to create and initialize a second SQL Server compact database named SyncCompactDB2 database.

11. In Solution Explorer, right-click ProvisionSecondCompactClient, and click Build.

12. In Solution Explorer, right-click ProvisionSecondCompactClient again, and click Set as Startup Project.

    Warning

    If you do not perform this step, when you press Ctrl+F5 again, the ExecuteCompactSync application is executed.

13. Press Ctrl+F5 to execute the program.

14. Press ENTER to close the command prompt window.

Verify that the second database is created

The following list has steps to verify that the second compact database named SyncCompactDB2 is created.

1. In SQL Server Management Studio, click File menu, and click Connect Object Explorer.

2. In the Connect to Server dialog box, select SQL Server Compact Edition for Server type.

3. Type C:\SyncSQLServerAndSQLCompact\SyncCompactDB2.sdf for Database file, and click Connect.

4. In the Object Explorer, expand Tables (under SQL Server Compact [My Computer\...\SyncCompactDB2], and verify that the Products table along with tracking tables exist.

5. Select SQL Server Compact [My Computer\...\SyncCompactDB2] in Object Explorer.

6. Click New Query on the toolbar.

7. Type and execute the following SQL command to confirm that Products table contains records that were copied from the 1st client.

   select * from Products
   

8. Keep Visual Studio and SQL Server Management Studio open.

Complete Code Example

using System.Data.SqlServerCe;
using Microsoft.Synchronization.Data.SqlServerCe;

namespace ProvisionSecondCompactClient
{
    class Program
    {
        static void Main(string[] args)
        {
            // connect to the first compact client DB
            SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");

            // create a snapshot of SyncCompactDB and save it to SyncCompactDB2 database
            SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization();
            syncStoreSnapshot.GenerateSnapshot(clientConn, @"C:\SyncSQLServerAndSQLCompact\SyncCompactDB2.sdf");
        }
    }
}

See Also

Concepts

How to: Execute Database Synchronization (SQL Server)