Microsoft SQL Server Compact Edition 2005 - Continued

In my previous article we had a brief look at scenarios where SQL CE can add value as well as problems I have encountered using SQL CE. In the following section I will be addressing how to overcome these problems.

Although the install can be executed silently an install is required to register SQL Server as a data provider

• Currently I am not aware of a means to overcome this issue. I believe that with enough registry and file system analysis the outcome of the install can reproduced without having to run the install though.

Although the database supports multi threading the provider only allows connections from a single process at any one time.

• This is not a very common scenario but a scenario that exists none the less. A small portable database with a small amount of traffic is required to service multiple clients. By exposing the database via a webservice multiple client are able to communicate with the database without running into the single process predicament.

When connected via management studio the database is locked for read access (Single process restriction).

• I unfortunately don't know any way around this SQL management studio. In Visual studio it is possible to specify the "File Mode" attribute as being only "Read" this will allow you to inspect the the data in the database without denying service to users.

The database engine does not support stored procedures

• Unfortunately there is no way around this. The CE engine does not support precompiled queries or an extensive security model, so I suggest you handle access in your data access and business tiers respectively.

I hope this article has given some insight into problems that need consideration when evaluating SQL CE as a database engine for your application.

Microsoft SQL Server Compact Edition 2005

SQL Compact edition 2005 is a complement to the existing range of SQL server products.

Although SQL Server compact edition is aimed squarely at the mobile market it could offer value in the following scenarios:

• Scenarios where the deployment of SQL express edition is too cumbersome.
• A small portable database is required that does not justify the need for SQL server express edition.

I have encountered the following problems when using SQL compact edition:

• Although the install can be executed silently an install is required to register SQL Server as a data provider.
• Although the database supports multi threading the provider only allows connections from a single process at any one time.
• Although SQL Server management studio 2005 supports mobile edition, you have write queries to view the data in tables .
• When connected via management studio the database is locked for read access (Single process restriction).
• The database engine does not support stored procedures.

In my next article we will look at how we can overcome some of these problems.

...........NET

Hi Guys,

I have been soo busy with new and exciting projects I have not had time to update my blog in ages! I will be making time daily and will be spoiling you with awesome tips & tricks I have picked up along the way! I will also be creating an exciting new blog which focuses on secure software development aswell as other security related topics.

Enough promising...the time has come to start sharing...