Walkthrough: Provisioning a SQL Express Client
In this walkthrough you will create a SQL Express database named SyncExpressDB, and create a console application that provisions the database with ProductsScope related artifacts. The provision process prepares the client database for synchronization with the server. In the previous walkthrough Walkthrough: Defining Scope and Provisioning a Server Database, you provisioned the server database with sync related artifacts already. . For in-depth technical details about provisioning, see Provisioning the Clients and Provisioning for Synchronization (SQL Server).
Create a SQL Server express database
The following list contains steps to create a sample SQL Server express database.
In SQL Server Management Studio, click File menu, and click Connect Object Explorer.
In the Connect to Server dialog box, select Database Engine for Server type.
Select .\SQLEXPRESS for Server name.
Tip
If the name of SQL Express database on your computer is different, specify that name instead.
Specify appropriate credentials and click Connect to connect to the SQL Express database.
Right-click Databases in the Object Explorer window, click New Database.
Type SyncExpressDB for Database name, and click OK.
Provision SQL Express database
The following list contains steps to create a console application using Visual Studio to provision the SQL Express database SyncExpressDB with artifacts related to sync scope ProductsScope.
In Visual Studio, In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLExpress’, point to Add, and click New Project.
Select Visual C# from Project Types, and select Console Application from Templates.
Type ProvisionClient for project name.
Click OK to close the New Project dialog box.
In Solution Explorer window, right-click ProvisionClient, and click Add Reference.
Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, and 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.
using System.Data.SqlClient; using Microsoft.Synchronization.Data; using Microsoft.Synchronization.Data.SqlServer;Add the following statement to the Main method to create a SQL connection to the express database.
Add the following statement to the Main method to create a SQL connection to the 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 statement to the Main method to get the description of the ProductsScope scope from the SQL Server. This statement invokes the GetDescriptionForScope(String, SqlConnection) method on the SqlSyncDescriptionBuilder class to retrieve description of the ProductsScope from server.
The DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. This information can be specified explicitly or it can be obtained by querying the database using the GetDescriptionForScope(String, String, SqlConnection) method. In this walkthrough, you will use the GetDescriptionForScope(String, String, SqlConnection) method of the SqlSyncDescriptionBuilder class to retrieve the description of the scope from the server.
Add the following statements to provision the SQL Express database with the ProductsScope related artifacts. The high level steps for provisioning a SQL Express database with sync scope related artifacts are:
Create an instance of the SqlSyncScopeProvisioning class based on the DbSyncScopeDescription obtained in the previous step and a connection to the SQL Express database. The SqlSyncScopeProvisioning class represents the provisioning of a SQL Express database for a particular scope that is represented by a DbSyncScopeDescription object.
Invoke the Apply method on SqlSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the SQL Express database.
Note
You do not need to invoke the SetCreateTableDefault(DbSyncCreationOption) method explicitly with DbSyncCreationOption value as CreateOrUseExisting because the CreateOrUseExisting is the default value that sync framework uses. The Products table is automatically created in the compact database during the provisioning process. The SetCreateTableDefault(DbSyncCreationOption) method is used to specify whether to create base tables when a scope is configured.
In Solution Explorer, right-click ProvisionClient, and click Build.
In Solution Explorer, right-click ProvisionClient again, and click Set as Startup Project.
Warning
If you do not set the current project as a startup project and press Ctrl+F5 again, the ProvisionServer application is executed again, and you get an error message similar to the following: “scope that already exists in the server database”.
Press Ctrl+F5 to execute the program.
Press ENTER to close the command prompt window.
In SQL Server Management Studio, expand .\SQLEXPRESS, expand Databases, expand SyncExpressDB, 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 other objects such as triggers and stored procedures created by the provisioning process.
Keep Visual Studio and SQL Server Management Studio open.
Complete Code Example
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
namespace ProvisionClient
{
class Program
{
static void Main(string[] args)
{
// create a connection to the SyncExpressDB database
SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncExpressDB; Trusted_Connection=Yes");
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
// create server provisioning object based on the ProductsScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
}
}