TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

The (Steep) SQL Server 2005 Learning Curve

Some customers still haven’t deployed SQL Server 2005 in production environments. Their rationale? There’s a heckuva lot to digest

• By Stephen Swoyer
• November 1, 2006

Just one year ago, Microsoft Corp. formally unveiled its SQL Server 2005 database. Next-gen SQL Server was a long time coming: five years (and more), in fact. It introduces a raft of changes, especially on the BI front. With retooled Reporting Services, Analysis Services, and Integration Services components, SQL Server 2005 presents a steep learning curve for even seasoned SQL Server data management pros.

It shouldn’t come as a surprise, then, that customers have taken their time with SQL Server 2005. In the past—with Microsoft’s Windows 2000 Server and Windows Server 2003 releases, for example—enterprise shops waited for Microsoft’s first Service Pack release before seriously pursuing deployments. But SQL Server 2005 Service Pack 1 shipped in April, a scant five months after that database first became generally available. As a result, some customers still haven’t deployed SQL Server 2005 in production environments.

“We only recently began testing SQL Server 2005. I have been working on the Reporting [Services] side of the testing, but given our limited resources in terms of people, it’s not been a path we have been aggressively pursuing as of yet,” confirms Topher Thiessen, a manager of metrics and analytics at a subsidiary of a prominent lending and mortgage firm. Thiessen says his employer is “completely dependent” on SQL Server 2000, which it uses to power most of its core operational systems. Perhaps for this reason, he acknowledges, his company has gingerly approached its SQL Server 2005 deployment.

“I would say our approach is very cautious, and we… were waiting for the first service packs to come out to address the first release bugs before moving forward with any serious company-wide testing,” he indicates.

Even so, there’s plenty of demand for SQL Server 2005—along with SQL Server 2005-oriented BI skills. Just ask Adam Machanic, a SQL Server MVP (“Most Valuable Professional”) and an independent consultant who specializes in SQL Server deployments. The upshot? Savvy shops might be taking a slow-and-steady approach to SQL Server deployment, but most Microsoft-oriented technology shops are at least in the planning stages of their SQL Server 2005 deployments.

“All of my work this year has been with SQL Server 2005 upgrades or implementations, and although I don't focus on BI much, I've been getting such a tremendous number of requests that I'm going to have to start working more in that area—it's clearly an extremely hot topic in SQL Server and the database world in general at the moment,” comments Machanic.

This is in spite of the fact, he says, that a lot of customers don’t fully understand how much has changed between SQL Server 2000 to its next-gen successor. “A lot of customers think that upgrading from SQL Server 2000 to SQL Server 2005 will be a quick insert-the-CD-and-press-Next-a-few-times experience. This could not be further from the reality of the situation,” he confirms.

One big change is query performance, Machanic explains: customers shouldn’t expect queries to behave as they did under SQL Server 2000. “[Q]uite a bit changed between SQL Server 2000 and 2005, and clients seem to notice this most readily when they discover—much to their surprise—that many of their queries no longer perform as well in SQL Server 2005. This doesn't mean that SQL Server 2005 doesn't perform as well as SQL Server 2000; rather, the queries fail to perform as well because the rules have changed a bit, and the queries must be upgraded to properly work with SQL Server 2005.”

That could be one reason why a lot of shops are still waiting to take the full-bore SQL Server 2005 plunge, Machanic speculates.

“I've been stressing a methodology of heavy pre-deployment testing for my clients, and that has allowed me to catch and fix most problems before they ever saw the light of production,” he concludes. “All in all I'm quite happy with SQL Server 2005, but many people I talk to are shocked by the apparent performance issues and do not understand how to remedy them.”

On the other hand, some SQL Server pros, including consultant and OLAP specialist Andrea Vincenzi, say they’ve been surprised by the scant SQL Server 2005-based BI work in the months since Microsoft shipped SP1. “I expected to work more with SQL Server 2005 in the last year, but I only had one project until now and the volume of data in that particular data warehouse was quite small,” Vincenzi indicates.

In last week’s quarterly earnings announcement, Microsoft reported 30 percent year-over-year growth for SQL Server, but that tally also includes sales of SQL Server 2000, which—even last year, ahead of the SQL Server 2005 launch and five years after its own release—remained a strong performer, consistently growing its market share from a quarter-to-quarter and year-to-year basis.

This isn’t to say that SQL Server 2005 is a disappointment, SQL Server pros say—just that it’s a very different animal from its predecessor. It’s also indicative of Microsoft’s own success, especially in the enterprise: Global 2000 customers don’t rip-and-replace mission-critical platforms on a whim. And while upgrading SQL Server might have been an only slightly trivial proposition in the days of SQL Server 6.5 or even SQL Server 7.0, SQL Server migrations are a lot more complicated—and typically have a lot more riding on them—now.

This is doubly true when you consider all of the new capabilities—and possibilities—SQL Server 2005 brings to the table, Machanic says. Consider SQL Server Integration Services (SSIS), the successor to the former Data Transformation Services (DTS). Microsoft likes to position SSIS as a full-blown enterprise ETL tool, and while that might be a bold claim, there’s no question SSIS is a much more sophisticated ETL offering than its (mostly) barebones predecessor. Consequently, it’s going to take data warehousing pros—especially those who are new to SQL Server as an enterprise DW platform—additional time to wrap their head around this, Machanic points out.

“Comparing DTS to SSIS… would be like comparing a pea shooter to a neutron bomb. DTS was initially a test project created by a single developer over a short period of time, and wound up shipping with SQL Server simply because there was no other choice. SSIS, on the other hand, was carefully planned and developed over a five-year period by a relatively large team. It has a much richer, more robust user interface and much greater performance than its predecessor, and is certainly ready for a wide variety of ETL challenges,” Machanic points out. “I've been using it extensively, and am extremely impressed with what Microsoft has done. I, for one, will certainly not miss DTS.”