SQL Server bulks up

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. 

Free IT resource Open Source Business Conference (OSBC) May 22-23, 2007 Sponsored by OSBC Free IT resource Virtualization Insights from Top Experts - Learn how virtualization gets real! Sponsored by Dell Return to special report DOWNLOAD PDF Click here to download InfoWorld's special report Microsoft SQL Server Microsoft SQL Server 2005 Microsoft, microsoft.com Excellent  9.1 criteria score weight Manageability 9 25% Performance 9 25% Availability 9 20% Scalability 9 20% Value 10 10% Cost: Standard Edition starts at $5,999 per processor or $1,849 with 5 CALs. Enterprise Edition starts at $24,999 per processor or $13,969 with 25 CALs. Platforms: Windows Server 2003 Bottom Line: SQL Server 2005 vastly improves capabilities on all fronts, including development, integration, management, and BI. Companies will be able to run safer databases, better manage their environment, and finally create a truly 24/7 operation. Among new high-availability features, partial restores will allow databases to be brought online faster after failures, and database mirroring, although not yet officially supported, will provide automatic fail-over for log shipping scenarios. About our Reviews and Scoring Methodology

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.