Tour de SQL Part I: Versions

Join us on a journey through the lush fields and tricky backroads of SQL Server 2005.

Tour de SQL Series

> Tour de SQL Part I: Versions (currently viewing)
> Tour de SQL Part II: Administrative Features
> Tour de SQL Part III: Performance Tuning
> Tour de SQL Part IV: Replication

This July 1, the world's best bicyclists will gather in northeastern France to kick off the planet's most grueling event: the Tour de France, a 20-stage race covering more than 2,100 miles. The cyclists will zip through wheat fields, bounce over cobblestone streets and power up mountains on their way to the finish line at the Champs Elysees in Paris.

The epic race takes in all four corners of the country. That makes it a perfect metaphor for our four-part series the Tour de SQL. This month, we introduce you to the different versions of SQL Server 2005. Subsequent articles cover the following topics:

  • May: SQL 2005 Administrative Features
  • June: SQL 2005 Performance Tuning
  • July: SQL 2005 Replication

So throw on your Oakleys, stuff yourself into your bib shorts, lube your chain and inflate your tires, and join us for the Tour de SQL.

Not Your Father's SQL Server
The road to SQL Server 2005 has, at times, seemed as long and as punishing as the Tour de France itself. When the latest version of Microsoft's database management system hit the shelves last Nov. 7, it was the first new release of the software in five years. What's more, the new version is the first major rewrite of the SQL engine since SQL 7 in 1998.

No wonder the SQL community has been abuzz with discussions about this release and the features that Microsoft has brought to the table. To appeal to a broad range of customers, SQL 2005 comes in a variety of flavors -- six, to be exact. Ranging from the free version to the not-so-free $25,000 per processor Enterprise Edition, SQL 2005 has an edition that meets your budget.

SQL 2005 Express Edition
The newly minted Express Edition replaces MSDE as the no-cost SQL. Being the baby of the group, Express supports a single processor, 1GB RAM, and is limited to databases smaller than 4GB. Express can be installed on Windows XP, Windows 2000 Server or any product in the Windows Server 2003 family. Because Express is built on the same database engine as the other versions, it offers a lot of the same functionality. Stored procedures, triggers, XML support and CLR integration are just some of the features available. You can manage Express from the Visual Studio 2005 environment or the SQL Server Management Studio, but you'll need your own copy -- they're not included with this version. Unlike its predecessor, MSDE, Express doesn't contain the SQL Server Agent, meaning there's no support for job scheduling.

Enterprise Edition is the Colnago C50 of the SQL family -- as the French would say, the creme de la creme.

Express was designed to give developers a rich but simple database engine for use with data-driven applications. Generally, its use is limited to a single application instance. It also makes it simple to create dynamic Web sites with Express as the data engine. Because it can function as a replication subscriber (with an additional client license), Express is also a great option for remote users to store a subset of larger databases when they're disconnected from the network. Since Express is often installed on client workstations, all security patches and updates are available via Windows Update. This edition isn't meant to be used for data storage in client-server applications or as a central database engine, which makes sense, given its limited capabilities. If you find yourself needing larger databases (4GB-plus), more powerful hardware, high availability options, task scheduling or even management tools, a more feature-complete edition should be considered.

SQL 2005 Workgroup Edition
Workgroup Edition, first introduced with SQL 2000, expands usability over Express with support for 2 CPUs and up to 3GB RAM. In addition, the database size limitation has been removed. Like Express, Workgroup can be installed on the XP, Win2K Server, or Windows Server 2003 line of products. Again, all the same core database functionality is available, but the SQL Server Agent is included in this release. Using Workgroup, you can publish databases via replication, but you're limited to 25 subscribers for merge replication and five subscribers for transactional replication.

Workgroup is, as the name suggests, a stripped down version of SQL for use in small offices or workgroups. This allows you to maintain a central database server for use by several Web servers or client-server applications. Workgroup also ships with SQL Server Management Studio, making it a fully manageable solution. Log shipping is available in Workgroup, giving you an option for high availability.

Workgroup is the perfect solution for small to midsize businesses (SMBs) that need a database server, but don't require advanced business intelligence, processing power or high availability. It can also be a great option for a small branch office in a large company. With a price tag of $3,900 per processor, Workgroup is affordable for small firms or projects on a budget. While a mission-critical application will probably not be set up on Workgroup, it remains a powerful, fully manageable database system. If you need more processing power, clustering or advanced business intelligence solutions, Workgroup won't be enough.

Top Tour de France Web Sites
The official Web site of the Tour de France (TDF). Fairly limited in scope and not very dynamic.
The Outdoor Life Network’s Tour de France section. OLN has been broadcasting the TDF for years, and its website includes, among other goodies, daily video clips of the race.
Very cool blogging Web site about the TDF. Frequently updated, even in winter.
Graham Watson is the most famous Tour de France photographer. His shots include breathtaking panoramas, and most of his photos are for sale. Also available are screen savers and wallpaper.
Everything you always wanted to know (and lots of stuff you didn’t, too) about the rider who had a bit of success at the TDF.
The place to go to feed your cycling jones year round. Excellent, up-to-the-minute coverage of the TDF, including live blogging.
-- K.W.

SQL 2005 Standard Edition
Think of Standard Edition as the Tour de France equivalent of a "domestique" -- the solid, consistent cyclist who does all the dirty work for the leader and is the real backbone of the team. It's the solution for SMBs that need data storage as well as high availability or basic data warehousing functionality. Standard supports up to 4 CPUs and has no inherent RAM limitations. Standard also ships in both 32-bit and 64-bit versions. Unlike SQL 2000 Standard Edition, clustering is available, although limited to two nodes. A few other tools are also included with Standard that you can't get with Express or Workgroup. For instance, the database tuning advisor, profiler and SQL Server Integration Services (the replacement for DTS, Microsoft's import and export tool) are at your disposal. While SQL Service Integration Services (SSIS) is available in Standard, it's limited to basic transformations in this edition.

Another widely anticipated feature of SQL Server 2005, HTTP Endpoints, is included in Standard. HTTP Endpoints allow you to expose objects on the SQL Server to users via Web services. (Note that HTTP Endpoints don't expose the entire SQL Server to the Web, but instead maps specific objects, such as function or stored procedures, to Web services interfaces.)

Basic Business Intelligence (BI) features are also available with Microsoft Analysis Services. This service facilitates the creation of data warehouse solutions, complete with data mining functionality to allow for an advanced and rich reporting environment.

Standard is good for midsize to large companies as a central database storage engine. With high availability via clustering and support for larger, more powerful servers, Standard edition will handle most SQL implementations. Unless you plan on a massive amount of data processing, require advanced BI or need maximum uptime, Standard should meet most of your needs. Standard comes in at $6,000 per processor. If you find that a two-node cluster is not enough, taking down the server for indexing is costing you money or you need more data warehousing capabilities, you'll want to check out SQL Server Enterprise Edition instead.

SQL 2005 Enterprise Edition
Enterprise Edition is the Colnago C50 of the SQL family -- as the French say, the creme de la creme. All the stops have been pulled out for Enterprise. It supports an unlimited number of CPUs, unlimited RAM and can only be installed on server-class operating systems -- Windows 2000 Server SP 4 or later and any version of Windows 2003. One notable change from SQL 2000 Enterprise is that SQL 2005 Enterprise doesn't require the enterprise version of Windows.

There are several features available only in Enterprise that make it a worthy investment, even if you don't need a 32-way server:

  • Clustering support for up to eight nodes.
  • Table Partitioning allows data within a table to be divided across multiple physical files.
  • Indexed views allow indexes to be added to views to improve performance.
  • With online indexing, indexes can be built and rebuilt without locking users out during the operation.
  • Online restores will permit users to log into a database during restore operations.
  • Using database mirroring, SQL can maintain a second copy of your database in the event of a failure.
  • All of Microsoft's BI tools (Analysis Services, Reporting Services, SSIS) are available.
  • SSIS is available in its entirety, allowing both basic and advanced transformations.
  • SQL replication allows you to subscribe to publications from Oracle servers.

Enterprise is the solution for large organizations that need extremely high availability or processing power. With the fully expanded set of BI features, Enterprise can handle even the largest data warehouses. Enterprise has a specialized niche; only deep-pocket companies with mission-critical applications or large data warehouses will be able to spring for this $25,000 per-processor behemoth.

SQL 2005 Developer Edition; SQL 2005 Mobile Edition
Microsoft has provided two other versions of SQL 2005: Developer Edition is functionally the same as Enterprise Edition except for licensing and cost. Developer retails for $49, with a license for development purposes only. In other words, you can't run any production applications on Developer. This gives you the power to develop SQL applications and test Enterprise Edition features without having to pay for it.

Mobile Edition is a special edition for use on portable devices such as PDAs or smartphones. This version replaces the old SQL Server CE.

Which Version Is Right for Me?
With so many versions available, which one should you use? That really depends on your needs. There are several factors you should consider to determine the proper version, including data storage, uptime and availability, processing power, functionality and cost.

The many faces of SQL Server 2005.
[Click on image for larger view.]
Figure 1. Version comparison of SQL Server 2005

Data Storage
This is simple -- estimate how much data you'll be storing. If it's more than 4GB, Express is out. In addition to the size, the way you store your data can also be important. Do you plan to put it on a RAID array? Do you plan on using local storage or a SAN? If you plan on using advanced storage such as a SAN or other shared device, you're best off with Standard or Enterprise, to allow for clustering, if needed. If your data model calls for very large tables, you may want to take advantage of table partitioning, only available in Enterprise.

Uptime and Availability
If you can't afford to cut back on availability and take the server offline for regular maintenance, you may need the advanced management functions of Enterprise. Features like online indexing, online restore and fast recovery allow you to perform maintenance that previously meant downtime.

If you can afford to take the box offline, one of the other versions could work for you. Otherwise, both Standard and Enterprise provide failover clustering for high availability. What if you need to install a service pack or the shared storage device becomes unavailable? SQL 2005 offers database mirroring (currently available for evaluation purposes only; expect it to be fully released in a future service pack) in both Standard and Enterprise. Database mirroring allows a database to be mirrored on two different SQL Servers. With mirroring, you can take one server offline for service packs or OS updates and leave the other online.

In short, those who cannot allow SQL 2005 to go offline at any time need Enterprise. Organizations that need high availability, but can endure scheduled downtime for maintenance, can get the job done with Standard.

Processing Power
This comes down to how hard you're going to work the server. The more users, queries, applications, transactions, jobs and so on, the more power you need. There's no hard-and-fast number, but testing or time will show if your server is underpowered. With the availability of multi-processor machines and endless amounts of RAM, the sky is nearly the limit on server power -- but to take advantage of that power you have to use the correct SQL version. For instance, if you need to use multiple processors, Express is out, and if you're using an 8-way server, Enterprise is your only option.

What features of SQL Server are you planning on using? Looking to set up a large data warehouse with all the trimmings? You'll need Enterprise. Notification services, reporting services, SSIS and all the advanced data warehousing features are available in Enterprise. Standard offers some of the features you need, but you'll find limitations. Some of the tools, such as profiler and the database tuning advisor, aren't available in Express or Workgroup. Likewise, notification services is only available on Standard and Enterprise. Make a list of the features that you absolutely need and you should be able to limit yourself to a version or two.

As with all projects, cost is often a huge factor. You may find that you have to give up high availability in order to stay within your budget. Obviously, the leap from $6,000 for Standard to $25,000 for Enterprise is a large one. You have to have very good reasons to spend that much more for the upgraded version. Luckily, Microsoft has pushed some formerly Enterprise-only features, like clustering, down into the less expensive versions of the product. Given this current cost structure, everyone should be able to find the version that meets their needs and budget.

SQL Can Compete
SQL Server has gone through some major changes over the years, and it continually matures. Each new version has improved on the last by adding new functionality or stability to existing features. SQL 2005 upholds that tradition, giving users many new features and continuing the version refinement that began with SQL 7. With the release of SQL 2005, Microsoft has proven it can keep up with the peloton of high-end database vendors -- and, in some circumstances, don the "maillot jaune" -- the Tour de France yellow victor's jersey.


comments powered by Disqus

Subscribe on YouTube