SQL Server 2005: Coming Down to the Wire
With slightly more than a month to go until launch, it looks as if SQL Server 2005 is finally a done deal—and that’s a big deal for BI pros.
- By Stephen Swoyer
- September 28, 2005
Microsoft Corp.’s Professional Association for SQL Server (PASS) conference takes place this week in Dallas, Texas—come hell or Hurricane Rita.
There’s a sense of anticipation among many of this year’s PASS attendees. After all, Microsoft’s SQL Server 2005 database is slated to “launch” in early November—and might even start shipping by the end of this year. And while next-gen SQL Server release dates have come and gone in the past, Redmond already has two public beta releases (“Community Technology Previews,” or CTP) under its belt. What’s more, more than 300,000 customers have snapped up Microsoft’s June and September CTP releases. So it looks as if the half-decade incubation of SQL Server 2000’s successor is finally coming to an end.
It’s enough to make many SQL Server pros giddy… unless they’re battling through the aftermath of two highly destructive hurricanes, that is. “I am trying to get to PASS 2005 to speak, and we have 4,000 New Orleans flood victims plus 8,000 Texas flood victims in a [Dallas] Metro area of 900,000 people, of whom [more than] 50,000 are [University of Texas] students. They are buying gas and water, blocking major highways, etc.,” e-mails Joe Celko, author of several SQL Server-oriented books, including Joe Celko’s SQL Programming Style.
Nevertheless, Celko and thousands of other SQL Server enthusiasts are on the ground in Dallas, hoping to hear the good word from Microsoft senior vice-president Paul Flessner and other SQL Server honchos. There’s a lot at stake. For starters, SQL Server 2005 is expected to deliver improved performance, availability, and reliability, along with several long-awaited features that should help close the gap with market-leading competitors IBM Corp. and Oracle Corp.
The next-gen SQL Server is also Microsoft’s most feature-rich database offering to date, boasting almost completely retooled business intelligence (BI) innards, including a revamped ETL capability (the new SQL Server Integration Services, or SSIS), enhanced OLAP and data mining capabilities, and a version 2.0 release of Microsoft Reporting Services. So there’s a sense in which SQL Server 2005 is Microsoft’s most consciously BI-oriented release to date.
Customers Seem to Know What’s in Store
Because of Microsoft’s lengthy SQL Server private and public beta programs, a large number of customers—more than 300,000, according to the software giant—have already gotten their hands on SQL Server 2005. But comparatively few of these have deployed SQL Server 2005 in production environments, experts say.
“I think that a lot of customers are already developing solutions using SQL Server 2005, but it's not my feeling that many of these have been deployed,” says Adam Machanic, a SQL Server Most Valuable Professional (MVP) and a database software engineer with a telecommunications and broadband services provider based in the Northeast. “Customers are aware, for the most part, that using beta software in production environments is dangerous. I do think there's a lot of interest in ramping up quickly—especially in the development community. But many large SQL Server users are huge corporations, and they take a more conservative view. Some will not upgrade for years.”
Nevertheless, Machanic and other SQL Server enthusiasts see much to like in SQL Server 2005. “By far, my favorite feature is Snapshot Isolation. This feature provides non-blocking reads and writes, while at the same time returning only consistent, committed data. This means that you get all of the benefits of so-called ‘dirty’ reads, which many shops exploit for increasing concurrency, and none of the drawbacks,” he says.
SQL Server MVP Erland Sommarskog—also en route to PASS—cites several key SQL Server 2005 improvements, such as improved error-handling, native support for XML data-types, a revamped system and database metadata implementation, common table expression, and native support for Microsoft’s Common Language Runtime (CLR)—along with, of course, snapshot isolation.
All things considered, says Malcolm Leach, a SQL Server programmer with Innovartis, a UK-based provider of change management products for SQL Server, next-gen SQL Server should formally close the gap with its competitive rivals. “I think the ‘gap’ has been entirely emotional for quite some time now,” says Leach, a self-described Oracle-to-SQL Server convert. “Sure, SQL Server used to be a poor cousin to Oracle and DB2 but it has been quite respectable in the TPC benchmarks recently, especially in terms of price/performance. With the advent of SQL 2005 I think Microsoft will really start to make a dent in [IBM’s] and Oracle’s market leadership.”
Of course, not all SQL Server pros are bowled over by some of SQL Server 2005’s purported “enhancements”—such as native XML and CLR. ( http://www.adtmag.com/article.asp?id=11148)
In fact, some SQL Server experts, such as author Celko, say that Microsoft has compromised key SQL Server 2005 improvements with these and other questionable design decisions. “It keeps getting closer to SQL-92, but at the same time it also adds a ton of proprietary [stuff],” argues Celko, who criticizes Microsoft’s move to native CLR, which should make SQL Server 2005 a more developer-friendly database platform. “Friendly is not always good,” he asserts.
BI Brilliance?
SQL Server 2005 is Microsoft’s most explicitly BI-ready release to date. To be sure, SQL Server 7.0 first incorporated OLAP and ETL capabilities, both of which were (more or less) unprecedented additions to the relational database stack; and SQL Server 2000 delivered improved OLAP and ETL, along with a first-generation enterprise reporting component; but SQL Server 2005 finally adds a measure of sophistication to Microsoft’s homegrown BI capabilities.
“Many are calling SQL Server 2005 the ‘SQL Server BI Release,’ and for good reason,” says MVP Machanic. “The Business Intelligence stack has been vastly improved from the SQL Server 2000 offerings. SQL Server Integration Services is an incredible ETL tool, and is getting a lot of attention from developers and DBAs. Comparatively, it makes Data Transformation Services [Microsoft’s former ETL entry] look like a child's toy. The other part that's getting a lot of positive attention is the Unified Dimensional Model, which will change the way SQL Server developers think about how to design dimensional data warehouses. I am very excited for both of these technologies.”
Chris Harrington, a principal with portal and Web application development house Active Interface Inc., concurs. Harrington says he is particularly taken with SQL Server 2005’s SSIS, which he likens to a quantum improvement over the erstwhile Data Transformation Services (DTS).
“By far, the most important feature is the evolution of DTS … into an enterprise-level ETL tool. Historically shops doing BI on the Microsoft platform were really in a bind. You either used DTS, which was really a toy—or you had to fork over some serious bucks for an ETL tool,” he argues. “With Integration Services, there is now a high-quality and right-priced option from Microsoft.”
Harrington also likes what he sees in the improved Analysis Services component. In the past, he’s built several very interesting analytic applications based on the XML for Analysis (XML/A) standard, and he says SQL Server 2005 brings even more to the table in this respect.
“The second most important or exciting feature is that now in Analysis Services there is a logical separation between a cube's definition [i.e., the schema] and the instantiation of this definition with data. Having the schema and the instance be the same thing [in SQL Server 2000] was just a real pain and dealing with that flawed architecture added hundreds of hours to BI project development.”
Peter Schott, a SQL administrator with automotive financing specialist Drive Financial Services, also lauds the revamped SSIS component. “SSIS is a major improvement over DTS and a lot of people will take a serious look at it before considering other ETL tools,” he says.
He’s less enthused about other SQL Server BI offerings, however, such as Reporting Services. “I think the enhancements will give people in BI something to look at, but I don't feel [Reporting Services] is really up to competing with major enterprise reporting systems at this time. It's getting closer, still improving,” he indicates. And Schott—like a number of users—says that with SQL Server 2005, there’s more to Microsoft’s freebie BI story than meets the eye. “Unfortunately, some of the tie-ins that seem the most valuable such as scorecards require SharePoint [Microsoft’s portal services offering], another server from MS that will add licensing and support costs.”
Author Celko also has a somewhat pessimistic take on SQL Server 2005’s BI fitness—particularly as a platform for an enterprise data warehouse. “Nobody in their right mind would use SQL Server for [data warehousing]. Look at Teradata, Sand and the real players in that niche. It is a nice product for its niche, but it is not the answer to all the database problems in the world,” he comments. And Celko, who has a background in statistics, is also critical of SQL Server 2005’s support for SQL-99 OLAP extensions. “I can use them, but I have a Masters degree in Math. The kids cannot write simple queries as it is.”