Prepare for SQL Server Compact Edition

Article
02/03/2012

Scott Swigart
Swigart Consulting

July 2006

Applies to
Visual Studio 2005
SQL Server 2005 Mobile Edition

Summary: Introduces SQL Server Compact Edition, a slimmed-down version of SQL Mobile. SQL Compact removes the restrictions that keep the SQL Mobile product from running on a desktop or laptop, making SQL Compact the ideal, lightweight database for many scenarios. (18 printed pages)

Introduction
Today's Landscape
SQL Mobile: The Lean, Mean, Data Machine
Enter SQL Compact
Getting Started
Easy Deployment
Conclusion
Additional Resources

Introduction

Microsoft recently announced that it will be evolving the SQL Mobile product into a product named SQL Server Compact Edition. SQL Mobile is currently a database that you can use on "mobile" devices, which include Tablet PCs, Pocket PCs, and Smartphones. SQL Mobile differs from SQL Server in that with SQL Mobile, there is no "server" running as a background process. The engine for SQL Mobile consists of a handful of DLLs that you reference from your application. When you want to connect to a database, you just put the path to the database file in your connection string. In fact, working with SQL Mobile feels a lot like working with an Access/Jet database. The limitation for SQL Mobile, today, is that it will only run on "mobile" devices. In other words, you can't deploy an application that uses SQL Mobile to desktops or laptops.

SQL Compact seeks to take the SQL Mobile product and remove the restrictions that keep it from running on a desktop or laptop. This would make SQL Compact the ideal database for many scenarios. In many instances, desktop applications require significant amounts of local data storage. For example, take an application like Microsoft Money. It's a desktop application that sits on top of a database containing years' worth of your financial information. Think of Outlook. Again, it's getting its information from a server, but once the e-mails have been retrieved from the server, they're stored locally. For me personally, I've stored about 5 GB of archived e-mail, split across 4 "database" (Outlook PST) files.

Today's Landscape

Desktop applications that require local storage are, in fact, quite typical. Forgetting, for a moment, anything about SQL Compact, what are the choices for local data storage today? For many .NET developers, the choice is typically one between Microsoft Access and SQL Server 2005 Express Edition (formerly Microsoft Database Engine or MSDE).

SQL Express grew out of Microsoft's desire to scale SQL Server down as small as possible. For the typical desktop application that requires local storage, SQL Server has too much power. SQL Server was designed for multi-user enterprise applications and dedicated hardware. It requires too many resources to run as a desktop database. It's also far too expensive to purchase as a local database that gets installed with each copy of an application. To address the local database need, Microsoft developed MSDE. This is a stripped-down version of SQL Server designed to run on the desktop. However, if you want to deploy an application that uses MSDE, you need to include MSDE with your install. It's not small, and it's not very easy to bundle with an application. It also requires the application to be installed by an administrator. MSDE runs as a separate process, and it listens for network connections, which means it has a high surface area for security attacks. In fact, MSDE was vulnerable to the SQL Slammer virus.

With the release of Visual Studio 2005, MSDE evolved into the product known as SQL Express. SQL Express solves many of the issues present with MSDE. It's much easier to deploy. In fact, if you use ClickOnce to deploy an application, you can deploy SQL Express along with it as a prerequisite. By default, SQL Express does not listen for remote connections, reducing its surface area for attack. However, weighing in at 53 MB, SQL Express is likely to bloat your deployment size by an order of magnitude. It also must be installed by an administrator, and it runs as a service. Compared with Access, it's still a heavyweight solution.

Despite the considerable effort that Microsoft put into developing MSDE and SQL Express, Microsoft Access databases remain a popular choice for many developers. Access is included with Microsoft Office, so most developers already have it installed. Access makes it very easy to create databases, and the database is stored as a single file. It's trivial to include an Access database with an application. You simply include the database as a file with your deployment. There's also no "server" that needs to be carried with your deployment and installed along with your application. This means that an application that uses an Access database can be installed by a typical user rather than an administrator. When the application connects to the database, it does so simply by using the path to the database file in the connection string. The application or user can back up the database by simply making a copy of the database file.

Access, however, is not perfect. Performance degrades significantly as the database size increases. The database is also prone to corruption. Finally, starting with an Access database has tempted many developers to do a dangerous thing. Sometimes a single-user application becomes popular enough that there's a desire for it to be used by multiple simultaneous users. The temptation is to just move the Access database file to a network share, copy the application to multiple machines, and let many users connect simultaneously. Access performance drops off quickly with multiple users, and it's highly unlikely that an application that was designed for a single user will work reliably with concurrent users.

SQL Mobile: The Lean, Mean, Data Machine

With the introduction of the .NET Framework, Microsoft started working to simplify application development for Windows CE devices. A version of the .NET Framework (known as the .NET Compact Framework) was developed to make it easier to write code for devices like the Pocket PC. This provided an easier programming model than Embedded Visual Basic. To handle local data storage, Microsoft developed yet another database engine specifically for these mobile devices. The database engine had to be very small, as these devices typically have no hard drive, and may have as little as 32 MB of memory. The database engine also had to be fast and reliable, as mobile devices have processors optimized for battery life, not speed. Finally, the database had to handle instant power-off scenarios and battery deaths.

The result was SQL CE, a small, fast, database engine. A SQL CE database is simply a file that you connect to by path. This database also has a powerful feature: it can synchronize with SQL Server. Using relatively simple APIs, you can pull select data down to the device from SQL Server, make changes, and push them back up. A local database lets you work when disconnected from the server (a requirement for most "mobile" applications), and you just sync to make your changes available to everyone.

With the release of Visual Studio 2005, SQL CE became SQL Mobile, and it picked up the Table PC as an additional platform to which it can be deployed. However, it's not licensed to run on desktops or non-Tablet PC laptops. This means that it's off-limits for most desktop applications that need local data storage. This limitation is a licensing restriction, not a technological limitation.

Enter SQL Compact

The next version of SQL Mobile is named SQL Compact, and this licensing restriction will be removed. You will be able to freely use SQL Compact on desktops, laptops, and handheld devices. In fact, the same database file can be used on desktops or handheld devices. The entire SQL Mobile engine is currently only 1.4 MB, and consists of a 7 native DLLs and 1 ADO.NET provider for SQL Mobile that you include with your application. It's not known at this time exactly what the final packaging for SQL Compact will look like, but it's a primary goal for Microsoft that it remain trivial to deploy.

With the introduction of SQL Compact, you will have a database that is as easy to code against and deploy as Access, but it will also support synchronization with SQL Server. This means that if you originally develop the application for a single user, you can start out with SQL Compact. If you later decide that you need the application to be multi-user, you will have a couple of options. You could easily upgrade the database to SQL Express or SQL Server. Or, you could continue to use SQL Compact locally, and just periodically instruct your local SQL Compact instance to synchronize with the back-end SQL Server. By keeping the local SQL Compact database, your application automatically has the capability to work offline. When your user is off the network, they can still use critical functionality, and make changes to the local database. When the user connects back up to the network, the local database can just synchronize with the back-end.

Getting Started

The following steps will walk you through using SQL Mobile, which is available today. This will give you an idea of the capabilities and ease of deployment for this database solution, and help you evaluate whether SQL Compact is likely to be useful to you down the road. To get started, you'll need Visual Studio 2005, which includes SQL Mobile under the Visual Studio for Devices option. This is installed by default. The following steps will walk you through using SQL Mobile as the database engine for a Windows application.

Start Visual Studio 2005.
Select the File | New | Project menu command.
In the New Project dialog, select Windows Application.
For the Name field, enter SQLMobileDemo.
Click OK.

Probably the easiest way to use the SQL Mobile engine is just to include the DLLs as part of your solution. This insures that your application can find them at run time, and that they will be deployed with your application.
Select the Project | Add Existing Item menu command.
Navigate to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE.
For the Files of type field, select All Files (*.*).
Select all seven files that start with sqlce.

Adding these files to your solution will increase the size of your deployment (uncompressed) by 1.4 MB. That's still smaller than deploying an empty SQL Express database file, and obviously much smaller than deploying the SQL Express database engine.
Click Add.
In the Solution Explorer, select the seven files that you just added, right-click them, and select properties.
Set the Copy to Output property to Copy if Newer.

This ensures that when you run the application inside the development environment the files will be copied to the bin directory, and your application will be able to find them at run time.

Now it's time to start adding database functionality to the application. SQL Mobile includes a version of the enduring Northwind database. Since it's a database that many are familiar with, it will be used here.
Select the Data | Add New Data Source menu command.

Figure 1. Adding a new data source
In the Choose a Data Source Type dialog, click Next.

Figure 2. Choosing a data connection

In the Choose Your Data Connection dialog, click New Connection.

Figure 3. Adding a connection
In the Add Connection dialog, for Data Source, click the Change button.

Figure 4. Choosing the SQL Mobile data source
In the Change Data Source dialog, select Microsoft SQL Mobile Edition, and click OK.

Figure 5. Browsing for the database file
In the Add Connection dialog, click Browse.
Navigate to C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0, and select Northwind.sdf.
Click Open.
Click OK.
Click Next.

Figure 6. Adding the database file to your solution
When prompted to add the database to the project, click Yes.

Figure 7. Saving the connection string as an application setting
In the Save the Connection String to the Application Configuration File dialog, click Next.

At this point, Visual Studio examines the database file and determines what tables the database contains.
Click the + sign next to Tables to see a list of tables in the database.

Figure 8. Selecting tables to generate a typed DataSet class

**Note **You may see options to select Views, Stored Procedures, and Functions, but SQL Mobile doesn't actually support these. This is a bug that's scheduled to be fixed.
Check the box next to Tables to select all the tables. This will instruct Visual Studio to create a Typed Dataset that contains classes and table adapters that map to the database tables.
Click Finish.

You now have an application that's set up to use a SQL Mobile database. It's time to build a user interface.

Select the Data | Show Datasources menu command.

Figure 9. Generating the user interface
From the Data Sources window, drag the Orders table, and drop it onto the Form Designer.

This generates a user interface from the Typed Dataset. In this case, the user interface consists of a DataGridView control and a BindingNavigator control. The BindingNavigator lets you perform data operations such as navigating through records, inserting, deleting, and saving changes back to the database.

Also notice that the DataGridView has a small Smart Tag icon near the top-right corner. Clicking this icon will give you access to some of the control's most frequently used settings.
Click the DataGridView Smart Tag.

Figure 10. Configuring the user interface with control smart tags
Select Dock in parent container.

The application is now ready to run.
Press F5 to run the application.

Figure 11. Application using a SQL Mobile database

The important thing to keep in mind is just how self-contained this application is. The application requires nothing other than the .NET Framework in order to run. The application carries with it, the database, and the database engine. This data-centric application doesn't even require MDAC to be installed.

Easy Deployment

Bundling any form of SQL Server with your application has traditionally made your deployment much larger, and your install more complex. If your application depends on SQL Express, you will have an extra 53 MB to deploy. The SQL Mobile engine, on the other hand, comes in at a featherweight 1.4 MB uncompressed. It's also fully deployable through ClickOnce. Here's how.

In the Solution Explorer, right-click Form1, and select View Code.
In the Form_Load event, right before the 'TODO comment, insert the following bit of code:
```
Dim dataDirectory As String
If AppDomain.CurrentDomain.DomainManager IsNot Nothing AndAlso _
AppDomain.CurrentDomain.DomainManager.ToString(). _
Contains("VSHost") Then
dataDirectory = Application.StartupPath
Else
dataDirectory = Application.UserAppDataPath
End If
My.Settings.Item("NorthwindConnectionString") = _
My.Settings.NorthwindConnectionString.Replace(".", dataDirectory & "")
```
When you added the Northwind.sdf database to your project, Visual Studio generated a connection string for the database and saved it as an application setting. If you press F5 to run the application, that connection string works just fine because the .sdf file is in the same directory as the application. However, if you deploy the application through ClickOnce, the database will be in a different location relative to the application executable because Visual Studio detected the .sdf file as a "Data File" (as shown in the figure below), and they are handled differently at deployment.

Figure 12. Visual Studio 2005 detects .sdf files as data files

The code checks to see whether or not the application is running from Visual Studio. The path to the database is then configured appropriately based on this information.
In the Solution Explorer, double-click My Project.
Click the Publish tab on the left side.

This shows the options related to ClickOnce deployment. When you ClickOnce-deploy your application, it will be posted on a Web site, allowing the user to download, install, and execute the application. Files that the application depends on, such as your SQL Mobile database and SQL Mobile engine DLLs, will also be downloaded with the application. Even if the user does not have the .NET Framework installed, you can deploy application to them with ClickOnce, and they will be prompted to download and install the .NET Framework as part of the application install.

Figure 13. Options for deploying through ClickOnce
Click the Publish button to compile and deploy the application to your local Web server. After the application has been deployed, the following Web page will be displayed allowing you, or any other user, to download and install the prerequisites and the application.

Figure 14. Application installation Web page
Click the Install button.
In the Application Install dialog, click Install.

The application will install and run.

Figure 15. Application installed through ClickOnce

Conclusion

If you're building .NET applications that use Microsoft Access databases today, then SQL Compact may be in your future. With SQL Compact, there's no server to install. The database is connected to through a file path. It's easy to deploy the database, and even the database engine, with an application. In addition, I expect that SQL Compact will be substantially faster than Access. My limited testing with SQL Mobile shows it to currently be 1.1 to 4.0 times as fast as Access, depending on the scenario. SQL Compact also offers a better upgrade path. Because SQL Compact will support synchronization with SQL Server, some applications will require minimal work to transition from a single-user, local database to a multi-user, central database. SQL Compact also uses data types that are a strict subset of SQL Server, so migrating to SQL Server or SQL Server Express will be significantly less work than migrating from Access to a SQL solution.

If you think that SQL Compact might be right for you, you can start experimenting with SQL Mobile today, and watch for previews of SQL Compact to ship in the summer of 2006.

Additional Resources

Information on SQL Mobile:
Microsoft SQL Server 2005 Mobile Edition
Information about SQL Compact, and the work Microsoft is doing around occasionally connected applications:
Steve Lasker's Web Log
Information about moving data between SQL Server and SQL Mobile with RDA:
Using Remote Data Access (RDA)
Information about moving data between SQL Server and SQL Mobile with SQL Server Replication:
Using Replication

About the author

Scott Swigart spends his time consulting, authoring, and speaking about converging and emerging technologies. With development experience going back over 15 years, and by staying in constant contact with future software development technologies, Scott is able to help organizations get the most out of today's technology while preparing to leverage the technology of tomorrow. Scott is also the author of several .NET Framework books, a certified Microsoft trainer (MCT) and developer (MCSD), and a Microsoft MVP. Feel free to contact Scott at scott@swigartconsulting.com or check out his musings at blog.swigartconsulting.com.