Questions, Answers, and Tips About SQL Server

Reader Tip: Visual SourceSafe
In your November column, you asked whether anyone had ideas about Visual SourceSafe (VSS) and SQL Server script integration. As a postdevelopment task, my company wrote a program to integrate the process of stored procedure development and version control check-in/check-out to work like it does in the Visual Basic (VB) IDE. The program solves part of the problem. The code uses Visual Basic 5.0 Service Pack 2 (SP2), VSS 5.0 SP2 (particularly the VSS Type Library), and SQL Server 6.5 SP3 (particularly the Distributed Management Objects—DMO—interface). You can extend the application to support views.

Jeffrey Juday
jeffrey_t_juday@crowechizek.com

Thanks, Jeffrey. Readers can download Jeffrey's VB5 sample code from the Windows NT Magazine Web site (http://www.winntmag.com).

—Karen and Brian

Reader Tip: Preparing for Disaster Recovery
Greetings from Norway. My company is in the process of setting up a SQL Server 6.5 system, but before putting it into production, we wanted to simulate a total machine failure. In preparation for this test, we searched extensively—but unsuccessfully—for relevant references to outlining a foolproof way to quickly and easily restore data from tape onto a temporary machine while the other was down for repair. Here's what we eventually did:

1. We formatted a new machine with NTFS and installed Windows NT 4.0 as a member server (i.e., not a Primary Domain Controller—PDC—or a Backup Domain Controller—BDC).
2. We installed SQL Server 6.5, fresh from the BackOffice CD-ROM, onto the new system. We created an empty master device that was large enough for the expected needs.
3. We restored the /MS3100/DATA/*.* directory with all its contents from the backup tape onto the new server.

We didn't think this procedure would work, but it did. After we rebooted the server, the traffic light came up green. The only job we had left was to adjust the client's .ini files to point to the server's new machine name—something we probably could have skipped if we had chosen not to change the machine name.

We are disappointed with the lack of explanations about how to perform such a vital task. What good is a backup if nobody knows what to do with it? So for what it's worth, we're happy to contribute our three-step survival guide to SQL disaster recovery.

Oyvind Overby
100271.2360@compuserve.com or oyvind.overby@ask.telemax.no

You've discovered a solution that works, Oyvind, but we certainly don't recommend that clients plan their disaster recovery in this way for all situations. Readers must realize that the fully qualified directory structure on both machines must be identical, which it was in your case. Also, readers need to remember to run

sp_dropserver <oldservername>

and then

sp_addserver <newservername>, local

if the new server name is different from the old server name.

Commercial NT backup products and clustering solutions can typically provide faster recovery times. Appendix E from the "Administrators Companion: Backup and Recovery Guidelines for Production Environments" in SQL Server Books Online (BOL) offers some helpful information.

—Karen and Brian

Q: How can I tell (in testing mode) whether a trigger has fired?

If you have constraints on the same table and they fail before your program gets to the trigger, your program won't call the trigger. However, if you want to keep track of when SQL Server calls the trigger, you can do something like this:

1. Create an audit table. You'll probably want to include the key to the underlying table that you've created the trigger for. Make a datetime stamp—and perhaps a column for comments—part of the new audit table.
2. Add a line to your trigger to write an entry to the audit table at a certain point in your trigger.
3. Review the file after you expect your application to have called the trigger.

The beauty of this method is that the updates tend to be almost instantaneous and you don't have to watch the operation of the trigger because you have a date timestamp in the file. When you're happy with whatever you are looking for, you can remove the insert entry from your trigger.

A more general approach would be to create a table called Audit_Trigger, with columns (ID, TableName, TriggerName, Date, Time, UserName), where the column ID is an identity data type. You can use this Audit_Trigger table to capture any trigger firing from any table that you want to test. Turn on Insert Row Locking (IRL), and don't create indexes on the table. This table probably won't cause a big bottleneck if you use it only for testing and evaluation.

Q: Sybase 10 has a thresholds feature for emergency management of transaction logs. Does SQL Server have an equivalent? If not, can you simulate threshold functionality?

Sybase has a whole family of threshold procedures. SQL Server doesn't have any direct procedure-based equivalents, but it does provide similar support through tight integration with the Windows NT Performance Monitor (Perfmon).

SQL Server exports dozens of counters, including Log Space Used %, that you can track from Perfmon. Perfmon's native features let you take action when a threshold has been exceeded for a counter you're tracking. Another solution is to create a WAITFOR procedure that checks log sizes with DBCC SQLPERF(LOGSPACE) and takes appropriate action.

Reader Tip: Another Approach to Crosstabs
In your December 1997 column, you gave an answer to the crosstab query question that involved several subqueries. I think that the solution shown in Listing 1 is easier to write and runs faster when you have a large table. I use a similar technique to create crosstab summary tables in our data warehouse with sales for a product code summarized for multiple time periods in each row.

—Don Clark
delan/dbrook/dwc%drexelbrook@mcimail.com

Crosstabs are never fun to construct, and they can be real performance dogs, so we're happy to have a handful of recipes to try. We like Don's solution!

—Karen and Brian