Just finished reading what I beleive is currently the only book dedicated to Microsoft SQL Server 2005 Compact Edition (SSCE).
The book is authoured by Prashant Dhingra and Trent Swanson and provides a good overview of SSCE; although I did find quite a bit of repetative content, especially in the introduction and first 2 chapters. There are heaps of exercises and code examples to assist both the novice and experienced SSCE developer/administrator and covers everything you'll need to know to get your first application synchronising with SQL Server 2005.
The book is published by SAMS (ISBN: 9780672329227) and currently sells for about US$37.79 at Amazon.
Chapter Summary
Introduction and Chapters 1 (Getting Started with SQL Server 2005 Compact Edition) and 2 (Platform Support and Installation)
These chapters could have been combined into one as there was a lot of repeated material about the history and versions of SSCE and how each version gets installed and what the differences and features are compared to SQL Server 2005 and SQL Server 2005 Express editions.
Chapter 2 provides a good summary of the names of each Dll used in SSCE 3.x as well as the following diagram showing where each is deployed.
Microsoft SQL Server 2005 Compact Edition Deployment Architecture.
Chapter 3 - Getting to Know the Tools
Provides a brief overview of how to use tools such as SQL Server Management Studio, Query Analyzer and Visual Studio to create, connect to, administer and deploy SSCE databases. No need to elaborate as nothing new here, however read it if you are new to SQL Server or Visual Studio!
Chapter 4 - Managing the SQL Server 2005 Compact Edition Database
This is where we started to get into the nuts and bolts of SSCE; although it was still a bit of an overview chapter. This chapter dealt with Creating, Deleting, Verifying, Repairing, Compacting, Shrinking, Securing, Backing up and Restoring databases using the tools explained in Chapter 2. Code examples are in VB.NET and C# which is good as I code in both which makes it easy to teach others how to use the technology. Key point
- SSCE is a file based system made up of a single file consisting of logical pages.
Chapter 5 - Defining Database Structure
Provides an introduction to creating, updating and dropping SSCE objects (Tables, Columns and Indexes), and using Templates and Metadata. If you already know how to do this stuff you can probably skip this chapter as there's nothing new here except the following two key points:
- Each table can have a maximum size of 512MB containing a maximum of 1024 columns and 249 constraints
- SSCE supports 17 Uicode data types
- .NET mappings
- SQL Server 2005 mappings
Chapter 6 - Manipulating the database
Introduces the syntax of common SQL statements and how to execute them. Key points:
- Add a reference to the System.Data.SqlServerCE namespace and use the SqlCeConnection object to make a connection using ADO.NET
- SSCE allows you to specify a Temporary DB in the connection string used for storing interim results during query execution and while executing SORT BY, ORDER BY and GROUP BY clauses.
- SSCE 3.x provides Multiuser access features by allowing multiple connections. This enables scenarios such as data synchronisation in the background while a client application is accessing data. SSCE 3.x allows 256 connections.
- SSCE uses Row, Table, Page and Database locks. By default it uses row-level for data pages and page-level for index pages.
- SSCE automatically locks rows and corresponding index pages when modifying. This will escalate to a table lock based on the number of locks being used by an application.
- Row identifier is used to lock a single row in a table.
- SSCE uses Shared, Update, Exclusive, Schema and Intent locks
- You can set the Mode property of the connection string to provide Read Write, Read Only, Exclusive and Shared Read for that connection.
- SqlCeConnection myConn = new SqlCeConnection(“DataSource=\\ MyPatients.sdf; temp path=\\ MyPatients2.sdf; Mode=’ Exclusive’;”);
- SSCE supports Serializable, Repeatable Read and Read Committed isolation levels. Does not support Read Uncommitted.
Chapter 7 - Programming SQL Server 2005 Compact Edition with ADO.NET
Covers how to use ADO.NET to connect to the SSCE database as well as how to read, update, and merge the changes back to the SSCE database and provides a fundamental understanding of ADO.NET objects for SSCE. Key points:
- The System.Data.SqlClient classes in the .NET Compact Framework match with the classes in the .NET Framework except for the following limitations:
- Connection pooling is not supported
- Distributed transactions are not supported
- Encrypted connections to SQL Server are not supported
- Connection string properties related to connection pooling, encryption, network library, and so on are not supported
- The use of SQL Server authentication to connet to SQL Server from smart devices is not supported
- SqlClientPermission and SqlClientPermissionAttribute classes are not supported
- The connection to the database does not close if the SqlCeConnection object goes out of scope so you must explicitly close it.
- To specify the Temporary DB for a connection, you must provide the temp file directory and temp file max size properties in the connection string
- SqlCeResultSet
- provides an updateable scrollable cursor and is only available to SSCE i.e no corresponding class for the SQL Server DB
- derives from SqlCeDataReader to provide the performance of a Data Reader and functionality similar to a DataSet.
- uses less memory as it does not perform double buffering of the database as a DataSet does. This is because a SSCE DB is already in memory so there is no need to buffer data in memory.
- SqlCeTransaction - Must explicitly Commit or Roll back a transaction
Chapter 8 - Introducing Native Access
Discusses how to use unmanaged code (non .NET) with SSCE. Didn’t read this chapter as not interested in developing using unmanaged code; besdies, it looked very confusing!
Chapter 9 - Using Operators in SQL Server 2005 Compact Edition
This chapter provides the syntax and examples of each operator that the SSCE database supports. Nothing new here, however read it if you are new to SQL Server. Key point:
- Arithmetic
- Assignment
- Bitwise
- Comparison
- Logical
- String
- Unary
Chapter 10 - Using the Built-In Functions
Provides an overview of the built-in functions supported by SSCE. Key points:
- SSCE supports a subset of the built-in functions SQL Server 2005 supports:
- Mathematical functions - ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN
- Aggregate Functions - AVG, COUNT, MAX, MIN, SUM
- String Functions - NCHAR, CHARINDEX, LEN, LOWER, LTRIM, PATINDEX, REPLACE, REPLICATE, RTRIM, SPACE, STR, STUFF, SUBSTRING, UNICODE, UPPER
- DateTime Functions - DATEADD, DATEDIFF, DATEPART, DATENAME, GETDATE
- System Functions - @@IDENTITY, COALESCE, DATALENGTH
- Others - CASE, CONVERT, NEWID
Chapter 11 - Upgrading from a Previous Version
Discusses how to upgrade a 1.0, 1.1 or 2.0 SSCE database to SSCE 2005, Visual Studio 2003 projects to Visual Studio 2005 and .NET Compact Fromework 1.0 projects to .NET Compact Framework 2.0. SSCE upgarde Key points:
- SSCE 3.x replaces Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE 2.0)
- No need to upgrade a SQL Server 2005 Mobile Edition 3.0 DB as this file format is compatible with 3.1
- There are a few ways to migrate to SSCE 3.1:
- If all data is synchronised using replication or remote data access, you can simply migrate using the replication technologies.
- Set up a Replication between the new SSCE 3.1 DB and the earlier SQL Server 2000 DB
- Upgrade SQL Server 2000 to SQL Server 2005 and set up a Merge Replication (covered in Chapter 12) or RDA (covered in Chapter 13) between the SSCE 3.x database and the SQL Server 2005 DB (covered in Chapter 12).
- Update application replication InternetURL property so that it points to the SSCE Server Agent (sqlcesa30.dll)
- If you need to migrate local data, you can:
- roll your own upgrade library or tool (not covered in this book), or
- leverage the command-line upgrade tool (upgrade.exe) available in SQL Server Compact. This option requires both DB engines to be installed on the device (or device emulator).
- Database Upgrade Tool Prerequisites
- Must perform the upgrade on the device.
- SSCE 2.0, 1.1, or 1.0 must be installed on the device.
- SQL Server Compact OLE DB Provider must be installed and registered.
- SSCE must be installed on the device.
- Options for Running Command Line Applications on Windows Mobile Devices
- Visual Studio 2005: Device Command Shell found on gotdotnet.
- Windows Mobile Developer Power Toys.
- Write your own application to call the upgrade utility.
- Open the command prompt window on supported devices.
- The upgrade program only upgrades schema and data of an older version of SQL CE. It does not upgrade a subscription or RDA tracking information. If the SQL Server CE database has subscriptions or tracked tables, you should upload your changes before doing the upgrade.
- The upgrade utility creates another database and uses approximately 300 KB of memory in the process of creating it. Make sure that you have enough storage for the source and destination database.
Chapter 12 - Synchronizing Data with Merge Replication
Discusses the architecture of Merge Replication and demonstrates how to use Merge Replication to transfer data from SQL Server to SSCE. Key points:
- SQL Server provides various methods of replication; however, Merge Replication is the only method used between SQL Server and SSCE.
- In Merge Replication terminology the server database is called Publisher and the client database is called Subscriber
- A SSCE Subscriber can subscribe data from multiple Publishers.
- Three main ways to transfer data:
- Data downloaded only at regular intervals and used for browsing
- Data created in SSCE and uploaded at regular intervals
- Data downloaded and updated to/from SSCE at regular intervals with updates occurring on both SQL Server ad SSCE and
- Replication makes it possible to synchronise some schema changes to SSCE.
- Although a SQL Server Publisher can publish other objects such as stored procedures, views, functions; these are ignored by SSCE as they are not supported.
- Compression can be used to reduce the amount of data transferred.
- Encryption can be used while transferring data between the device and Web Server by using SSL.
- SSCE provides settings, such as the timeout property, which enable you to obtain better GPRS connectivity.
- The snapshot folder should be setup as a Network share as local paths are not accessible from agents running on the Subscriber.
Chapter 13 - Synchronizing Data with Remote Data Access
Discusses the use of Remote Data Access (RDA) to pull data from SQL Server into SSCE and how to push changes back to SQL Server. Key points:
- RDA uses the IIS Web Server to transfer data from the client db to the server db.
- Changes pushed to the backend db will overwrite any changes in the db, regardless of whether there are newer changes already on the server. This is why Merge replication is better for multiple update scenarios.
- Pull method is used to get a table from a SQL Server and will create a table and indexes in SSCE and then fetch the data.
- Data Type Conversions issues to consider
- The data types that are supported in both databases can be pulled directly.
- The data type is not supported in SSCE but is a compatible data type. At the time of the pull data, it is converted from SQL Server data type to SSCE data type.
- The data types in SQL Server are incompatible with SSCE data types. These data types cannot be used for RDA pull.
- You can switch on Change Tracking by passing a parameter to the Pull method. This will cause SSCE to track changes so they can be uploaded.
- Restrictions on Tracking
- The resultset produced by SELECT statement must be updatable.
- Primary Key must be defined on updatable resocrdset returned by SELECT statement used in Pull method.
- Multi Table Query can’t be tracked. You will receive an error at runtime if SELECT statement, View, or stored procedure refers to multiple tables.
- Push method is used to send changes back to SQL Server. Need to switch on Track Changes to use this method.
- By default the Push method sends changes one-by-one. You can use the batch feature to send all changes as a transaction. The Push operation will fail if a single row is not applied successfully.
- SubmitSQL method can be used to execute a SQL Statement on the SQL Server. Can only be used with statements that do not return rows.
- An error table in SSCE is used to log any errors that occur during RDA
- Restriction on changing the Pulled table in SSCE:
- You can’t remove the Primary key from the table that you fetched using RDA Pull.
- You can’t add columns on a pulled table.
- You can’t rename a column on a pulled table.
- You can’t delete a column from a pulled table.
- You can’t rename the table that you fetched using RDA Pull.
- Restrictions in RDA
- The Pull method gives an error if the recordset returned by the SELECT statement contains a computer column. You should exclude the computed column in the SELECT statement that pull data from the backend SQL Server.
- SSCE can have 1024 columns in a table. Tracked RDA tables require 7 system columns. You can have up to 1017 columns in a tracked RDA table.
- SQL Server 2005 supports Triggers on a table. SSCE does not support Triggers in a table. If you pull a table from SQL Server 2005 that has triggers, triggers will not be pulled into SSCE. However Triggers will be executed when you push changes back to SQL Server 2005.
- If the pulled table contains an identity column, the management of identity columns should be done by your application. By default SSCE assigns the value of the identity seed and identity increment value to 1. To manage the Identity column you should use ALTER TABLE to change the identity seed and identiy increment value.
- If you have set up Replication in SQL Server 2005 table, a rowguid column is added on Published tables. This column is not required in a local table on SSCE.
- SQL Server 2005 is a case sensitive database. SSCE is a case insensitive database.
- SSCE supports only a subset of data types supported by SQL Server. You should consider the guidelines given in the Data Conversion section.
Chapter 14 - Securing the SQL Server 2005 Compact Edition Database
Explains the SSCE features that you can use to address security issues and discusses the factors you should consider while planning to secure data for mobile workers. Key points:
- DB password can be up to 40 characters long and contain alphabet, digit and nonalphanumeric characters.
- Encryption needs to be used to stop a user from opening the DB file in clear text and gaining access to the content. A password alone will not prevent this.
- A password is required to encrypt and decrypt a DB. If this password is lost you will never be able to decrypt the DB.
- Should use SSL to encrypt the data between the client and the IIS Web Server
- Recommended Practices
- Basic authentication with SSL on a Web Server and SQL Server authentication on an SQL Server are the most suitable for an Internet-based application.
- When you synchronize using Replication or RDA, the Server Agent allocates a worker thread pool consisting of worker threads. The thread pool is created per virtual directory. Having a separate virtual directory means a separate thread pool per application.
- You should create an NTFS content folder for each virtual directory. The content folder contains a copy of the Server Agent dll as well as input and output files for data transfer.
- Remove the default password and any unnecessary accounts.
- Use an encryption method such as SSL to ensure secure data transfer over a network.
- Remove access to an Internet Guest Account from the SQL Server if you are not using Anonymous access at the Web Server.
- The Replication Merge Agent and Distribution Agent should be in a PAL (Publication Access List).
- The Merge and Distribution Agent should have read access on a snapshot share.
- Snapshot Agent should have write access on a snapshot share.
Chapter 15 - SQL Server 2005 Compact Edition Performance Tuning
Discusses tips you can use to increase the performance of the SSCE database and obtain faster synchronization. Key points:
- SSCE uses a cost-based Query Optimizer i.e. it tries to estimate the cost of various query plans to determine the best query to execute.
- SSCE Query Optimizer does not support query hints
- SQL Server Compact edition does not support the SHOWPLAN_ALL, SHOWPLAN_TEXT, SHOWPLAN_ XML options.
- Although it is possible to create multiple indexes on a table, the SQL Server Compact Edition Query Processor will consider all indexes but select only one index in the execution plan.
- SSCE supports two types of hints
- Use a forward-only cursor on Mobile devices where possible - SqlCeDataReader
- Minimise the use of the DataSet. Use SqlCeResultSet instead
- Synchronization Considerations
- Avoid using text columns in a publication
- Use the Download Only Articles feature if you are just synchronizing Lookup data
- Avoid using complex joins
