It's been almost 4 years since SQL Server 2000 hit the street. With it, Microsoft unleashed a powerful but often underutilized toolset. Think for a moment about your own organization. Are you doing anything more creative with your SQL Server software than running an occasional backup and letting your applications do the basic reading and writing of data? Microsoft is projecting a mid-2005 release for SQL Server's next major revision, SQL Server 2005. This delay, combined with the traditional lag in customers moving to a new database platform, guarantees SQL Server 2000 a life span well into 2007 at least. So as we await the next upgrade, let's review seven frequently overlooked features available in SQL Server 2000 today and consider how they can help you as administrators and developers obtain a higher return from your existing software investments.
These 7 hidden gems can help you get the most out of SQL Server 2000
1. Log Shipping
Have you ever considered how to keep an offsite disaster-recovery server refreshed or how to deploy a hot-standby server to decrease local recovery time? Or what about creating a "mirror" server to off-load operational reporting from your high-volume transactional system? I often encounter organizations using standby servers to achieve these three goals. Many make a nightly database backup, copy it across the network, then restore it to another server. Other organizations often take their production systems offline, detach the database, copy the data and log files across the network, then reattach the databases on the source and destination servers. Both approaches—backup and restore and detach-reattach—work, but they're limited. In both cases, you must move the entire database, which results in a lot of disk I/O, increased network traffic, and a scalability problem because eventually you won't have enough time per refresh cycle to complete the process. The detach-reattach option also forces a system outage each time the refresh occurs.
Log shipping provides an often-overlooked alternative for maintaining mirrored servers. It uses built-in backup and recovery facilities to track data changes through a transaction log backup, automatically copying the transaction log backup to the new server, then applying the log. By moving only the data that has changed, log shipping avoids the common disk I/O, network traffic, and scalability penalties I described. SQL Server 7.0 introduced log shipping, and SQL Server 2000 Enterprise Edition takes it to a new level by providing built-in support and automation for establishing and maintaining the log-shipping process. Standard Edition also includes log shipping; however, similar to log shipping in SQL Server 7.0, it requires manual configuration. See Ron Talmage's December 2001 and January 2002 articles, "Log Shipping in SQL Server 2000, Part 1" (InstantDoc ID 23056) and "Log Shipping in SQL Server 2000, Part 2" (InstantDoc ID 23230) for an in-depth look at establishing and maintaining a log-shipping environment.
2. SQL Server Profiler
Of all SQL Server's utilities, I have to classify SQL Server Profiler as the single most misunderstood and underutilized development (yes, I said development) tool available. Profiler gives administrators a window into SQL Server's inner workings, letting them track performance metrics, perform database-auditing activities such as identifying the users who access the server, and capture various workloads to aid in tuning the server. But it also helps developers gain a unique understanding of how their applications interact with the database. As a developer, have you ever wanted to see the exact SQL statements that your application code generates? Have you tried to track down and debug a blocking or deadlocking problem in your system? If so, Profiler needs to be part of your toolkit.
Profiler's tracing facilities let developers capture and examine the internal attributes, metrics, and events that SQL Server exposes. Once you've captured these elements, you can perform realtime analysis through Profiler's UI, or you can store the data for later consumption by other analytical tools. For example, to diagnose a deadlocking bug, you could capture user information, the connections corresponding to those users, statements those connections have executed, and the time of each activity. With those results, you could then analyze the time dependencies and statements to identify exactly what combination of user activity is generating the deadlocks. Itzik Ben-Gan offers a valuable overview of Profiler in his May 2001 article "What's New in SQL Server 2000 Profiler" (InstantDoc ID 20126). In addition, Brian Moran regularly shares insights into Profiler tracing options in his SQL Server Savvy column (you can access column archives at http://www.winnetmag.com/departments/departmentid/721/721.html).
3. INSTEAD OF Triggers
What technology do you use if you need to intercept a particular Data Manipulation Language (DML) statement and perform some alternative processing? Maybe you need to change a non-updateable view to be updateable, or while tuning your production database, you realize that partitioning the Transaction table would result in a significant performance improvement. However, given all the existing application code, you need to avoid a major rewrite that would result in a lot of regression testing. If any of these situations sounds familiar, INSTEAD OF triggers might offer a solution.
New in all editions of SQL Server 2000, INSTEAD OF triggers provide the ability to do something else in place of the triggering action. For example, when an INSTEAD OF trigger exists on the insert action of a table (or view) and an INSERT statement executes against the table, the trigger code instead handles any necessary processing. And unless the trigger's code explicitly forces it, SQL Server doesn't apply any data to the table. Because views support INSTEAD OF triggers, this mechanism of intercepting and controlling data changes lets developers make previously non-updateable views updateable. Most important, when addressing the partitioning scenario, administrators and developers can hide schema changes from anyone or anything accessing the table while maintaining backward compatibility with existing applications. Kalen Delaney's November 2000 article, "INSTEAD OF Triggers" (InstantDoc ID 15524), explains their usage in detail.
4. Multi-Instance Support
All editions of SQL Server 2000 support multiple instances, letting you run multiple, independent instances of SQL Server simultaneously on the same box. This feature offers an opportunity to consolidate production environments, but where people frequently overlook its benefits is not in the production environment, but rather in the development and testing environments. If your organization supports multiple development projects and several teams need their own environments to evolve and build their applications, SQL Server 2000 lets you host multiple projects on one box while the teams operate as freely as if each had its own machine. Likewise, testing environments, specifically those used for functional testing, have the same opportunity to share resources. Depending on your hardware's capacity and your system's memory, CPU, and storage needs, this type of consolidation might not be practical. But don't overlook the potential cost benefit of eliminating one or more servers and the corresponding OS licenses from your budget. Kalen Delaney provides a good overview of multi-instance support in her July 2000 article "Multiple Instances" (InstantDoc ID 8686).
5. Workflow Management
Most SQL Server users are familiar with Data Transformation Services (DTS), a set of utilities for extracting, transforming, and loading data. However, many people don't realize that DTS's workflow engine also provides a straightforward, easy way to manage task execution, even when those tasks have nothing to do with moving or transforming data. Say you need to run several stored procedures and, for efficiency reasons, you want to run as many in parallel as you can. One DTS package could do the trick. The package could contain one Execute SQL task for each procedure and one connection for each parallel task. You could then use SQL Server Agent to schedule the package to run at whatever interval you choose. You can set up this type of workflow management with just a few clicks of the mouse.
6. Computed Columns
Often overlooked by data modelers are SQL Server 2000's abilities to define a dynamic or computed column within a table as part of the table's underlying definition and to improve query performance by indexing the computed column. Rather than storing a data value in such a column, which increases row size, SQL Server 2000 derives the column's value at query runtime. Centralizing the column's calculation lets your users access a single, uniform column definition, thereby reducing the inherent risk that arises when you duplicate a frequently reused formula across multiple queries. And if testing reveals an index is warranted, you can add it to the computed column to increase performance. In his October 2001 T-SQL Black Belt column, "Storing Computations" (InstantDoc ID 22091), Itzik Ben-Gan shows you how to implement computed columns.
7. Reporting
By now, you've heard about Microsoft's new reporting solution, SQL Server 2000 Reporting Services. Although it's too soon for Microsoft to claim total victory in the reporting arena, early customer feedback indicates that Reporting Services is quickly gaining popularity and will be a big player in the business intelligence (BI) and traditional relational reporting markets. But even if you're not yet interested in Reporting Services, you're likely equipped already with not just one but three other Microsoft reporting options: Microsoft Access, Microsoft Excel, and the Microsoft Office Web Components. Granted, these are in no way full-fledged, end-user reporting tools, but when combined with a well-designed database and a bit of custom coding, they all can offer a rich end-user experience for presenting canned parameter-driven reports. For example, in their June 2002 article, "Building a Web-Based Analysis System" (InstantDoc ID 24692), Mark Scott and John Lynn explore a reporting system they built by combining SQL Server technologies with the Office Web Components. Their solution includes Analysis Services' OLAP functionality, but you can also use the Office Web Components to report against native SQL Server tables.
Why Wait?
Don't let all the hype surrounding SQL Server 2005's new-and-improved functionality keep you from implementing useful solutions now. You still have plenty of creative ways to leverage SQL Server 2000's valuable, often overlooked features. Why put off until tomorrow what you can start today?
