SQL Server 2005 Express Edition and the SQL Server Desktop Engine (MSDE) are both free and redistributable. Each product provides a medium where database administrators can work between MS Access and the full version of SQL Server. But there are key differences to consider.
One common complaint about SQL Server was the lack of a step between Microsoft's desktop database system, Access, and the full-blown version of SQL Server. To address this, Microsoft created the SQL Server Desktop Engine (MSDE), a scaled-down version of SQL Server that could run on a desktop system and only lacked a few features.
Since then, MSDE has been eclipsed by a slightly different product: SQL Server 2005 Express Edition, a similarly scaled-down version of SQL Server 2005. In this article, I'll talk a bit about the needs it fills, its general relationship to SQL Server and how SQL Server 2005 Express Edition varies from the original iteration of MSDE.
Why SQL Server 2005 Express?
SQL Server 2005 Express Edition, or 2005 Express for short, is a way to use SQL Server in an environment where the full version isn't practical. For instance, if you're building a desktop application that needs a powerful database system, but you don't want to write one from scratch, 2005 Express may fit that need. Another use is if you want to begin creating a SQL Server-based solution of some kind -- such as a database back end for a Web site or intranet -- but you don't want to commit to the full cost of SQL Server just yet.
The 2005 Express package lets you assess the way SQL Server 2005 works without paying for it. It's also not closed-ended: Databases created in 2005 Express are fully compatible with the regular version of SQL Server. The process of upgrading to the full version (should you need to do that) typically involves little more than backing up the database, then restoring it on the SQL...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
Server 2005 installation. Or you can simply install the full SQL Server 2005 instance side-by-side with 2005 Express and use database-to-database connectivity to transfer objects and data.
Changes and limitations to SQL Server 2005 Express
Given that 2005 Express is a free version of a commercial product, it does come with some limitations on its functionality. One major constraint is the size of any individual database that you can work with in 2005 Express. Each database can only be a maximum of 4 GB, but if you're not dealing with a big data set to start with (and don't anticipate it growing to that size in the near future), this probably won't be an issue at all. In MSDE, the limit was 2 GB, so 2005 Express is actually an improvement. Furthermore, there's no limit to the number of databases you can connect to in any given instance of 2005 Express, so it's even less of a restriction than it might seem at first.
One other commonly cited limitation with MSDE was the "workload governor," a way for MSDE to throttle performance to prevent stalling when more than eight concurrent connections were in use. 2005 Express doesn't have the workload governor. Instead it is limited to only running on a single CPU and working with 1 GB of RAM. MSDE allowed two CPUs and 2 GB of RAM, so this is another mixed bag. Microsoft claims that 2005 Express is a more predictable environment than MSDE. The only other constraints are connection limitations that are imposed by the operating system itself.
Other features explicitly not available in 2005 Express are typically not used in a desktop configuration to begin with, such as the analysis or data mining services and the SQL Agent that is used to schedule jobs, but can be eclipsed here by the Task Scheduler in Windows itself. Another item not included with 2005 Express -- DTS Runtime -- can be added separately. DTS Runtime lets you execute (but not build) DTS packages in 2005 Express.
Finally, MSDE is not supported for use on Windows Vista, so if you're planning to deploy an application that needs to run on Vista (whether now or at some point in the future), it makes sense to migrate to 2005 Express when possible.
Upgrading to SQL Server 2005 Express
Generally, existing MSDE users can get up and running with 2005 Express fairly easily. If you're already using MSDE -- either in a desktop application or as a mini-server -- and want to upgrade to 2005 Express, see Microsoft's article Upgrading MSDE 2000 to SQL Server 2005 Express, in which Microsoft steps through most of the major points in performing an upgrade from one to the other, including a step-by-step procedure for performing an in-place upgrade.
Keep in mind that under some circumstances you won't be able to perform an in-place upgrade. For instance, if you want to change languages or collation, you'll need to install 2005 Express side-by-side with MSDE and move databases that way. Both MSDE and 2005 Express can coexist on the same system, so this is not terribly difficult.
ABOUT THE AUTHOR:
Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site WindowsInsider.com, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
