SQL Server bulks up
Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master
Tying the BI package together is SSRS. With this new release, Microsoft has taken away much of the pain of writing and distributing reports. Perhaps the most important improvement, Report Builder, is a new Web interface that allows end-users to write and publish their own reports. Admins publish a report model, which is basically a definition of the data they want the users to be able to write reports against, and users step through the wizard interface to create whatever reports they like. In a way, these reports can be richer than traditional SSRS reports because they come with infinite drilldown already built in, which is something that designed reports simply don’t have.
Report Builder’s controls allow developers to embed reports in applications. Along with this desirable capability, Report Builder also includes support for custom controls that allow developers (most likely third-party vendors) to extend SSRS functionality.
SSRS also includes lesser enhancements that end-users will find helpful. One of these is multivalued parameters, which allow users to pass more than one value (multiple sales regions, for example) inside a single parameter. This functionality previously relied on passing in delimited elements into a single parameter, and then parsing them out into single entities in the database. Other nice
additions are interactive sorting, which allows users to change the sorting criteria of the columns in their reports as they see fit, and a calendar picker, which allows users to choose a date from a pop-up calendar instead of having to type it.
For developers, SSRS works directly with SSAS and SSIS, using them both as data sources. This is a huge step forward because current SSRS users can follow processes already in place to produce any number of reports in any number of ways. The SSIS integration alone gives you the capability to pull in data from multiple sources, including those merged from different databases, Web services, RSS feeds, SSAS, and data-mining repositories. The possibilities are limitless.
What got left out of this release? Many developers and DBAs were looking forward to IntelliSense for T-SQL coding. Many were also hoping for some sort of centralized code repository. We’ve still got templates, but those typically reside on clients; DBAs and developers can’t easily pull them from a centralized online resource. SSIS is a huge improvement over DTS, but it still isn’t an actual ETL server, meaning it still uses the resources of the server that kicks off the package. This has been a source of grief for many DBAs, although the current architecture has advantages too, and it’s something SQL DBAs have grown to live with and work around. Finally, Microsoft still hasn’t offered an answer to Oracle’s grid solution, which allows you to cluster multiple database servers for load balancing and fail-over and manage them as a group. That’s the last major advantage Oracle can still claim over SQL Server.
Who should upgrade and when? Shops who should look at an upgrade right away -- assuming their applications have been certified for the new version and migrating won’t void their support contracts -- are those that need true 24/7 availability, rely heavily on DTS, or need to comply with Sarbanes-Oxley audits.
If you’re at the point where you just can’t keep pulling downtime for maintenance, or for any other reason, then this upgrade shouldn’t wait. SQL Server 2005’s replication enhancements, online reindexing, partial restores, and (soon) database mirroring will offer welcome relief.