How To: Provision for Synchronization Using Snapshot Initialization
This topic describes how to provision a SQL Server Compact database efficiently by using a snapshot of a database that is already initialized. The examples in this topic focus on the following Sync Framework classes:
SqlCeSyncStoreSnapshotInitialization
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.
Provisioning the Clients
If you are provisioning more than one SQL Server Compact database for synchronization, you can reduce the total time required for initialization by using a database snapshot. A snapshot is a specially-prepared SQL Server Compact database that contains table schema, data (optional), and change-tracking infrastructure. After you have fully initialized one SQL Server Compact database, you create a snapshot that you copy to subsequent SQL Server Compact databases that you want to synchronize. The process of applying the snapshot to the database is faster than fully initializing it.
During the first synchronization session for a client, client-specific metadata is updated, and any changes that occurred 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.
The code examples shown here demonstrate creating and initializing a SQL Server Compact database, and then using a snapshot of that database to initialize a second database.
The following code example retrieves scope information from the server and uses the base table and change-tracking schemas that are retrieved to provision a SQL Server Compact client database.
// Create a SQL Server Compact database and provision it based on scope
// information that is retrieved from the server. Compact databases
// do not support separate schemas, so we prefix the name of all
// synchronization-related objects with "Sync" so that they are easy to
// identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCe1Desc);
clientSqlCe1Config.ObjectPrefix = "Sync";
clientSqlCe1Config.Apply();
' Create a SQL Server Compact database and provision it based on scope
' information that is retrieved from the server. Compact databases
' do not support separate schemas, so we prefix the name of all
' synchronization-related objects with "Sync" so that they are easy to
' identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
Dim clientSqlCe1Desc As DbSyncScopeDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCe1Desc)
clientSqlCe1Config.ObjectPrefix = "Sync"
clientSqlCe1Config.Apply()
The following code example generates a snapshot named SyncSampleClient2.sdf from the SyncSampleClient1.sdf database. The code then synchronizes SyncSampleClient2.sdf with the server database. The client database is initialized during the synchronization, but you also could use InitializeSnapshot explicitly to initialize the database.
// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");
// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
' Create a snapshot from the SQL Server Compact database, which will be used to
' initialize a second Compact database. Again, this database could be provisioned
' by retrieving scope information from another database, but we want to
' demonstrate the use of snapshots, which provide a convenient deployment
' mechanism for Compact databases.
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")
' The new SQL Server Compact client synchronizes with the server, but
' no data is downloaded because the snapshot already contains
' all of the data from the first Compact database.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
Example
The following complete code example includes the code examples that are described earlier and additional code to configure and clean up the databases. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.
using System;
using System.Collections.ObjectModel;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
// Create the connections over which provisioning and synchronization
// are performed. The Utility class handles all functionality that is not
// directly related to synchronization, such as holding connection
// string information and making changes to the server database.
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
SqlCeConnection clientSqlCe1Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync1);
SqlCeConnection clientSqlCe2Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync2);
// Create a scope named "filtered_customer", and add two tables to the scope.
// GetDescriptionForTable gets the schema of each table, so that tracking
// tables and triggers can be created for that table. For Customer, we add
// the entire table. For CustomerContact, we add only two of the columns.
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
scopeDesc.Tables.Add(customerDescription);
// Definition for CustomerContact, including the list of columns to include.
Collection<string> columnsToInclude = new Collection<string>();
columnsToInclude.Add("CustomerId");
columnsToInclude.Add("PhoneType");
DbSyncTableDescription customerContactDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
scopeDesc.Tables.Add(customerContactDescription);
// Create a provisioning object for "filtered_customer". We specify that
// all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverConfig.ObjectSchema = "Sync";
// Specify which column(s) in the Customer table to use for filtering data,
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply();
// Write the configuration script to a file. You can modify
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
serverConfig.Script());
// Provision each of the client databases.
// Create a SQL Server Compact database and provision it based on scope
// information that is retrieved from the server. Compact databases
// do not support separate schemas, so we prefix the name of all
// synchronization-related objects with "Sync" so that they are easy to
// identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCe1Desc);
clientSqlCe1Config.ObjectPrefix = "Sync";
clientSqlCe1Config.Apply();
// Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
// information that is retrieved from the SQL Server Compact database. We could
// have also retrieved this information from the server.
DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc);
clientSqlConfig.ObjectSchema = "Sync";
clientSqlConfig.Apply();
// Initial synchronization sessions. 7 rows are synchronized:
// all rows (4) from CustomerContact, and the 3 rows from Customer
// that satisfy the filtering criteria.
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// first SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");
// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer");
// Synchronize again. Three changes were made on the server, but
// only two of them applied to rows that are in the "filtered_customer"
// scope. The other row is not synchronized.
// Notice that the order of synchronization is different from the initial
// sessions, but the two changes are propagated to all nodes.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"),
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
serverConn.Close();
serverConn.Dispose();
clientSqlConn.Close();
clientSqlConn.Dispose();
clientSqlCe1Conn.Close();
clientSqlCe1Conn.Dispose();
clientSqlCe2Conn.Close();
clientSqlCe2Conn.Dispose();
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server);
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client);
Console.Write("\nPress any key to exit.");
Console.Read();
}
}
public class SampleSyncOrchestrator : SyncOrchestrator
{
public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
}
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.Collections.ObjectModel
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.SqlServer
Imports Microsoft.Synchronization.Data.SqlServerCe
Namespace Microsoft.Samples.Synchronization
Class Program
Public Shared Sub Main(ByVal args As String())
' Create the connections over which provisioning and synchronization
' are performed. The Utility class handles all functionality that is not
' directly related to synchronization, such as holding connection
' string information and making changes to the server database.
Dim serverConn As New SqlConnection(Utility.ConnStr_SqlSync_Server)
Dim clientSqlConn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
Dim clientSqlCe1Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync1)
Dim clientSqlCe2Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync2)
' Create a scope named "filtered_customer", and add two tables to the scope.
' GetDescriptionForTable gets the schema of each table, so that tracking
' tables and triggers can be created for that table. For Customer, we add
' the entire table. For CustomerContact, we add only two of the columns.
Dim scopeDesc As New DbSyncScopeDescription("filtered_customer")
' Definition for Customer.
Dim customerDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn)
scopeDesc.Tables.Add(customerDescription)
' Definition for CustomerContact, including the list of columns to include.
Dim columnsToInclude As New Collection(Of String)()
columnsToInclude.Add("CustomerId")
columnsToInclude.Add("PhoneType")
Dim customerContactDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn)
scopeDesc.Tables.Add(customerContactDescription)
' Create a provisioning object for "filtered_customer". We specify that
' all synchronization-related objects should be created in a
' database schema named "Sync". If you specify a schema, it must already exist
' in the database.
Dim serverConfig As New SqlSyncScopeProvisioning(serverConn, scopeDesc)
serverConfig.ObjectSchema = "Sync"
' Specify which column(s) in the Customer table to use for filtering data,
' and the filtering clause to use against the tracking table.
' "[side]" is an alias for the tracking table.
serverConfig.Tables("Sales.Customer").AddFilterColumn("CustomerType")
serverConfig.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = 'Retail'"
' Configure the scope and change-tracking infrastructure.
serverConfig.Apply()
' Write the configuration script to a file. You can modify
' this script if necessary and run it against the server
' to customize behavior.
File.WriteAllText("SampleConfigScript.txt", serverConfig.Script())
' Provision each of the client databases.
' Create a SQL Server Compact database and provision it based on scope
' information that is retrieved from the server. Compact databases
' do not support separate schemas, so we prefix the name of all
' synchronization-related objects with "Sync" so that they are easy to
' identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
Dim clientSqlCe1Desc As DbSyncScopeDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCe1Desc)
clientSqlCe1Config.ObjectPrefix = "Sync"
clientSqlCe1Config.Apply()
' Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
' information that is retrieved from the SQL Server Compact database. We could
' have also retrieved this information from the server.
Dim clientSqlDesc As DbSyncScopeDescription = _
SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc)
clientSqlConfig.ObjectSchema = "Sync"
clientSqlConfig.Apply()
' Initial synchronization sessions. 7 rows are synchronized:
' all rows (4) from CustomerContact, and the 3 rows from Customer
' that satisfy the filtering criteria.
Dim syncOrchestrator As SampleSyncOrchestrator
Dim syncStats As SyncOperationStatistics
' Data is downloaded from the server to the SQL Server client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Data is downloaded from the SQL Server client to the
' first SQL Server Compact client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Create a snapshot from the SQL Server Compact database, which will be used to
' initialize a second Compact database. Again, this database could be provisioned
' by retrieving scope information from another database, but we want to
' demonstrate the use of snapshots, which provide a convenient deployment
' mechanism for Compact databases.
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")
' The new SQL Server Compact client synchronizes with the server, but
' no data is downloaded because the snapshot already contains
' all of the data from the first Compact database.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer")
' Synchronize again. Three changes were made on the server, but
' only two of them applied to rows that are in the "filtered_customer"
' scope. The other row is not synchronized.
' Notice that the order of synchronization is different from the initial
' sessions, but the two changes are propagated to all nodes.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
serverConn.Close()
serverConn.Dispose()
clientSqlConn.Close()
clientSqlConn.Dispose()
clientSqlCe1Conn.Close()
clientSqlCe1Conn.Dispose()
clientSqlCe2Conn.Close()
clientSqlCe2Conn.Dispose()
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server)
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client)
Console.Write(vbLf & "Press any key to exit.")
Console.Read()
End Sub
End Class
Public Class SampleSyncOrchestrator
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
End Class
End Namespace