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

SQL Server 2005: The Good, the Bad, and the Lovely

Thanks to five years and more of gestation, many of the business intelligence (BI) facilities Microsoft ships with SQL Server 2005 bear only a facile resemblance to their predecessors. We spoke with SQL pros about the good, the bad, and the lovely qualities of Redmond’s next-gen BI stack.

• By Stephen Swoyer
• March 15, 2006

SQL Server 2005 doesn’t so much constitute an overhaul as a rebirth of Microsoft Corp.’s business intelligence (BI) stack entire. Thanks to five years and more of gestation, many of the BI facilities Microsoft ships with SQL Server 2005 bear only a facile resemblance to their predecessors.

Some resemblances are more facile than others, of course.

Consider the case of Microsoft’s SQL Server Reporting Services (SSRS), which debuted two years ago as a free add-on for the aging SQL Server 2000 database. SSRS already has a couple of service pack releases under its belt, and SQL Server 2005 ships with a revamped SSRS implementation, along with a new end-user report authoring tool, called Report Builder.

The latter deliverable, in particular, is probably one of the most eagerly anticipated components of the new SQL Server 2005 BI stack. Nevertheless, some SQL pros say there really isn’t that much else too the new SSRS. “I don't see revolutionary changes in Reporting Services between 2000 and 2005,” says SQL Server consultant Steve MunLeeuw.

MunLeeuw, like many of his colleagues, says he couldn’t wait to put the new Report Builder tool through its paces. Having done so, however, he now describes the new deliverable as somewhat anti-climactic. “We were excited about the ad hoc abilities of Report Builder [that are] new to 2005. Because you can't build full-featured reports in the Report Designer on the same Report Model that Report Builder relies on, I think Report Builder as it is today falls short,” he explains. “Our users want to take the canned reports built with the full-featured Report Designer [in the Visual Studio IDE] and add a column of data, filter it differently, and share it within their circle—or offer it as a template to IT or to other departments.”

That just isn’t possible in Report Builder 1.0, MunLeeuw laments. “By the time we build a Report Model we might as well build a cube and skip Report Builder completely. OLAP offers so much more Ad-Hoc ability to the end user and I think it is easier to use. I didn't find Report Builder to be all that straight forward. I've built a lot of reports and wasn't sure what I could drag where.”

This isn’t to say SQL shops are dissatisfied with the new Reporting Services. Not at all. Indeed, many customers plan to replace third-party reporting tools with SQL Server 2005 and its revamped SSRS component.

In this respect, says Wayne Snyder, a managing consultant with Charlotte-based BI consultancy Mariner USA, the new SSRS will be the catalyst for much of this activity. “We are seeing many customers moving from Crystal, and Business Objects to Reporting Services. That is the big area of movement for our customers,” he comments. That’s not all, however: Snyder says the revamped SQL Server 2005 is also attractive as a data warehousing platform. “New Data warehouse people find that SQL is often a better ROI’d solution than Oracle.” Putting aside the still-coalescing rock-stardom of SSRS and the rest of Microsoft’s SQL Server 2005 BI stack, Snyder and other SQL pros cite a number of not-so-glamorous, but nevertheless highly significant BI-oriented enhancements in the new SQL Server database. Take, for example, SQL Server 2005’s native Common Language Runtime (CLR), which has been the subject of near-endless speculation—some of it good, much of it bad—since it was first announced. (For more on CLR and the controversy surrounding native CLR, see http://www.adtmag.com/article.aspx?id=11171&page)

The CLR is a Big Deal, of course, but less well known is the fact that Microsoft has also tweaked SQL Server’s bread-and-butter Transact SQL (T-SQL) language, which is the lingua franca of DBAs and database programmers. (For more on T-SQL, see http://www.adtmag.com/article.asp?id=11148).

“The CLR integration is exciting to many developers, but with a DBA background, I am more careful about making sure that we use the technology where it’s appropriate,” says Snyder. “I’m a big T-SQL guy, so the Common Table Expressions (CTEs) are great,” he comments, noting that CTE support and a handful of similar T-SQL improvements are almost as much of a boon to T-SQL codejockeys as a SQL-native CLR is to Visual Studio programmers.

Microsoft describes CTEs as temporary named result sets that SQL programmers can define (in the context of T-SQL queries) so that they can be invoked by the FROM clause of the query. (Another upside of CTEs is that they can be used to perform recursive operations.) Nor is that all. Other T-SQL improvements include PIVOT and UNPIVOT support, TOP enhancements, new date and time data types, and support for exception handling in transactions.

A kind of meta-narrative has taken shape in the months after SQL Server 2005’s release. For many if not most SQL shops, SQL Server 2000 already provided all of the performance (and most of the manageability and availability) features they could ask for. (In fact, SQL Server 2000 remained a strong performer—at least marketshare-wise—through all of last year, according to market watchers Gartner Inc. and International Data Corp.)

As a result, a number of SQL pros say, SQL 2005’s new BI features are actually its strongest selling point. “For myself, the enhancements to Integration Services and Reporting Services are what make SQL 2005 substantially more attractive than SQL 2000,” confirms SQL Server pro Matt Heere, who adds that “the [SQL Server] database engine functionality has been adequate for my needs since SQL 7.0,” which shipped in late 1998.

Instead, Heere, like many of his colleagues, lauds SQL Server 2005’s substantially revamped—if not reborn—ETL, OLAP, and data mining capabilities. “The ETL/EII technology is particularly exciting for me. It is my hope that SSIS will be able to handle my ETL/EII needs in a much more real-time manner than that to which I am accustomed.”

Ditto for DBA, SQL programmer and consultant Snyder, who applauds the “new speed and flexibility” of SSIS, which—as a full-scale replacement for the erstwhile Data Transformation Services (DTS)—should be a boon to his company’s business. “We mainly do SSIS and Analysis Services, and both have been greatly improved. One of the big issues for DTS was related to deployment, and these issues have been addressed as well in SSIS.”

The new Integration Services, Snyder argues, are a significant improvement over the former DTS. “Although I really liked DTS, the developers have done a really good job in SSIS. It is more flexible, much, much faster. It is easier to debug in the development environment,” he concludes.

This doesn’t mean Microsoft can rest on its BI laurels, however. Features such as SSIS, SSRS, and SSAS are probably enough to tempt many non-traditional customers, says SQL pro Matt Heere, but Redmond needs to improve its outreach efforts to Oracle 10g, DB2, and other relational databases.

“My satisfaction with the feature set is complete, right up to the point where one attempts to integrate with Oracle,” he comments. “Microsoft would do well to reconsider their strategy here. I believe that SSIS, Reporting and Analysis Services would be attractive enough to get SQL server 2005 ‘in the door’ of many Oracle shops. Use of the SQL database would spread quickly once the product suite was in place, ultimately dislodging Oracle. However, due to the limited capabilities of the ancillary services when connecting to Oracle databases, most Oracle shops will probably look elsewhere for their BI tools.”