SQL Server bulks up

To call SQL Server 2000 a 90-pound 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, 24/7 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 (see “Taking the database beyond SQL”).

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, 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 modeling paradigm that DTS uses, logically dividing its building blocks into separate control-flow and data-flow components.