Walkthrough: Defining Scope and Provisioning a Server Database
To prepare the server database for synchronization, you will need to describe a sync scope and provision the server database with scope related artifacts.
By describing a sync scope, you define what you want to synchronize. A sync scope is a set of tables that must be synchronized as a single unit. The tables can already exist in the database or they can be described by using the Sync Framework object model and then be generated at runtime when the underlying store is provisioned. In this walkthrough, you will use the Products table that already exists in the server database.
The provisioning of a database involves adding sync scope related artifacts such as tracking tables, triggers, and stored procedures to the database. These artifacts are used by the synchronization process at runtime. Optionally, the base table is also added to the database as specified in the previous paragraph. For in-depth technical details about provisioning, see How to: Execute Database Synchronization (SQL Server) and Provisioning for Synchronization (SQL Server).
In this walkthrough you will create a console application that defines a sync scope named ProductsScope, which includes the Products table, and provisions the SQL Server database that you created in previous walkthrough with sync scope related artifacts.
To define a scope and to provision server with scope related artifacts
Launch Visual Studio 2008 or Visual Studio 2010: Click Start, point to Programs, point to Microsoft Visual Studio 2008 or Visual Studio 2010, and then click Microsoft Visual Studio 2008 or Visual Studio 2010.
Click File on menu bar, point to New, and then click Project.
Select Visual C# from Project Types, and select Console Application from Templates.
Type ProvisionServer for the project name, C:\ for Location, and SyncSQLServerAndSQLExpress for Solution Name.
Click OK to close the New Project dialog box.
In the Solution Explorer window, right-click ProvisionServer, and click Add Reference.
Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer on the .NET tab. These are the assemblies that are shipped with Microsoft Sync Framework.
Click OK to close the Add Reference dialog box.
Add the following using statements to the beginning of the Program.cs file after the existing using statements. These namespaces contains the classes that you will be using in the code for this console application.
using System.Data.SqlClient; using Microsoft.Synchronization.Data; using Microsoft.Synchronization.Data.SqlServer;Add the following statement to the Main method to create a connection to the SyncDB server database.
Important
In the above statement, replace the server name with your server’s instance name, if you are not using the default instance. For example: if your SQL Server instance is called MYSQLINSTANCE, replace (local) with .\MYSQLINSTANCE.
Add the following lines of code to the Main method to define a sync scope based on the Products table in the SyncDB database. This code creates the ProductsScope sync scope, gets the description of Products table in the SyncDB database, and adds the description to the ProductsScope. The high level steps for defining a sync scope are:
Create an instance of the DbSyncScopeDescription class. The DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. The tables are specified using the DbSyncTableDescription class.
Create an instance of the DbSyncTableDescription class based on the schema of Products table retrieved from the SyncDB server database. The DbSyncTableDescription class is used to specify the name of the table, columns of the table to be synchronized, data types of the table, and other information that is required for the sync. This information can be specified explicitly or it can be obtained by querying the database using the GetDescriptionForTable(String, SqlConnection) method.
In this walkthrough, you will use the GetDescriptionForTable(String, SqlConnection) method of the SqlSyncDescriptionBuilder class to retrieve the description of the table.
Add the DbSyncTableDescription object to Tables collection of the DbSyncScopeDescription object using the Add method.
Add the following lines to code to the Main method to provision SQL Server with sync related artifacts. This code creates a SqlSyncScopeProvisioningobject, skips the creation of Products table on the server since the table already exists, and provisions the SyncDB database with scope related artifacts. The high level steps for provisioning the server database with sync scope related artifacts are:
Create an instance of the SqlSyncScopeProvisioning class based on the DbSyncScopeDescription object and a connection to the server database. The SqlSyncScopeProvisioning class represents the provisioning of a SQL Server database for a particular scope that is represented by a DbSyncScopeDescription object.
Invoke the SetCreateTableDefault(DbSyncCreationOption) method by specifying the DbSyncCreationOption value as Skip because the Products table already exists in the server database. The SetCreateTableDefault(DbSyncCreationOption) method is used to specify whether to create base tables when a scope is configured.
Invoke the Apply method on SqlSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the server database.
In Solution Explorer, right-click ProvisionServer, and click Build.
Press Ctrl+F5 to execute the program.
Press ENTER to close the command prompt window.
In SQL Server Management Studio, expand SyncDB node, expand Tables, and you should see the following additional tables created by the provisioning process: Products_Tracking, schema_info, scope_config, and scope_info. There are also other database objects such as triggers and stored procedures created by the provisioning process.
Keep Visual Studio open and SQL Server Management open.
Complete Code Example
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
namespace ProvisionServer
{
class Program
{
static void Main(string[] args)
{
// connect to server database
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
}
}