Mining for Gold in Yukon

EDITOR'S NOTE: Portions of this article have been adapted from the upcoming book SQL Server "Yukon" New Features (McGraw-Hill).

Planned for delivery in late 2004 or early 2005, Yukon, the next version of Microsoft SQL Server, marks the end of a 5-year development cycle for Microsoft. The company has added so many new features to Yukon that it's impossible to list them all in one article. So, here are 13 valuable gold nuggets you can expect to find in the next major release of SQL Server.

1. CLR Integration
Without a doubt, the most significant new feature in Yukon is the integration of the Windows .NET Framework's Common Language Runtime (CLR) with the SQL Server database engine. Integrating the CLR with the database engine lets developers and DBAs create SQL Server database objects, such as stored procedures, triggers, user-defined functions (UDFs), and aggregates. This new feature directly addresses one of the few remaining shortcomings—the inability to use an OOP language to create database objects—that SQL Server has had in comparison with competing relational databases, such as DB2 and Oracle. With Yukon, you can use Visual Basic .NET, Visual C# .NET, Visual C++ .NET, Visual J# .NET, or any other .NET-compatible language to create database objects. Because the .NET languages are modern and fully object-oriented (OO), they're better suited to address complex business problems than the procedural T-SQL language is.

To use this new feature, you create an assembly by using a new SQL Server project type that Whidbey, the next version of Visual Studio .NET, will provide. (Microsoft plans to release Whidbey at the same time as Yukon.) Then, you load that assembly into Yukon and use an extended version of T-SQL's CREATE PROCEDURE, TRIGGER, or FUNCTION statement to create the new .NET-based database object.

The integration of the CLR with the SQL Server database engine is more than just skin deep—the database engine actually hosts the CLR in process. Yukon handles all the required memory management. The CLR database objects access the SQL Server database by using an updated version of ADO.NET in conjunction with a new SQL Server .NET Data Provider.

Unlike T-SQL assemblies, which don't have any native facilities for referencing resources outside the database, .NET assemblies are fully capable of assessing both system and network resources. Therefore, developing secure .NET assemblies is important.

In Yukon, Microsoft has integrated the user-based SQL Server security model with the permissions-based CLR security model. Following the SQL Server security model, users can access only those database objects (including .NET assembly objects) to which they have user rights. The CLR security model extends this security measure by providing control over the type of system resources that .NET code running on a server can access. You specify the CLR security permissions when you create the assembly. Specifically, you use the WITH PERMISSION_SET clause of the CREATE ASSEMBLY statement. Table 1 summarizes the CLR database security permissions that you can apply to SQL Server database objects.

As Table 1 shows, the SAFE permission restricts all external access. The EXTERNAL_ACCESS permission allows some external access of resources through managed APIs. Yukon impersonates the caller to access the resources. You must have the new EXTERNAL_ACCESS permission to create objects that use this permission. Only systems administrators can create objects with the UNSAFE permission because this permission allows external access to any resource, including the file system and registry.

2. T-SQL Enhancements
Although Yukon integrates the CLR with the SQL Server database engine, T-SQL isn't going away. CLR database objects are a great solution for database objects that require complex logic, but T-SQL is still the language of choice for row-oriented data access. One welcome enhancement to T-SQL in Yukon is the inclusion of full IntelliSense support. IntelliSense provides interactive parameter prompting and smart command completion for all T-SQL commands edited within SQL Server Workbench, which I discuss later.

Another area of T-SQL that Microsoft has improved is the TOP clause. In SQL Server 2000, you were forced to use a constant value in conjunction with the TOP clause. In other words, you could select only the TOP 5 or TOP 10 rows, in which the value of 5 or 10 was a constant. With Yukon, the enhanced TOP function lets you use an expression in conjunction with the TOP clause. The expression can be any allowed T-SQL expression, including a variable or scalar subquery. INSERT, UPDATE, and DELETE statements support the TOP clause.

Another important advance in Yukon's T-SQL is improved transaction-abort handling. Although you can abort a transaction in SQL Server 2000, you can't maintain the complete context of the aborted transaction, so you can't completely recover that transaction. In Yukon, new Try...Catch...Finally statements have been added to the T-SQL language. These new statements let you capture transaction-abort errors with no loss of the transaction context, so you have the option of completely recovering that transaction.

3. FileStream Support
Another important enhancement in Yukon is the addition of FileStream support, which adds a new dimension to SQL Server's ability to support binary large objects (BLOBs), character large objects (CLOBs), and other large objects (LOBs). Yukon's new varbinary(max) data type enables FileStream support. Microsoft introduced the new varchar(max) data type to make working with these large data types the same as working with standard string data. With SQL Server 2000 and earlier, you had to use an entirely different programming model to access BLOB, CLOB, and LOB data that was stored in the database. With Yukon's FileStream support, you can use the same programming model for varchar(max) data that you use to work with standard varchar data.

The varchar(max) data type is an extension to the image, ntext, nvarchar, text, varbinary, and varchar data types. Like the image, ntext, and text data types, the varchar(max) data type supports as much as 2GB of data. However, unlike the image, ntext, and text data types, the varchar(max) data type can contain both character and binary data. However, the most important difference is that the varchar(max) data is stored in the file system outside the SQL Server database. FileStream support maintains the linkage between the objects stored in the file system and their references stored in Yukon's relational tables.

4. Database Mirroring
Probably the biggest new feature in the area of availability is Yukon's support for database mirroring. Database mirroring protects against database or server failure by giving Yukon an instant standby capability. Database mirroring provides database-level failover. In the event that the primary database fails, a second standby database can be available in 2 or 3 seconds. Database mirroring ensures zero data loss. The mirrored server will always be in sync with the current transaction that's being processed on the primary database server.

You can set up database mirroring so that the database failover occurs automatically or manually. The manual failover mode is good for testing, but you'll probably want your production databases to fail over automatically. Database mirroring works with all standard hardware. You don't need any special systems, and the primary server and the mirrored server don't need to be identical. Database mirroring's impact on transaction throughput is minimal. In addition, unlike high-availability clustering solutions, you don't need any shared storage between the primary and mirrored servers.

As Figure 1 shows, database mirroring involves three main systems: the primary server, the mirrored server, and the witness. The primary server is the system providing the database services. Depending on the configuration of the mirrored server, the primary and mirrored servers can seamlessly switch roles. The witness acts as an independent third party that helps determine which system will assume the role of the primary server. Each system gets a vote as to which server will be the primary server; two identical votes determine the winner. This point is important because the communications between the primary and mirrored servers might be cut off, in which case each server would elect itself as the primary server. The witness would then cast the deciding vote.

Database mirroring works by sending transaction logs between the primary and mirrored servers. So, in essence, database mirroring is a real-time transaction log shipping application. You can set up database mirroring for one or more databases.

When a client system sends a request to the primary server, the primary server writes the request in its transaction log before writing the request in the data file because Yukon uses a write-ahead log. Next, the primary server sends the transaction record to the mirrored server. The mirrored server writes the record to its transaction log, then sends an acknowledgement to the primary server that the record has been received, giving both servers the same data in each of their logs. In the case of COMMIT operations, the primary server waits for the acknowledgement before it sends the client a response noting that the operation has finished.

To initialize database mirroring, you back up the database that you want to mirror on your primary server, then restore that backup on your mirrored server. This process puts the underlying database data and schema in place. A state of continuous recovery (i.e., taking the data from the log and updating the data file) keeps the data files up-to-date on the mirrored server.

5. Transparent Client Redirect
The new Transparent Client Redirect feature works closely with database mirroring. A new version of Microsoft Data Access Components (MDAC) implements the Transparent Client Redirect feature, which automatically redirects client systems to the mirrored server when the primary server becomes unavailable. No changes are necessary to the client or data-layer applications.

Here's how this new feature works: MDAC is aware of both the primary and mirrored servers. MDAC acquires the mirrored server's name on its initial connection to the primary server. When the connection to the primary server is lost, MDAC first tries to reconnect to the primary server. If that connection attempt fails, MDAC automatically redirects a second connection attempt to the mirrored server. Just as with clustering, if the connection is lost in the middle of a transaction, the application will roll back that transaction. The application must redo that transaction after the client connects to the mirrored server.

6. MARS
Another important new feature in Yukon is its support for Multiple Active Result Sets (MARS). Earlier releases of SQL Server limited you to one active result set per connection. Although COM-based ADO and OLE DB had a feature that let the application work with multiple results sets, under the covers that feature was actually spawning new connections on your behalf to process the additional commands.

In conjunction with enhancements to ADO.NET, MARS lets you use multiple active commands on one connection. With this feature, you can open a connection to the database, run the first command, and process some results, then run a second command and process results. You can then go back to the first command and process some more results.

MARS and the new version of ADO.NET let you freely switch back and forth between different active commands. No blocking occurs between the commands, and the commands share a single connection to the database. As a result, MARS provides big performance and scalability gains for ADO.NET applications. Because MARS relies on a Yukon database enhancement, this feature doesn't work with earlier versions of SQL Server.

7. SQL Server Workbench
One of Yukon's biggest changes occurs in the administrative tools. Server Manager, SQL Server Enterprise Manager, and SQL Query Analyzer are gone. In their place, Yukon provides the new SQL Server Workbench, which is essentially a customized version of Visual Studio .NET's IDE. You use SQL Server Workbench to manage SQL Server and create queries. Although you can use SQL Server Workbench to manage earlier versions of SQL Server, you can't use Enterprise Manager or Query Analyzer to manage a Yukon server.

As Figure 2 shows, SQL Server Workbench combines all the functionality of Enterprise Manager and Query Analyzer into a single tool. The Registered Servers window in the upper-left corner lists the available SQL Server systems. Double-clicking a registered server connects you to that server and opens up the Object Explorer window, which you can see in the lower-left corner. Object Explorer lists all the database objects. Unlike Enterprise Manager, which insisted on enumerating all database objects when it opened up, SQL Server Workbench is much smarter and lists only the objects that you expand in the tree, making it much more responsive for databases with numerous objects. In the right pane, you can create and debug T-SQL queries.

Another significant Yukon enhancement is SQL Server Workbench's ability to script administrative actions. You can save these scripts and replay them to repeat administrative actions. SQL Server Workbench uses the new SQL Management Objects (SMO) API to manage Yukon. SMO replaces the COM-based SQL Distributed Management Objects (SQL-DMO) object model.

8. Dedicated Administrator Connection
Another new administrative feature in Yukon is the dedicated administrator connection, which provides access to SQL Server, regardless of the server's current workload. The dedicated administrator connection lets you access the server and kill any runaway processes. You use the new command-line tool Sqlcmd to access this new feature.

9. XML Integration
Yukon provides a new level of unified storage for XML and relational data. Yukon adds a new XML data type that provides support for both native XML queries and strong data typing by associating the XML data type to an XML Schema Definition (XSD). In addition, Yukon provides bidirectional mapping between relational data and XML data. The XML support is well integrated into the Yukon relational database engine, which provides support for triggers on XML data, support for replicating and bulk loading XML data, and enhanced support for data access through Simple Object Access Protocol (SOAP).

The new XML data type is defined by using the new data type name of XML. You can use the XML data type as a column in a table or as a variable or parameter in a stored procedure. If the data stored in an XML column has no XSD schema, Yukon considers the data untyped. If an XML column has an associated XSD schema, Yukon checks the schema to make sure that the data stored in the column complies with the schema definition. In all cases, Yukon checks the data that's stored in the XML data type to ensure that the XML document is well formed before storing the document. The XML data type can accept a maximum of 2GB of data.

Another new XML-related feature in Yukon is XQuery Designer, a visual drag-and-drop design tool in SQL Server Workbench. Through XQuery Designer, you can use the XML Query Language (XQuery) to query XML documents stored in XML columns, standalone XML documents, and relational data.

10. Completely Rewritten DTS
In Yukon, Microsoft has rewritten Data Transformation Services (DTS) as managed .NET code. If you're a DTS expert, be prepared to relearn a completely new development tool. Yukon's DTS has a new object model and has been redesigned for better reliability and improved enterprise scalability. Internally, DTS has a new high-performance data pipeline. Yukon's DTS retains the ability to support any OLE DB–compatible database source and data sources other than SQL Server.

In addition to being revamped internally, DTS sports the new DTS Designer, which Figure 3 shows. Like the other Yukon tools, the DTS Designer runs inside SQL Server Workbench. To access the DTS Designer, you open a new SQL Server Workbench Project, select Data Transformation Project, and add a DTS Package to the project. The DTS Designer's View pane shows the available DTS tasks. To build a DTS package interactively, you first select the task, then select the desired Data Flow components from the Data Flow Items toolbox that appears. Microsoft has greatly increased the number of Data Flow components in Yukon. You use the connector arrows to connect the Data Flow components. After you design the DTS package, you can run and debug it with SQL Server Workbench's menu options.

11. SQL Service Broker
The SQL Service Broker is a new subsystem that adds asynchronous queuing support to Yukon. Asynchronous queuing is present in many other highly scalable applications, such as the OSs' I/O subsystems, Web servers, and even the internal operations of the SQL Server database engine.

Asynchronous queuing is an important factor for scalability because it lets a program respond to more requests than the platform might be able to physically handle. For example, if 10,000 users simultaneously request resources from a Web server that doesn't have asynchronous queuing, the Web server would be overwhelmed as it attempted to launch threads to handle all the incoming requests. When a Web server has asynchronous queuing, the server captures all requests in a queue. Instead of being overwhelmed, the Web server processes entries from the queue at its maximum level of efficiency. With asynchronous queuing, the Web server can effectively handle a far greater number of user connections than would otherwise be possible. The SQL Service Broker lets you build this same type of asynchronous scalability into database applications.

12. Notification Services
Originally introduced as a Web release for SQL Server 2000, Notification Services is one of the base subsystems in Yukon. Notification Services is a programming framework that lets you build applications that send notifications to a variety of devices in response to events that a developer defines in the system. Like many of the other new features in Yukon, Microsoft used managed .NET code to build Notification Services.

Notification Services applications consist of subscribers, events, subscriptions, and notifications. Subscribers are end users or applications that need to be notified of given events. An event is a change in a specific piece of information in which the subscriber is interested. Subscriptions define the information that will be sent to the subscribers, and a notification is the message that the subscriber receives when the event occurs.

13. Reporting Services
Reporting Services fills the last big hole—the inability to generate reports for the database—present in earlier releases of SQL Server. Out of the box, you can immediately use Yukon's Reporting Services to create reports based on relational data from SQL Server and other OLE DB–compliant databases or create reports based on OLAP cubes.

Reporting Services addresses all aspects of the reporting process, from design through deployment and delivery. For report design, it provides a graphical report builder with an integrated graphical query builder. Underneath the covers, the graphical report builder generates a Report Design Language (RDL) document. Based on XML, RDL is Microsoft's new report-definition language. For deployment, Reporting Services provides the Web-based Report Manager, which uploads reports to the server. After deployment, Reporting Services stores the reports' definitions in a SQL Server database. Report Manager also handles the delivery of reports. You can set rights that govern who can run a given report. You can also set options that determine the reports' format and subscription model.

Yukon Offers Even More Gold Nuggets
While Yukon has been a long time coming, Microsoft has added a significant array of new features. In addition to the major features I just described, Yukon offers many other gold nuggets, including 8-way clustering support, support for Non-Uniform Memory Access (NUMA), a new schema-based security model, a built-in HTTP server, the ability to expose stored procedures as Web services, and the ability to work with SQL Server 2000 Windows CE Edition databases. As you can see, Microsoft is delivering a gold mine of new features in Yukon.