Get Ready for SQL Server 2005
Belated Beta 2 shows what's in store for data architects, project managers, and DBAs.
by Roger Jennings

August 20, 2004

Technology Toolbox: VB.NET, C#, XML, Visual Studio 2005 Enterprise Beta 1, SQL Server 2005 Beta 2, SQL Server 2005 Express Beta 2, Web services

SQL Server 2005—the database formerly known as Yukon—is in the throes of what promises to be a five-year-plus gestation. Projected beta release dates have come and gone since its first demonstration in 2001, and some features have been cut. Microsoft released Beta 2 this July for download by MSDN Enterprise, Universal, and Professional subscribers (see Additional Resources). Microsoft's Tom Rizzo, director of SQL Server product management, says Beta 2 is "close to feature-complete," with later betas to deliver enhancements to SQL Server Reporting Services and the management tools. The actual product is due to ship in 2005, as its new name promises, with the additional promise that it will be fast, bulletproof, and secure.

I'll describe the SQL Server 2005 Beta 2 feature set and comment on the features Microsoft cut or deferred to future revs. I'll focus on SQL Server 2005's future impact for DBAs, project managers, data architects, and IT management—not developers testing Visual Studio 2005 Beta 1 with SQL Server Express. You need ADO.NET 2.0 to take advantage of several new SQL Server 2005 capabilities; I'll include details of features that depend on .NET Framework 2.0 upgrades to the System.Data.SqlClient and the new System.Transactions namespaces.

Beta 2 adds data security by offering selective encryption of sensitive information with functions that convert clear (character) text to ciphertext (varbinary). You need data encryption to comply with federal and state privacy and confidentiality regulations, such as the Health Insurance Portability and Accountability Act (HIPAA). You also need encryption to gain exemption from California's Information Practices Act (SB 1386).

You can specify symmetric keys for better performance or asymmetric keys for greater security. Asymmetric keys can encrypt symmetric keys. All keys are stored in the database. I'll describe T-SQL encryption statements and encrypt/decrypt functions later.

Data availability is another hot topic. Beta 2's database mirroring creates hot-standby servers that can supplant failover clusters and are much easier to manage. Database administrators get a dedicated connection for use with the Sqlcmd.exe utility when the server won't accept new connections.

A second edition of Beta 2 supports Advanced Micro Devices' 64-bit Opteron processors with Direct Connect Architecture. Expect a third version supporting Intel 32-bit CPUs with 64-bit extensions in the Beta 3 timeframe, says Rizzo. That's when more platforms using these chips will become available for testing.

SQL Server 2005 sports a new set of management tools: SQL Server Management Studio, Computer Manager, and Business Intelligence Development Studio. Management Studio (Beta 1's SQL Server Workbench) combines SQL Server 2000's Enterprise Manager, Query Analyzer, and Analysis Manager apps into a unified IDE modeled after Visual Studio 2005 (see Figure 1). You manage Notification Services, replication, Reporting Services, earlier SQL Server versions, and SQL Server Mobile Edition in the same UI. You can create and save solutions containing projects including connections, queries, and associated miscellaneous files.

Online help supplements Books Online with links to SQL Community sites, as VS 2005 Beta 1 does for Code Wise Community sites. A Microsoft SQL Server 2005 | Tutorials | SQL Server Tutorials menu choice opens a list of tutorial topics. However, most Beta 2 tutorials are in the "Information yet to come" category.

SQL Server 2005 Express Edition, which replaces the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), adds a simplified set of graphical management tools (see Additional Resources). A preview of SQL Express Manager includes a set of wizards to assist users in configuring SQL Express Beta 2. It should be available by the time you read this. All VS 2005 editions will include SQL Express and will support ClickOnce database deployment when SQL Express Beta 3 arrives. SQL Express runs on one CPU only, handles database sizes up to 4 GB, and eliminates MSDE 2000's workload governor.

Try the New Query Editor
Management Studio's Query Editor replaces Query Analyzer, letting you write T-SQL scripts without a database connection. Query Editor extends authoring capabilities to Multidimensional Expressions (MDX), XML for Analysis (XMLA), and Data Mining Extensions (DMX). A new SQLCMD mode lets scripts execute operating system instructions. The sqlcmd.exe utility replaces osql.exe and isql.exe with an OLE DB-based connection. Query Editor's new features also include an improved display for resultsets containing XML data and a graphical execution plan tool for SQL Server and SQL Server Mobile Edition.

The Beta 2 installer doesn't copy by default the sample AdventureWorks database scripts and sample projects unless you specify installation of all databases and samples. Otherwise, click on the Change button on the Microsoft SQL Server 2005 Tools Beta 2 component in Add or Remove Programs, set Databases and Samples to install, and select Attach Databases. Add the Northwind and Pubs databases by downloading the installation scripts, loading them into the Query Editor, and executing them (see Additional Resources).

Beta 2's Management Studio omits SQL Server 2000 Enterprise Manager's useful ability to display a table's contents in an editable grid by right-clicking on a table item, then choosing Open Table. However, you can open a table for editing from Visual Studio's Server Explorer. Simply right-click and select Retrieve Data from Table (VS 2003) or Show Table Data (VS 2005 Beta 1). Hopefully this feature will reappear in Beta 3.

SQL Server 2005 and SQL Express also install SQL Computer Manager, which unites SQL Server 2000's Server Network Utility, Client Network Utility, and Services Manager into a single MMC snap-in. This in turn integrates with the Computer Management snap-in you open from My Computer.

SQL Computer Manager configures SQL Server 2000's shared memory (Sm), named pipes (Np), TCP/IP (Tcp), and Virtual Interface Architecture (VIA) network libraries for clients and servers. VIA is a new low-latency, high-speed interconnect. It replaces TCP/IP for connecting many servers in a system area network (SAN). The SQL Server 2005 VIA NetLib supports Emulex Giganet cLAN server cluster interconnect hardware (see Additional Resources). You can enable or disable each NetLib DLL for server and client, and set NetLib priorities for clients by selecting the Client Network Configuration node (see Figure 2).

SQL Profiler gets a minor UI facelift. It saves Showplans as XML files you can import into Query Editor, and it lets you save XML-formatted trace result files you can open for replay.

The Database Tuning Advisor replaces SQL Server 2000's Index Tuning Wizard. Enhanced features include being able to import traces and save reports as XML files (see Figure 3).

Data architects and project managers will appreciate Business Intelligence Development Studio, which lets you design cubes, data sources, reports, and Data Transformation Services (DTS) packages as project components of a solution (see Figure 4). A BI solution is a server-independent container holding multiple DTS, Analysis Services, and Report projects.

Open the AdventureWorksAS.slnbi file in the \Program Files\Microsoft SQL Server\90\Tools\Samples\1033\awasdb folder to check out new BI Dev Studio features. You can install SQL Server 2005 as a named instance—just make sure to change localhost to ServerName\InstanceName in the Server Name textbox of the Project Properties dialog's Deployment page and in the connection string for the adventure_works.ds data source. Double-click on adventure_works.ds or right-click and choose Open to display the Data Source Designer dialog; choosing Properties doesn't work in Beta 2.

The graphical DTS Designer can extend packages created by the enhanced DTS Import/Export Wizard, which provides improved support for flat-file import. You can customize column widths and data types, then preview the result to make sure data isn't truncated. The wizard also lets you specify creation of a new database, a new table, or new columns in an existing table.

Use the SQL Native Client
The SQL Native Client (SQLNCL1.dll) replaces SQL Server's OLE DB and ODBC data providers with a single DLL. This DLL doesn't depend on finding a Microsoft Data Access Components (MDAC) version later than MDAC 2.5 SP3, which Windows 2000 SP3 installs.

SQL Native Client lets ADO access new SQL Server 2005 features such as user-defined types (UDTs) and XML, varchar(max), nvarchar(max), and varbinary(max). This client also enables snapshot transaction isolation, Multiple Active Result Sets (MARS), asynchronous commands, and changing expired passwords on clients.

You can redistribute the Windows Installer version (SQLNCL1.msi) to enable upgrading existing client apps with SQL Native Client features. SQL Native Client supports async commands with the shared-memory NetLib, but SQL Server 2000 doesn't. I'll discuss snapshot transactions, MARS, and async commands later.

Managing and replicating databases has changed. SQL Server 2005 substitutes SQL Management Objects (SMO) and Replication Management Objects (RMO) for SQL Distributed Management Objects (SQL-DMO). SMO and RMO are .NET 2.0 assemblies for the Microsoft.SqlServer.Management and Microsoft.SqlServer.Replication namespaces, which support SQL Server 7.0 and 2000. Both objects deliver improved performance and support new SQL Server 2005 features. SMO eases a DBA's transition to the new managed-code version by using SQL-DMO terminology wherever it can. You program SMO and DMO operations with VS 2005 by adding .NET references to assemblies. COM wrappers support unmanaged VB, C++, and VBScript SMO programming.

XML users will appreciate the XML datatype and XQuery features. The XML columns store XML values natively, so you can choose to type an XML column with a collection of one or more XML schemas or leave it untyped. You can store XML values as binary large objects (BLOBs) to preserve document hierarchy, element order, and recursive structures.

You can store well-formed documents with a single top-level element, or document fragments (content) that lack one. The standard encoding for internal storage and XML output is Unicode (UTF-16), not the more common UTF-8. Typed XML columns specify the target namespace of the schema collection, which enables the data engine to validate new or modified XML data instances (see Figure 5). Typed XML columns also take advantage of query optimization and indexing for improved performance. XML query plans also appear in Showplan diagrams.

SQL Server 2005 supports XQuery 1.0 and XPath 2.0 operations on XML columns. The W3C XQuery specification set includes six working drafts for XQuery 1.0, XPath 2.0, and XSLT 2.0. The set was in "Last Call" stage at time of writing (summer 2004). I believe the specs will gain recommendation status before Beta 3 arrives. You can familiarize yourself with XQuery syntax by running sample test-case queries on a live XQuery demo page (see Additional Resources).

Microsoft has added sql:column and sql:variable extended functions to support incorporating relational data in resultsets that the XQuery return clause produces. XQuery 1.0 doesn't handle table updates, so SQL Server 2005 includes a non-standard XML data manipulation language (DML) syntax for data modification. The Beta 2 XQuery implementation doesn't fully support the FLWOR (for, let, where, order by, return) expressions specified by the W3C XQuery 1.0 section 3.8 XML Query Language working draft. The let keyword for binding variables isn't implemented, and the order by clause doesn't support the empty least, empty greatest, and collation keywords. I hope let appears before release.

The AdventureWorks sample database and the Books Online XQuery examples will help you master the intricacies of XQuery 1.0 and XPath 2.0 queries and other new SQL Server 2005 features. The Database Engine samples assemble about 45 projects and scripts in nine categories, including three XML projects.

Test-Drive New T-SQL Features
T-SQL enhancements include structured TRY…CATCH blocks that replace @@ERROR error handling. PIVOT and UNPIVOT operators rotate columns and rows to create crosstab reports, but don't match the flexibility of Jet crosstabs. For example, PIVOT operators require fixed column headers specified by an IN predicate list.

Common table expressions (CTEs) are temporary, in-memory table objects that enable recursive queries and disappear when the query completes execution. The TOP operator now accepts an int variable value as well as a fixed integer. The TABLESAMPLE operator for the FROM clause resembles TOP but returns a random sampling of rows from a query. So FROM TableName TABLESAMPLE (25 PERCENT) returns 25 percent of the rows from each page. Nested FOR XML queries return XML hierarchies with siblings; adding the TYPE directive returns the inner query as the XML datatype. New Data Definition Language (DDL) triggers fire on execution of CREATE, ALTER, or DROP statements.

New snapshot transactions increase data accessibility during execution by not requesting locks when reading data. Each transaction behaves as if it's receiving a snapshot of committed data when starting, so other transactions' data modifications aren't visible during execution. Snapshot transactions aren't enabled by default—you must issue an ALTER DATABASE name SET ALLOW_SNAPSHOT_ISOLATION ON statement. Then you can specify SET TRANSACTION_LEVEL SNAPSHOT in T-SQL and take advantage of ADO.NET 2.0's SqlConnection.BeginTransaction(IsolationLevel.Snapshot) instruction to create a snapshot transaction object.

T-SQL enables new Beta 2 column-level and cell-based encryption features with the CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' to create the TRIPLE_DES-encrypted master key for the database. Executing the statement adds a ##MS_DatabaseMasterKey## item under Object Explorer's Database/Security/Symmetric Keys node. Installing a SQL Server 2005 instance creates a service master key, which adds a similar item to the master database. The service master key encrypts the database master key.

DUMP SERVICE MASTER KEY TO FILE creates a backup copy you can restore with LOAD SERVICE MASTER KEY. ALTER SERVICE MASTER KEY regenerates the key and all other dependent keys when you change the service account or its password. CREATE ASYMMETRIC KEY, CREATE SYMMETRIC KEY, and CREATE CERTIFICATE statements with corresponding ALTER and DROP statements manage keys and certificates stored in the database. You can assign these statements to specific user accounts and encrypt symmetric keys with a certificate, password, symmetric key, or asymmetric key. Certificates are encrypted with the database master key by default, but you can encrypt them with a password instead.

The EncryptByAsm, EncryptByCert, EncryptByKey, and EncryptByPassPhrase functions transform clear text to ciphertext with an asymmetric key, certificate, GUID of an existing symmetric key, or symmetric key with an ad-hoc password, respectively. Don't encrypt table columns with asymmetric keys—it impacts performance. Clear text can be a literal string or a varchar, nvarchar, char, nchar, or wchar variable. The return ciphertext value is varbinary (8,000 characters, maximum). A symmetric set of DecryptBy… functions returns varbinary clear text data from ciphertext character variables. Search Books Online for "encrypt" with SQL Server Database Engine as the Technology filter for a complete list of encryption-related T-SQL topics.

Create an HTTP Endpoint
I think the most controversial T-SQL addition will be CREATE ENDPOINT, which enables mapping one or more HTTP URL namespaces to the server. HTTP endpoints enable SQL Server 2005 running under Windows 2003 Server or Windows XP SP2 to register the URL with Http.sys, which listens for and receives requests on TCP port 80, 443 (SSL/TLS), or both.

This feature lets DBAs create in-process SOAP Web services resembling those created by SQLXML 3.0, but don't involve IIS. You return SOAP response messages by adding multiple Web methods with ALTER WEB METHOD statements that execute stored procedures or user-defined functions. You can specify BASIC, DIGEST, INTEGRATED, or any combination as the AUTHENTICATION type, and COMPRESSION ENABLED to use gzip encoding if the client's SOAP request HTTP header specifies gzip in accept-encoding.

Beta 2 adds WS-Security headers for SQL Server authentication with username/password credentials, requiring a LOGIN_TYPE = MIXED modifier and a Secure Sockets Layer (SSL) channel to encrypt clear-text credentials. Incidentally, the T-SQL sample of Books Online's CREATE ENDPOINT topic requires an SP and UDF not in AdventureWorks' current version. Create the missing elements by running the SP and UDP scripts in the "Initial Setup for Native HTTP SOAP Sample Applications" topic, then create the endpoint by running the topic's script.

Alternatively, you can create a TCP endpoint on a designated LISTENER_PORT and specify TSQL, SERVICE_BROKER, or DATABASE_MIRRORING instead of SOAP as the payload type. Today's emphasis on the use of XML Web services for loosely coupled systems and platform interoperability means HTTP[S] transport and SOAP payloads will predominate.

IT management and DBAs must weigh the impact of heavily used SOAP Web services on other database operations. SOAP request and response message processing consumes much more CPU resources than a T-SQL query—especially if you compress large response messages. In-process Web services scale up by adding CPUs and memory, but don't scale out. You can scale out ASP.NET Web services with IIS 6.0 Web gardens and/or server farms running under Windows 2003 Server Web Edition.

Beta 2 delivers many more new or modified T-SQL keywords, with Books Online providing an almost-complete list. However, Beta 2 lacks a comprehensive set of topics on new encryption features.

Service Broker, a new SQL Server 2005 feature, provides reliable asynchronous messaging between databases in the same instance or another local or remote instance. Service Broker is the conceptual equivalent of a simplified in-process version of the Microsoft Message Queue (MSMQ) service. The database persists sets of related messages ("conversations"), so they survive reboots and failovers, and are preserved in backups. Service names (not network addresses) determine message routing making application code instance-independent. You create a service in four steps that define message types, contracts, queues, and the service.

Meet Service Broker Programming
The HelloWorld example in the Database Engine examples' ServiceBroker group introduces you to Service Broker programming. HelloWorld is a Management Studio project with a set of three T-SQL scripts. The scripts activate message delivery in the AdventureWorks database, send an XML message from an InitiatorService to a TargetService, and display the messages in grids. The EventLogging sample project creates NotifyService and CreateDatabaseEventNotification, then sends DDL event messages to NotifyService. Run the two examples, connect Object Broker, then expand AdventureWorks' Service Broker node and subnodes to display the Message Types, Contracts, Queues, Services, and Routes you created.

Notification Services integrate SQL Server 2000 Notification Services 2.0 database components as a native SQL Server 2005 service. Those familiar with Notification Services 2.0 will feel at home with the new version. Otherwise, read "Alert Users with Notification Services" (see Additional Resources). Programming has changed, but not Notification Services usage scenarios and architecture by and large. You don't need notification functions; use VS 2005 to create the application definition file (ADF), subscription management application, and configuration file. Beta 2 enables running NSControl commands from VS 2005 with batch files.

Beta 2's query notifications aren't related to Notification Service's event notifications. Query notifications let client apps request notification when table modifications change the results of a specified query or indexed view.

Query notifications help you maintain lookup table and catalog data with on-demand lookup table refresh operations. This saves expensive server round trips. Query notifications can trigger a Fill operation on a DataAdapter or execute a SqlDataReader to repopulate a DataTable, instead of updating lookup data daily or hourly. The database stores notification subscriptions when a client program executes an ADO.NET 2.0 SqlCommand bound to a SqlDependency object with a handler for its OnChange event. Data modifications affecting the query specified by the SqlCommand.CommandText property cause SQL Server 2005 to place a message in a previously defined queue. This fires the SqlDependency.OnChange event. Alternatively, you can program the client to poll for a NotificationRequest.

The .NET Framework 2.0's System.Transactions namespace lets ADO.NET 2.0 applications team with SQL Server 2005 to provide LightweightCommittableTransactions (LCTs), a new class of distributed transactions. The first server you enlist in an LCT creates a fast local transaction. Enlist a second server and the LCT creates a full COM+ distributed transaction, then promotes the local transaction automatically. LCTs can boost performance significantly in specific transaction scenarios.

Beta 2 doesn't introduce major new CLR-integration features, and stands pat with current MARS and asynchronous SqlCommands. .NET Framework 2.0 and VS 2005 Beta 1 add support for new SQL Server 2005 features, but eliminate asynchronous SqlConnections and put SqlResultSets and the SqlCommand.ExecutePageReader method on the chopping block. You won't see them in future .NET Framework 2.0 betas. Promised T-SQL features now gone missing include array parameters and data and time datatypes. Dropped support for data diagrams—another surprise—has riled many in the Whidbey and SQL Server 2005 newsgroups, so data diagrams might appear in a future release. On the whole, however, I believe you'll agree with Tom Rizzo's conclusion: "We added more features than we dropped."

About the Author
Roger Jennings is an independent XML Web service and database developer and author, a Visual Studio Magazine contributing editor, and an FTPOnline columnist. He manages OakLeaf's XML Web Services demonstration site. His Code of Federal Regulations Web services won Microsoft's 2002 .NET Best Horizontal Solution Award. Reach him at .