Making the Move to SQL Server 2008
Thinking of migrating to SQL Server 2008? Here are some features that might just push you to do it.
The newest version of Microsofts venerable relational database has
a myriad number of features, but writer Grace Townsend asked Lenni Lobel of
Sleek Technologies, Inc. what he thought makes this version migration-worthy.
(You can catch Lennis SQL Server sessions at VSLive! in Dallas this December.
For details, go to http://vslive.com/2008/dallas/.)
What new SQL Server 2008 programming features should developers familiarize
themselves with first? In other words, where's the most "bang for the buck?"
You definitely want to check out Table-Valued Parameters (TVPs). They let you
marshal entire sets of rows across the network from client to server and pass
them between stored procedures and UDFs.
There are many applications for TVPs. You dont need to stretch your imagination
very far to come up with good uses for the ability to pass a strongly-typed
result set as a single parameter between stored procedures. For example, you
could create a stored procedure to accept two TVPs; one with a header row and
one with multiple detail rows. You could then call this stored procedure to
insert a new order with one call to the server, rather than multiple round-trips
(one for the header and one for each detail row). You could even call this stored
procedure from a client application using ADO.NET. TVPs can also facilitate
bulk inserts and updates. Other, perhaps less applicable, uses include using
TVPs as a parameter-passing mechanism for parameter lists and dictionary-style
key/value pair lists.
MERGE is another important feature. This powerful new DML statement combines
the capabilities of four (or more) separate operations. Although MERGE doesnt
do anything that couldnt be done using separate statements as weve
done in the past, it is a more elegant solution than multiple statements for
any typical MERGE scenario. Because it operates on the same join principles
as a standard query, and because it compiles and executes as a single statement,
MERGE is faster than multiple statements. And, combined with the new INSERT
OVER DML feature that lets you filter change data emitted from the OUTPUT clause,
it is extremely useful for incrementing large data warehouses with slowly changing
Before this version, youd have to write separate statements.
What kind of learning curve can developers expect when moving from 2005
The 2008 release is an evolutionary upgrade from 2005 which was, at the time,
a revolutionary upgrade from 2000 and earlier versions. Developers who are already
comfortable in 2005 should feel right at home with 2008 from the start.
Are there migration issues to be aware of?
SQL Server 2008 doesnt include Notification Services (NS), so organizations
that have made an investment in NS will need to think of alternative implementations
before upgrading to 2008. Some notification requirements can be addressed by
existing Reporting Services features, such as standard and data-driven subscriptions.
Moving forward, expect Reporting Services to incorporate more support for notification
Although SQL Server 2008 does support upgrading replicated databases from previous
versions while other nodes remain online, you should be aware that SQL Server
2000 service pack 3 (SP3) is the minimum version required to participate in
a replication topology with SQL Server 2008.
Otherwise, SQL Server 2008 supports virtually all SQL Server 2005 components
and features. The Upgrade Advisor is the way to go; it will help you prepare
for the migration. It analyzes your current configuration, and reports any issues
you'll need to fix before (or after) the upgrade.
What about how SQL Server 2008 handles security? Are there changes developers
should be aware of?
Two new security features (available in the Enterprise Edition only) help meet
the demands of increasingly stringent requirements for regulatory compliance:
Transparent Data Encryption (TDE) lets you encrypt entire databases (and their
log files and backups) in the background without special coding requirements.
With SQL Server Audit, you can record virtually any action taken by any user
for auditing in either the file system or the Windows event log.
With SQL Server Data Services, can we expect to see more applications with
data residing in the cloud? What about any specific security issues to be aware
in those scenarios?
Yes, but slowly. It's still very early for this technology. Understand that
SQL Server Data Services (SSDS) is not actually a relational database with tables
and schemas. It's really just a collection of records, each with its own dictionary
of name/value pairs, which can be a nice way to store and serve structured data.
On the issue of security, you basically go with SSL. The real question is if
you trust a third-party to host your proprietary data.
Shifting gears, we all like to keep our users happy. How will the changes
in Reporting Services 2008 make a difference in the lives of business users?
The new Tablix data region (which is a hybrid of Table and Matrix layout) adds
greater flexibility by allowing fixed or variable rows and columns in any combination,
with grouping on rows, columns or both. Tablix solves a lot of presentation
problems and limitations that, in earlier versions, resulted from forcing the
report into either the Table or Matrix layout.
Are there other features users will notice and appreciate?
There are many. Here are just a few:
- Change Data Capture (CDC) facilitates incremental updating of large
data warehouses by capturing change data without resorting to triggers or
code changes (Enterprise Edition only).
- FILESTREAM is an abstraction layer that stores BLOB data in the NTFS
file system. Developers can treat BLOB data as though it were stored in-line
with other relational table data as a varbinary (max) data type (with integrated
- Hierarchyid is a new data type that lets you cast a hierarchical
structure over relational tables.
- date, time, datetime2, datetimeoffset are all
separate date and time data types that improve date and time range and accuracy
and add native time-zone support.
The submitter of this "Never Again" story wishes to remain anonymous.