Synchronization Under the Hood

Let's briefly discuss what is happening during synchronization. If this doesn't interest you, go ahead and skip over it. The CRM geeks among us will undoubtedly want to know how and why because synchronization is never an easy proposition.

Microsoft is definitely eating its own dog food when it comes to the synchronization mechanism used for Microsoft CRM. Microsoft is using SQL Server Replication to keep the main CRM database synchronized with the user's local CRM database.

SQL Server replication is a technology that provides various methods of enabling developers to keep data sources synchronized. There are several benefits gained by using SQL Server replication for data synchronization:

• Schema changes— SQL Server 2000 supports schema changes during replication. This means that as you create custom fields and add them to forms, the changes will push out to your offline users without any further configuration.

• Row and Column tracking— SQL Server replication allows for change tracking by row and by column. This means an offline user can make a change to a single field on a CRM record without the entire record being updated upon synchronization.

• Horizontal and Vertical Filtering— SQL Server replication provides the capability to synchronize a subset of a particular table (for example, horizontal filtering) or only certain columns in a table (for example, vertical filtering).

• Ability to transfer database objects— SQL Server allows for the replication of not only tables, but also stored procedures.

• Existing pool of trained resources— Because SQL Server replication is a standard component of SQL Server, a pool of knowledgeable resources exists to maintain it. This can substantially reduce your organization's total cost of system ownership.

SQL Server replication is based on a publishing metaphor. SQL Server enables you to create publications that can be published to subscribing computers.

In the case of Microsoft CRM, a traditional publisher/subscriber model is used. The CRM server has two publications (one for the MSCRM database and one for METABASE database). Likewise, each CRMSO client has a pull subscription for each of its two databases (one for the mscrm_msde database and another for the metabase database). The word pull is used here because it is the client (the subscriber) that initiates synchronization and pulls the data from the publisher. In reality, it is a bit more complex than this. The client does initiate the replication, but it is then the Distributor, which in the case of Microsoft CRM is also the CRM server, that brokers the replication through the Merge Agent. This distributor has its own database (<Organization>_MSCRMDistribution) used to track replication status.

The type of replication just described is known as merge replication and is used in Microsoft CRM because it can handle the situation where data conflicts occur because of conflicting modifications made on the publisher and subscriber. Merge replication is used exclusively after the user's local database is initially populated. However, to initially populate the user's local database another type of replication known as snapshot replication is used.

Snapshot replication in its simplest form is the best method of replication for copying read-only tables from a publisher to a subscriber. When all subscribers are also SQL Servers (SQL Server replication can be used with databases other than SQL Server) snapshot replication can be used in native mode. Native mode means that SQL Server will use the Bulk Copy Protocol (BCP) to copy the data from the publisher to the subscriber. BCP is an SQL Server utility that has been around for several years and is a quick and proven method of copying large data sets from one SQL Server to another. After the BCP snapshot of the initial data set is copied to the subscriber, the merge replication takes over for the ongoing incremental updates.

Figure 8.17 shows SQL Server Enterprise Manager with both a Microsoft CRM server and client (for example MSDE database) registered. On the left side of the screen you can see the two server publications, and on the right side you can see the two client subscriptions. Double-clicking any of these items provides more detail about them.