The amount of SQL Server 2005 editions can be confusing and sometimes it can be hard to choose the right edition. This is especially true with the smaller editions, like the Express Edition and Compact Edition that on the surface look the same.
The editions currently listed on the SQL Server 2005 homepage are:
- SQL Server 2005 Enterprise Edition (32- & 64-bit)
- SQL Server 2005 Standard Edition (32- & 64-bit)
- SQL Server 2005 Workgroup Edition
- SQL Server 2005 Express Edition
- SQL Server 2005 Compact Edition
- SQL Server 2005 Developer Edition (32- & 64-bit)
Mini-FAQ
Q: What is the difference between Developer Edition and another editions?
A: Developer Edition has all the same features as the Enterprise Edition. Unlike Enterprise Edition, it shouldn’t be used for other purposes than development. In addition to Windows Server, Developer Edition can be installed on a workstation OS like Windows XP and Vista. Enterprise and Standard Edition require Windows Server.
Q: What about the Express Edition? Couldn’t I just use it instead of the Developer Edition?
A: Express Edition is easier to obtain, but it doesn’t have all the same features that the Developer / Standard / Enterprise have. For example, if you need to develop for Reporting Services or Service Broker, then the Express-version isn’t enough.
Express Edition is the edition that replaced the MSDE.
Q: What’s the difference between Express Edition and Compact Edition?
A: SQL Server Express Edition is aimed primarily as a service-oriented database and Compact Edition is better suited for local storage. For example, with ASP.NET application you would choose the Express Edition, but for a WinForm/WPF application you would choose the Compact Edition.
The Compact Edition is actually just a bunch of DLL’s that run in-process with your application. It doesn’t require separate installer and the size is very small, under 2MB!
Express Edition installer is about 57MB and it takes 197MB disk space after installation. Compact Edition also runs on Windows Mobile devices (like SQL Server CE did).
There are trade-off’s in the Compact Edition, of course. Here are the most important ones:
- Not all the T-SQL -features work (if, case etc)
- There is no support for sprocs, views or native XML/XQuery
- The number of connections is limited to 256 (unlimited in Expression), but it is for local storage, anyway.
- No role-based security
Here is an excellent white paper on the Microsoft’s SQL Server -site. The whitepaper not only describes well the differences between the Express and Compact Edition, but it also compares the goals of all editions briefly. Worth reading out, if you want to make sure you know everything about SQL Server editions. It has also good security tips.
Q: What is the SQL Server Everywhere Edition?
A: It was just another name of the Compact Edition before the name was changed.
Q: What is the SQL Server Embedded Edition?
A: It is hard to find much information about it. A post by Microsoft employee says: “This is an embedded data service that can only be used by a handful of Windows Services.”
People seem to learn about it when installing the Windows SharePoint Services in single server / stand-alone mode, which installs the SSEE on their machines. Then they realize the mistake as they wanted to use an existing instance of SQL Server (to save resources). The next step is to try to remove the Embedded Edition, but it isn’t in the Control Panel!
Luckily, there is a way to uninstall the SQL Server Embedded Edition.
Yes, I’ve been there, too. Apparently I’m not the only one, as that is my most popular blog post so far with several daily views. Rumors tell that it will be called SQL Server PITA Edition in the next version 
Remember kids: When installing Window SharePoint Service (WSS) and you want to control your data better, always choose the advanced setup and web farm -mode to save yourself some trouble.
Commit … er… conclusion
I wanted to make things clearer to myself and hopefully some other reader will find some joy out of this post. I’m definitely going to do tests with the Compact Edition some day and I promise I’ll post here some results when I get more experience and some tips to share.
August 22, 2007 at 14:28 |
Nice post!
Also I had some fun with the SQL Server Embedded Edition. If you decide to live with it instead of uninstallation, you can connect to it with Management Studio using this as Server Name:
\\.\pipe\mssql$microsoft##ssee\sql\query
For some reason, connecting with instance name does not work.
Also DDL operations are not allowed in this edition. So if you need to modify the structure of the db, choose another edition.
August 22, 2007 at 14:45 |
Hi and thanks for the first ever comment on this blog
You are right that it is not easy to connect to the SSEE and it must be so for the reason as it isn’t intended for editing or using by 3rd parties in the first place.