Showing posts with label OCS. Show all posts
Showing posts with label OCS. Show all posts

Wednesday, June 06, 2007

Tech●Ed 2007 Coverage from FTPOnline

Redmond Media Group's FTPOnline columnists (including me) are covering Tech●Ed 2007—the 15th Anniversary of Microsoft's yearly technical conference—for the .NET Insight newsletter's "Tech●Ed Show Daily" issues. (Updated June 8, 2007.)


From the June 5, 2007 .NET Insight "Tech●Ed Show Daily" issue:

Muglia Maps Microsoft's 'Journey Towards Dynamic IT'

The traditional "vision" keynote gives way to optimization, virtualization and standardization of existing IT infrastructure along with new agile business processes and modeling to reverse the 70/30 ratio of IT maintenance to new solution spending at Tech●Ed's 15th anniversary.

Hot at Tech●Ed 2007: SQL Server 2008

The next version of SQL Server will deliver native data types for documents, filestreams and geocoding, provide policy-based management, support the Entity Data Model (EDM) and Language Integrated Query (LINQ), and integrate with Microsoft's enhanced business intelligence (BI) offerings.

Click here for a table of Tech●Ed sessions about SQL Server 2008 and implementing business intelligence projects.


From the June 7, 2007 .NET Insight "Tech●Ed Show Daily" issue:

Hot at Tech●Ed 2007: Introducing the Entity Framework and LINQ

The Entity Framework (EF) and Language Integrated Query (LINQ) are two new technologies in Visual Studio 2008 that form one of the Pillars of SQL Server 2008 (formerly "Katmai"): Dynamic Development. Both play nicely with SQL Server 2005 and 2000, too.

Click here for a table of Tech●Ed sessions about the Entity Framework and LINQ.

Exploit SQL Server Compact Edition and Occasionally Connected Systems

SQL Server Compact Edition v3.1 and v3.5's free license covers deployment on Windows PC clients as well as devices, so you can take advantage of this lightweight, in-process relational database as a local data cache and automatically synchronize its data with a back-end SQL Server 200x instance.

Click here for a table of Tech●Ed sessions about SQL Server Compact Edition, data synchronization, and occasionally connected systems.

Video: Preview SQL Server 2008's Database Engine (01:04:00)

Christian Kleinerman, group program manager of Microsoft's Relational Engine Team, offers a first look at SQL Server 2008, formerly code named "Katmai." Specifically, his presentation at VS Live! Orlando delivers a high-level overview of enhancements to the relational database engine. (Taped May 8, 2007.)


Note: Visual Studio Magazine has become a controlled-circulation publication. Sign up now for your free subscription.

Click here to sign up for any or all of Redmond Media Group's Insight Newsletters:

  • .NET Insight—Takes subscribers through the maze of Visual Studio and .NET technologies, from development tools and platforms to hosted Web services and migration strategies. Published weekly.
  • Java Insight—Delivers the latest news, trends and techniques for creating enterprise Java applications, including technologies like J2EE, Web services, JSP, wireless systems and much more. Published weekly.
  • Web Design & Development Insight—Delivers proven tactics for creating intuitive and reliable sites. Published monthly.
  • Enterprise Architect Insight—Provides enterprise architects with information on technologies and techniques that are shaping the design and integration of enterprise systems today. Published twice monthly.
  • Upside Business Insight—Takes you behind the news and technologies that are shaping the high-tech industry. It will help you gain insights on who is shaping technology and where the industry is going, so you can better plan your IT efforts. Published monthly.
  • SQL Pro Insight—Provides essential content on SQL Server to help with your database administration and programming needs. Get tips on performance, management, security, and more. Published twice monthly.
  • Eclipse Insight—Provides information on the Eclipse platform, including new projects and technologies that shape this exciting trend. Published monthly.

Saturday, May 12, 2007

Synchronization Services Runtime Beta 1 for ADO.NET 2.0 Is Available

Rafik Robeal announced in his Just Published! Microsoft Synchronization Services for ADO.NET Beta 1.0 post of May 12, 2007 that the Beta 1 version of the Sync Services runtime for .NET 2.0+ and Visual Studio 2005 is available here. These bits are the same as those for the Sync Services runtime in Visual Studio "Orcas" Beta 1.

Updated documentation for runtime Sync Services, which also applies to the Orcas Beta 1 implementation, is available here. (It must be a cold day in hell, because the documentation preceded the binaries.)

You'll need to make a few changes to using/Imports directives and altered object and property names than have changed from the earlier versions used in Rafik's samples.

Update 5/17/2007: Rafik has updated his six sample applications for the Beta 1 runtime release.

The runtime doesn't include the graphic Sync Designer that opens when you add Orcas Beta 1's Local Database Cache template to a VB or C# project. My A Sync Services Bidirectional Test Harness blog of March 23, 2007 describes a Windows form app that synchronizes the Northwind Orders and Order_Details tables between an SQL Server 2005 Express (SSX) instance and an SQL Server 2005 Compact Edition v3.5 (SSCE) database.

Note: Converting the Test Harness from the Orcas March 2007 CTP to Orcas Beta 1 failed due to issues with the Sync Designer's generation of the Northwind.sdf database. Specifically, the Sync Designer didn't add the required LastEditDate and CreationDate fields to the SSCE Orders and Order_Details tables, which causes replication to fail with error messages about the missing fields. For more information see my Sync Designer/SSCE Version Problems with Orcas Beta 1 post in the Microsoft Synchronization Services for ADO.NET forum and Bad Error Message Connecting to SSCE v3.5 from Orcas Beta 1 in SSMSX SP2 under Vista post in the SQL Server Compact Edition forum.

Wednesday, May 09, 2007

SQL Server "Katmai" to Deliver Entity Data Platform and Support LINQ

Microsoft's announcement this morning that 2008 is the target release date for SQL Server "Katmai" (presumably SQL Server 2008) might explain why the ADO.NET team dropped the Entity Framework (EF) from Visual Studio "Orcas" and postponed EF's target release date to the "first half of 2008."

Mike Pizzo, an Architect on the Data Programmability team, announced that EF was cut from Orcas in an April 28, 2007 (Saturday) post, ADO.NET Entity Framework Update, which contained the following blurb about the Entity Data Model/Platform and a teaser for the "Astoria" and "Jasper" incubator projects:

Microsoft will be leveraging the Entity Data Model in future versions of Microsoft products such as SQL Server. This Data Platform vision enables customers to leverage their investment in a common conceptual entity model across product lines. Stay tuned for announcements starting next week at Mix around new products building on the ADO.NET Entity Framework. [Emphasis added]

In his  Microsoft's Data Access Strategy post that followed the announcement by three minutes, Mike asks "Does Microsoft have a Data Access Strategy?" and then gives the following answer:

Yes, it turns out we do. Microsoft envisions an Entity Data Platform that enables customers to define a common Entity Data Model across data services and applications. The Entity Data Platform is a multi-release vision, with future versions of reporting tools, replication, data definition, security, etc. all being built around a common Entity Data Model. [Emphasis added.]

Update: 5/25/2007: As an IBM senior executive used to say, "Vision without execution is hallucination." (From the Microsoft Architectural Journal's "Architecture Journal Profile: Don Ferguson" interview. Don Ferguson is a former IBM Fellow who moved to Microsoft as a Technical Fellow in early 2007.)

The newly released SQL Server Code Named "Katmai" data sheet's "Develop with Entities" section says:

As part of the next generation of the ADO.NET data access framework, developers will be able to access their data by defining business entities like Customers, Orders and Products instead of tables and columns using the Entity Data Model. Query and retrieve these entities natively within any .NET language with the introduction of LINQ. These services enable developers to work within the logical entity model while administrators can define the physical implementation of the model as tables and columns. [Emphasis added.]

According to InformationWeek's May 9, 2007 Microsoft Gives Peek At Next Version Of SQL Server article by J. Nicholas Hoover from the first Microsoft Business Intelligence Conference 2007:

The next SQL Server also will include the "entity data platform," ostensibly the ADO.Net entity framework,which makes it so. [I]nstead of having to deal with raw data in a relational table, developers can easily deal with data on an object level—for example, as a customer or product object—regardless of whether the information and data that defines the object is in multiple tables.

There also will be support for Language Integrated Query, or LINQ, which simplifies the process required for developers to query databases.

Katmai additionally will introduce a technology called "occasionally connected systems" to provide core services that allow developers to synchronize devices with data whenever they're connected to a network. [Emphasis added.]

Mary Jo Foley's May 10, 2007 Making sense of Microsoft’s myriad database projects post to her All About Microsoft blog quotes Francois Ajenstat, director of SQL Server:

At the core of these different data-programmability projects is the concept of a common entity data model. EDM — a way of describing entities and their relationships — isn't new; it has been used by database programmers for more than 30 years. Ajenstat described EDM as a way of allowing programmers to program at a logical level, instead of a physical level.

Microsoft is building an entity data model framework around its ADO.Net technology that is designed to raise the level of abstraction for database programmers. Up until recently, the ADO.Net Entity Framework was slated to be part of the first release of Visual Studio Orcas; now the entity framework won't be released until some time in 2008, as a post-Orcas update to the .Net Framework, Ajenstat confirmed.

Microsoft's goal is to enable LINQ, its Language Integrated Query extensions to the .Net Framework, to query the new ADO.Net Entity Model. Microsoft wants to simplify SQL Server developers' lives by freeing them up from having to understand SQL the language, said Ajenstat.

Paul Flessner, then Senior Vice President of the Server Applications, alluded to support for LINQ in his SQL Server 2005 Update of April 6, 2006 with the following sentence under his "Beyond Relational" topic:

We will strive to deliver the best platform for integrated storage, and advanced applications such as spatial data, while also making it dramatically easier for you to build data-driven applications, without needing to invest significant resources in bridging the gap between data and programming language data structures. [Emphasis added.]

Katmai and Entity Framework Dependencies?

CRN's Barbara Darrow wrote the following two days before EF's Orcas swansong:

Microsoft may synchronize its "Longhorn" Windows Server release with its promised SQL Server and Visual Studio updates, even if that means delaying Longhorn's availability slightly, CRN has learned.

I doubt very much that Longhorn will have dependencies on SQL Server and Orcas, or vice versa. However, EF might end up with a dependency on Katmai, despite Mike Pizzo's insistence that EF will have:

The ability to query relational stores other than the Microsoft SQL Server family of products.

Without Orcas as a release vehicle, EF is an orphan. Will Katmai adopt EF as an SQL Server toolset and delay EF's debut to the Katmai RTM date?

Update 5/23/2007: According to a comment to a blog post by ADO.NET team member Andrew Conrad, "Katmai (next version of Sql Server) for the Entity Framework is a ship vehicle." [Emphasis added.]

Mike Pizzo was adamant about the delayed EF shipping in the 2008H1. Here's his committment in ADO.NET Entity Framework Update:

Based on the need to align with requirements from key internal partners that are building on the Entity Framework, along with the need for a better tool experience, we have decided to ship the ADO.NET Entity Framework and Tools during the first half of 2008 as an update to the Orcas release of the .NET Framework and Visual Studio.

and in Microsoft's Data Access Strategy:

A few months after the shipment of Orcas, and within the first half of 2008, Microsoft will release the ADO.NET Entity Framework as an extension to the Orcas version of the .NET Framework.

Stay Tuned

There's more coming from Christian Kleinerman's A First Look at SQL Server codename ‘Katmai’ Database Engine presentation on Monday, May 8, 2007 at SQLLive! Orlando that promises:

[A] high level overview of some of the enhancements to the relational database engine in SQL Server code name "Katmai".

and Ted Kummert's keynote on Thursday, May 10, 2007, which should shed more light on Katmai's Enterprise Data Platform, LINQ, and OCS (synchronization) features. Kummert took over Paul Flessner's duties in October 2006. According to Mary Jo's post:

All of what Microsoft lumps under "data programmability" falls under the domain of Ted Kummert, the Microsoft Corporate Vice President in charge of the data and storage platform division, Ajenstat said.

Update 5/15/2007: Microsoft posted a Webcast of Ted Kummert's keynote, and Podcasts of interviews with Francois Ajenstad, Director of Product Management for Microsoft SQL Server and Ram Ramanathan, Senior Product Manager for Microsoft SQL Server Data Warehousing and Data Integration. Arjenstad says there will be a "CTP of Katmai coming out shortly, probably within the next month" (see Katmai CTP Scheduled for June.)

Here's an excerpt from Mark Whitehorn's The four pillars of Katmai report of May 11, 2007 for the Reg[ister] Devloper:

Dynamic development. Ted talked about a fascinating development in Katmai, the introduction of an ER modelling layer. This provides a logical layer which sits between application developers and the relational tables – allowing them to interact with the data more easily.

Fans of ER modelling will want to know that this is not (at least in Katmai) a design tool – you cannot develop an ER model and then generate the schema. However, Microsoft sees this as a first step and there is a strong possibility that design capabilities will appear in later versions. Database engines in general have been crying out for this kind of functionality for years so this first tentative step is very welcome.

Mark and Seattle Post-Intelligencer reporter Todd Bishop, along with other members of the trade press, were excluded from Steve Ballmer's Friday keynote. Here's a long story from Mark and a briefer report from Todd. I'd characterize the exclusion as a very unwise attempt at a commercial version of prior restraint.

Update 5/15/2007: Microsoft posted a Webcast of Steve Ballmer's keynote, so the preceding tempest in a teacup is moot.

Update 5/14/2007: Added excerpt from SQL Server 2005 Update from Paul Flessner of April 6, 2006, The four pillars of Katmai report of May 11, 2007, and stories about the Steve Ballmer keynote fiasco.

Update 5/11/2007: Patrice Luong's detailed post from the Microsoft BI Conference didn't mention the Entity .

Update 5/10/2007: Added links to Mary Jo Foley's post.

Related OakLeaf Posts

Entity Framework

Occasionally Connected Systems, Sync Framework/Systems, and Sync Designer

Wednesday, April 11, 2007

LINQ and Entity Framework Resource Updates

Jim Wooley's LINQ articles on MSDN2 post this morning reminded me that I hadn't been updating my prior LINQ and Entity Framework resource posts as often as this rapidly-moving field justifies. Jim's post provides links to five Microsoft LINQ-related white papers that were updated in February and March 2007 to coincide with the Orcas March 2007 CTP's release. Jim's comment

I scanned over the documentation and there are a number of issues in the documentation, which is understandable considering the moving target. I do find it interesting that they leverage some language features not yet available in the current CTP, most notably VB's anonymous functions which will equate to the C# lambda expression.

surprised me because of the recent updates. Jim's reply of 4/11/2007 1:09 AM UCT in the Compiled Queries in LINQ to SQL - performance considerations and entity caching thread in the LINQ Project General forum explains some of the discrepancies in the LINQ to SQL: .NET Language-Integrated Query for Relational Data whitepaper.

I've updated the following posts this morning to add the updated white papers, as well as more recent LINQ and EF-related articles and blog items:

Update 4/12/2007: Beth Massi has posted links to sign up for the following VB-oriented "Live From Redmond: VB9" Webcasts, most of which are focused on LINQ or include LINQ-related content:

  • Orcas Overview, April 24, 2007, 9:00 AM PST. John Stallo will cover the breadth of features that you can expect to see in the upcoming release of Visual Basic. John will demo how all the features such as LINQ, n-tier, WCF support, and OCS work together to help you build your applications faster than ever before.
  • LINQ Overview, April 25, 2007, 9:00 AM PST. Kit George will explore the new LINQ features in Visual Basic. We will show how to take advantage of LINQ to build applications that query and aggregate data from multiple sources, including in-memory objects, databases, and XML.
  • LINQ to SQL & O/R Designer Deep Dive, May 2, 2007, 9:00 AM PST. Young Joo extends the SQL and LINQ web cast by showing you how to use the new OR designer to build applications that take advantage of LINQ to SQL.
  • Building N-Tier Applications, May 9, 2007, 9:00 AM PST. Young Joo will show you how you can separate business logic from data access logic using the new n-tier features in Visual Basic.
  • Offline Data Caching May 16, 2007, 9:00 AM PST. Milind Lele will demonstrate how to use SQL Server Compact Edition to build applications that can cache your data offline and have it automatically sync to your database when you are online.
  • Deep Dive into LINQ May 23, 2007, 9:00 AM PST. Amanda Silver will dive into details how the LINQ features work. In the process, we will discuss anonymous types, type inference, inline functions, extension methods, and how these features work together to give you the LINQ experience. In addition, we will demo how these features can be used independently to help you build your applications quicker and with less code.
  • Building Service-Oriented Applications May 30, 2007, 9:00 AM PST. John Stallo will show you how you can produce and consume WCF services using the new WCF support with Visual Basic and build applications that take advantage of the next-generation service architecture.
  • LINQ Best Practices June 13, 2007, 9:00 AM PST. Lisa Feigenbaum will explore best practices for writing easy to read, consumable queries, including naming, usage, and performance ideas.

All presenters are program managers on the VB Team.

Update 4/24/2007: Young Joo has posted zip files of slides from his two VS Connections 2007 presentations: VMS305: LINQ to SQL: Bringing SQL Code into Visual Studio and VMS309: Sneak Peak at Visual Studio "Orcas". VMS305 sounds to me like a preview of his LINQ to SQL & O/R Designer Deep Dive presentation.

Note: The June 6th Webcast is Using the Interop Toolkit to Migrate your VB6 Applications to .NET, which is outside the VB9 scope. For unknown reasons, the author of the presentation descriptions insists on substituting LinQ for LINQ.

There's an average of about 35 blog entries per day tagged with LINQ, so it's not easy to keep up with the new goings on. As an example, Channel9 reported on April 6, 2007:

C# team members Shon Katzenberger, Scott Wiltamuth, Todd Proebsting, Erik Meijer, Peter Hallam, Anders Hejlsberg, and Peter Sollich were recently awarded a Technical Recognition Award for Outstanding Technical Achievement for their work on the C# language. This award is particularly meaningful to the recipients as it is awarded by their technical peers. It's quite an honor.

A video segment featuring the recipients is here.

Thursday, March 22, 2007

SSCE Sync Designer Q&A and Screencast

Sync Services pilgrims working with the Sync Designer preview in the Orcas March 2007 CTP had many of their questions answered by Steve Lasker's Additional Q&A on the Visual Studio Orcas Sync Designer post of March 21, 2007, which supplements Steve's original Q&A on OCS & Sync Services for ADO.NET post of March 18, 2007. First look at the Visual Studio Orcas Sync Designer and Going N Tier w/WCF, Synchronizing data using Sync Services for ADO.NET and SQL Server Compact Edition are a pair of screencast posts (dated March 22 and 23, 2007), which cover the Sync Designer that's scheduled to debut in Visual Studio Orcas. SSCE Sync Designer Q&A

Steve answers these questions to which I've added some related references:

  • Why does the Orcas Feb CTP Typed DataSet designer not work on Vista? I discovered this problem at the end of the aborted guided tour described in my Guided Tour of Orcas's Sync Services Designer for SSCE post of March 17, 2007.
  • Will the Sync Designer generate time based sync? I mentioned the lack of this feature in the same post.
  • Will tombstone records be automatically cleaned up? Rafik Robeal covers this topic in his Sync Services: Periodic Tombstone Cleanup post of February 16, 2006 to The Synchronizer blog.
  • How do I get my cached tables to be synchronized in a single transaction? Rafik's A nice gift from SQL Server 2005 SP2 to sync developers post discusses an SQL Server 2005 fix for potential timestamp errors with uncommitted transactions.
  • Once all the tables are placed in a single transaction, how do I control the order the tables are updated to handle parent/child relationships? Rafik discussed this issue in a "Synchronizing an 'Order'" thread in the Microsoft Synchronization Services for ADO.NET forum.
  • Does the sync runtime create relationships locally within SQLce? I mentioned this problem in conjunction with the problem of inability to save design changes to SSCE DataSets in the Guided Tour of Orcas's Sync Services Designer for SSCE post.
  • Does the sync runtime work with server side identities for PK's? The test harness I'm building has OrderID identity columns on the client and server sides and currently uses identity partitioning (similar to merge replication's approach) to identify the client machine that's the source of the update. Ultimately, the test harness will use ROWGUIDCOL columns.

SSCE Sync Designer Screencast—Part 1: First look at the Visual Studio Orcas Sync Designer

Steve's first Sync Designer (a.k.a. Cache Designer) screencast (25:49) demonstrates two-tier, one-way (download-only) synchronization of updates to reference data (Customers, Employees, and Shippers) for the Northwind Orders table. Reference (also called catalog) data, such as customer, vendor, or product lists, ordinarily are quite large but usually change relatively slowly. Two-tier, one-way sync for changes only is likely to be the most common Occasionally Connected System (OCS) scenario.

These are the only two lines of code in the Synchronize button's event handler that you need to sync the client with the server tables using the defaults you set in the designer:

Dim SyncAgent As NorthwindCacheSyncAgent = New NorthwindCacheSyncAgent

Dim SyncStats As Microsoft.Synchronization.Data.SyncStatistics = _ SyncAgent.Synchronize

The test harness's Synchronize button's event handler has about 100 lines of code to specify sync type and conflict handling, add and remove event handlers, and display SyncStatistics.

SSCE Sync Designer Screencast—Part 2: Going N Tier w/WCF, Synchronizing data using Sync Services for ADO.NET and SQL Server Compact Edition

The second screencast covers the n-tier scenario with a Windows Communications Foundation (WCF) service as an intermediary between the client and server. The architecture is similar to that Rafik Robeal demonstrated in his Demo III: Offline Application – WebService project.

Update 3/23/2007: My Sync Services demo project (a work in progress) has been moved to this new location: A Sync Services Bidirectional Test Harness. Added link to Part 2 of the screencast. Incorporated reference to original Sync Services Q&A in first paragraph.

Monday, March 19, 2007

Sync Services for ADO.NET Overview

Steve Lasker's lengthy Q&A on OCS & Sync Services for ADO.NET post covers use of merge replication, Remote Data Access (RDA) and Sync Services for ADO.NET for synchronizing data between servers and clients (or publishers and subscribers) of Occasionally Connected Systems. As you'd expect, the emphasis is on Sync Services for ADO.NET with SQL Server Compact Edition (SSCE) v3.5.

Surprisingly, there's only one brief reference to the Sync Designer (in the answer to "Does Sync Services Support N Tier?") With the Sync Designer debuting in Orcas, I've been expecting more Q&A on the Sync Designer in the Microsoft Synchronization Services for ADO.NET forum. So far, searching on "designer" returns only two hits (one on 1/24/2007 and another 2/28/2007).

Maybe the lack of traffic is due to the strange name of the Orcas designer template: Local Database Cache (see Guided Tour of Orcas's Sync Services Designer for SSCE.) Data[base] Synchronization Service makes more sense to me. As of today, Google returned relevent hits on "Local Database Cache" only for Nick Randolph's and my posts. Same for "Sync Services Designer."

Update 3/21/2007: I should have searched for "local data cache" orcas, to pick up the Visual Basic Team blog's New Data Tools Features in Visual Studio Orcas post by Young Joo (3/13/2007). The post includes a "Local Data Cache with SQL Compact Edition" topic. The author omitted "base" and called the template "Local Data Cache." The article also:

  • Describes Hierarchical Updates with the new TableAdapterManager class, which simplifies code for executing updates on all of your DataSet's table adapters with the TableAdapterManager.UpdateAll(DataSet) method
  • Previews the newly-renamed Object Relational Designer (formerly the DLinq Designer)
  • Introduces n-tier support for typed DataSets by splitting the class file into another project.

Local Database Cache also suffers from lack of any documentation whatsoever. Try searching online help for "Local Database Cache" -- nada. (Hierarchical Update's help topics appear complete and Object Relational Designer has an unfinished walkthrough.)

Get The Bits

Here are OakLeaf links to details for downloading the current SSCE CTPs, RTMs and samples:

Monday, February 12, 2007

Microsoft Sync Services 1.0 Books Online CTP

Microsoft promised documentation for the Microsoft Synchronizaton Services CTP would be available in "early February" when the Occasionally Connected Services (OCS) team released the Microsoft Synchronization Services for ADO.NET v1.0 CTP (OCS-CTP) on January 22, 2007. Surprise! The OCS team posted the Microsoft Synchronization Services for ADO.NET Books Online Community Technology Preview (OCS-BOL) on February 12, 2007. The download site offers SYNCSVCSBOL-ENU.msi, the 16.7-MB installer for OCS-BOL and SyncServicesCtpSamples.exe, which generates a "Two-Tier Download-Only Sample" application. The sample app synchronizes the Adventure Works sample database's Products table with an SQL Server Compact Edition (SSCE) client database. You need the OCS-CTP, which also installs an updated SSCE v3.5 CTP, the Orcas successor to the SSCE v3.1 RTM bits, which Microsoft released on January 11, 2007. (The prerequisites are the same as those for Rafik Robeal's four OCS demo applications that I've previously described here and here, plus a current version of AdventureWorks.) The SyncComponents_Sample1 Project SyncComponents_Sample1.sln is a C# project that generates a new 276-kB SSCE 3.5 database from AdventureWorks' Customer, Employee, Product, ProductModel, ProductSubcategory, UnitMeasure, SalesOrderHeader, SalesOrderDetail, and Store tables. Synchronization is DownloadOnly, so changes only propagate from the server to the client. Here's an overview of the steps required to test the sample project:

  1. Prepare the server tables for synchronization. Execute the ServerDb_CreateSyncObjects.sql script to add InsertOriginatorID, UpdateOriginatorID and CreateDate tracking columns, a TableName_Tombstone table, and a trgDel_TableName delete trigger to each of the nine tables.
  2. Create the SSCE database and populate its tables. Build and run the project, and then click the Synchronize button. The DataGridView displays changes, not rows added for initial table population.
  3. Add insert, update and delete stored procedures. Execute the ServerDb_DMLSyncObjects.sql script to create uspProductInsertCommand, uspProductUpdateCommand, and uspProductDeleteCommand stored procedures.
  4. Make changes to the server data. Execute the stored procedures a few times to generate changes to the Product table.
  5. Synchronize the client. Click Synchronize to pull and display the server changes.

The following screen capture reflects inserting 21 rows with the uspProductInsertCommand.

This sample project won't win any awards for comprehensive coverage of Sync Services code. Unlike Rafik's demos, it doesn't support Bidirectional synchronization. On the whole, Rafik's projects provide a much better overview of Sync Services programming techniques. Tip: If you receive an unhandled exception that starts with "Table '[ProductSubCategory]' could not be created," you have an old AdventureWorks version in which the the ProductSubCategoryID had the smallint data type. The GetSchema method attempts to add it to the SSCE ProductSubCategory table as a smallint identity column, which SSCE doesn't suport. Update your AdventureWorks database from the link provided. You must delete the AWClientDB.sdf file in the \Program Files\Sync Services\Samples\SyncComponents_Sample1\SyncComponents_Sample1.Client\bin\Debug folder before you can successfully populate the client database. Technorati tags: , , , , , , , , .

Wednesday, February 07, 2007

Conflict Resolution with Sync Services

Rafik Robeal has posted the fourth Microsoft Synchroniztion Services (OCS) demo project, Offline Application—Conflict Handling, on his The Synchronizer blog. This C# project demonstrates handling Sync Services' ApplyChangesFailed event to determine the failure location (server or client) and type of concurrency conflict, and then specifying an action to ignore or resolve the conflict. Rafik's post, which augments three preceding Sync Services demo projects, describes the ApplyChangesFailedEventArgs.Conflict.ConflictType enumeration:

  • ClientInsertServerInsert also known as PK Collision; occurs when both client and server insert a row with same PK
  • ClientUpdateServerUpdate the most common conflict; occurs when client and server make changes to the same row independently
  • ClientUpdateServerDelete occurs when the client updates a row and the server independently deletes the same row
  • ClientDeleteServerUpdate occurs when the client deletes a row and the server independently updates the same row

The SyncForm has four added buttons to generate random conflicts on the client or server:

Actions available to attempt to resolve concurrency conflicts are:
  • ApplyAction.RetryApplyingRow without changing to the datarow the retry will fail causing the event to be raised again. The demo does not allow you to edit the datarow though.
  • ApplyAction.RetryWithForceWrite has support built in the ClientSyncProvider. On the server side, you need to add support in your sync adapter commands; the demo shows an example for update-update conflict.
  • ApplyAction.Continue ignores the conflict
  • Abort the sync operations by throwing an exception; all changes will be rolled back and re-synced in the next sync session.
The ConflictForm's four action buttons are enabled as appropriate for the conflict type and location:

After taking the appropriate action, the ProgressForm opens: Rafik explains that the synchronization framework defines the following two commands to find the rows in conflict:
  • SyncAdapter.SelectConflictUpdatedRowsCommand this command finds conflicting rows in the base table. The sync runtime executes this command if insert, update or delete command failed (i.e. returned 0 row count)
  • SyncAdapter.SelectConflictDeletedRowsCommand this command finds conflicting rows in the tombstone table. The sync runtime executes this command if the conflicting row was not found in the base table. This is how ClientUpdateServerDelete conflict is detected.

The two commands are implemented for the two tables—orders and order_details—by four T-SQL stored procedures on the SQL Server 2005 [Express] server.

Note: Be sure to run the demo.sql script (to the -- Test Sample comment only) to create (or recreate) the pub database and the four server-side tables (orders, order_details, orders_tombstone, and order_details_tombstone) on an SQL Server 2005 [Express] instance, then run the server_procs.sql to generate the stored procedures, and finally build and run OfflineAppDemo-Conflicts.sln. Note that the tombstone tables have a new schema to support multiple deletions of records with the same primary key.

Technorati tags: , , , , , , , , .

Wednesday, January 31, 2007

Three Sample C# SSCE Sync Services Projects

As mentioned in my January 28, 2007 post, "Microsoft Releases Synchronization Services CTP," Rafik Robeal has posted the source code for C# demo applications that synchronize data bidirectionally between SQL Server 2005 [Express] (SS[X], server) and SQL Server 2005 Compact Edition (SSCE, client). Rafik is the primary Microsoft participant in the new Microsoft Synchronization Services for ADO.NET forum and writes MSDN's The Synchronizer blog. To build and run the three projects, you must download and install the Microsoft Synchronization Services for ADO.NET v1.0 CTP release of January 22, 2007, which installs a post-Orcas release of SSCE v3.5 and the Microsoft.Synchronization....dlls on your test machine.

The projects use simple orders and order_details tables that are empty to start. You add random rows to the two tables with buttons on a two-tab Windows form, optionally execute random updates and deletions on the SSCE client, and finally synchronize the client-side changes with the SS[X] server). Each base table has a corresponding tombstone table to track deletions.

Here's a screen capture of the main SyncForm.cs window displaying the order_details table: When you click the Synchronize button, the following window reports the results:

Following are brief descriptions of the three sample projects, each of which uses the same UI:

  • Offline AppDemo—Builder (released 1/25/2007) demonstrates basic Sync Services functionality by using the SqlSyncAdapterBuilder to generate a bidirectional SyncAdapter for each table that includes information on the base and tombstone table columns. SyncAdapters are similar to ADO.NET 2.0 TableAdapters. There's more information in Rafik's Demo I: Offline Application—SyncAdapterBuilder post and my earlier post gives some tips to get started on the right track.
  • Offiline AppDemo-T-SQL + SProc (released 1/28/2007) substitutes T-SQL queries and stored procedures executed by SqlCommand objects for the SqlSyncAdapterBuilder. Using T-SQL and SProcs provides exposes SyncAdapter code for customizing the process and gives additional insight into how SyncAdapters work. Documentation is in the Demo II: Offline Application—TSQL+SProc post.
  • Offline AppDemo—WebServices (released 1/30/2007) illustrates the N-tier approach by moving the server-side code to a Web service and wrap the client-side code to enable it to plug into the Web service's sync agent. Using a Web service complicates the installation, so be sure to read Demo III: Offline Application—WebService before attempting to build and run the project.

These three demo apps provide the basic code that you need to test two-tier and N-tier synchronization with more complex data structures. Hopfully the Sync Services documentation will arrive as promised in early February 2007.

Update 2/5/2007: Rafik provides a comparison of Sync Services with SSCE as the subscriber (client) and SQL Server 2005 Merge Replication with SQL Server 2005 as the publisher and SQL Server or SQL Express as the subscriber (Synchronization Services for ADO.NET Vs SQL Merge Replication). It would be interesting to compare a scenario with SQL Server 2005 as the publisher and SSCE as the subscriber.

Update 2/7/2007: Rafik has added a fourth OfflineAppDemo for concurrency conflict detection and resolution. See a summary with screen captures here. Get further details and download the project here. Technorati tags: , , , , , , , , .

Friday, January 26, 2007

Microsoft Releases Synchronization Services CTP

The Microsoft Synchronization Services for ADO.NET v1.0 CTP release appeared without fanfare on January 22, 2007. The release contains "v1.0 early CTP" bits for the ADO.NET sync framework and and an updated SQL Server 2005 Compact Edition (SSCE) v3.5, the Orcas successor to the SSCE v3.1 RTM bits, which Microsoft released on January 11, 2007. Sync Services for ADO.NET's goal is to enable synchronization between any pair of data sources that have ADO.NET managed providers. The current code name for Sync Services is OCS (for Occasionally Connected Systems).

According to the download details:

Microsoft Synchronization Services for ADO.NET provides the ability to synchronize data from disparate sources over two-tier, N-tier, and service-based architectures. Rather than simply replicating a database and its schema, the Synchronization Services application programming interface (API) provides a set of components to synchronize data between data services and a local store.

Applications are increasingly used on mobile clients, such as laptops and devices, that do not have a consistent or reliable network connection to a central server. It is crucial for these applications to work against a local copy of data on the client. Equally important is the need to synchronize the local copy of the data with a central server when a network connection is available.

The Synchronization Services API, which is modeled after the ADO.NET data access APIs, gives you an intuitive way to synchronize data. It makes building applications for occasionally connected environments a logical extension of building applications where you can count on a consistent network connection.

The OCS installation folder is \Program Files\Microsoft Synchronization Services\ADO.NET\v1.0\, which contains Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll, Microsoft.Synchronization.Data.Client.dll, and ReadMeSyncServices_ENU.htm. ReadMeSyncServices... has no useful content.

Documentation, Support and Sample VS 2005 Sync Projects

The download includes no white papers, tutorials, or other documentation for programming Sync Services. Microsoft says to expect documentation here for Sync Services in early February 2007.

In the meantime, there's a new Microsoft Synchronization Services for ADO.NET forum and an MSDN The Synchronizer blog run by Rafik Robeal. Rafik's blog has a link to Offline AppDemo-BuilderV1.1.zip, which contains a C# demo application that works with SQL Server.

Tip: If you install and run the Offline AppDemo.sln project be sure to read the readme.txt file in the ...\setup folder, select and run only the specified part of the demo.sql script with SSMS[X], add \SQLEXPRESS to the Server Machine text box if you're running SQLX, and click the Synchronize button to create the clientdb.sdf database in the location specified in the Client Database text box before you click any other buttons.

Steve Lasker mentioned in his January 23, 2007 "Sync Services for ADO.NET (OCS) CTP Now Available" post that the Orcas February CTP will include a Sync Designer and that he plans to "post a screen capture of the designer soon." If you check out Rafik's demo project, you'll see why the Sync Designer is a must. (Too much code required for a simple, two-table project.)

SSCE Backward Compatibility

The download details state that the SSCE v3.5 runtime bits install side-by-side with SSCE v3.1 on desktop, laptop, and Tablet PCs. The two SSCE installation folders are \Program Files\Microsoft SQL Server Compact Edition\v3.1\ (RTM) and \Program Files\Microsoft SQL Server Compact Edition\v3.5\ (CTP).

I've confirmed that complex (1,000+ lines) WinForm apps created with SSCE v3.1 RTM (file v3.0.5300.0, assembly v9.0.242.0) work fine with SSCE v3.5 from the Orcas January 2007 CTP (file v3.5.5277.0, assembly v3.5) and v3.5 in the OCS CTP (file v3.5.5305, assemby v3.5). Upgrading current SSCE 3.1 projects to v3.5.5305 requires removing the current System.Data.SqlServerCe reference, and adding a new v3.5.5305 reference by browsing to \Program Files\Microsoft SQL Server Compact Edition\v3.5\System.Data.SqlServerCe.dll.

Note: The 3.5.5305 version installs as expected with Windows Vista running under Virtual Server 2005 R2 SP1 CTP. This configuration isn't explicitly listed as supported by SSCE v3.5 in ReadmeSSCE35_ENU.htm. Version 9.0.242.0 failed to install completely under virtual Vista.

New v3.5 SSCE Features in the CTP Release

The SSCE v3.5 ReadmeSSCE35_ENU.htm file's "New Features in SQL Compact Edition 3.5 CTP" section list the following enhancements to v3.1:

  • Support for the timestamp datatype
  • Nested queries in the SELECT FROM clause
  • CROSS APPLY and OUTER APPLY operators
  • CAST function and TOP clause
  • Support for the ADO.NET Entity Framework and LINQ to Entities with System.Data.SqlServerCe.Entity.dll (under Orcas)
  • Enhanced table editor for VS Orcas can add foreign key constraints
  • Support for local transaction scope

Note: The enhanced table editor for VS Orcas didn't appear in my installation of the Orcas January CTP. A later Orcas build (February CTP?) probably is required.

Surprisingly, the new features did not include the expected removal of the 4-GB database size limit. The ssce:Max Database Size range remains 16 to 4091 kB.

The bits appear to me to be a stealth release. As of today (1/27/2007), there was no mention of Sync Services on the SSCE main page and there were no posts about Sync Services in the SQL Server Compact Edition forum until I added a message with a link. Microsoft might be waiting for initial documentation to be completed before promoting Sync Services.

Update 1/28/2007: Minor clarifications and corrections.

Technorati tags: , , , , , , , , , , , , , , , , .

Friday, January 12, 2007

SQL Server 2005 Compact Edition RTM Available

Microsoft delivered its Web release of SQL Server 2005 Compact Edition (SQLceSSCE) on January 11, 2006. The Web release finally eliminates previous references to Windows Mobile and SQL Server 2005 Everywhere Edition.
SSCE is a lightweight, single-user relational database engine designed for client-side data storage on Windows desktops, laptops, and devices. Its query engine supports a subset of SQL Server 2005 data types and T-SQL.
The Web release consists of the following four downloadable components:
SQLce30setupen.msi—the only file that doesn't use the SSCE abbrevation—apparently didn't need updating from the SQL Server Mobile 3.0 version.
Support: SSCE's main page is here and the newsgroup is here. There's an SQL Server Compact Edition blog and Steve Lasker's blog has links to many SSCE resources. Steve is the SQL Server Program Manager/Technical Lead for Smart Client and Occasionally Connected Systems.
Programming and Managing SSCE
The SqlServerCe namespace provides data access classes similar to ADO.NET's SqlClient. Installing the runtime also registers an OLE DB driver for applications running unmanaged code. Data files (*.sdf) are stored in the file system and are compatible with Windows Mobile 3.0 applications. SSCE 3.1 is ideally suited for Click Once installation of data-driven Windows forms apps. See my May 1, 2006 "SQL Server 2005 Mobile Edition Goes Everywhere" Special Report for an early look at SSCE. The SQL Server Management Studio [Express] SP2 December 2006 CTP or later (SSMS[X] SP2) is required to manage SSCE if you aren't running Visual Studio 2005 SP1 because the VS Express editions don't include Server Explorer. But you'll find SSMS[X] SP2 to have somewhat better management tools than VS 2005 SP1.
Note: Download SSMSX December 2006 CTP here or the full SSMS version here.
Use SSMS[X] or VS 2005 SP1's or T-SQL DDL scripts to create and modify SSSCE's *.sdf data files, which store character-based data in Unicode only. You'll need to use DDL for operations other than creating databases and adding, deleting and modifying tables.
For an in-depth look at programming SSCE with Visual Basic .NET, download a copy of SQL Server guru and author Bill Vaughn's Hitchhiker's Guide to Microsoft SQL Server 2005 Compact Edition eBook (a bargain at the US$9.95 introductory price).
Replicating SSCE
SSCE supports merge replication and Remote Data Access synchronization scenarios with SQL Server 2000 SP3 and later. The next version (3.5) of SSCE will scheduled to include a new Microsoft Synchronization Services for ADO.NET (code-named OCS for Occasionally Connected Systems) in Visual Studio Orcas. A pre-release version of SSCE v3.5 is included in the Microsoft Pre-release Software Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP).
Update 1/27/2007: Microsoft released on January 22, 2007 a Microsoft Synchronization Services for ADO.NET CTP, which also includes support for SSCE LINQ to Entities.
For more details on SQL Server <-> SSCE replication technologies, see the "ADO.NET Sync Framework for Occasionally Connected Systems" post. An SQL Server Client Access License (CAL) is required for replication between SQL Server and SSCE. (SQL Server 2005 Express Edition (SSX) has the same CAL requirement for replication.)
Note: Microsoft says Virtual Server 2005 R2 SP1 is not a supported system for SSCE. This might explain my problem with installing the runtime on "virtualized Vista" but not the lack of problems with "virtualized Windows Server 2003 R2" installation.
Update 1/15/2007: Abbreviation changed from SQLce to SSCE. Google shows 124,000 hits for the SQLce and 14,000 for SSCE abbreviations, but Microsoft appears to have standardized on SSCE. SSCE is consistent with use of SSX for SQL Server Express and SSMS[X] for SQL Server Management Studio [Express].
Update 1/27/2007: Updated for OCS CTP and added links to SSMS[X] downloads.
Technorati tags: , , , , , , , , , , , , , ,