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.

Free IT resource Open Source Business Conference (OSBC) May 22-23, 2007 Sponsored by OSBC Free IT resource Virtualization Insights from Top Experts - Learn how virtualization gets real! Sponsored by Dell Return to special report DOWNLOAD PDF Click here to download InfoWorld's special report Microsoft SQL Server Microsoft SQL Server 2005 Microsoft, microsoft.com Excellent  9.1 criteria score weight Manageability 9 25% Performance 9 25% Availability 9 20% Scalability 9 20% Value 10 10% Cost: Standard Edition starts at $5,999 per processor or $1,849 with 5 CALs. Enterprise Edition starts at $24,999 per processor or $13,969 with 25 CALs. Platforms: Windows Server 2003 Bottom Line: SQL Server 2005 vastly improves capabilities on all fronts, including development, integration, management, and BI. Companies will be able to run safer databases, better manage their environment, and finally create a truly 24/7 operation. Among new high-availability features, partial restores will allow databases to be brought online faster after failures, and database mirroring, although not yet officially supported, will provide automatic fail-over for log shipping scenarios. About our Reviews and Scoring Methodology

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.

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.