Editions of SQL Server

With SQL Server 2005, there are numerous editions of the SQL Server product. The features available to you in each edition vary widely. The editions you can install on your workstation or server also vary based on the operating system. The editions of SQL Server range from SQL Express on the lowest end to Enterprise Edition on the highest. The prices of these also vary widely, from free to more than $20,000 per processor.

SQL Express

SQL Express is the free version of SQL Server meant for installation to laptops or desktops to support distributed applications such as a remote sales force application. You can use this edition to store sales or inventory data for your disconnected sales force and replicate updated data to them when they become connected again. SQL Express was called Microsoft Desktop Edition (MSDE) in SQL Server 2000. It is extremely lightweight and does not occupy much hard drive space. Vendors are free to distribute SQL Express, and it can be wrapped into your application's installation as just another component.

SQL Express is not meant to scale past a few users. Key features missing from SQL Express are SQL Agent and some of the robust management tools. It does ship with a very lightweight tool for managing the database, but scheduling of backups will have to be done in the Windows scheduler, not SQL Server.

Workgroup and Standard Editions

The Workgroup Edition of SQL Server is the lowest-cost edition of SQL Server editions that you pay for. It scales minimally up to two processors and 3GB of RAM, but it's adequate for small and medium-sized businesses. This edition of SQL Server was initially introduced to compete with lower-end vendors such as MySQL.

The Standard Edition of SQL Server has been beefed up in SQL Server 2005. It now has high-availability options that were exclusive to the Enterprise Edition in SQL Server 2000. For example, you can now cluster SQL Server 2005 Standard Edition instances.

Enterprise, Evaluation, and Developer Editions

Enterprise Edition is the best option for SQL Server if you need to use some of the more advanced business intelligence features or if the uptime of your database is very important. Although the Standard Edition of SQL Server allows you to have high-availability options, Enterprise Edition far outdoes its sister edition with higher-end clustering as well as more advance mirroring and log-shipping options. The counter to this, of course, is the price. This edition of SQL Server will cost you more than $20,000 per processor if you choose that licensing model. (We discuss licensing later in this chapter.)

The Evaluation Edition of SQL Server is a variant of SQL Server Enterprise Edition that expires after a given time. After the allotted evaluation period, SQL Server will no longer start. The Developer Edition of SQL Server allows you to run all the Enterprise Edition features in a development environment. Neither of these editions is licensed for production use.

Operating System

The edition of SQL Server that you can install varies widely based on the operating system on your server or workstation, as summarized in the following table.

Open table as spreadsheet

Operating System	SQL Express	Workgroup	Standard	Developer	Enterprise
Windows 2000 Professional (with SP4+)
Windows 2000 Server (with SP4 +)
Windows 2003 Server (SP1+)
Windows XP Home Edition (with SP2+)
Windows XP Professional Edition (with SP2+)

Maximum Capacity of SQL Server

Memory and the number of processors is a huge contributing factor when you're scaling SQL Server. As you can imagine, the amount of memory you can scale and the number of processors will vary based on the edition of SQL Server you purchase. In some cases, your scalability is restricted only to the operating system's maximum memory or number of processors. This is where 64 bit becomes really useful. (We cover 64-bit scalability much more in Chapter 15.)

Open table as spreadsheet

Capacity	SQL Express	Workgroup	Standard	Enterprise
Memory Supported 32 bit	1GB	3GB	OS Maximum	OS Maximum
Memory Supported 64 bit	N/A	N/A	OS Maximum	OS Maximum
Maximum Database Size	4 GB	No Limit	No Limit	No Limit
Number Processors	1	2	4	Non Limit

Database Features by Edition

The main benefit from one edition of SQL Server to the next are the features enabled. In the following set of grids, you can see how the features line up to each other across the various editions. These grids do not capture all the features of SQL Server but instead focus on areas that we receive common questions about and areas that help distinguish the editions.

Developer Features by Edition

SQL Server 2005 really tries to appeal to the developer. It has many features that will improve the efficiency of the developer's day-to-day job or make his or her code more reliable. In most cases, you can see that the developer slate of features works across all the various editions.

Open table as spreadsheet

Feature	SQL Express	Workgroup	Standard	Enterprise
CLR Integration
XML Data Type
Try…Catch Exception Handling
Service Broker	Client Only

Business Intelligence Features by Edition

The fastest-growth area for SQL Server revolves around business intelligence. Business intelligence allows you to bring the large amounts of data you have in your systems to the mass of users that would normally not touch this data.

Open table as spreadsheet

Feature	SQL Express	Workgroup	Standard	Enterprise
Analysis Services (SSAS)			Up to 16 instances	Yesup to 50 instances
SSAS Clustering			2 nodes
Parallelism for Data Mining Model Processing
SSAS Data Mining
SSAS Perspectives
SSAS Translations
SSAS Proactive Caching
Partitioned Cubes
Reporting Services
Report Caching
Report Scheduling
Report Subscriptions
Data-Drive Subscriptions
Report Builder
Report Manager
Infinite Drill-down
Notification Services
SQL Server Integration Services (SSIS)

DBA Features by Edition

The DBA features have the largest disparity among editions. You can see in the following table that most of the disparity revolves around high availability. If it's of the utmost importance that your system remain available all the time, use Enterprise Edition.

Open table as spreadsheet

Feature	SQL Express	Workgroup	Standard	Enterprise
Indexed Views
Indexing of XML Data Type
Failover Clustering			2-node
Number of Instances Supported	16	16	16	50
Log Shipping
Database Snapshots
Database Mirroring Primary or Secondary			Safety Full Mode Only
Dedicated Administrator Connection
Dynamic AWE
Fast Start of the Instance
Hot Memory Addition
Online Indexing Operations
Online Page and File Restoration
Replication	Subscriber Only
Table partitioning
Updatable Partitioned Views

Licensing

Every DBA has probably received a dreaded licensing question or two, and we hope to answer some of those common questions in this section. There are several ways to license SQL Server, and we can't address this ever-changing landscape completely in this book. Instead, we've tried to answer common questions that are not as likely to change from year to year. If you were to purchase any of the licenses we refer to in this section, they are compatible with previous releases of SQL Server as well as SQL Server 2005.

The Server plus User Client Access License (CAL) licensing model works well if you can trace each connection to a user and if you have a low number of connections to your SQL Server services. This license licenses the server and each named user connecting to SQL Server.

The Server plus Device CAL licensing model works well if you expect that a moderate number of named devices will connect to your instance. In this model, you license the server and then each device (a kiosk or desktop, for example) connecting to the services of SQL Server. If you have multiple users using a single desktop, you need only a single device CAL.

The Processor licensing model works well if you expect to have a high number of connections on your SQL Server or if you can't identify connections, such as an Internet application exposed to others outside your company. This model licenses each physical or virtual processor available to the server. If you were to disable a processor to the operating system and in turn SQL Server, this processor would not have to be licensed. Once the available processors are licensed, you can have unlimited connections to the server.

Modern Processor Issues

In 2005, Microsoft clarified its licensing stance on multiprocessor systems. Hyperthreading allows a single processor to simulate multiple processors. If you had a four-processor server, you would actually see eight processors in Task Manager. With SQL Server, if you were licensing per processor, you would only have to license the physical chip connected to the mainframe and would not be charged for the hyperthreaded processors. This also applies in a multicore server. In a multicore machine, you would have one physical chip connected to the mainframe that had multiple processors sitting on it. It's essentially a processor hub. You are charged only for a single chip versus each processor on the chip.

Scaling and High Availability Licensing Issues

As we mentioned earlier, you are only charged for the physical chips on the machine if you choose the per-processor model. If you have 10 instances of SQL Server on a single server, you're not charged for each instance in a per-processor model. Another common question is with clusters. In an active-passive cluster, you are only charged for the active server, and the passive server is at no charge. In an active-active cluster, you are charged for each active node, so you might be charged for each node.

Oftentimes, DBAs decide to scale out SSRS, SSIS, or SSAS to avoid slowing down the relational engine. If you were to scale one of the SQL Server BI products off of the SQL Server machine, you would need to license the other server even though SQL Server may not be installed on it.