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.
Prev. page
[1]
2
next page 
