Sunday, August 06, 2006
Microsoft is adding another member to the SQL Server family: SQL Server 2005 Everywhere Edition, short "SQL Everywhere". This new version of SQL Server aims to be a scale-down SQL data engine that can be used and installed easily, with little impact on security, and few demands performance-wise.
So what exactly is SQL Everywhere? For one, it is not SQL Server. It may share the name with its big cousin, but it actually is a completely different data engine. It is the same data engine in fact, that is used be SQL Server Mobile Edition on mobile devices (Pocket PCs, SmartPhones,...). In fact, SQL Everywhere shares all the object names and APIs with SQL Mobile. For instance, the command object used by SQL Everywhere is called SqlCeCommand ("SQL Server CE" is the original/internal name of SQL Mobile). Really, SQL Everywhere is SQL Mobile, it just runs on Windows PCs now. (Note: SQL Mobile is not the Jet engine as is often incorrectly stated...).
SQL Everywhere is easy to use. It comes as a set of assemblies that can easily be distributed with your main application. Think of SQL Everywhere as an extension of the .NET Framework. It is deployed like any other .NET assembly. It does not require a sophisticated install as SQL Express does. It also doesn't require admin rights to be installed, which makes SQL Everywhere the ideal database for ClickOnce Smart Client applications. Also, SQL Everywhere does not run as a service as other SQL Server versions do. It only gets loaded into memory when it is really needed, because at the end of the day, the entire SQL Everywhere engine is just a set of .NET assemblies.
SQL Everywhere databases are stored in SFD files. A single database is stored in a single SFD file that contains all tables. This approach makes it easy to deploy the database itself, because it is just one more file that comes with the install. It is also possible to put the database files into partially trusted locations (isolated storage). Today, it is possible to build partially trusted ClickOnce applications, but when the app needs a database, one usually had to use SQL Express and the partial trust scenario went out the window(s). SQL Everywhere is a very important step forward in supporting partial trust scenarios for real.
SQL Everywhere is not a functional equivalent of SQL Server though. The biggest limitation of SQL Everywhere is the lack of stored procedure support.
For VFP developers, this whole setup isn't anything new of course. After all, this is very similar to using DBF files in VFP. Except that SQL Everywhere uses a single file per database, while VFP has multiple files per table. Also, SQL Everywhere is accessed through a standard object-based API with command objects and the like, while VFP uses procedural database commands to access and manipulate data. For multi-tiered applications, SQL Everywhere has the upper hand, since procedural data access does not work well in multi-tiered scenarios. On the other hand, SQL Everywhere still doesn't rival the simplicity of VFP's "USE xxx" command.
To provide a quick overview, we now have the following versions of SQL Server available:
- SQL Server 2005 (the main thing, which comes in several sub-versions, such as "Enterprise") - This is the big moma. The workhorse of databases designed to handle "serious" database needs of any size in client/server scenarios.
- SQL Server 2005 Express Edition (formerly known as "MSDE") - This is the one you want for serious database needs on the local workstation. It is easier to install than regular SQL Server and just much better suited for local database needs. Be aware that you need admin rights for installation.
- SQL Server 2005 Everywhere Edition - This is the best choice if you need a database for an individual smart client application that needs to be installed with as little fuzz as possible, even in partial trust environments.
- SQL Server 2005 Mobile Edition (formerly "SQL CE") - This is the database technology for mobile devices. It is also the same database engine as SQL Everywhere.
One use of the SQL Everywhere engine I really like is Unit Testing. Many unit tests have database dependencies. It has been a problem for us in the past to make sure that those dependencies are always available. For instance, our Milos Solution Platform product provides a lot of business logic which requires some database under the hood, be it SQL, Oracle, or XML files. We have unit tests that test this business logic independent of the database (testing the database is a different issue altogether), but we need a database for this stuff to work. So someone sets up a database for this purpose and everything is fine, but then 10 months later, someone wants to run the unit tests at a completely different location (such as our office in Austria) and - bang - the dependency is missing! As a result, no unit tests are executed. With SQL Everywhere, we can now provide a test database and source control it with the unit tests. It is great!
Talking about Milos: The latest version of this product now supports SQL Everywhere natively. It is just a configuration option. And since we put support for SQL Everywhere into the product, we also had the opportunity to take it for a serious test run, and we like what we are seeing! It really works very well.
For more information about SQL Everywhere, check out this article, or visit Steve Lasker's Blog (the MS Product Manager on SQL Everywhere).
Posted @ 3:11 PM by Egger, Markus (firstname.lastname@example.org) -