You’ve certainly heard the news by now: Microsoft is on the verge
of releasing a new version of their flagship enterprise-ready
database, SQL Server. In fact, they announced at this year’s Tech Ed
that SQL Server 2005 (formerly code-named “Yukon”) would be released
to manufacturing the week of November 11. So, even if you haven’t been
paying much attention yet, it’s time to start now. Of course, no one
in their right mind switches to a new version of a server database
overnight. But we’re at the point in the development cycle where it
makes sense to start considering whether it’s worth your while to
upgrade. A big part of your decision should be based on whether you
need the new features offered by the new version. That’s the reason
for this article: to give you a quick rundown of the major areas of
improvement, so you can decide if it’s time to investigate further.
One note: I’m picking and choosing the features that I consider the
most significant in this list. There are literally hundreds of new
features in this release. After all, Microsoft has been working on ir for
half a decade, and that’s a long time in software years.
Begin at the Beginning
I’ll start with the SQL Server database engine itself. Though there are
a lot of other pieces in the SQL Server box (and I’ll talk about most of
them later in this article), without the ability to store and retrieve
data they’re not worth a whole lot. Fortunately, the SQL Server team
lavished a lot of attention on the core of the product this time around.
Here are some of the major improvements and new features you’ll find in
the database engine:
- .NET Common Language Runtime integration: This means you can write
stored procedures, functions, triggers, user-defined types, and even new
aggregates in any .NET language. - Native HTTP SOAP Access: This means that the database server can
serve up data via Web Services with no additional middleware at all, not
even an instance of IIS. - New xml data type: You can store XML data natively in its
own column type now. - XQuery support: SQL Server supports XQuery for querying against
XML data, including extensions that allow XML DDL operations. - Improved Error Handling: T-SQL now supports TRY-CATCH
blocks for modern error handling. - DDL Triggers: You can set up triggers that fire when CREATE,
ALTER, or DROP statements are executed. - Query Notifications: With query notifications, an external
application can request the database engine to notify it when the data in
a cached result set changes. This can dramatically lessen the load from
requerying. - Failover clustering now extends to 8 nodes on 32-bit systems, and
4 nodes on 64-bit systems. - Database mirroring introduces a new reliability model for hot
standby on a database-by-database basis. - Users now have acess to a database during a restore operation,
within certain limits. This helps get you up and running faster after
problems. - Multiple Active Result Sets (MARS): SQL Server 2005 introduces
the ability for multiple statements to return result sets at the same
time on a single connection.
Crunching the Data
Turning from online transaction processing to online analytical
processing, the people who work on SQL Server Analysis Services haven’t
been idle either. Here are a few of the enhancements you’ll find in SQL
Server Analysis Services (SSAS):
- Like SQL Server itself, SSAS now supports multiple instances on
the same physical computer. - The new Analysis Services Scripting Language provides an XML-based
syntax for administering and scripting instances of SSAS. - The new Business Intelligence Development Studio provides a modern
IDE (similar to Visual Studio .NET) for developing solutions using SSAS
objects. - Multiple Fact Tables: One cube can now contain more than one fact
table. - Perspectives are pre-defined subsets of cube metadata, similar to
SQL Server views. You can use perspectives to reduce the perceived
complexity of a cube for certain users. - Data mining has been enhanced with several new algorithms as well
as a new wizard and editor for building and modifying data mining
models. - SSAS is fully integrated with the .NET Framework for management
and development.
No More DTS
You won’t find Data Transformation Services in SQL Server 2005 – at
least, not by that name. That’s because it’s been upgraded so much that it
now has a new name to boot, SQL Server Integration Services (SSIS). Here’s
some of what lurks in that corner of the new product:
- An extensible object model that lets you build new SSIS tasks and
other components using the .NET Framework. - The new Business Intelligence Development Studio provides a modern
IDE (similar to Visual Studio .NET) for working with SSIS objects (yes,
this is the same IDE used by SSAS). - New workflow features that make it easier to create complex
packages with looping and branching. - New tasks that can interact with WMI, files, Web Services, XML,
and data mining queries. - New transformations including union all, merge, sort, pivot,
unpivot, and sampling. - A new package deployment utility that makes it easy to move
packages to another instance of SQL Server.
New Technologies
SQL Server 2005 bundles three new technologies as part of the standard
database deployment. Two of these (Notification Services and Reporting
Services) are available as add-ons to SQL Server 2000; the third is brand
new.
The new technology is Service Broker, which Microsoft describes as “a
new technology for building database-intensive distributed applications
that are secure, reliable, and scalable.” Built into the database engine,
Service Broker lets you store message queues in a database. It also
provides T-SQL statements that let you set up communications channels and
then use those channels to send and receive messages. This gives you an
asynchronous programming model and reliable messaging between SQL Server
instances.
SQL Server Notification Services provides a platform for developing
applications that send notifications, potentially to thousands or even
millions of recipients. Notification Services provides a programming
framework based on XML and T-SQL, and a high-performance engine to perform
the actual notifications.
SQL Server Reporting Services is a server-based reporting technology
that supports report authoring and distribution. A new Report Builder
makes it easier than ever to create reports, and there are other tools
than enhance the manageability of SSRS. With SSRS, you can create reports
based on relational or multidimensional data from a wide variety of data
sources.
Replicate That Data
SQL Server was an early entrant in server-based replication, and its
support for replication has always been at the cutting edge. SQL Server
2005 doesn’t disappoint in this regard, offering quite a number of new
features and enhancements, including:
- Improved monitoring features include a new Replication Monitor
interface and the ability to inject tracer data into a complex replication
topology to measure its latency. - A wide variety of schema changes are automatically replicated.
- If a snapshot gets interrupted, it will resume from the point
where it left off when connectivity is restored. - Oracle data can now be published to SQL Server subscribers
without custom programming. - Transactional publications now support updates to large data types
(varchar(max) and varbinary(max) columns).
Potpourri
Of course, there’s the final list of new features that I couldn’t avoid
mentioning, but that don’t quite fit anywhere else:
- The new SQL Native Client replaces the old MDAC stack for client
data access, and fully supports all the new database engine features. - SQL Server Management Studio provides a spiffy new IDE to replace
the aging Enterprise Manager and Query Analyzer for all of your SQL Server
administrative needs. - Books Online has had a complete facelift, and has a bundle of new
tutorials and other helpful content. - The new sqlcmd utility replaces both osql and isql, and uses OLE
DB to communicate with the database engine.
Sounds Great! What Now?
If you’ve gotten this far, you may be itching to get your hands on a
copy of SQL Server 2005 to see the new functionality for yourself. The
good news is that as of Tech Ed, this is easy: Microsoft just released a
new Community
Technology Preview (CTP) build of SQL Server for public download. So
(assuming you’ve got a decent Internet connection), trying out the goodies
is just a click of the mouse away. But remember, please: this is a beta.
Don’t put it on your production servers just yet! So far, I’m finding the
June build to be pretty darned stable; it’s definitely ready for most
organizations to evaluate now. I look forward to rolling out a production
server early next year.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer (from which this
article was partially adapted)and Developer to Designer, both from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.
