SQL Server Compact 3.5 is a compact database that can be deployed on smart devices and computers. SQL Server Compact 3.5 can be deployed on smart devices either by manually copying and installing the .cab files or through Microsoft Visual Studio 2008 Service Pack 1 (SP1). SQL Server Compact 3.5 can be deployed on computers either by using the desktop installer (SSCERuntime-ENU.msi) or through Microsoft Visual Studio 2010.
Development support for SQL Server Compact 3.5 is provided by Visual Studio 2008 SP1 for smart device applications and Visual Studio 2010 for desktop applications. Database administration support is provided by Microsoft SQL Server 2008 R2. You can access SQL Server Compact 3.5 databases stored on a smart device or on the desktop computer by using SQL Server Management Studio in SQL Server or SQL Server Management Studio Express (SSMSE). The SSMSE user interface is a subset of SQL Server Management Studio. SSMSE is a free download from the Microsoft Web site.
Visual Studio 2010 is the preferred environment for developing desktop applications. Visual Studio 2008 SP1 is preferred for developing smart device applications. Visual Studio 2010 does not have support for developing smart device applications. For more information, see Installing a Development Environment. |
Some of the distinct features of SQL Server Compact 3.5 are as follows:
Synchronization
Several enhancements are available to improve how you replicate and synchronize data with SQL Server Compact 3.5. The enhancements include the following:
Support for multiple subscriptions
In earlier versions of SQL Server Compact 3.5 (version 2.0 and earlier), only one subscription could be created for each database. In an environment where the published data derives from multiple publications, a developer had to create a separate subscription database for each publication. Then, the developer had to specify which database the application should open. By using SQL Server Compact 3.5, multiple subscriptions can be contained in a single database, reducing the programming required. For more information about multiple subscriptions, see Supporting Multiple Subscriptions.
Multiuser support and synchronization
To support multiple applications that access the same database at the same time, SQL Server Compact 3.5 provides multiuser support. Multiuser support enables multiple users of a database to synchronize data without having to disconnect the database before they use merge replication or remote data access (RDA). For more information about multiuser synchronization, see Multiuser Access and RDA and Multiuser Access and Synchronization.
Synchronization progress status
Developers can use the managed APIs to provide synchronization status feedback. This can be used to inform users of synchronization progress. For more information about the status bar, see Asynchronous Data Synchronization.
Column-level tracking
In earlier versions of SQL Server Compact 3.5, a whole row was synchronized with Microsoft SQL Server, even if only one column in the row had been modified. This frequently resulted in longer synchronization times if the unmodified columns in the row contained lots of data. In SQL Server Compact 3.5 Service Pack 1 and later versions, only the modified columns are synchronized. For more information about column-level tracking, see Using Row-Level and Column-Level Tracking.
Storage Engine
The new and improved features of the storage engine in SQL Server Compact 3.5 enhance the reliability and performance of mobile applications. These features include the following:
Revised storage engine
The storage engine architecture has been rewritten to optimize for a mobile architecture in which each application shares a common memory pool. Better data reliability is now ensured through true atomicity, consistency, isolation, and durability (ACID) support, and when devices experience battery power issues and connectivity disruptions. For more information, see Overview of Database Engine (SQL Server Compact).
Multiuser support
To support multiple applications that access one database at the same time, SQL Server Compact 3.5 provides row-level locking of data pages, page-level locking, and isolation levels to help guarantee data integrity during concurrency. For more information about locking, see Locking (SQL Server Compact) and Lock Escalation.
Auto reuse of empty pages
SQL Server Compact 3.5 supports the autoshrink feature. This automatically reclaims unused data pages and saves device storage space. For more information about autoshrink, see Maintaining Databases (SQL Server Compact).
Query Processor
Improvements to the SQL Server Compact 3.5 query processor include the following:
Cost-based optimization
The query processor takes advantage of statistics support in the storage engine to create significantly better cost-based query plans. For more information about statistics, see Overview of Database Engine (SQL Server Compact).
Execution plan and query hints
With SQL Server Compact 3.5, developers can view the query plan and then refine the queries as appropriate. For example, you can refine the query plan based on the database design, access time of the media, or CPU speed for your specific supported devices.
A new SqlCeResultSet object
The new SqlCeResultSet object reveals an updateable, scrollable cursor so that developers can directly access a SQL Server Compact 3.5 database without double-buffering data on the device with a DataSet object. This class provides performance improvements when compared with using the DataSet object and it reduces the code that is required to accomplish the same task. It also supports WinForms data binding interfaces and can be bound to UI controls such as DataGrid, TextBox, and ListBox. For more information about cursors, see Cursors (SQL Server Compact) and the System.Data.SqlServerCe namespace.
Integration with SQL Server
You can manage a SQL Server Compact 3.5 database on a desktop computer or a device by using SQL Server Management Studio, a SQL Server management tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer. This integrated tool provides the same experience for users whether they connect to SQL Server or SQL Server Compact 3.5. SQL Server Compact 3.5 databases can be also managed by using SQL Server Management Studio Express.
Benefits of using Management Studio include the following:
Graphical query execution plans
Graphical query execution plans enable developers to easily see query execution plans and so better understand potential query performance issues. A separate feature, query hints, is available to control some aspects of the execution plan.
New Subscription Wizard
The New Subscription Wizard requires minimal effort by users when they create, and subsequently synchronize, a subscription database. For more information, see New Subscription Wizard.
Improved Configure Web Synchronization Wizard
SQL Server Compact 3.5 supports improved versions of the Configure Web Synchronization Wizard. Improvements in the Configure Web Synchronization Wizard include enhanced usability and support for HTTPS virtual directories. For more information, see Configure Web Synchronization Wizard Help
Integration with SSIS
SSIS lets developers transfer data between a SQL Server Compact 3.5 database and a variety of data sources. They include Microsoft Access, Oracle, and IBM databases. SSIS includes a destination adapter and connection manager for SQL Server Compact 3.5.
SQL Editor
The SQL Editor component of Management Studio is the primary tool for interactively designing and testing Transact-SQL statements, queries, and scripts. SQL Editor has several features that simplify writing and editing queries and code. You can also edit scripts that are created from files or from Object Explorer.
Database Deployment
From Management Studio and Management Studio Express, you can create SQL Server Compact 3.5 databases on the local computer. You can configure these databases, populate them with data, and then deploy the databases to multiple devices. This saves significant development and deployment time.
Some of the enhancements made in SQL Server for merge replication provide the following benefits for SQL Server Compact 3.5 applications:
Managed API access to synchronize business logic
SQL Server reveals a managed API that lets developers modify business logic data before that data is stored in the SQL Server database. For more information, see "Executing Business Logic During Merge Synchronization" in SQL Server 2008 R2 Books Online.
Support for download-only articles (tables)
This feature reduces the amount of metadata transferred during initial synchronization and reduces the processing time on SQL Server when developers perform subsequent synchronizations. For more information, see Introducing Merge Replication and Creating the Publication.
Support for partitioned (filtered) articles
Many mobile applications filter data so that users do not access the same data across devices. By using support for partitioned articles, developers can significantly reduce synchronization time and increase scalability. This feature is especially useful for deployments of many smart devices. For more information about partitioned articles, see Creating the Publication.
Integration with Visual Studio
There are enhancements to the integration of SQL Server Compact 3.5 with Visual Studio.
Database Management
Developers can manage a SQL Server Compact 3.5 database on a desktop computer by using Visual Studio 2010 or on supported devices by using Visual Studio 2008 SP1. Developers can drag a SQL Server Compact 3.5 table to a control in the application to enable that control to automatically bind to the table.
Automatic Installation of SQL Server Compact 3.5
When you use Visual Studio to build a Microsoft .NET-connected application that uses SQL Server Compact 3.5, the SQL Server Compact 3.5 engine is automatically installed on the device the first time that you deploy the application. If you are building a native application by using Visual Studio, you will have to manually install SQL Server Compact 3.5. For more information, see Deploying Smart Device Applications.
Data Directory Support
DataDirectory is a substitution string that indicates the path of the database. DataDirectory makes it easy to share a project and to deploy an application by eliminating the requirement to hard-code the full path. For example, instead of having the following connection string:
"Data Source= c:\program files\MyApp\Mydb.sdf"
By using |DataDirectory| (enclosed in pipe symbols as shown), you can have the following connection string:
"Data Source = |DataDirectory|\Mydb.sdf"
You set the DataDirectory property on the AppDomain by calling AppDomain.SetData.
ClickOnce Deployment for Managed Applications
ClickOnce is a software installation technology that is supported by SQL Server Compact 3.5 to deploy managed applications on computers. ClickOnce simplifies deploying a Windows-based application to a Web server or network file share. For administrators, deploying or updating an application consists of updating files on a server. You do not have to update each client individually. Microsoft Visual Studio 2010 provides full support for publishing and updating applications that are deployed with ClickOnce. ClickOnce deployment is available for projects that are created by using Visual Basic, Visual C#, and Visual J#, but not for Visual C++. For information about 64-bit ClickOnce deployments, see Managing 64-bit Database Applications.
Reference
Configure Web Synchronization Wizard Help
Concepts
Transactions (SQL Server Compact)