Like earlier versions, the major editions of SQL Server are Enterprise and Standard, with a number of other specialized editions. Let's briefly walk through the editions, noting the significant features and limitations of each.
The edition of choice for mission-critical database systems, the Enterprise edition offers all the SQL Server features, including a number of features not available in any other edition, such as data and backup compression, Resource Governor, database snapshots, Transparent Data Encryption, and online indexing. Table 1.1 summarizes the scalability and high availability features available in each edition of SQL Server.
Enterprise | Standard | Web | Workgroup | Express | |
---|---|---|---|---|---|
Capacity and platform support | |||||
Max RAM | OS Max[] | OS Max | OS Max | OS Max[] | 1GB |
Max CPU[] | OS Max | 4 | 4 | 2 | 1 |
X32 support | Yes | Yes | Yes | Yes | Yes |
X64 support | Yes | Yes | Yes | Yes | Yes |
Itanium support | Yes | No | No | No | No |
Partitioning | Yes | No | No | No | No |
Data compression | Yes | No | No | No | No |
Resource Governor | Yes | No | No | No | No |
Max instances | 50 | 16 | 16 | 16 | 16 |
Log shipping | Yes | Yes | Yes | Yes | No |
DB mirroring | All[] | Safety[] | Witness[] | Witness | Witness |
—Auto Page Recovery | Yes | No | No | No | No |
Clustering | Yes | 2 nodes | No | No | No |
Dynamic AWE | Yes | Yes | No | No | No |
DB snapshots | Yes | No | No | No | No |
Online indexing | Yes | No | No | No | No |
Online restore | Yes | No | No | No | No |
Mirrored backups | Yes | No | No | No | No |
Hot Add RAM/CPU | Yes | No | No | No | No |
Backup compression | Yes | No | No | No | No |
[] | |||||
[] | |||||
[] | |||||
[] | |||||
[] | |||||
[] |
[] OS Max indicates that SQL Server will support the maximum memory supported by the operating system.
[] The 64-bit version of the Workgroup edition is limited to 4GB.
[] SQL Server uses socket licensing; for example, a quad-core CPU is considered a single CPU.
[] Enterprise edition supports both High Safety and High Performance modes.
[] High Performance mode isn't supported in Standard edition. See chapter 11 for more.
[] "Witness" indicates this is the only role allowed with these editions. See chapter 11 for more.
Despite lacking some of the high-end features found in the Enterprise edition, the Standard edition of SQL Server includes support for clustering, AWE memory, 16 instances, and four CPUs, making it a powerful base from which to host high-performance database applications. Table 1.2 summarizes the security and manageability features available in each edition of SQL Server.
Enterprise | Standard | Web | Workgroup | Express | |
---|---|---|---|---|---|
Security and auditing features | |||||
C2 Trace | Yes | Yes | Yes | Yes | Yes |
Auditing | Fine-grained | Basic | Basic | Basic | Basic |
Change Data Capture | Yes | No | No | No | No |
Transparent Data Encryption | Yes | No | No | No | No |
Extensible key management | Yes | No | No | No | No |
Manageability features | |||||
Dedicated admin connection | Yes | Yes | Yes | Yes | Trace flag[] |
Policy-based management | Yes | Yes | Yes | Yes | Yes |
—Supplied best practices | Yes | Yes | No | No | No |
—Multiserver management | Yes | Yes | No | No | No |
Data Collector | Yes | Yes | Yes | Yes | No |
—Supplied reports | Yes | Yes | No | No | No |
Plan guides/freezing | Yes | Yes | No | No | No |
Distributed partitioned views | Yes | No | No | No | No |
Parallel index operations | Yes | No | No | No | No |
Auto-indexed view matching | Yes | No | No | No | No |
Parallel backup checksum | Yes | No | No | No | No |
Database Mail | Yes | Yes | Yes | Yes | No |
[] |
[] Trace flag 7806 is required for this feature in the Express version.
Including the core SQL Server features, the Workgroup edition of SQL Server is ideal for small and medium-sized branch/departmental applications, and can be upgraded to the Standard and Enterprise edition at any time. Table 1.3 summarizes the management tools available in each of the SQL Server editions.
Enterprise | Standard | Web | Workgroup | Express | |
---|---|---|---|---|---|
SMO | Yes | Yes | Yes | Yes | Yes |
Configuration Manager | Yes | Yes | Yes | Yes | Yes |
SQL CMD | Yes | Yes | Yes | Yes | Yes |
Management Studio | Yes | Yes | Basic[] | Yes | Basic[] |
SQL Profiler | Yes | Yes | Yes | Yes | No |
SQL Server Agent | Yes | Yes | Yes | Yes | No |
Database Engine Tuning Advisor | Yes | Yes | Yes | Yes | No |
MOM Pack | Yes | Yes | Yes | Yes | No |
[] |
[] Express Tools and Express Advanced only. Basic Express has no Management Studio tool.
In addition to Enterprise, Standard, and Workgroup, a number of specialized SQL Server editions are available:
Web edition—Designed primarily for hosting environments, the Web edition of SQL Server 2008 supports up to four CPUs, 16 instances, and unlimited RAM.
Express edition—There are three editions of Express—Express with Advanced Services, Express with Tools, and Express—each available as a separate downloadable package. Express includes the core database engine only; the Advanced Services and Tools versions include a basic version of Management Studio. The Advanced Services version also includes support for full-text search and Reporting Services.
Compact edition—As the name suggests, the Compact edition of SQL Server is designed for compact devices such as smart phones and pocket PCs, but can also be installed on desktops. It's primarily used for occasionally connected applications and, like Express, is free.
Developer edition—The Developer edition of SQL Server contains the same features as the Enterprise edition, but it's available for development purposes only—that is, not for production use.
Throughout this book, we'll refer to a number of SQL Server tools. Let's briefly cover these now.
18.223.171.162