{"id":736,"date":"2007-12-20T20:08:00","date_gmt":"2007-12-20T20:08:00","guid":{"rendered":"\/blogs\/bobb\/post\/SQL-Server-2008-and-ADONET-Sync-Services.aspx"},"modified":"2013-01-03T23:59:56","modified_gmt":"2013-01-04T07:59:56","slug":"sql-server-2008-and-ado-net-sync-services","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/","title":{"rendered":"SQL Server 2008 and ADO.NET Sync Services"},"content":{"rendered":"<p>\nSince its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server&#39;s new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I&#39;d encourage you to start there. But about &quot;why it&#39;s there&#8230;&quot;.\n<\/p>\n<p>\nThe main reason for change tracking, as far as I can see, is to be used with ADO.NET Sync Services. ADO.NET Sync Services is part of the Microsoft Sync Framework and is a way to do (I&#39;m trying to summarize here) &quot;client directed programmable replication&quot;. For database folks ADO.NET Sync Services can be thought of as providing synchronization between SQL Server Compact Edition and any relational datastore, but the model is extensible. ADO.NET Sync Services 1.0 shipped with SQL Server Compact Edition 3.5 in Visual Studio 2008 (there may be other ship vehicles I&#39;m unaware of) and provides 2-tier, 3-tier, or service-oriented synchronization. It provides hub-and-spoke synchronization. ADO.NET Sync Services version 2.0 will add (the CTP is out now) peer-to-peer synchronization. Microsoft Sync Framework (CTP 1 is out now)&nbsp;includes sync support for data stores that aren&#39;t necessarily databases. The best place to go to find some highly informative examples that illustrate the synchronization patterns supported by the model is &quot;The Synchronizer&#39;s&quot; (Rafik&#39;s)&nbsp;<a href=\"http:\/\/blogs.msdn.com\/b\/synchronizer\/\" class=\"broken_link\">blog<\/a>.\n<\/p>\n<p>\nWhat&#39;s this all have to do with SQL Server 2008 change tracking? Although ADO.NET Sync Services 1.0 is a good start (and there&#39;s GUI-based designers in VS2008), it usually means adding timestamp or datetime columns and tombstone tables (tables that track the primary key of deleted rows) to existing database tables and triggers to populate the information sync services needs. That&#39;s fairly intrusive, especially with packed applications.&nbsp;SQL Server 2008 change tracking takes care of all of this for you.\n<\/p>\n<p>\nYou turn on change tracking on a database with ALTER DATABASE and on individual tables with ALTER TABLE. Change tracking does the rest. You can access the information you need for Sync Services applications using the CHANGETABLE table-valed function and a few related functions. You usually want to turn on ALLOW_SNAPSHOT_ISOLATION in the database as well, because change tracking works by tracking when a transaction is committed, rather than when its started.\n<\/p>\n<p>\nChange tracking provides the information ADO.NET Sync Services needs to answer request such as &quot;what rows have changed since my particular client (change originator is tracked by change tracking as well) last synchronized&quot; and &quot;have I synchronized with the main database so long ago (change tracking info has a DBA-specified retention) that I need to sync the entire table because the incremental info I need is no longer available&quot;.\n<\/p>\n<p>\nCurrently the VS2008 designers (they&#39;re accessed by Added a &quot;Local Database Cache&quot; item to a programming project) don&#39;t have an option to indicate &quot;just use SQL Server 2008 and I&#39;m using change tracking&quot;, so you have to code the synchronization procedures to use change tracking with Sync Services by hand. Perhaps such a feature is in the works for ADO.NET Sync Services 2.0.\n<\/p>\n<p>\nAnd how about SQL Server Compact Edition (currently the only &quot;ClientProvider&quot; that ship with Sync Services 1.0). Well the SQL Server Compact Edition, Sync Services &quot;just works&quot;. No special setup is required, although you may (I haven&#39;t determined this) need SQL Server Compact Edition 3.5. This version of SQLCE&nbsp;ships with VS2008. One last thing&#8230;ADO.NET Sync Services 1.0 isn&#39;t available for compact devices yet, so when you sync with SQL Server Compact Edition, it must be deployed on the desktop.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server&#39;s new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I&#39;d encourage you to start there. But about &quot;why it&#39;s [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,29],"tags":[],"class_list":["post-736","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sql-server-2008"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server&#039;s new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I&#039;d encourage you to start there. But about &quot;why it&#039;s [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2007-12-20T20:08:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:56+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/\",\"name\":\"SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2007-12-20T20:08:00+00:00\",\"dateModified\":\"2013-01-04T07:59:56+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server 2008 and ADO.NET Sync Services\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin","og_description":"Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server&#39;s new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I&#39;d encourage you to start there. But about &quot;why it&#39;s [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/","og_site_name":"Bob Beauchemin","article_published_time":"2007-12-20T20:08:00+00:00","article_modified_time":"2013-01-04T07:59:56+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/","name":"SQL Server 2008 and ADO.NET Sync Services - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2007-12-20T20:08:00+00:00","dateModified":"2013-01-04T07:59:56+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-server-2008-and-ado-net-sync-services\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"SQL Server 2008 and ADO.NET Sync Services"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/736","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=736"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/736\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}