Exploring Yukon Territory

With the Yukon release of SQL Server now in private beta, SQL Server Magazine talked with Microsoft's Eric Brown about the release's long-awaited development and administration enhancements and how DBAs and developers can start preparing for the changes ahead. Brown, a SQL Server Group product manager who has worked with the database system since SQL Server 7.0 launched in 1999, hits the Yukon high spots, including Common Language Runtime (CLR) integration, the new SQL Workbench management tool, and business intelligence (BI) enhancements—all designed to make SQL Server the most comprehensive and integrated relational database management system (RDBMS) available.

What are the most significant changes that SQL Server Yukon will bring to the lives of SQL Server DBAs and developers?

We've made significant changes to the SQL Server subsystems by adding new technology in the areas of distributed applications, business intelligence (BI), high availability, manageability, and .NET development. Yukon will be a major advancement for both developers and DBAs because of these new features and because of the integration that Yukon has with other Microsoft technologies such as Windows Server and Visual Studio.

In addition, when we presented the new SQL Server Reporting Services technology in February at the Yukon Technical Preview, our customers were excited about the report creation and distribution innovations we were working on. Microsoft in general and the SQL Server team specifically are very focused on the SQL Server community. And we're responding to this overwhelming customer demand for Reporting Services by shipping the first version as a SQL Server 2000 add-in this year. Yukon will then ship with Reporting Services built into it. Anyone interested in trying out Reporting Services now can get the beta at http://www.microsoft.com/sql/evaluation/betanominations.asp.

We've continually evolved the SQL Server feature set to help match the varied needs and requirements of our customers. With Yukon, we are working hard to incorporate feedback from customers and to offer a substantive upgrade that will position SQL Server as the single source for meeting all our customers' database-computing needs.

How important is Visual Studio .NET integration and knowledge to the success of the Yukon release?

The next release of Visual Studio .NET, code-named Whidbey, includes deep SQL Server integration and is scheduled to ship at the same time as Yukon. This release of Visual Studio and the .NET Framework hosting technology provides the powerful new Common Language Runtime (CLR) capabilities that developers can use to better leverage object-oriented programming skills in the database tier.

T-SQL is the best query language for relational databases and always will be. But our goal in integrating Yukon with the CLR is to allow customers to use the .NET Framework class libraries within the database and use CLR languages such as Visual Basic .NET and C# to write database code as needed. With Yukon, the SQL Server database and its requisite developer tools will ship simultaneously.

Microsoft has talked a lot about its .NET development efforts in Yukon, but how do you position T-SQL in the product? And to what extent will Yukon comply with the ANSI SQL-99 standard?

T-SQL is still and will remain SQL Server's core query language. In our development of SQL Server Yukon, we didn't pursue ANSI SQL-99 core compliance simply for the sake of compliance. Instead, we've chosen to implement features that are in line with feedback we've received from customers. When such features become standard and useful to customers, we try to bring them into our products so that they're as standards-compliant as possible. We'll be adding the following SQL-99 features in Yukon:

Recursive queries (WITH )

Separate date and time data types

Ranking functions and windowing, according to OLAP extensions in SQL-99

Among our many other T-SQL enhancements, we've also added TRY...CATCH constructs for handling errors, a native XML data type, new operators for pivoting results, an improved TOP function, and more functional and flexible event-notification capabilities.

What key enhancements are you providing for database administration in Yukon?

We've worked hard to enhance SQL Server's administration features to ensure that there are very few system optimizations that will require taking SQL Server offline. We've examined how to reduce planned downtime to make recovery faster. And we've incorporated new functions such as a dedicated administrator connection. We've also made significant enhancements to our reliability and availability features and overall security.

We've enhanced replication in Yukon, expanded SQL Server's self-tuning capabilities, and enhanced SQL Server Profiler. But one of the biggest changes for DBAs will be the replacement of the server-based Enterprise Manager tool with the new client-side management console code-named SQL Workbench.

The Workbench provides a single, integrated administration and development environment for relational, OLAP, and mobile databases. The Workbench features integration with Analysis Services, Reporting Services, and Visual SourceSafe, and you can use it to develop T-SQL, XML, and MDX code. The Workbench, which incorporates IntelliSense functionality, is essentially a one-stop shop for your database development, deployment, and management needs. Integrated with Visual Studio .NET's Whidbey release, the Workbench represents an effort to provide the DBA with the best toolset possible, from designing databases to debugging to deployment. For DBAs, the new toolset will be a big change from Enterprise Manager—but a good change, especially in terms of productivity.

We've also created a new management language called Server Management Objects (SMO), which will replace SQL DMO over time. SMO is easy and flexible to use, and you will be able to program against SMO by using the .NET environment.

Currently, 24x7 environments have a hard time dealing with index maintenance. When users have very little opportunity for scheduled downtime, rebuilding indexes isn't an option, and online index defragmentation doesn't produce the optimal results. Are you addressing this issue in Yukon?

As part of our mission to eliminate downtime, we will ship SQL Server Yukon with online index creation and maintenance. By turning on the T-SQL index option create index with online, the indexes can be updated with each change to the indexed structures. We've also made the dropping and altering of indexes an online option.

Microsoft has rewritten Data Transformation Services (DTS) in Yukon and will likely give the extraction, transformation, and loading (ETL) tool a new name. What enhancements does this revamp bring?

Yes, as part of our BI enhancements, SQL Server Yukon will ship with a new DTS toolset that is enterprise ready. The upgraded DTS will have a completely new architecture that provides both performance and programmability, making it more reliable and easier to use than the current version of DTS. The new toolset will feature a high-performance data pipeline and a highly reliable execution engine. The revamped DTS will also be readily extensible with native and managed-code language capabilities. In addition, we'll provide new out-of-the-box complex transformations, data cleansing and analytics, and tight BI integration with Analysis Services, data mining, and the new Reporting Services. Yukon will truly provide an end-to-end BI solution.

What can we expect in terms of improvements in XML and Web services functionality?

As I mentioned earlier, Yukon includes a native XML data type for simplifying the storage, management, and retrieval of XML data. Yukon supports XQuery for retrieving XML data and XML Schema Definition (XSD). Yukon also will feature native HTTP connectivity, so you'll be able to run Web services natively on a SQL Server instead of having to run Web services on IIS on the middle tier.

What kind of performance improvements will Yukon bring?

We've made big investments in scalability with Yukon. Two key features will enable users to realize faster performance. The first feature, table partitioning, will essentially let you divide a table into smaller chunks. The second feature, snapshot isolation, when turned on lets client connections read a consistent view of the data based on the connection time. For database applications that handle high transaction volumes and support many concurrent users, customers who use these features will see faster performance. In addition, Yukon will be available in both 32-bit and 64-bit editions.

What is Microsoft doing with Yukon to improve security and reduce buffer-overflow and other kinds of security vulnerabilities in SQL Server?

Yukon significantly enhances the database platform's security model with the goal of providing more management and security options for both developers and administrators. We've made a considerable investment in a broad spectrum of features—from enforcing policies for SQL Server login passwords in the authentication space, to providing for more granularity in specifying permissions at various scopes in the authorization space, to allowing for the separation of owners and schemas in the security-management space.

Furthermore, for the Yukon release, we're implementing core security tenets such as reduction in surface area and the principle of least privileges as well as sound principles such as secure defaults and designs.

As the CLR, the SQL Workbench, and other client-side services cross the traditional line between "database professional" tasks and "programmer" tasks, what should SQL Server professionals and application developers do to prepare for this new model of managing data?

At the outset of our work on Yukon, we really didn't know how DBAs would react to having procedural-logic capabilities in the database or to mastering a new toolset. But in working with customers and beta testers, we believe that the new functionality will bring valuable new capabilities and flexibility—not to mention productivity enhancements—to SQL Server professionals. What helped me personally when I first started working on Yukon was to install it, then read as much as I could about the .NET Framework and about Visual Basic .NET and C#. I also spent time reading about XML, XSD, and XQuery on the World Wide Web Consortium (W3C) Web site at http://www.w3.org. Then, I built some simple applications to see whether I could solve some specific problems. I created an XML database that stored Microsoft Office documents, and I tracked my cycling performance using a user-defined data type and the .NET Framework. This hands-on approach gave me a lot of insight into how the .NET integration features work. Microsoft will also be producing many best-practices documents, how-to white papers, and other guides to getting started with the CLR, the SQL Workbench, and other new tools. This is also a great time for the database and application programming teams to start talking about the needs of their organization and how they can use Yukon and .NET to meet those needs most effectively.

When should customers expect the public beta of Yukon and the release to manufacturing (RTM) version?

We are currently in a private beta (Beta 1) and expect to release the public beta (Beta 2) in the first half of 2004. A third beta will come later next year, with RTM scheduled for the second half of 2004. Pricing and packaging haven't been determined yet.