The admin Subsystem

Note: A table driven version of the admin subsystem with an optional ASP front-end is available for download at www.swynk.com. Check it out!

Inevitably, the need arises to create scripts and stored procedures to administer a SQL Server. The options as to where to house these utilities range from stuffing them into the master database to keeping them on the administrator's client computer in a mesh of flat files. While I would agree that any method will work if you establish a good procedure, I would additionally argue that most procedures are inherently self limiting. Try recovering stored procedures compiled into the master database after you've been forced to rebuild master or applying a routine data fix process when the computer where the script is stored is having its mail client upgraded and you'll get an introduction to the reality of such limits. If you create a user database to store all the admin stored procedures, scripts and utility tables, the procedures remain available to any SQL client and can be easily recreated from a dump if disaster strikes the SQL Server.

Integration of a user database to the SQL Enterprise Manager's Scheduler, the command line, and the network constitutes a subsystem intended for SQL Server administration. What follows is a working example of such a subsystem.

This subsystem could actually be set up on a SQL Server Workstation as a stand alone administrator SQL Server. Using a combination of remote stored procedure calls (only used here to check the status of dumps and loads in msdb) and isql to keep processing local to the server where the operations are executing, the admin subsystem does not need to live where it works.

DATABASE: 10MB with log on data device, truncate on checkpoint enabled, and no users except dbo (mapped to the sa login)


USE master
GO

-----------------  Device  ---------------
DISK INIT
     NAME =     "admin_dev",
     PYHSNAME = "E:\admin_dev.DAT",
     VDEVNO =   99,
     SIZE =     5120
 
-----------------  Space and Log  ---------------
CREATE  Database  admin
       on  admin_dev  =  10  -- 5120  of two Kb pages

---------------------  DB Options  -------------------
EXECute sp_dboption  admin ,'ANSI null default' , false
EXECute sp_dboption  admin ,'dbo use only', false
EXECute sp_dboption  admin ,'no chkpt on recovery', TRUE
/***EXECute sp_dboption  admin ,'offline', false***/
/***EXECute sp_dboption  admin ,'published', false***/
EXECute sp_dboption  admin ,'read only', false
EXECute sp_dboption  admin ,'select into/bulkcopy, false
EXECute sp_dboption  admin ,'single user', false
/***EXECute sp_dboption  admin ,'subscribed', false***/
EXECute sp_dboption  admin ,'trunc. log on chkpt.', false

--------------------  sa  is  dbo  ---------------------

TABLES:

PRIMARY STORED PROCEDURES:

logSyncThenSetUsersToHotSite

maintenance

setUsersToHotSite

setAllSearchDBsToTruncLog

CALLED STORED PROCEDURES:

convert_share_to_drive

setSearchDBsToTruncLog

Scheduled Tasks

Sample scheduled tasks used with the admin subsystem.

The scripts table

Device independance is an objective of the admin subsystem. One measure used to achieve this is to execute scripts at the target server that are stored on the admin server. This process involves extracting the script out of the scripts table and pushing them to a file location on the target machine, then submitting an isql command to the target that invokes the CMD process on that target CPU to run the script. This sounds more complicated than it is. All steps are done in a small stored procedure: run_script_remote.

SCRIPTS (Add to table admin..scripts):

script_update_statistics

script_reindex

script_showcontig

script_row_level_locks

The Semaphore

A permanent table rather than a temp table is needed to enjoy full functionality of the BCP utility. Manipulation of data using isql to fetch data into a flat file, then using BCP to suck the data into the SQL Server for parsing and formatting can create contention on the BCP target table. In the admin subsystem, a semaphore technique is used to manage the allocation of the textreader table. Before data is imported into the textreader, the process must hold the textreader semaphore by calling the hold_semaphore stored procedure. Then, when the procedure has finished processing all data out of the textreader table, a call to the release_semaphore stored procedure makes the taxt reader available to any other requestor. The intent of the subsystem is to support batch operations, so the correct behavior is for each process to wait for the textreader table to become available. This will prevent batch jobs from blowing up because the textreader is not available. To protect the batch process in the event of a hard crash, a third stored procedure is needed: free_semaphore. This procedure is compiled in master as a startup procedure and in the admin database for utility use. In general, this can be called to initialize the semaphore construct when the server crashes or a batch job is killed.

If you read this far, you might be planning on trying the subsystem. You can now download the admin database schema and a character mode bcp output file of the scripts table content.