I often find it hard to find my own demos scattered throughout my blog, so figured I'd just create a single posting that had all my current demos and powerpoints.

Recent Presentations at PDC and Tech Ed

SQL Server Compact Overview (lots of demos with supporting slides) 
This was a very demo heavy deck with supporting slides.  Covers many of our SQL Server Compact goals and non-goals, including the tooling scenarios, split between VS and Management Studio.  Slides and demo on ASP.NET soft block, programming models with DataSet and SqlCeResultSet.  (This deck was before we had LINQ to Entity support available).  Covers a quick overview of optimized online, offline enabled architectures, with demos of Sync Services for ADO.NET, including 2 tier and N Tier.  Then wraps up with Logical Queuing as the overall architecture for client based apps.  I had a lot of fun with this presentation, but it was hard to fit in an hour.


Unlocking the Power of SQL Server Compact (Tech Ed EMEA 08)
Covered several topics, such as the questions on how fast can SQL Server Compact insert data.  For Tech Ed EMEA, I added the section discussing stored procedures, and how a common DAL layer can actually provide more functionality than sprocs for both local data and server data. (yes, I'm sure there will be lots more to debate on this topic, but here's our thought process J)

With some coverage on private deployment, including 64bit, deep dive on perf and the managed sprocs demos, I bubbled back up to cover several interesting aspects for using SQL Server Compact, including a TraceListener to capture information from the field and sync it back with Sync Services for ADO.NET.  Then covered read only databases on DVD's and using Compact as a custom Document format.

Associated Demos:

Managing and Versioning Client Databases (Tech Ed EMEA 08)

Discusses the pros and cons of managing database deployment on the client.  Why deploying databases as files, through MSI's, ClickOnce or other technologies can be easy for V1, but could lead to data loss in V2.  Why a little investment up front can yield you productive results long term, and make for a better user experience.

Associated Demos:

  • Creating & Managing Local Database Versions through Scripts
    When deploying applications with a local database, deploying the database with the app can seem easy at first, but once the user starts making changes to the local database, it gets messy quickly. If you're not careful, you can even lose the local data created/updated by the user. In this presentation, I created several animated slides that explain the complications and why script based deployment and versioning is the only practical way to manage local databases. This sample walks through the creation and versioning of local tables. To focus on the script based versioning aspects, I excluded any interaction with synchronized tables as that just gets even more complicated.
  • Versioning Local Databases with Scripts when using Sync Services for ADO.NET
    In this demo we see how to version schemas when using Sync Services for ADO.NET. This includes two projects. One for the initial installation of V1, and another for the upgrade, or initial creation of V2. V1 includes creating a set of tables that aren't synchronized, such as state management for submitting orders to an existing service. Once sync completes, there are a set of additional scripts to create foreign keys between the synched tables, and between the synched tables and the local tables.
    V2 enhances the synchronized tables with additional columns. The setup of this demo is a bit more complex as I've configured the server side sync components in a dll, then hosted the dll in a WCF Web Project. The WCF Web Project is published to an IIS instance, but uses different sub directories: for V1-http://localhost/VersionSchema/V1/LookupSyncService.svc, for V2-http://localhost/VersionSchema/V2/LookupSyncService.svc. Using this versioning scheme we can publish V2, allowing apps to slowly migrate over to V2, while the V1 apps continue to function and the business continues to function. The animation in this presentation helps explain this somewhat complex subject.

Just the demos, and nothing but the demos...

  • SQL Server Compact Trace Listener
    Demo for using Compact as a TraceListener to capture the output of System.Diagnostics.Trace.WriteLine and then synchronize that info back to the server providing visibility into what's happening out on the client. This deck includes animation, which looks a bit like Missile Command, demonstrating how this sample works.
  • Using SQL Server Compact under ASP.NET
    Demonstrates how to "unblock" SQL Server Compact for usage under ASP.NET. I discussed our reasoning in this post. A quick recap: The reason we blocked Compact for ASP.net scenarios was simply to not set expectations Compact is meant to be a replacement for SQL Server. It performs better than Jet. For the most part, we unblocked it for pre-building a SQL Server Compact database for sync scenarios where it's faster to download the database as a stream, then to sync the initial snapshot of data. It's also perfectly fine for small websites or other services. The main intention is strictly to make sure developers know, this is not meant to compete with SQL Server. There are coding differences between Compact and SQL Server, so we really didn't want developers to start with Compact, only to later find out it doesn't scale to thousands of concurrent website/service users for updating data. As long as developers understand the scope, it's perfectly fine to use for ASP.NET, WCF, or other service scenarios...
  • SqlCeResultSet Demos - Scrollable, Updateable, ISAM like cursors
    Demonstrates how developers can take advantage of embedded a database directly into your app. The best thing about the SqlCeResultSet is it works directly over the data in your local database. No queries that bring the results into another copy in memory that you must mange potential conflicts with the local store, or potential conflicts between the local store and the remote server. With the SqlCeResultSet you can scroll directly over the local table, even setting the index to filter and order the rows you're viewing. The SqlCeResultSet is a scrollable, updateable cursor and can really improve the perf and overall working set of your application.
    • Direct Access (ISAM) with the SqlCeResultSet
      Why do we need to make copies of the data, update it, then send it back? In a shared database environment, where you're likely connecting to the database across the network, and sharing with potentially thousands of others, because it's the only way we can scale. However, when the database is local, in-proc with the app, things can be much simpler. In this demo, you can easily update data, and as soon as you move off the row, it's committed to the database. To get the most of this demo, run the same app twice (CTRL+F5). Make changes in one of the instances, move off the row. In the other instance, move to the row that was just updated, (note: if you're already on the row, you'll need to move off then back). You'll see the change made by the other app and can make the change back. The one piece missing from this scenario is an implementation of eventing so that instance 2 would see the changes made by instance one. This is why you must move off, then back to the row to see the change. As you move across the rows, the DataGridView requests the data. The data is fetched directly from the local database/table.
    • Another ISAM/SqlCeResultSet demo
      In this demo we see how the index can be set to control the order of the data. Using a system view, we can populate the ListBox with the list of potential indexes. As you change the index, the view is updated to reflect the data sorted by that index. What isn't covered in this demo is the ability to "filter" the values using the SqlCeCommand.SetRange() api which uses the index to directly filter the visible rows.
  • SQL Server Compact Bulk Insert Performance Test Harness (Compares Compact & Express)
    Demonstrates the power of an embedded database and how much faster it can run than SQL Server for many operations. By implementing a few things, such as "connection pooling" and caching your SqlCeCommand objects, you can achieve similar functionality provided by SQL Server's connection pooling and Query Plan caching. However, because Compact is "in-proc" with your application, you can get the benefits of not having to work through additional networking, process and security layers required by SQL Server which is designed to work over the network.
  • Privately deploying both the 32 and 64bit versions of SQL Server Compact with your app
    Do your users know whether they have 32bit or 64bit machines? If they are 64bit, are they running a 64bit version of the OS? Should they have to figure this out? Using private deployment, developers can include both the 32 and 64bit versions of Compact so the user never has to make a choice. Yes, it's a bit more k to include in your app, but if it "just works", is that a small price to pay?
  • Running Compact from Read Only Media (DVD, CD, Locked USB Key)
    Ever get a CD or DVD in the mail. Pop it in the drive to see what's on it, and it prompts you to install some software - yikes! Let's see if you can use this as a Frisbee and get it in the trash across the room, J With this demo, you can deploy a large amount of content, but stored in a Compact database so your users can run the app, search the data, and with private deployment, you can include the Compact engine so your users can "view the data" without having to install anything.
    The demo is simply an extension of private deployment, but modifies the connection string to tell Compact to open the database in ReadOnly mode, and specifies a temp directory:
    connStr = String.Format("Data Source = {0}\Northwind.sdf;Mode = Read Only;Temp Path={1} ", Windows.Forms.Application.StartupPath, My.Computer.FileSystem.SpecialDirectories.Temp)
  • Using Compact as a Custom Doc Format (Recipe Manager)
    Need to save data from your app in a "document"? Want to post that "document" to a SharePoint site or email it to another team member? Want to double click your document and launch the associated app? Since SQL Server Compact uses a single file, code free, file format, and since it doesn't care what extension you use, developers can use custom extensions for the database file to launch their app. In this demo I use Click Once enhancements in .NET FX 3.5 SP1 to associate the custom extension with my ClickOnce installed application.
  • Managed Stored Procedures (A Common DLL for data access to Compact and SQL Server)
    The great debate will forever continue on the debate of sprocs and its religion or science. I wrote about this here, but rather than debate the topic, here's a sample that shows how you can write a common data access layer to target against SQL Server (Express included) and Compact (SQLce) As outlined in this presentation, you can get far better scalability from your database by moving the logic out of the database as you can create several front end services to a single database, but you can only scale up a database so far before you need to scale out, which today is a difficult task.
  • Logical Queuing
    In a service oriented world, is it really practical to assume all "services" can be synchronized? Or, is it a mixture of synchronizing some data, and submitting to existing, non synchable services that really touches on reality? In this demo, associated by this blog post, I demonstrate why using a database as your "queue" to services provides much more practical and realistic methods for working with existing services than expecting all the existing services to rev to support "sync". Sync is a very important technology, but no one technology serves all. In this blog post I noted down some thoughts, but not as polished as I was hoping to post.
  • Using Compact within Excel
    Ginny Caughey and I co-presented at Tech Ed US in 08. Ginny assembled some great demos on using SQL Server Compact within VSTO, particularly within Excel.
  • Database Viewer
    Every wanted to just look inside a SQL Server Compact Database, but didn't want to install Visual Studio or SQL Server Management Studio Express? With this little utility you can see the list of tables, and the data within each table. Not a full featured app, but rather a utility I use to show encryption or the ability to open a database across multiple users. It does show how to query the system views to get the list of tables.
  • Creating & Managing Local Database Versions through Scripts
    When deploying applications with a local database, deploying the database with the app can seem easy at first, but once the user starts making changes to the local database, it gets messy quickly. If you're not careful, you can even lose the local data created/updated by the user. In this presentation, I created several animated slides that explain the complications and why script based deployment and versioning is the only practical way to manage local databases. This sample walks through the creation and versioning of local tables. To focus on the script based versioning aspects, I excluded any interaction with synchronized tables as that just gets even more complicated.
  • Versioning Local Databases with Scripts when using Sync Services for ADO.NET
    In this demo we see how to version schemas when using Sync Services for ADO.NET. This includes two projects. One for the initial installation of V1, and another for the upgrade, or initial creation of V2. V1 includes creating a set of tables that aren't synchronized, such as state management for submitting orders to an existing service. Once sync completes, there are a set of additional scripts to create foreign keys between the synched tables, and between the synched tables and the local tables.
    V2 enhances the synchronized tables with additional columns. The setup of this demo is a bit more complex as I've configured the server side sync components in a dll, then hosted the dll in a WCF Web Project. The WCF Web Project is published to an IIS instance, but uses different sub directories: for V1-http://localhost/VersionSchema/V1/LookupSyncService.svc, for V2-http://localhost/VersionSchema/V2/LookupSyncService.svc. Using this versioning scheme we can publish V2, allowing apps to slowly migrate over to V2, while the V1 apps continue to function and the business continues to function. The animation in this presentation helps explain this somewhat complex subject.

·         Deploying Express with Scripts
Similar to the Compact deployment model, but with the additional steps required to create the physical mdf/ldf combination.

  • Private Deployment using the ADO.NET Entity Provider
    One of the most popular features of SQL Server Compact is its ability to privately deploy the Compact runtime as a set of dlls directly within your application. But when using the ADO.NET Entity framework, things get a little more difficult as the Entity Framework uses the Provider Factory model for loading the individual data providers. I discussed this in more detail in this post, but here's a sample showing how to include the dlls and changes in app.config to work properly.
  • Common Programming between Desktop & Device
    While SQL Server Compact provides the same apis between desktop and device (.NET Framework and the .NET Compact Framework), it can get a little more complex as the surrounding apis, such as the lack of the |DataDirectory| macro, and the lack of settings make it more difficult. This project shows a strategy for filling in the missing pieces of the .NET Compact framework so you can compile the same sourcecode for desktop, then device.

Other interesting links to previous posts:

Some great partner tools

  • Data Port Console by Primeworks
    Great tool for scripting tables from SQL Server Compact. For only $50, it's hard to beat
  • Remote SQLce
    Great tool for managing a Compact database on the device, from the desktop
  • CodePlex
    Codeplex is starting to collect several great code projects related to SQL Server Compact. I'm sure this will continue to grow, so it's a great place to look for what pain someone else has already endured

Great books

  • HitchhikerGuides.net - Bill Vaughn
    Bill has a great, no BS style that just helps you get to the facts. It's a quick read, and at only $10, it's hard to pass up when you want to get up to speed quickly on Compact.
  • Microsoft SQL Server 2005 Compact - Prashant Dhingra
    Prashant originally worked in the Compact team, and provides a great insiders look at SQL Server Compact
  • SQL Server Compact (In Spanish)- Jose M. Torres
    If you're looking for a book on Compact in Spanish, here you go. I wish I could say I've read it, but, it's been years since I was at all fluent in Spanish. From what I have flipped through, it looks like it's covered all the topics. Jose has been active in the community, so I'd say it's a great bet.
  • Windows Mobile Data Synchronization - Rob Tiffany
    Need some help, or want to know about Merge Replication? Might as well get it from the mast of Merge - Rob Tiffany. Rob spends his time traveling around the world helping customers scale and configure Merge. If it's been done, Rob has seen it, and likely has helped get it working.

Other Support/Info Channels

SQL Server Compact Blogs & Forums

Steve Lasker's Blog
The blog you got this document from, :) Lots of information on how we got here, demos and powerpoints.  But, since I've moved off the team, won't be able to maintain the information going forward, so I'd recommend the following blogs going forward.

SQL Server Compact Team Blog
Where most of the team posts info.  Everything from our encryption details, Entity support, query processor details, etc.  You can think of it as the authoritative source.  Even Laxmi posts to the Team Blog as well as his own...

Laxmi's Blog
One of great and passionate developers on the SQL Server Compact team.  Be careful what you ask for, ‘cause Laxmi will tell you J.  Seriously, Laxmi spends a lot of time in the forums listening and answering the issues and has a lot of experience with the product.

SQL Server Compact Forums
Where the team and our MVPs hang out to answer your questions.  In addition to the product team, you'll also find answers from those that are actually using the product.  We've gotten some great info here as well for how the product is actually used, as opposed to what we thought developers would do with Compact <g>

Sync Framework/Sync Services for ADO.NET Blogs & Forums

Sync Development Center
The home for the sync team and discusses the Sync Framework as well as Sync Services for ADO.NET.

Sync Team Blog
Less formal information on Sync Services and the Sync Framework

Sync Framework/Sync Services for ADO.NET Forums
A great place to post questions and find answers from the Sync team

Syncmaster Rafik website and Blog
Rafik Robeal was one of our core developers for Sync Services for ADO.NET and has all the nuts and bolts knowledge for what we originally built.  Unfortunately, Rafik has moved onto other ventures.  However, he's still got some great content on his site.

Enjoy the holidays,

Steve