Steve Lasker Uncategorized

Demos, Presentations, Links, Screencasts and Videos for SQL Server Compact

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

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

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
mso-pagination:widow-orphan;
font-size:11.0pt;
mso-bidi-font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

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