In-Depth

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.

• By Anonymous
• 11/05/2008

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 don’t 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 doesn’t do anything that couldn’t be done using separate statements as we’ve 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 dimensions.
Before this version, you’d have to write separate statements.

What kind of learning curve can developers expect when moving from 2005 to 2008?

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 doesn’t 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 scenarios.

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 transactional support).
• 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.