SQL Server Express & Everywhere

 

I assume by now most SQL Server fans know that SQL Server Mobile Edition has been released on Windows and the name has been changed to SQL Server Everywhere Edition.  The first thing I noticed about this is there are now three editions – SQL Server Everywhere Edition, SQL Server Express Edition, and SQL Server Express Edition - with the same acronym SSEE.  This is a great inconvenience for those of us with poor typing skills because we now have to spell out the names.  To save myself time, for the duration of this entry, I will call them Everywhere, Express, and Enterprise.

 

The question I have heard most often on the newsgroups is why another free version of SQL Server?  Isn’t Express good enough by itself?  The short answer as I see it is yes Express is good enough but in a few scenarios, it’s too good or at least too big.  Express is the Enterprise code with a few features turned off and a  few limitations on database size, memory, and processors.  This makes Express ideal for supporting a low-end version of your application so you can have the same code running for applications with a few users or a few thousand users.  It’s also a good solution if you want to develop a database application on a free database for a few users while leaving open the possibility to move to a much bigger database server without changing the application as your needs or your customer base expands.

 

The complaint I hear most often about Express is that the download package is too big.  This is probably very painful to the SQL Server setup team who worked long and hard to get a product that takes almost a gigabyte of disk space installed into a download package of around 50MB.  This near miraculous feat involves a significant rewrite of the setup as well as an extremely aggressive compression algorithm that takes significantly more processor time than building all the rest of SQL Server.  Still, if you don’t have a broadband connection, the download can take all night – and that’s after a few hours downloading the dotNet Framework.  If you distribute your application on CD’s or if you need the full feature set of express then this size is probably something you can live with but if your application just needs a simple way to store a list of your CD collection, Express may be bigger than you can afford to download.

 

Everywhere has to be able to run on your phone so it doesn’t have many more features than the minimum required to provide reliable, transactional storage of relational data.  On the other hand, this may be all your application needs.  Everywhere is designed to be as compatible with the other editions of SQL Server as possible within the constraints of its limited feature set.  It’s usually pretty simple to move an application written to run on Everywhere to on of the full versions of SQL Server but moving an application written for Enterprise to Everywhere will seldom work without significant effort.  Many of the more powerful features of SQL Server 2005 – stored procedures, CLR integration, native XML support, Service Broker, Reporting Services, Full Text Search, etc. are not available on Everywhere so if your application uses these features, it will be difficult to port to Everywhere.

 

My guess is the most common use for Everywhere on XP will be for smart client or rich client applications that need to cache some data for better performance or off-line use.  The Everywhere architecture works well for single-user applications like smart clients.  Multi-user applications will normally work better on Express or one of the more capable SQL Server editions.  If your smart client application needs some of the Express features like CLR integration or Service Broker then Express will be required but for simple, single-user smart client applications, Everywhere will provide the required functionality with a significantly smaller download size and a smaller memory and disk footprint.

 

I don’t foresee a lot of Express or MSDE applications moving to Everywhere but I do think quite a few applications that didn’t use databases at all because Express was to large or too complex will see Everywhere as a way to provide more reliability, data integrity, and functionality.  In other words, Everywhere will replace a lot more flat files or XML files used for data storage than other databases.  Take Everywhere for a test drive.  I think you will find it’s simple and lightweight and easy to use in a lot of places where you might not have considered using a database before.