Beta Man

At Long Last: SQL Server 2005

Well worth the wait, Beta Man looks at what can be expected with the next version of SQL Server.

• By Don Jones
• 08/01/2005

SQL 2005 also breaks new ground with respect to Microsoft's typical product testing and release cycles. After two beta cycles, Microsoft skipped Beta 3 and may not put out a Release Candidate of SQL 2005. The database is instead progressing through a series of Community Technology Previews (CTPs). The June 2005 CTP at TechEd was the first time Microsoft made a preview available to the general public. The company also formally announced an early-November final release for SQL 2005. I wonder how many marketing folks Microsoft had to sedate to keep them from renaming it SQL Server 2006.

Under the Hood
There will be quite a range of different editions of SQL 2005: Express, Workgroup, Standard and Enterprise (plus evaluation editions, a developer edition and select others). The Express Edition replaces the old Microsoft Database Engine (MSDE). It supports one CPU, 1GB of RAM, 4GB databases and there's no native 64-bit edition.

As the name implies, the Workgroup Edition is intended for small departments and workgroups. It increases processor support to two, pushes the RAM limit to 3GB (all that Windows with AWE memory extensions will support) and removes the database size restrictions. However, SQL 2005 Workgroup will only be released for 32-bit Windows. Standard Edition supports four processors, has no memory or database limits, and will be released in both 32-bit and x64 editions.

The Enterprise Edition adds parallel indexing and partitioning capabilities and removes the processor limit. It's intended to run on Enterprise Editions of Windows, although that's not a requirement. Enterprise also adds online indexing, online page and file restoration and faster database availability during recovery scenarios. Both the Standard and Enterprise editions support database mirroring and failover clustering for high availability.

SQL 2005 Standard and Enterprise editions run on all server editions of Windows 2000 or later with the exception of Windows Server 2003 Web Edition. (For full details see http://microsoft.com/sql/evaluation/sysreqs/2005/default.mspx.)

SQL 2005 makes a great first impression (see Figure 1). The new SQL Server Management Studio is a welcome evolution of the old SQL Enterprise Manager console. It has a tabbed interface (now why can't Internet Explorer get that?) and a built-in query tool, effectively replacing Query Analyzer as a standalone utility.

Figure 1. SQL Server 2005 will sport a tabbed interface and a built-in query tool. (Click image to view larger version.)

The fact that queries can now co-exist with administrative information in individual tabs (see Figure 2) is a huge improvement. The team behind Management Studio has done a bang-up job.

Figure 2. Queries and administrative information can now co-exist in individual tabs. (Click image to view larger version.)

Another major change is that SQL 2005 now includes the .NET Common Language Runtime (CLR) as a core part of the SQL Server engine. This means you can write SQL Server objects—like triggers and stored procedures—in managed .NET languages like VB.NET and C++, in addition to SQL Server's native Transact-SQL (T-SQL) language. In fact, Microsoft developed SQL 2005, version 2.0 of the .NET Framework and Visual Studio 2005 in parallel with massive interaction between the respective development teams. They're practically three aspects of one über-product.

Administrative Assets
From an administrator's perspective, SQL 2005 rocks. Database mirroring—a new quasi-replication feature—is intended to keep a "hot spare" server ready to go. In fact, it works a lot like RAID disk mirroring, with the "mirror" database continuously updated and ready to step in should the principal fail. It's like clustering in several ways, but is configured per-database, meaning each database could potentially have a different mirror server, or that one "hot spare" could act as a mirror for multiple "principal" servers. It still supports regular Windows Cluster Service as well, but database mirroring actually provides a redundant copy of the data, which Cluster Service does not (as Cluster Service protects against hardware failures—not data failures).

Another nice touch is that SQL 2005 provides a dedicated administrative connection, ensuring that you can always connect to the server—even if SQL Server Agent has snatched up the only available connection on a recovering, single-connection server (a common scenario). This special connection is available only through the SQLCMD command-line utility, but you can use it at the server console or remotely. Speaking of recovery, SQL 2005 now lets you rebuild, create and drop indexes without taking the table being indexed offline. You can also perform restore operations online, which improves availability of SQL Server. Only the data actually being restored is unavailable.

Security also had a big overhaul in SQL 2005. SQL Server logins now have password policies—a feature conspicuously absent from earlier versions. You can make permissions more granular, and specify an independent set of credentials for any given database module. SQL 2005 also supports native in-database encryption, making it easier to protect sensitive data from physical attacks against the database files themselves. Client/server communications are encrypted by default, and you can set the server's policies to reject unencrypted communications.

The Integration Advantage
Microsoft's big competitive advantage right now is the integration of the .NET CLR within the SQL 2005 engine. By letting developers work within a single managed language from the client through to the server, they can take a more end-to-end approach.

In the past, many companies had to employ client developers as well as specific database developers. SQL 2005 brings those two roles much closer together. The insanely tight integration with Visual Studio 2005, which will launch simultaneously with the database, makes SQL 2005 a full development environment. It will truly make developers more productive.

SQL 2005 offers significant, demonstrable performance improvements across the board, coupled with major improvements in manageability, availability and scalability. The new editions, particularly the small business- or departmental-friendly Workgroup Edition, will make SQL 2005 accessible to a much broader range of businesses.

Throw in the vastly improved development environment created by the tight integration with Visual Studio 2005 and you'd have a tough time finding reasons not to move to SQL 2005. Microsoft has had a long time to look at how SQL 2000 was used in businesses of all sizes, and they've clearly applied some of those lessons to the design of SQL 2005.

Cost will probably be the biggest hurdle to deploying SQL 2005. Most organizations that purchased SQL 2000 with Software Assurance (see "SA Exposed," Redmond, April 2005) did so years ago, and won't be getting a new version for free as they probably expected.

Pricing aside, there's no good reason not to get SQL 2005 up and running very soon. While the lion's share of new features and improvements is targeted toward developers, administrators have definitely not been left out in the cold.