SQL Server bulks up

Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master

Page 2 of 2

Other goodies for developers include ranking, random data sampling, pivot/unpivot, synonyms, much richer error handling, and code security context (see the infographic “Cool tools for SQL coders”). Many developers will also be singing in their Cheerios over SQL Server 2005’s native XML storage capabilities. As I mentioned in my April 2004 comparison of the leading relational databases’ XML handling capabilities, SQL Server 2000 was limited to “shredding” XML into relational data, a cumbersome process that fails to preserve XML hierarchies. Now, you can import XML into a column and not only perform native XML queries, but also index it for fast retrieval.

DBAs haven’t been left out of the coding revolution. A number of coding changes give admins management capabilities from the command line that are not accessible through Management Studio. One in particular, DDL (Data Definition Language), will not only change the way DBAs do their jobs but will also help their CEOs sleep much better at night. DDL triggers fire whenever changes to the database schema occur, and they can be defined on pretty much any object type in the server or database, helping to make unwanted changes a thing of the past. 

Indexing is always a volatile subject in any busy production environment. DBAs need to maintain the database; production managers don’t want to interrupt operations. Online indexing allows DBAs to create or rebuild indexes without taking the database offline. The index is built in parallel to the table and then applied once it is created. Of course, you have to be aware of resources, but this feature will greatly increase availability for shops that have to be up 24/7. DBAs can now also control the level of locking used by indexing operations. 

Not your father’s OLAP

SQL Server 2005’s suite of BI tools includes SSAS (SQL Server Analysis Services), SSDM (SQL Server Data Mining), and SSRS (SQL Server Reporting Services), all of which are finally designed to work together to answer your intelligence needs. SSAS is looking pretty good these days, thanks to many usability enhancements. The new Business Intelligence Wizard comes loaded with pre-canned solutions to common problems. Currency conversions, semi-additive measures, and time-based calculations are just some of the situations that the wizard handles.

SSAS has had one very important structural change -- the UDM (Unified Dimensional Model). It’s hard to fully grasp the UDM until you work with it, but it basically provides a bridge between clients such as Excel to any number of heterogeneous data sources. Or put in simpler terms, it sits between you and these sources and shows you the view of the data you request. UDM brings several important changes to the way you will operate with SSAS. One is that now you can have multiple fact tables, and in fact, you have your entire relational schema available to you. UDM eliminates the need for virtual cubes and virtual dimensions, providing not only increased functionality but a much easier model with which to work.

One of my absolute favorite new features is proactive caching. This mechanism allows you to set up metrics that determine when your data gets refreshed. There are several ways to do this, but one of the better ones is to define your cube to be refreshed when the base data changes. The data is refreshed at the partition level, and there are many options for balancing performance and latency.

SQL Server Data Mining has grown from what seemed like an experiment into a full-fledged application. Whereas SQL Server 2000 had two mining algorithms -- simple clustering and simple decision trees -- SQL Server 2005 has 10. It also has 25 new visualizations and visual query editors. All of these tools make it much easier to develop and work with mining models. I wouldn’t say that Microsoft has exactly achieved its goal to bring data mining into the hands of nonprofessionals, but it is far easier than using the third-party mining tools.

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.

Wish list

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.

If you are currently pushing DTS to the limit, converting to SSIS will ultimately streamline operations but may not be as easy as you’d like. Proper planning will be essential. The best way to go about it is to put a dedicated SSIS server in place and upgrade your packages one at a time. This method will give you an opportunity to upgrade and test at your leisure.

If you’re concerned about compliance, the new security features and the DDL triggers will whip your database into shape very quickly. Here again, though, implementing password policies can cause some grief in your environment, so you’ll have to plan appropriately. 

For shops without these pressing problems, SQL Server 2005 offers plenty of other reasons to consider it. From management to development, just pick your woe and chances are SQL Server 2005 will help alleviate it. 

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. Maybe those other guys should start hitting the weights.

InfoWorld Scorecard
Manageability (25.0%)
Scalability (20.0%)
Value (10.0%)
Performance (25.0%)
Availability (20.0%)
Overall Score (100%)
Microsoft SQL Server 2005 9.0 9.0 10.0 9.0 9.0 9.1
| 1 2 Page 2