InfoWorld Gives Thumbs Up to SQL Server 2005

InfoWorld magazine's "Special Report" for January 16, 2006, "SQL Server's extreme makeover," begins with this summary:

"Microsoft's flagship database pumps up to enterprise class with high availability, slick disaster recovery, rebuilt integration services, stronger analytics, and other heavy-duty enhancements."

Contributing Editor Sean McCown, who wrote "SQL Server bulks up," the five-page review of SQL Server 2005 and its management tools, says:

"More than five years in the re-making, SQL Server 2005 not only addresses shortcomings in high-availability and disaster recovery, but brings sweeping mprovements 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.)"

He gives SQL Server a score of 9.1 (Excellent) with 9 out of 10 points for managability, performance, availability and scalability, plus 10 points for value. I would have awarded 10 points for managability, which has been SQL Server's forte since version 7.0 introduced the "Starfighter" management tools. Starfighter was the code-name for the first version of SQL Server Enterprise Manager implemented as a Microsoft Management Console (MMC) application.

McCown reviews new SQL Server Integeration Services (SSIS), which replace Data Transformation Services (DTS) and feature .NET Framework 2.0 integration. He points out some annoyances with SQL Server Management Studio (SSMS), but gave the new Data Management Views (DMVs) high marks for providing DBAs with management assistance, server statistics, and reports. Increased security with DDL triggers to prevent undesired schema changes, better password management, control over schema changes, and column/cell-level encryption also received a nod. (See my "Encrypt and Decrypt Data in Yukon" article from Visual Studio Magazine for a developer's eye view of SQL Server 2005's encryption/decryption processing.)

High-availability (HA) features and disaster recovery capabilities get high marks for mirroring, online restores, and page-level restores. Although McCown noted that mirroring was unsupported in the November 2005 release version of SQL Server 2005, Microsoft promises a supported version in 2006. Online indexing also contributes to HA.

A "Development Snapshot" graphic's Pros section outlines new SQL Server 2005 features for developers, such as CLR integration, new T-SQL constructs, and native XML data storage. The Cons include lack of IntelliSense for T-SQL queries, which Microsoft promised and then reneged, problems with an "unresponsive interface" for SSMS (which I haven't encountered), and more complex queries for DVMs than their "deprecated counterparts." A "Cool Tools for SQL Server Coders" graphic lists the most important T-SQL enhancements. See "Take Advantage of New T-SQL Features" and "Exploit Yukon's XML Data Type" for more details and downloadable Visual Basic 2005 source code examples for these new features. The article doesn't review new SQL Server Service Broker (SSSB) features or native XML Web services capabilities. (See my "What’s New With VS and Data" interview, forthcoming Visual Studio Magazine article on SSSB and August 2005's "Create Interoperable Native Web Services.")

McCown gives SQL Server 2005’s business intelligence (BI) tools—SQL Server Analysis Services (SSAS), SQL Server Data Mining (SSDM), and SQL Server Reporting Services (SSRS)—a test drive and finds them "finally designed to work together to answer your intelligence needs." SSRS's Report Builder gets praise for its Report Model, which lets administrators define the data available to user-defined reports.

The review concludes with a rehash of what's missing in SQL Server 2005—Intellisense for T-SQL queries, a centralized code repository, and lack of a separate extract, transform, and load (ETL) server—and advice about if and when to upgrade to SQL Server 2005. McCown's closing remarks, however, say it all:

SQL Server 2005 is an excellent release that will gain Microsoft some real respect in the high-end database market. SQL Server’s tools now lead Oracle and DB2 in pretty much every area—management, monitoring, and especially development—and of the three vendors, Microsoft seems to be the most concerned about improving them.

About the Reviewer:

Sean McCown is a Contributing Editor for InfoWorld magazine and a frequent contributor to SQLServerCentral.com as well as the SQL Server Worldwide Users Group. He also writes the occasional product review for Computerworld and article for SQL Server Standard. His April 2004 "Databases flex their XML" review for InfoWorld drew this detailed response from Michael Rys, Microsoft's point man for SQL Server 2005's XML data type.

You can read Sean's book reviews on database programming, database and Windows security, and general database topics at his ITBookworm site, and read his advice for DBAs and DBA "war stories" on his DBA Rants blog. Sean is the Senior DBA for SourceCorp of Dallas, Tx, where he lives with his wife and two kids. He's practiced and taught Kenpo for the past 20 years. [Kenpo competency is very useful for DBAs, especially Senior (supervisory) DBAs.]

Sidebar: Taking the Database Beyond SQL

Tom Yeager, InfoWorld's Test Center chief technologist and new-technology blogger begins his analysis with this paean to integration of the .NET Framework 2.0 with SQL Server 2005:

Through integration with .Net, SQL Server 2005 takes its rightful place as an active peer, not a detached agent, in large-scale distributed applications. ... SQL Server 2005’s .Net integration creates a dream scenario in which SQL developers can incorporate the advanced functionality of .Net in their stored procedures, while C#, Visual Basic, and even JavaScript developers can work with databases as though they were language extensions.

But .NET integration isn't limited to creating stored procedures with native code; you can also write user-defined functions (including table-valued functions), user-defined aggregates, triggers, and—if you have the courage—user-defined data types with Visual Basic 2005 and C# 3.0. (JavaScript isn't a supported language for creating SQL Server objects.)

.NET might be the best thing since sliced bread (or Java integrated with competitors' RDBMSs), but native code running in the SQL Server 2005 process has the potential to be the most misused feature in this release. (See the "When to Use SQLCLR" sidebar to my November 2005 "Manage Data With VS 2005" Visual Studio Magazine article.

Ahead of the Curve: SQL Server could make 'better together' ring true

Tom Yeager says, "Windows [2003] Server [R2], Visual Studio [2005], and SQL Server [2005] may impress even hardened skeptics," and goes on to explain why "reality-based" is a better marketing slogan than "better together" for Microsoft's newly upgraded server systems and developer tools. Yeager contends that testing by Microsoft's IT department and tens or hundreds of thousands of independent beta testers creates a truly "reality-based" server suite.

Database Underground: An Answer to Open Source

Sean McCown's December 11, 2005 blog post argues that SQL Server and Oracle "freeware" (Express Edition) versions of their proprietary relational database management systems (RDBMSs) are more suited to mission-critical applications than "open-source" databases like MySQL and PostgresSQL. (See "Oracle Releases Free 10g Express Edition Beta," October 2005). McCown sees advantages to Express Editions' maturity, performance monitoring, and well-travelled upgrade path.

McCown didn't make an important argument in favor of Express Editions: There is little or no relief from vendor lock-in with "open-source" databases. Migrating databases from MySQL to PostgreSQL or—heaven forbid—HSQLDB is likely to be far more difficult than moving to and from Oracle, SQL Server, DB2, or Sybase. The commercial RDBMS vendors provide tools to accomplish the transation; with an "open-source" database, you're mostly on your own.

McCown wants Microsoft and Oracle to remove the 4-GB size limits from their Express Editions in the hope that doing this will induce Web site owners to migrate from "open-source" to "proprietary" databases. It seems to me that Microsoft and Oracle are delivering much a more useful and stable product than MySQL or PostfresSQL—the two major players in the open-source database market—and neither vendor charges for support. Microsoft provides an Express version of SSMS to handle simple database chores in a Windows UI. Bear in mind that the size limit is per database, not per server, and you can run many named instances of SQL Server Express on a single computer running Windows XP or 2003 Server. (See my May 2005 "Migrate to SQL Server 2005 Express Edition" Visual Studio Magazine article.)

Editor's Letter: SQL Server 2005 leaves open source databases in the dust InfoWorld editor Steve Fox supplies a pointer to Sean McCown's December 28, 2005 review of MySQL 5.0, "Long-awaited MySQL 5.0 makes its debut," and quotes McCown as referring to DBAs that use "open source" databases as “'tree huggers' who don’t want to pay for commercial software." The woods are full of folks who subscribe to the "software should (or must) be free" theory, so it's unlikely that this opinion piece will change many "open-source" proponents' minds. However, McCown's An Answer to Open Source might convince rational DBSs to at least consider an Express Editions of a commercial RDBMS. Note: eWeek's Lisa Vass wrote a similar column titled "MySQL vs. the Lite Databases: A Fair Comparison?" that appeared December 29, 2005. She published a related column, "Readers Respond to MySQL, Lite Databases Comparison," the following week. This second column described how readers took her to task for omitting PostgreSQL and Firebird from the initial comparisons. Technorati: Databases Database SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Express SQL Server Express SQL Express Oracle Express Oracle 10g Express MySQL PostgreSQL P.S. In honor of Martin Luther King, Jr.'s birthday, celebrated Monday, January 16, 2006, I've finished a major update to my Squidoo lens (Web page) about The Black Scholar: Journal of Black Studies and Research, its history, editors and contributors.