Finding Gold in Yukon

Not your father's SQL Server, administrators will find comfort in Yukon, while developers are left out in the cold.

• By Don Jones
• December 01, 2004

What excites me the most about SQL Server 2005 is looking back to see how far the SQL Server platform has come in the last few years.

After releasing SQL Server 7.0 in 1998, Microsoft quickly followed up with SQL Server 2000 (using the model-year naming scheme that has since become standard for its server products). While SQL7 was almost a complete rewrite, SQL2000 was a less dramatic upgrade—viewed by many as including the features that didn't make it into SQL7. Since then, aside from a few features released over the Web (many of which added XML functionality) and three Service Packs, all has been quiet in the SQL Server world.

SQL Server 2005—code-named Yukon—is easily the most talked-about version of SQL Server to date. Based on an early version that I saw, Microsoft has been listening to database administrators' and developers' requests. The company is adding new development options, troubleshooting tools and reliability features. Some of these features stem from a deep integration with the second version of the .NET Framework and Visual Studio 2005, which actually may put off some companies that aren't ready to adopt those new products. Also, the sheer stability of the current SQL Server 2000 platform and the unknown quantity that SQL 2005 represents will likely make for a long rollout process—not unlike what we saw with WinXP.

Early reports in 2003 positioned Yukon as a major part of the future Windows OS, serving as the database engine for everything from Active Directory to Exchange Server. In that context, SQL 2005 will be a more modest release. The project to switch Exchange to a SQL back end, for example (code-named Kodiak), has been postponed indefinitely. SQL 2005 will "only" be a relational database management system (RDBMS), albeit a darn powerful one.

Microsoft SQL Server 2005 Code-name: Yukon Version reviewed: Express Edition, Beta 2 Current status: Beta 3 due by year-end Expected release: Mid-2005

Now in Several Flavors
We'll see SQL 2005 in both 32- and 64-bit versions, running on most new versions of Windows. It's likely we'll see an Enterprise Edition, which will support clustering. There is already a download of SQL Server 2005 Express available. This is the edition I saw. Express is designed for easier installation and simpler application management. It includes lightweight management and query tools, for example, and more Wizards. It's also completely free, like the current Microsoft Database Engine (MSDE), also called SQL Server Desktop Edition. You can install Express on anything later than Windows 2000.

SQL 2005 Express offers a great preview of what the full SQL 2005 will look like. The SQL Computer Manager snap-in (see Figure 1) is a vastly trimmed-down version of the full administration tool set included with other SQL 2005 editions. It's better than no tool, which is what the MSDE comes with, but not by much. All you can do is simple service management (like stop, start and pause), network protocol configuration and other basic tasks. For more complex tasks like creating new databases, you'll need the full management tool set that comes with the other editions of SQL2005.

Figure 1. The SQL Computer Manager snap-in that comes with SQL Server 2005 Express is a trimmed-down administration tool set that offers only basic management tasks. (Click image to view larger version.)

Interestingly, you can install SQL 2005 alongside SQL 2000 as a separate instance. This allows for some pretty creative migration and co-existence scenarios and gives you the flexibility to run both on a test box to make sure your applications are SQL 2005-compatible.

Powered by .NET
SQL 2005 requires version 2.0 of the .NET Framework, which is itself still in the Beta 1 phase, and includes massive .NET and Visual Studio 2005 integration. For example, SQL 2005 embeds the .NET Common Language Runtime (CLR), which lets developers create database objects in .NET languages like C# and VB.NET, rather than the traditional, native Transact-SQL (T-SQL) language. This capability is a major upgrade for developers, who will be able to leverage a single set of programming skills to create database applications. However, if you're not already a Visual Studio shop (which is probably unlikely if you're using SQL Server), this new integration could be a disadvantage. Unlike previous versions, SQL 2005 needs Visual Studio 2005 (also still in beta) to get at the stellar development capabilities.

Through a series of Web releases, Microsoft enhanced and expanded SQL 2000 to include significant XML functionality, including the ability to shred XML into relational data structures, accept datagrams and perform XML-based queries. SQL 2005 will come with all of these features and more built-in, making it a more flexible tool for developers creating XML-centric applications. With XML becoming the underpinning of practically everything—I think even my Jeep's onboard computer uses it—having XML built into SQL Server is definitely a good idea.

Developers will also pick up the usual new-version enhancements to T-SQL, including error handling, recursive queries, a ROW_NUMBER function, a PIVOT relational operator and more. Reporting Services, an add-in for SQL 2000, is built into SQL 2005 and will be tightly integrated with Visual Studio 2005. Reporting Services is Microsoft's long-overdue way for applications to include robust reporting capabilities without resorting to third-party controls like Crystal Reports (which Microsoft has bundled with its development products for years). Reporting Services even provides a scale-up path by importing reports created in Microsoft Access, giving developers an easier way to migrate an entire Access application to SQL Server.

Additions for Administrators
Administrators will appreciate a new feature called database mirroring, which lets you create a standby server that receives regular database updates. It's a sort of non-clustered failover mechanism, and an extension of SQL 2000's log shipping features that you could use to set up a similar standby scenario.

Online restore is another welcome feature, allowing a complete restore to another instance of SQL Server, while that instance is running. During an online restore, only the data actually being restored is unavailable to users. Any other data hosted by the current instance will remain available, improving overall system uptime.

The new management features tie together in a new user interface, the SQL Server Management Studio, which builds on previous versions' Enterprise Administrator MMC snap-in. The Studio provides the ability to deploy and troubleshoot SQL Server, as well as more traditional day-to-day management tasks. To ensure that administrators are always able to manage their SQL Server boxes, Microsoft added a dedicated administrator connection. In the past, you could lose all connections with SQL Server. For example, in recovery mode, only a single user connection was provided. If you configured SQL Server Agent to start automatically, it would grab that connection and leave you, the administrator, out in the cold. This new "private line" to SQL Server ensures that you always have a way in, even if the server is in a highly restricted mode.

Beta Man's Routine Disclaimer: The software described here is incomplete and still under development; expect it to change before its final release--and hope it changes for the better.

Security Check
Microsoft's security folks clearly had a hand in SQL 2005. While SQL Server has always had fairly straightforward, robust security capabilities, it now supports encrypted databases, more secure default settings, password policies for SQL-type logins (which have always been able to ignore a company's password policies) and more. What hasn't changed is that bugs will exist, and patches will be released to correct them. To address that issue, Microsoft built an Automatic Updates-like feature into SQL 2005, leveraging Windows Update version 5's ability to support server and desktop applications, in addition to Windows itself. In other words, SQL 2005 will download and install its own patches if you let it.

Microsoft doesn't release products that don't have some kind of Internet gimmick built-in, and SQL 2005 is no exception. SQL 2000 picked up native Web capabilities through one of the first Web Releases, adding the ability to query data via HTTP. SQL 2005 builds on that with the addition of an HTTP listener (essentially an embedded Web server) and the ability to host Web services applications within the database tier.

Making SQL Server a Web services platform from the get-go is definitely a cool idea, but it's not without security concerns. I'm all in favor of making it easier to write great applications, but I have a bad feeling about exposing SQL Server through HTTP, simply because everything that's been exposed through HTTP in the past has wound up being a major security flaw. I'm not clear on how much SQL 2005 leverages the enhancements in IIS 6.0 that address these issues, or what HTTP technology it uses when running on Win2000 (which does not support IIS 6.0).

All Your Betas are Belong to Us! Beta Man is always on the lookout for quality products to review. If you know of a software product that is currently or soon to be in beta, contact Beta Man at [email protected]. Vendors are welcome, but please act early–the meticulous Beta Man needs plenty of lead time.

Not Your Father's SQL Server
SQL Server is growing up again. The enterprise-class features for backup, recovery and reliability will be much appreciated in SQL 2005, as will the new streamlined administration environment. Developers will, I suspect, either love or hate the heavy Visual Studio 2005 integration, depending on how much they like Visual Studio as a product and on how likely they are to convince their boss to buy the new 2005 version when it comes out (which is, I suspect, an integral part of Microsoft's overall strategy). The security enhancements are nice to see, especially the addition of password policies for built-in SQL Server accounts. Microsoft finally seems to be admitting that these accounts have a place in the universe and deserve some management and security features.

Administrators will probably feel more immediately comfortable with SQL 2005 than developers. While administration has evolved considerably in this version, development has taken a quantum leap forward. That also means a steep learning curve for new features. Developers who haven't moved to .NET development are definitely left out in the cold with SQL 2005, with its deep .NET Framework integration and its use of Visual Studio 2005 as practically a native development environment. Granted, SQL 2005 will work fine without Visual Studio 2005, but it won't be as much fun.

By now, SQL 2000 is definitely last-generation—a six-year-old set of technologies with XML patches tacked on. Of course, SQL 2000 is also incredibly stable and benefits from the resources available to a mature, well-understood product. But SQL 2005 should definitely be on your radar. I suspect most companies will find themselves migrating by mid- to late-2006.