By Zach Skyles Owens

One of the biggest challenges we faced when designing this demo was trying to make the breadth of Microsoft's Data Platform technologies easily understandable in 5 minutes.  The major architecture components are SQL Server 2008, SQL Server Data Services (SSDS), SQL Server Compact and Sync; which I outlined in a previous post.  We've been following the blogosphere as closely as possible and, understandably so, one of the areas we've seen a bit of confusion around is the type support and conversion between the Entity Data Model, SSDS and SQL Server 2008.

There are a lot of moving parts here so I'll do my best at explaining how everything was integrated and how we got around some of the differences in type support of the data platform technologies.

Web Application

image Bloggers use this application to submit geo-tagged articles and images which are stored in SSDS.  We embedded a Virtual Earth control which the users use to manually geo-tag their content.  Those who have been following SSDS closely may have noted that SSDS does not currently support blob storage or spatial types. 

  • Blob Storage in SSDS - This is the number one feature that customers are asking for right now with SSDS.  Until SSDS supports blobs our current workaround was to Base64 Encode the images for storage in SSDS.
  • Spatial Storage in SSDS - Currently SSDS does not have support for Spatial types.  This was an easy work around for us by converting the spatial POINT coordinates to the Well Known Text (WKT) format and storing them as text. 

Mobile Application

image In this scenario we are also using a Windows Mobile device that allows bloggers to submit photos which are automatically sent to SSDS via the Microsoft Sync Framework.  Here are some key points:

  • A SQL Server Compact database stores the application's meta-data
  • Images are stored on the device's file system
  • Geo-tagging is pulled directly from the GPS enabled device.  The app caches the last known GPS coordinates in case of lack of GPS connectivity.
  • The Sync provider running on the device converts the geo-data to WKT and the image to Base64 text as in the web app

Sync from SSDS to SQL Server

sync2 Our application uses a powerful WPF desktop application connected to a local SQL Server 2008 database.  Since SQL Server 2008 supports Spatial we have the ability to run high performance spatial queries which aren't currently possible in SSDS.  FILESTREAM allows us to use the high-performance of the file system for binary file storage along with transactional consistency and great manageability of the database.  Type conversion here was very simple and outlined below:

  • Sync and FILESTREAM - The Sync Provider sitting on our SQL Server database pulls the Base64 binary data from SSDS and inserts it into a varbinary(max) FILESTREAM column in our database.
  • Sync and Spatial - This Sync Provider inserts the WKT POINT data into a SQL Server GEOGRAPHY type.

EDM, Spatial and FILESTREAM

  imageimageOur WPF application uses the Entity Data Model (EDM) to provide the application developers with a more natural business representation of the data. This allows the database model and application data model to evolve independently. Currently the EDM has limited support for FILESTREAM and does not natively support the new SQL Server 2008 Spatial types (GEOGRAPHY and GEOMETRY).  These were also very easy to work around in the following ways.

  • EDM and FILESTREAM - Currently the EDM treats FILESTREAM in the same way it treats any other varbinary(max) column.  You get the transaction consistency and manageability of the database.  It interacts with the FILESTREAM data through T-SQL so you don't get the Win32 streaming performance that FILESTREAM has the ability to provide.  If that type of read/write performance is needed you can easily write a section of code that interacts with traditional database connections and SQL.
  • EDM and Spatial - Currently the EDM does not support the new Spatial types.   Our WPF application had two main requirements for Spatial: high performance queries and showing the Spatial meta-data.  We wrote a stored procedure for the queries and mapped a calculated column which converted the spatial data into WKT for displaying the meta-data.

Summary

There were definitely some things that we had to consider when building this application but in the end none were major barriers.  The application works great and was an enjoyable development experience.