Tech Ed '05: Client and Middle Tier Data Caching with SQL Server 2005

  • Article

Last week Pablo Castro and I presented two topics related to caching.  Pablo presented caching datasets in memory and Service Broker for use in ASP.net projects.  The second half of the presentation covered how to cache data on the client.  The demos I presented were geared around caching for longer periods of time then what in-memory caching can support. 
Since we only had a limited amount of time for content that really deserved sessions of their own, we had a lot to cover and had to be fairly high level without the ability to drill down into the demos.  For the client caching half I covered the use of SQL Mobile Remote Data Access as well as SQL Server Merge Replication.  RDA is only available with the SQL Mobile platform, however SQL Mobile now runs on the Tablet PC and is no longer restricted to the CE/Pocket PC platform.  This is a huge advantage to developers that are writing mobile apps that span device and Tablet PC as they can no write one set of data access code and use a single database across these two very different platforms. 
Merge replication is actually available for both SQL Mobile as well as SQL Express.  Due to limited time I didn’t have time to demo both SQL Express as well as SQL Mobile, however the merge replication scenario is just as useful with SQL Express.

 

There were a few demos that I showed, and several others that I just didn’t have time to cover so I’ve included them here for download. 

These samples should all work with Whidbey B2 and SQL Yukon

 

Session Abstract:

At one point or another, almost every developer has had to deal with the problem of building a data cache for their application -- whether it is for performance or to enable offline capabilities. In both scenarios, there are a number of issues that need to be addressed: How do I know the cache is invalid?; How do I synchronize the cache?; And do I have all my data or only a subset in the cache?; etc. In most cases, every developer implements their own favorite flavour of the data cache solution. In this session we look at a number of new features in SQL Server 2005 that are specifically designed to address this problem and look at two specific solutions, one using replication, and one using Query Notifications.

 

Samples:

SQL Mobile with RDA
This demo uses SQL Mobile and Remote Data Access to pull a customer table from a remote database.  This sample is fairly simple and demonstrates the very basic things you need to know to leverage RDA.  To configure RDA please use the SQL Books Online to configure the web server to proxy your RDA queries to your OleDB database.

SQL Mobile with Replication
This demos is similar to the RDA sample above except that it uses SQL Server Merge Replication.  The demo is fairly simplistic but covers the basics to leverage Merge Replication.

 

Version Numbers
When attempting to verify your application is the correct version the question must be asked, which version number are you talking about?  Since RTM we have File Version and Assembly Version.  With ClickOnce we now have a Deployment version.  This sample simply shows the different API’s to call for each.

 

Data Directories
Where did your data files go?  With ClickOnce we have yet another location to place data files.  There are several different API’s to identify a Data Directory but they aren’t always accessible.  This demo simply shows several of the key API’s:

  • AppDomain.CurrentDomain.GetData("DataDirectory")
  • My.Application.Deployment.DataDirectory AKA System.Deployment.Application.ApplicationDeployment.CurrentDeployment.DataDirectory
  • Application.LocalUserAppDataPath
  • Application.UserAppDataPath

Occasionally Connected ClickOnce w/Replication
This is the more complete scenario.  This is also a finished version of the sample I presented in a Yukon Webcast several weeks earlier.  In this demo I essentially demonstrate how to deploy a custom app that leverages the Outlook 2003 model for syncing.  As long as the app has network connectivity the server used for Database Syncing and Application Updates, the application will constantly sync any database changes from the client to the server.  Once a sync is completed the application will check the database for the required application version.  If the version of the app in the database is newer then the currently deployed version on the users desktop, the application will download updates using the ClickOnce API’s.  All of this happens on a background thread so the user can continue to interact with their application while the sync operations happen behind the scenes. 

The important concepts in the demo relate to abstracting the database sync and application updates from specific actions the user must take.  You users shouldn’t have to restart their app when the network comes available just to find out a new version is available.  The user shouldn’t have to press a button to push their changes from their Tablet PC to the server. 

Misc technologies in this sample:
• BackgroundWorker
• ClickOnce API’s
• SQL Mobile
• SQL Server Merge Replication

 

Powerpoint for the Tech Ed '05 Session

 

I'll try to field as many questions on the demos as possible, but I didn't try and explain how to configure RDA or Merge Replication.  The docs are fairly robust in this area. 

There's a readme in most of the samples that desribes the content.

Thanks,

Steve Lasker