To call SQL Server 2000 a 40-kilogram weakling because it lacks certain high-end features is a bit like calling Hercules a wimp because he never ran a marathon. Not every strongman can perform every feat, and not every company needs every heavyweight feature. Many enterprises, large and small, have been running their businesses on SQL Server for years.

Still, there's no denying that SQL Server always fell short of Oracle Database when it came to true enterprise features, especially in the areas of high-availability and disaster recovery. Whereas Oracle met the demands of mission-critical, 24x7 operation with features such as online re-indexing, snapshot-based restores, and fail-over log shipping, SQL Server couldn't provide true zero-percent data loss without the help of third-party solutions. For customers who didn't need these protections, SQL Server offered a solid database with lower licensing fees. But maintenance-related downtime was a fact of life.

Until now. More than five years in the re-making, SQL Server 2005 not only addresses shortcomings in high-availability and disaster recovery, but brings sweeping improvements in almost every area. Management tasks have been streamlined and monitoring and diagnostics have been beefed up. A default locked-down configuration, data encryption, and other new features strengthen security. Analysis Services, Reporting Services, and Data Mining are now fully integrated and mature. DTS (Data Transformation Services) has been rewritten from the ground up, emerging as the more powerful and flexible SQL SSIS (Server Integration Services). Integration with Visual Studio and the .Net/CLR opens SQL Server internals to .Net developers and .Net to SQL developers, meaning the power of .Net can now be tapped for stored procedures, data flows and transformations, BI applications, and more.

SQL Server 2005 also promises better performance. I didn't run performance benchmarks for this review, but in addition to allowing DBAs to partition the database to balance load, Microsoft has done a lot of work to improve memory management, indexing strategies, and query optimization. Users will see the difference.

Many of the changes in SQL Server 2005 will take current SQL developers and DBAs some time to learn. One key high-availability feature, database mirroring, is for practical purposes still "beta". Microsoft still has work to do, especially in integrating and refining SQL Server's many tools. Microsoft's extreme makeover closes the gap between SQL Server and Oracle, however, and makes SQL Server a true contender even for very large databases and the most demanding enterprise environments.

A new way to move your data

SSIS (SQL Server Integration Services), the revamped set of tools for moving and manipulating data, not only provides greatly enhanced performance, reliability, and functionality over DTS, but also offers many new prebuilt tasks that reduce the amount of coding needed to perform the most common operations, including backing up databases, re-indexing tables, and running integrity checks. More important, SSIS completely changes the modelling paradigm that DTS uses, logically dividing its building blocks into separate control-flow and data-flow components.

Control-flow containers are graphical representations of operations that would otherwise take developers dozens of lines of code and many hours of debugging to create. Some of the operations you can perform with the new containers (namely Sequence, For Loop, and Foreach) can't even be done in DTS or would be too difficult to manage.

SSIS also extends the reach into different platforms, and not just different databases. For example, whereas DTS provided limited visibility into OLAP and practically none into data mining, these are natively supported in SSIS, meaning you can run data-mining queries, work with slowly changing dimensions, and send the data wherever you like, even to Reporting Services reports. In fact, SSIS serves as a bridge between SSAS and SSRS and any ODBC-compliant data source.

Package configurations are another huge enhancement. These allow you to define certain elements of the package -- file locations, database connections, special log-ins, and such -- to be read in from an external source (like an XML file) and applied at run time. This function lets you deploy packages very quickly because you don't have to change them to move them from one system to another; you simply change the dynamic elements in the configuration and the package will run on the new server.

Last but not least, the new .Net integration releases SSIS from DTS's dependence on the limits of VBscript and moves serious coders into the richness of VB.net. For this reason, file and string manipulations, math operations, and Win32 calls are much easier and faster.

As you would expect, the increased power and flexibility of SSIS come at the price of increased complexity. SSIS presents a learning curve that can seem daunting, but after you learn how to use it, SSIS will change the way your company thinks about its data. Systems that couldn't communicate before are now perfectly integrated and have the full power of .Net behind them. Complex data load operations into warehouses and disparate systems will take a fraction of the time to build, execute, and support. Other complicated tasks can also be performed much faster, which means deadlines will be met more easily and projects won't be held up because of the limited functionality of DTS and the way operations have to be taped together.