Review: SQL Server 2008

With the release of Microsoft SQL Server 2008, SQL Server has become more than just a relational database hidden behind your corporate applications. Microsoft’s ambitious goal for SQL Server 2008 is for it to be an enterprise data—not a database—platform, and Microsoft is positioning SQL Server 2008 to become the provider for all your enterprise information needs. With its built-in integration, analysis, and reporting services, SQL Server 2008 is more than ready to meet this expectation. I point out the most important new features in SQL Server 2008 and give you my recommendations on whether the latest SQL Server version is worth upgrading to—and who should upgrade.

New Enterprise Edition Features
Many of the most important new features for SQL Server 2008 are in the Enterprise edition only. For a rundown of the different editions of SQL Server 2008, see the sidebar “SQL Server 2008 Editions.” Here are some of the most important new features in SQL Server 2008 Enterprise Edition.

Hot Add CPU support. Hot Add CPU support lets SQL Server 2008 recognize and use any new processors added to the system without needing to reboot the server or stop and start the SQL Server service. Support for Hot Add CPU augments SQL Server’s existing Hot Add RAM support, well-equipping SQL Server 2008 to be installed in virtual environments and take part in dynamic workload management.

Resource Governor. Possibly the most important new feature in SQL Server 2008 is the Resource Governor, which lets you control the amount of system resources that SQL Server will allocate to a given workload. For example, the Resource Governor lets you limit the system resources consumed by poorly designed end-user queries that could otherwise adversely impact the system’s overall performance. The Resource Governor can also provide more predictable execution for queries and jobs running on the system.

Data compression. The new data compression capability is completely transparent to client applications and requires no application changes to use compression. SQL Server 2008’s data compression can reduce the size of database data stored on disk and can also significantly decrease backup and restore times by reducing the required I/O.

Transparent data encryption (TDE). TDE extends SQL Server 2005’s encryption capabilities. SQL Server 2005 encrypts data at the cell level using encryption functions. However, managing the encryption keys is a manual process, and applications require code changes to access encrypted data. SQL Server 2008’s TDE lets you encrypt an entire database and have the encryption be completely transparent to end-user applications.

Policy-Based Management. Policy-Based Management lets a database administrator enforce corporate standards such as database-configuration settings and object-naming conventions across multiple servers.

Change data capture (CDC). For organizations needing to populate data warehouses or other external databases from SQL Server, the new CDC feature alone might be worth the upgrade. CDC does away with the need to write custom code to capture changes. Instead, changes to tables are automatically captured from the transaction log and propagated to a set of target capture tables. CDC can also enhance auditing by capturing all the data changes to target tables.

New Features in All Editions
Enterprise isn’t the only SQL Server 2008 edition that boasts new features. There are a number of additional enhancements available in all editions of SQL Server 2008 (Standard, Web, Workgroup, and Enterprise). Here are some of the most important ones.

IntelliSense, debugging, and code-outlining support. Two of the biggest missing features in SQL Server 2005 were its lack of IntelliSense auto-completion and the ability to debug T-SQL code. SQL Server 2008’s new IntelliSense support—in SQL Server Management Studio’s (SSMS’s) Query Editor—provides T-SQL syntax checking and automatic prompting for database object names, as Figure 1 shows. T-SQL syntax errors are displayed using a red squiggly marker at the end of each T-SQL statement.

SQL Server 2005 required you to use Microsoft Visual Studio to debug your T-SQL scripts. SQL Server 2008’s Query Editor supports full debugging of T-SQL code and provides the ability to set breakpoints, as Figure 2 shows, so that you no longer need to use Visual Studio for T-SQL debugging. Another useful improvement to Query Editor is the code-outlining feature, which lets you expand or collapse a block of code within the Query Editor window.

However, these new features are limited to SQL Server 2008 instances. If you connect Query Editor to a SQL Server 2005 database, you won’t get IntelliSense, debugging, or code outlining. In addition, these features are also not present in SSMS Basic, which is used to manage SQL Server 2008 Express and SQL Server 2008 Express with Advanced Services.

New data types. Unlike the DATETIME data type in earlier SQL Server versions, which required you to combine date and time data in one column, SQL Server 2008’s new DATE and TIME data types let you store data and time values discretely and there are other new date and time data types that enable more accurate storage of data and time data. A new FILESTREAM data type allows high-speed access to large objects (LOBs). SQL Server 2008 also provides new spatial data types that you can use to build mapping applications. (For more information about SQL Server 2008’s new data types, see the sidebar “New Data Types in SQL Server 2008.”)

T-SQL enhancements. As you’d expect from a new SQL Server release, SQL Server 2008 includes a collection of T-SQL enhancements. The DECLARE statement now lets you assign values to a variable. New compound-assignment operators (+=, -=, /=, %=, &=, |=, and ^=) let you more efficiently perform mathematical, XOR, XAND, and string-concatenation operations. A new row-constructor feature lets you use a single INSERT statement to insert multiple rows. A new MERGE statement lets you perform INSERT, UPDATE, and DELETE functions in one statement.

Another new T-SQL feature, table-valued parameters, lets you pass a table as a parameter to a stored procedure or function. Table-valued parameters are useful in situations where you need to pass large numbers of parameters to a stored procedure or function. In addition, a new GROUPING SETS feature gives you more control over the output and aggregations used in SQL Server queries.

BI enhancements. Although the business intelligence (BI) enhancements in SQL Server 2008 aren’t quite as notable as those in SQL Server 2005, the SQL Server 2008 BI subsystems—SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS)—have gained some important enhancements nonetheless. SSIS features a new Data Profiling task that lets you analyze data passed through an SSIS package. The Data Profiling task can also output data for later viewing with the Data Profile Viewer. A new Cache Transform lets you cache lookup operations for improved package performance. In addition, the SSIS Script Task is now a Visual Studio Tools for Applications (VSTA) implementation with full support for Microsoft .NET Framework references.

SSAS has revamped Cube and Dimension Wizards that make the design process faster and more intuitive. Microsoft has enhanced the Dimension Designer by enabling it to define attributes’ relationships and adding a new visual designer. There are also improvements to the backup and restore process for SSAS databases.

One of the most important enhancements to SSRS is that it no longer requires you to install Microsoft IIS as a prerequisite. In addition, the Report Designer has had a major facelift. There’s also a new report format called Tablix that combines the attributes of table and matrix-style reports.

Another feature that can help end users access information in SQL Server 2008 is the ability to author reports in Microsoft Excel and Word. Microsoft has licensed SoftArtisans’ OfficeWriter, which integrates Excel and Word with SQL Server and SSAS databases.

Installing and Upgrading to SQL Server 2008
I installed SQL Server 2008 on a new Windows Server 2003 SP2 system and upgraded another of my existing SQL Server 2005 systems to SQL Server 2008. The setup program prompted me to upgrade my systems with .NET Framework 3.5 SP1, then with Windows Installer 4.5, after which I needed to reboot the system. After the reboot, I needed to manually restart the setup process, which first installed the setup prerequisites and checked the system setup requirements, then displayed the Installation Center, which Figure 3 shows.

The Installation Center provides a new setup experience. The Planning page provides links to the SQL Server 2008 documentation, and the Installation tab provides links to the new installation and upgrade programs. For a new installation, you launch the New SQL Server stand-alone installation or add features to an existing installation option. To upgrade a SQL Server 2000 or SQL Server 2005 instance to SQL Server 2008, you select the Upgrade from SQL Server 2000 or SQL Server 2005 option. The setup program is also cluster aware and has links that you use to either set up a new SQL Server cluster installation or add a node to an existing cluster. Both the installation and upgrade procedures went smoothly for me. The installation process took only a few minutes. The upgrade process was longer, taking about half an hour.

If you’re familiar with previous SQL Server versions, it should take you no time at all to get used to SQL Server 2008. The management and development experiences are essentially the same. Your primary management tool is SSMS, and you develop SSAS cubes, SSIS packages, and SSRS reports using Business Intelligence Development Studio (BIDS).

A few more changes to note: The old Surface Area Configuration tool is gone. Most of its functionality was duplicated in SQL Server Configuration Manager, which still exists in SQL Server 2008. In addition, the little-used Microsoft SQL Server Notification Services has been depreciated. The sample AdventureWorks database and AdventureWorksDW data warehouse are also no longer available through the installation process. If you want to have those databases, you’ll need to download them at www.codeplex.com.

SQL Server Books Online (BOL) remains the DBA’s best friend and is an excellent source for SQL Server 2008 technical information and tutorials. A useful new feature in BOL is its integration with the Microsoft online SQL Server forums. BOL links you to the Microsoft SQL Server forums when you ask a question and lets you search the Microsoft online forum from within BOL for an answer to your question.

Value for the Enterprise
Although it’s likely that many businesses aren’t ready for another major SQL Server release just yet, I strongly encourage you to take a look at SQL Server 2008 anyway. SQL Server 2008 delivers exceptional value with an array of new enterprise-oriented features. For organizations that are already SQL Server Enterprise Edition customers or are considering moving to Enterprise Edition, SQL Server 2008’s new enterprise-oriented features make it a must-have upgrade. The upgrade case is less compelling for SQL Server 2005 Standard Edition customers, although all organizations would benefit from the new data types, IntelliSense, and T-SQL debugging.

If you’re running SQL Server 2000, now is the time to make the jump to SQL Server 2008. SQL Server 2008 has all the SQL Server 2005 features plus the new features, all of which make the upgrade worthwhile. Furthermore, the integrated upgrade option makes it easy to move from SQL Server 2000 to SQL Server 2008, and the next SQL Server release might not have that capability.