Hop on the SQL Server Express

Rick Heiges did a talk on SQL Server Express (SSE). SSE is the natural successor to Microsoft SQL Server Desktop Engine 2000 (MSDE2000) and a light-weight version of SQL Server 2005. And it is free! Here's a summary of the talk. It is interleaved with some last minute information provided by two SSE team members from Microsoft.

Features of SSE
SSE supports quite a lot of features from SQL Server 2005 and more:

• XCopy deployment (not possible for SQL2005 databases)
  

  A SSE database can be configured to automatically close the database (effectively detaching the database) when no connections are available any more. This feature is called Auto-close. The connection string is different for SSE including AttachDBFileName to point to the file of your choice (like so AttachDBFileName=C:\databases\MyDB.mdf)

• Unlimited CLR Integration, so you can do from SSE whatever you can do from SQL2005.
• Management GUI: Express Manager, which is going to change to a lighter version of SQL Server Management Studio (SSMS).
• Replication: can be a replication subscriber for merge and transactional replication. It can even use the Synchronize tool from Windows to replicate with SQL2000.
  
  
  
• Service Broker (allowed to send and receive messages)
• Reporting Services. Although the limitations are still being worked out, it is likely that it can only report on SSE databases, has less publishing options and lacks the Report Builder end user tool. This will not ship on november, 7th, but probably in the first half of 2006 :0 .
• Can be used as a database mirroring witness.

Some numbers (and comparing SSE to MSDE2000)
MSDE2000 had a "workload governor" limited to 10 connections, meaning only 10 connections can perform simultaneous work (which made MSDE2000 into a badly scaling database engine). You could have more connections in MSDE2000 though. This limitation has been lifted.

SSE uses SQL Native Access Client (SNAC), instead of the MDAC libraries. It runs on Windows 2000 Server, Windows Server 2003 and Windows XP.

SSE has a 4 GB RAM limited. The CPU and RAM is down to one CPU and 1 GB of RAM (from 2 CPU and GB on MSDE2000). SSE only has one task/thread scheduler so one hyperthreaded and dual core processors it will only effectively run on one thread and one core. It will run on x64 processors, but not on Itanium processors. The database file size is 4 GB, but there is no limit on the log files.

Supported tools
SSE can be used from many tools to manage it. These include Visual Studio, SqlCmd (sqlcmd.exe /e /s.\sqlexpress), SQL Express Manager (and again, this is going to be replaced). Side note: in fact SQL Express Manager can even manage SQL2005 databases.

So, where MSDE2000 was limited in such a way that it was hard to use it in a scalable, enterprise scenario, SSE does not have such limitations.