The little database that could. Deploying SQL Mobile as a local / single user database across Microsoft Mobile devices


UPDATE – April 6th 2006, Paul Flessner announced that SQL Mobile will released as SQL Server Everywhere Edition.  http://www.microsoft.com/sql/letter.mspx


This will be the first of a several part series of blogicles that cover some of the thinking we’re doing around the Occasionally Connected scenarios, and what you can do today with Visual Studio 2005.   …ok, so today you can use a beta or CTP build, but with technology that will be released really, really soon…


SQL Mobile now runs on the Win32 platform
In Whidbey we now support deploying SQL Mobile, the new version of SQL CE, directly on the Tablet PC.  Why SQL Mobile compared to SQL Express?  With SQL Mobile you can design, develop and deploy a single database, single set of data access code across your Smart Phone, Pocket PC and Tablet PC applications.  Why is this important?  Shops that are deploying Smart Client applications that go mobile aren’t limited to one device type.  The same application may be needed across different form factors.  Just as mail is read on your Smart Phone, Browser and Outlook client, so are custom applications. 
A company may be deploying a beverage sales and delivery system.  Some of the delivery people simply deliver based on route given to them in the morning.  They barcode scan the packages as they deliver them and sync their local database with corporate when they return.  This person really only needs a minimal screen, input UI and a bar code scanner.
Another delivery person has a lot of information that needs to be filled out so they opt for a Tablet PC user interface so they can still get pen based input, but now they can see a larger set of information at one time.  Using SQL Mobile, developers can now build the same data access layer, business layer, sync layer and local database across all these device apps.  They then simply focus on different UI projects for each device.


Comparing SQL Express and SQL Mobile
In future blogicles I’ll go into more details comparing SQL Express and SQL Mobile.  Essentially both have their markets and both are excellent products, they just have slightly different user models.  Both SQL Mobile and SQL Express support Merge Replication.  SQL Mobile has an additional replication technology called Remote Data Access (RDA) which is very easy to use, but not as powerful merge replication.  In Whidbey, Visual Studio 2005, SQL Mobile also has an updatable resultset (SqlCeResultSet) which really simplifies data access for local databases.  SQL Mobile can be deployed via ClickOnce without admin rights.  It’s entire runtime is < 1.4mb.  notice the decimal
SQL Express is essentially SQL Server, so it has all the power features from a multi-user, big/bad mofo type database.  Because it’s a service, you’ll require Admin rights to install and it’s quite big.  For the 32bit platform, the compressed install is 55mb, and this doesn’t include the replication components.  If you’re deploying a multi-user application, SQL Express is perfect.  If you’re deploying a local, single user database, SQL Mobile is your starting point as it really nails the local database scenarios.


Deploying SQL Mobile via ClickOnce
To get a feel how easy it is to deploy SQL Mobile with Click Once, here’s a quick walk through:



  1. Using Visual Studio 2005 (Whidbey) create a new Windows Forms project. 
  2. Using the Data Sources Window choose to add a new Database Data Source for the Northwind sample database

    • On the Choose your Data Connection step, click New Connection
    • In the top of the dialog you can change the Data Source – I know this term is overloaded… 
    • Click the [Change…] button and select Microsoft SQL Server Mobile Edition as the data base data source
    • Your now given a Connection dialog where you can either create a new SQL Mobile Database or you can choose an existing SQL Mobile database.
    • Click [Browse] and you should see Northwind.sdf . .sdf is the SQL Mobile data file extension. 
      If for some reason you don’t see the sample database, verify you’re looking in C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

    • Once you select the sample database, click Ok, and click next to the Save Connection step
    • At this point you’re prompted to copy the .sdf file to your project.  Choose yes so the data file travels with your application.
    • Again, choose yes save the connection string in the Settings file.
    • You can now choose which tables.  For the purposes of this simplified demo, just choose Employees and click finish.
    • This creates a strongly typed DataSet, new in Whidbey for devices, for your Northwind SQL Mobile file.

  3. Drag the employees table from the Data Sources Window to the Form1 design surface.  You can choose the Smart Tag on the grid to dock-fill. 
    So far, this is the same experience for server based databases.

At this point you’d think you can run your application, and you can certainly try hitting F5.  But, you’ll get an exception stating the runtime can’t find sqlceme30.dll.  ADO.net for SqlMobile can’t find the SQL Mobile runtime.  No problem, we’ll just add it to our project. 
But wait you say.  Isn’t that an install?  Or a ClickOnce boot strapper thingy?  Nope.  The SQL Mobile runtime is a few very small dlls.  To get the full functionality for SQL Mobile you only need to add less then 1.4mb to your project.  That’s less then some home page images.



  1. To add the runtime we need to place these files in a path that can be found by ADO.net.  You could MSI install these to the System32 directory, but because they’re so small, why not just ship them with your app? 
  2. Within Solution Explorer, select your project and choose Add Existing Item
  3. Navigate to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE
  4. Change the files of type to all files
  5. Click in the file list window and type sqlce to get to the files that start with these letters
  6. Shift select all 7 files, sqlcera30.dll, sqlcecompact30.dll, sqlceer30en.dll, sqlceme30.dll, sqlceoledb30.dll, sqlceqp30.dll, sqlcese30.dll and click Add.

You now have the runtime in the project, but it’s not in the execution path.



  1. Select all 7 files in solution explorer and select the property grid.
  2. Change the Copy to Output Directory to Copy if newer
    This will make sure these files are copied to the output directory and can be found at runtime.

  3. Hit F5 to test your app.

Voila.  You now have a local database ready to go.
Deploying with ClickOnce
Because these dll’s are just private to the project, you can simply publish your app just as you would normally.  Post Beta 2 however, you’ll have a minor problem.  ClickOnce actually places “Data” files in a different directory then you’re app.  For more info check out my Tech Ed blog and Data Directory sample.


 


Post Beta 2 Changes for SQL Mobile Data Files. 
SQL Mobile data files don’t leverage a new macro called |DataDirectory|.  This is a macro that gets expanded by ADO.net at runtime to resolve the location of the Data Directory for a given deployment model.  For ClickOnce, this is a special directory associated with your application.  For Web Applications, it’s yet another.  To resolve the Data Directory for SQL Mobile you can handle the SettingsLoaded event in the Settings partial class.



  1. Open the Settings Designer under the My Project node in Solution Explorer
  2. Select View Code from the top of the designer and add the following code:

Partial Friend NotInheritable Class Settings


    Private Sub Settings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded


        Dim dataDirectory As String


        ‘ When running under debug mode, use the data file from the same directory as the executable


        ‘ otherwise, use the Data Directory which will be set for ClickOnce or MSI based installs


        ‘ This assumes that MSI based apps placed the data file in the standard non roaming data directory:


        ‘ C:\Documents and Settings\<UserName>\Application Data\<Company>\<Application Name>\<Version>


        If AppDomain.CurrentDomain.DomainManager IsNot Nothing AndAlso _


                AppDomain.CurrentDomain.DomainManager.ToString().Contains(“VSHost”) Then


            dataDirectory = Application.StartupPath


        Else


            dataDirectory = Application.UserAppDataPath


        End If


        Me.Item(“NorthwindConnectionString”) = My.Settings.NorthwindConnectionString.Replace(“.\”, dataDirectory & “\”)


    End Sub


End Class



3.   As the sample shows, this will replace the .\ with the appropriate directory.  You may be wondering why VSHost?  When running under VS, you’ll want to use the same directory as the executable. This is one way you can make sure you’re running under VS.


With the above code, you’re good to go to deploy a local data file under ClickOnce


 


Ok, now back to shipping Whidbey


Steve

Comments (35)

  1. Greg Donovan says:

    Are there any reasons why we shouldn’t use SQL Mobile in place of SQL Express as a desktop database for regular desktop/laptop PC’s?

    Great Blog btw!

    Regards,

  2. SmartClientData says:

    Hi Greg,

    The only reason is that we limit SQL Mobile to the Tablet PC for the Win 32 platform. So, if your desktop is a Tablet PC, you’re good to go 🙂

    Steve

  3. Joe says:

    Hi Steve,

    Why is SQL Mobile limited to the Tablet for the win32 platform? If it can run on Tablet, then can it not also run on 2000/XP? I’d like to be able to support it on any Windows platform, being used as a simple single user data store. Seems you could share most of the source code for all platforms this way. Is there a plan to support 2000/XP?

    Thanks much

    Joe

  4. Allen says:

    Since when is a laptop running 2000/XP not mobile? I agree with Joe in that I should be able to support any windows platform. I have end users that go offline with their laptops and have the need of a local data store. SQL Mobile looks like a good tool for that scenario and I like the looks of it’s RDA functionality.

    Thanks,

    Allen

  5. Ruud says:

    Hi Steven,

    Thnx for the blog, its a great point of information. As a newbee to Pocket PC programming, I have build a simple application based on SQLMobile (sqlce3.0) and Windows Mobile 5.0 (yes, I was one of the lucky bastards to get my hands on a Imate Jasjar during the PDC05).

    When I deploy this application with the 7 sqlce* dll’s which you mentioned, I’m still getting the "cannot find sqlceme30.dll" error with the first time I try to connect. But the file is there. I tried to deploy with registering the dll’s, but still no luck.

    Suggestions would be really great!

    Best regards,

    Ruud

  6. SmartClientData says:

    Hi Ruud,

    When using mobile devices you need to actually install the SQL Mobile cab files. The solution I posted was for the Win 32 platform. Just look in the docs for deploying SQL Mobile on the device.

    Steve

  7. Aryeh Holzer says:

    I just tried it on a desktop running Windows 2000 with Vs 2005 Beta 2, and it worked ok. Is it just unsupported on non-tablet platforms?

  8. SmartClientData says:

    Yup, it currently is only supported on the Tablet PC OS, or desktop if you have a VS or SQL SKU installed.

    Steve

  9. Today Paul Flessner announced that SQL Mobile will released as SQL Server Everywhere Edition. &amp;nbsp;…

  10. Today, Microsoft announced its plan to make SQL Server Everywhere Edition available later this year.&amp;nbsp;…

  11. Anthony says:

    Steve,

    I came across this blog while looking into accessing a SQL Server Mobile DB from a desktop application.

    To be honest, I am upset that MS does not allow access to SQL Server Mobile databases for non tablet Win32 platforms.

    I started using SQLCE with the release of 2.0 but eventually dropped it because it could not be accessed from a desktop win32 application.  I figured that MS would allow access to it with the next release but once again they have let me down. On purpose this time.

    Will the new version support access from non tablet win32 apps?

    Anthony

  12. Mark Dykun says:

    I tried to create a reference to the System.Data.SqlServerCE.dll but recieved an error a ref … could not be added. This is not a valid assembly or COM component. what gives ….

    Any ideas?

  13. SmartClientData says:

    Anthony,

    Yes, the new version, SQL Server Everywhere, (SQL/e) will no longer have the desktop license restriction.  

    Mark,

    It sounds like your making a COM reference rather then a .NET reference.

    Steve

  14. Steve Lasker says:

    Here’s a link to a post I did a while back describing how to leverage SQL Mobile with Visual Studio 2005.&amp;nbsp;…

  15. Steve Lasker says:

    On April 6, 2006 Paul Flessner announced SQL Server Everywhere.&amp;nbsp; http://www.microsoft.com/sql/letter.mspx&amp;nbsp;…

  16. DonS.CF says:

    On April 6th, 2006, Microsoft (via a SQL Server 2005 Update from Paul Flesner) announced a &quot;new&quot; product&amp;nbsp;in…

  17. Brian Tucker says:

    Today’s Top 10!

    10. The April Microsoft Security Patches were released. Not always interesting news,…

  18. SQL Server Everywhere 追加情報(SQL MobileのWindows desktop 展開)

  19. syncmobile says:

    I am a little confused.  In your article you state that SQL Express supports Merge replication.  But in the MS supported platform pages it states that it also supports transactional replication (as a subscriber).  

    http://www.microsoft.com/sql/editions/express/features.mspx

    I assume this was just a mistake in your blog, correct?

  20. Steve.Lasker says:

    SQL Express supports Merge as a subscriber, not a publisher.  Although the actual merge runtime isn’t in the standard Express download, you’ll have to download the bits seperatley.

    Steve

  21. Touraj says:

    Hi Steve

    Can you please tell us when we can have sql mobile synching via active sync or USB Cable to

    the local SQL Express

    This is what we need. Desperately need

    Touraj

  22. Steve.Lasker says:

    Hi Touraj,

    It depends on what type of sync your looking for.  As a business we’ve decided that SQL Express will not support Merge Replication as a Publisher.  We have to have something people would pay for in the paid skus.  We do support RDA between SQL Mobile and SQL Express.

    For Orcas we are working on a new Sync API for something we call Occasionally Connected Systems.  This sync model will work with SQL Express in a constrained way.

    Hope this helps.

    Steve

  23. Thom Vreeland says:

    It seems like the choice to exclude SQL Express from any simplified sync/replication scenario with Mobile is hurting many “at home” users.

    I’m a teacher with a decent amount of programming experience.  I have a simple gradebook/attendence app for my PocketPC that I wrote in eVB to go along with a desktop app.  Both use an Access DB and it’s kept synched via ActiveSync.

    I wanted to update the software to add alot more features (I’ve been using it “as is” for two years.)  It seemed that the best option was to switch over to developing it all in VS03 or VS05 using SQL Mobile/Express — I thought one development environment for everything made more sense and easier reuse of code.  However, as I understand it, I can only access the Mobile DB from an XP desktop if it has VS05 installed.  And, I can only use Replication if I have full fledged SQL Server and IIS installed on my machine (WHO has THAT running on their home desktop — most of us have XP Home that won’t support IIS without some silly workaround).

    Can RDA be used without having IIS installed on the desktop machine?  Darren Shaffer at Microsoft forums says: “So for all of you asking about SQL Mobile to SQL Express data sync, you can use web services to achieve this.”  That seems to imply the answer to my last question is NO.

    So, in summary, there is NO WAY, based upon Microsoft’s business decisions, to sync my Mobile DB and my Desktop DB using .NET development without spending LOTS of money to have XP Pro (for IIS), VS05 (I would use the app at work to develop), and/or SQL Server 2005 at home.  In other words, I’m stuck using antiquated developement tools and Pocket Access.

    As a last ditch effort, here’s my final question:  Can I use SQL Mobile database if I have VC# 2005 Express installed?  I fear the answer will probably be no…

  24. shalin says:

    I am considering different options abour creating mobile application.. what do you guys think about pocketpowerbuilder and SQL anywhere in comparison with sql Mobile.. we are a small company.

  25. Ken says:

    I am new to the PPC world; All the examples for Snycing (From what I understand) today involve having to run the application and then have the application perform the snyc!  Is there any way that I can Snyc a SQL Mobile 2005 DB with SQL 2005 by simply placing the PPC in the cradle and letting active sync do the rest!   (I of course understand that there would be setup and some programming, but in the end I am just looking for the enduser to just perform a normal activesync.)

  26. Ravi says:

    Hi,

    I am unable to commit the data to sql server mobile edition db file .sdf file.

    I have tried using transactions but didnt help….

    any suggestions pl…

    Thanks
    Ravi

  27. srinsoft says:

    Hi

    We are devloping a database management utility for MSDE,sql server enterprise and sql EV

    There are system queries available for performing index rebuild and setting minimum and max memory on msde and sql server by using the following statements below

    "DBCC DBREINDEXALL(S)"

    "SP_configure ‘min server memory’,1024 Reconfigure"

    "SP_configure ‘max server memory’,6144 Reconfigure"

    But is there a way I can perform the same on SQL Ev. I tried using the above statements, but it doesn’t wor out, can anyone help me with statements specific to SQL Mobile or Ev.

  28. Steve.Lasker says:

    SSEv has a focused set of management apis available on the SqlCeEnging object:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceengineclasstopic.asp

    Here’s some quick samples:

    Dim connStr As String = "Data Source = MyDatabase.sdf"

    Dim ssevEngine As New SqlCeEngine()

    ssevEngine.LocalConnectionString = connStr

    ssevEngine.Shrink()

    ssevEngine.Repair(connStr, RepairOption.DeleteCorruptedRows)

    ssevEngine.Repair(connStr, RepairOption.RecoverCorruptedRows)

    ssevEngine.Verify()

    ssevEngine.Compact(connStr)

    ssevEngine.CreateDatabase()

    Steve

  29. Very many thanks for a good work. Nice and useful. Like it!

  30. Vivek Gour says:

    WIthout Deploy of sql express on end user can be access data on database server?