In-Depth
Tour de SQL Part I: Versions
Join us on a journey through the lush fields and tricky backroads of SQL Server 2005.
- By Eric Johnson
- 04/01/2006
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 |
www.letour.fr/indexus.html
The official Web site of the Tour de France (TDF). Fairly
limited in scope and not very dynamic.
www.olntv.com/tdf
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.
www.tdfblog.com
Very cool blogging Web site about the TDF. Frequently updated,
even in winter.
http://grahamwatson.com
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.
www.lancearmstrong.com
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.
www.cyclingnews.com
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.
[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.
Functionality
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.
Cost
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.