SQL Server Reference Guide

Here at InformIT we cover multiple versions of SQL Server. A version is a release of the software containing various technologies and architectures based on a release date. But within each version of the software there are editions, which reference the capabilities and features within those versions.

Although you might not think that understanding the differences in the editions for each version is important, it really is. There are limitations and advantages to each, such as the licensing costs and the operating systems they support. Depending on what your shop does, you may see many of these editions, so it's important to know what each does and how you can use them.

At the core of all these editions are the file structures. Microsoft made sure that the files are the same (with some limitations) between editions. What this means is that a database created on the smallest edition of SQL Server running on a supported operating system can be accessed, restored, transferred, and programmed just like a more capable edition on a higher-level operating system.

It's typically easier to go “up” in editions than “down”, meaning it’s easier for the MSDE or Express Editions to work with Enterprise Edition than the other way around. Some features from the higher versions just aren’t available in the lower ones and so on. Even so, the file formats and data types are usually compatible even if you do need to use them in a lower version, and you can always transfer data between them using Replication, Data Transformation Services or SQL Server Integration Services. I’ll show you how to do that in other tutorials.

Let's look at what each of the SQL editions offers. I'll cover multiple versions of SQL Server here, so I'll arrange them from the smallest editions to the larger editions. What I mean by that is the “smaller” editions have fewer capabilities, features and resources, but also normally have lower costs, and that the “larger” editions have higher features and capabilities but higher costs.

I won't dwell a lot on the features in each edition in this tutorial since I have an entire section for that, but instead help you focus on what matters to you: What can each edition do for me, and where do I use it? Make sure you check out my other tutorials on these features and then the online resources I have at the end of this section.

The Smaller Editions

The smaller versions of SQL Server are intended for developers to be able to distribute applications that have only a few users, usually less than 25 or so. You can also set these up for the developers on their local systems or in some cases even their handheld devices. By the way — I’ll mention those handheld editions at the end of this overview, since they have their own uses and limitations.

The smaller editions include the relational database engine (see the previous articles for more on what that means) and in SQL Server 2005 and 2008, they also include Reporting Services. That means you can write and run Transact-SQL code, store and maintain data, and more. You can also replicate, or exchange data, between these editions and the larger ones.

Any database you create on a smaller edition will run on a larger edition. You can back up the database from the smaller edition and then restore it on the larger edition, or use the attach and detach procedure for databases between them.

SQL Server 2000

SQL Server 2000 is an older version of Microsoft's flagship RDBMS, but it is still in use all over the world. If you're starting out as a DBA, you'll still run into this version from time to time. The smaller editions in this version include the Microsoft Data Engine (MSDE) and the Personal Edition.

MSDE

The Microsoft Data Engine (MSDE) is the smallest of all the desktop computer distributions Microsoft delivers in this version. It ships with the Visual Studio Development tools, you can get it stand-alone, and it even comes on the Office 2003 Professional CD. MSDE replaces JET as the distribution engine of choice for distributed applications.

It has many of the same basic features as a full installation SQL Server Edition, but it has a smaller footprint. It's a great way to develop and create applications that can be easily migrated to other SQL Server editions. You can back up a database on MSDE and restore it to an Enterprise Edition of SQL Server with no changes.

Many web servers use this edition of SQL Server because the distribution is royalty-free. In other words, if you develop an application with the products listed below, you can provide the MSDE install along with your product, and not have to pay Microsoft a fee. (That assumes, of course, that you purchased the development tools to begin with, and this is always subject to change as Microsoft evolves its licensing.) These are the tools:

• SQL Server 2000 (Developer, Standard, and Enterprise Editions)
• Visual Studio .NET (Architect, Developer, and Professional Editions)
• ASP.NET Web Matrix Tool
• Office XP Developer Edition
• MSDN Universal and Enterprise subscriptions

You can use MSDE with Microsoft Access, but you can't give it away for free with your Microsoft Access applications.

There are two major limitations to MSDE. A query-governor is built in, to limit the performance of applications of greater than five threads or around five users, depending on how your application is written. It also has no graphical tools or online books installed with it, which means that you'll have to write code to manage it. There are sites on the web that provide free or inexpensive tools to manage MSDE, however.

You're limited to databases less than 2GB, but that really isn't so bad considering the cost. SQL Mail also can't be used with MSDE.

You can use MSDE in SQL Server replication, but if you connect to a full edition of SQL Server you'll need to license (with a Client Access License, or CAL) the MSDE machine to be a client to that server. You'll also usually want to set up MSDE to be a subscriber or publisher of data, but not a distributor — it's just not powerful enough to do serious levels of replication.

If you're looking for a quick, easy way to jump into SQL Server or set up a web database, MSDE is a great way to go.

Personal Edition

The Personal Edition of SQL Server has the same features as the Standard Edition. Its primary constraints are the licensing and operating-system limitations.

The Personal Edition won't use more than two processors, and has a query governor that will impact performance if you push it hard. It also won't do transactional replication, but it can be a subscriber to that kind of replication.

This version can be installed on Windows 98 and higher. You get full-text search and SQL Server 2000 Analysis Services with Personal Edition, but it can't be installed on Windows 98 or Windows ME. The other issue with these operating systems is that they don't have services, so certain functions such as the SQL Agent can't run when you're not logged on.

SQL Server 2005

SQL Server 2005 is significantly different than 2000, so the smaller editions change as well. A new add-on to the Express Edition, for instance, gives you graphical management tools.

Express Edition

SQL Server 2005 replaces the MSDE edition with the SQL Server 2005 Express edition. It's more capable than MSDE, because it supports using the Common Language Runtime (CLR) layer and also supports Reporting services, making it a valuable part of a Business Intelligence infrastructure. It can also serve in SQL Server 2005 clustering as a witness.

SQL Server 2005 Express, like all SQL Server 2005 editions, includes the Service Broker, which allows you to create a Service Oriented Architecture (SOA) using the database.

The licensing for this edition is also a bit different than MSDE. In this case, the software is essentially free — you don't have to pay anything to use it.

You'll need 200MB of drive space to run Express, and at least 128MB of RAM. You'll want more than that for any serious application, of course. It runs on the following operating systems:

• Windows XP Home Edition
• Windows 2000 Professional
• Windows Vista
• All Windows Server Editions to 2008

Your limitations with this edition are 1GB of RAM and 1 CPU. It comes in a 32-bit code base. You're also able to create a database up to 4GB in size.

SQL Server 2008

SQL Server 2008 continues the Express Edition from version 2005, as well as the Compact Edition for handheld devices. I’ll talk about that more at the end of this article.

Medium Editions

Medium sized editions are the types that you'll run into most often in a department or a smaller to medium sized offices. The primary difference between this level of editions and the smaller ones are the hardware that is supported and the feature set. In these versions you're given more room to grow.

Once again, any database you create on this edition will operate in any other edition, with the size exceptions listed above.

SQL Server 2000

Workgroup Edition

The Workgroup edition is aimed squarely at the smaller office. It provides most of what you'll need to run the office with licensing advantages. It supports most everything that the Standard Edition (below) does, but you're limited to two processors and 2GB of RAM. There is no limit on the size of the database.

Standard Edition

About 80% of the systems I've seen run the Standard Edition of SQL Server. This edition supports from one to four processors and 2GB of RAM, which is in the right ballpark for a lot of departmental implementations. The Standard Edition provides all the features that SQL Server is famous for, such as:

• Data Transformation Services (DTS), which provides import, export, and transformations of data between various sources
• Analysis Services (OLAP server)
• The SQL Server Agent (for automation)
• Replication between other Microsoft SQL Servers and other vendors
• English Query
• XML support
• Full-text searches

In short, this edition provides most of what you need for departmental or small-business implementations.

There are some limitations; you don't as much clustering ability or some of the advanced features for OLAP. This is the right version for a larger shop that needs high availability and advanced SQL features such as indexed views.

This edition installs only on Windows 2000 Server or higher.

SQL Server 2005

SQL Server 2005 includes all of the features from the previous versions as listed above, and includes the Common Language Runtime (CLR), Analysis Services, The Database Tuning Advisor, Reporting Services and more.

Workgroup Edition

Just as in the case of SQL Server 2000, the Workgroup Edition has the same feature set as the Standard Edition, just with more limitations. You can use up to 2 processors and 3GB of RAM with the SQL Server 2005 Workgroup edition.

Standard Edition

In SQL Server 2005, this edition really moves into the higher-end spectrum. It has more capabilities than its predecessor, and can even be clustered together, up to two nodes. There is no memory limits for this Edition, and it supports up to 4 processors.

In addition, the Standard Edition has a 64-bit version, which uses a flat memory space and a larger addressable CPU space. It has the following primary features:

• Relational Database Management System with development studio integration
• Clustering
• Replication
• Encryption
• Full XML support
• HTML support
• Service Broker
• Analysis Services
• Reporting Services
• Data Mining
• Common Language Runtime integration (you can write stored procedures in any .NET language)
• Integration Services (replaces the Data Transformation Services from 2000)

SQL Server 2005 is also highly secure, making the system safer "out of the box."

SQL Server 2008

SQL Server 2008 continues the same editions as SQL Server 2005, with one new addition: The Web Edition. This new edition has everything that the Standard Editions does, with the exception of things like Clustering and Analysis Services. You don’t normally need those kinds of features on a web edition, and with this edition you don’t have to pay for them, either.

Larger Editions

The larger editions of SQL Server have all of the features from the other versions, but are more capable from the memory, CPU and storage perspectives. In both SQL Server 2000 and 2005, there are 64-bit versions in addition to the 32-bit versions. They also support more processors per system. These are normally used in larger shops or where you've hit a wall in one of two areas: memory or CPU.

In the 32-bit arena, the combination of the processor and the operating system limit SQL Server to the first 2GB of RAM for things that make queries go faster. With a special set of switches and some changes to the SQL Server configuration, you can use the space above 4GB (up to 8GB) for read-ahead data pages. In the 64-bit versions, these limitations are gone. SQL Server can use all of the memory that the operating system can address, with no special switches or configurations. It can use all of the memory for either faster-query operations (cache, query plans, etc.) and/or data pages.

SQL Server 2000

Enterprise Edition

The Enterprise Edition is the big-iron edition of SQL Server. It has everything that all the other editions have — and more. I'll cover the features in a moment. In addition to those features, Enterprise Edition boasts better performance than the other editions, and can use up to 32 processors and 64GB of RAM. You'll get better parallelism of queries, meaning that the server uses the symmetric multiprocessors to chunk up the queries to run at the same time.

In addition to the performance gains, the other advantage with this edition is its scalability. By pairing it with Windows 2000 Advanced Server, you can cluster SQL Server Enterprise to failover to another system for production-level uptime. Data on SQL Server Enterprise can be carved up to work in parallel, called scaling out. Another way to provide higher uptime using this version is log shipping, meaning that changes made on one server can automatically be transferred to another server (perhaps located elsewhere) and applied to keep them in periodic sync.

As far as the enhanced features go, you'll get advanced analysis with Enterprise Edition. You can define OLAP partitions and create larger cubes. To store these increased amounts of data, you often need a type of storage called ROLAP, and this edition handles that better than its smaller siblings. If you need almost real-time OLAP, this is the edition for you.

Another feature that this edition provides that's often overlooked is as a back-end to reporting data. When you structure reporting data, it often has a negative impact on performance. With SQL Server Enterprise Edition, you get indexed views, which can really help a skilled developer speed up those reports.

There's yet another benefit to this edition: The samples area is greatly enhanced in this release. For the developer, this sample code can be very useful.

There are a couple of minor issues with this edition. For one, the 64-bit version of this edition doesn't support running Data Transformation Services, although you can run that on another 32-bit system and reference a 64-bit edition. You're also limited to the Itanium 64-bit processor.

As you might guess, you need a bit more horsepower to run this version. The more RAM the system has the better, and multiple CPU's (up to 8 or 16 in certain configurations) are possible.

SQL Server 2005

SQL Server 2005 follows the same feature set as SQL Server 2000, with two limitations removed. SQL Server Integration Services (SSIS), which replaces DTS, now runs on the 64-bit versions. There are also two 64- bit platforms supported: x64 and IA64.

There are no limitations in this edition on CPU's, Memory, and database size. You also have Online restore, Online Indexing, and other high-availability features added. You get Oracle replication capabilities, and the Analysis Services is enhanced in this edition to include advanced analytics.

SQL Server 2008

SQL Server 2008 has the same editions as SQL Server 2005 in the larger space, along with the same features and capabilities. What you do get in addition is support for Windows Server 2008 — in fact, with the improvements in the networking and file structures for Windows in that version, SQL Server runs even faster, and features like replication are far more efficient.

Other Editions

I'll mention the other editions of SQL Server for both versions. These editions are sometimes just a repackaging of others, as you'll see.

CE Edition, Mobile Edition

The CE Edition of Microsoft SQL Server has the same tools and SQL syntax that the developers use for full-fledged SQL Server applications. It's the only relational database that runs on a pocket device. The primary use you'll see for this edition is to act as a merge-replication platform for distributed applications. It does this with HTTP, and even supports encryption. You have to run CE 2.11 or later to run the program, and you can use databases to 2GB.

In SQL Server 2005 this edition is called SQL Server Mobile, and it includes enhanced replication and feature sets.

In SQL Server 2008, there’s a new “Compact Edition” that runs in memory for embedded database applications.

Developer Edition

With the Developer Edition, you can develop applications for SQL Server. You get all the features of the Enterprise Edition, but with a development license that prohibits you from deploying this edition in production. It installs on just about any current operating system from Microsoft.

With this edition, you have the right to install SQL Server CE Edition and deploy those apps royalty-free, although the CE devices have to license the CE version.

The other beauty of this edition is that it can be upgraded for production use to SQL Server Standard or Enterprise Edition. And the best part of all — it’s less than a hundred bucks U.S.

Evaluation Editions

The Evaluation editions are just that — the full-up featured editions (Standard and Enterprise) that stop working after about 120 days. They don't cost anything, but you can't legally use them in production or even development. They're to be used strictly for deciding whether to buy the product.

MSDN

If you're a member of the Microsoft Developer Network (MSDN), there are repeats of each of the editions in your subscription. The primary differences are the licensing (you can't deploy it for production) and the fact that some of the editions are "checked builds," meaning that they can display more debug information as they work. These debug editions are much slower than the standard released code.

InformIT Articles and Sample Chapters

To learn more about SQL Server in general, make sure you bookmark this page.

Books and eBooks

Need more info on the Compact Edition? Check this book out from InformIT.

Online Resources

The Microsoft Official site for SQL Server is here.