SQL Server bulks up
Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master
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.