A collection of SQL Server admin scripts, tools, and utilities I collectively refer to as the admin subsystem . The tool set is intended for the DBA working in a distributed SQL Server 6.5 environment that finds the Maintenance Wizard too limiting. Features include automated DUMP/LOAD replication, error reporting by email and network broadcast, and log dump replication with automated failure recovery.
?bill's vast expanse
(my personal web site since 1995) includes a collection of SQL Server admin scripts, tools, and utilities I collectively refer to as the
. The tool set is intended for the DBA working in a distributed SQL Server 6.5 environment that finds the Maintenance Wizard too limiting. Features include automated
DUMP/LOAD replication, error reporting by email and network broadcast, and log dump replication with automated failure recovery.
albatross grade Administrator to get the job done. The tasks are table driven and the enrollment of remote servers is automated from an ASP page. The result is an administration tool that is
slightly more difficult to set up because it is more complex yet extremely flexible and easy to
use once in place.
This article will describe a three step implementation of the utility.
The first step will be to set up an Intranet web site that will allow
you to check the status of the active scheduled tasks
on a SQL Server. In this step, you must resolve all issues around web site access
and security. The second step will be to establish a database that will act as the focal point of
all administrative tasks in the environment. The aim will be to establish a single point of
The third step will be to enable the browser front end to drive
the administrative database. This will provide you with an alternate access to your SQL
Server administration system for
those times when you do not have access to an Enterprise Manager. At this point you will be
prepared to modify and
enhance functionality of the admin subsystem to the precise requirements of your
So, if you're ready and interested start building
your admin subsystem. If your not absolutely convinced
that this is the best thing since the
pop top beer can, maybe you'd like to see a
demo of the front end
. There is also a working copy of this component at VI Gallery.
Here at www.swynk.com the demo is a snapshot of the utility while the VI Gallery
had a SQL Server 6.5 scheduler available to show the utility in action.
Download the complete admin subsystem with installation instructions.
Who needs it?
Have you ever wundered how your SQL Server(s) were doing
down on the 13th floor while your up on the 15th floor
troubleshooting a workstation with no Enterprise Manager
available on this side of the elevator?
Have you ever had to reinstall the OS on the PC you use to dial
in from home and not have the SQL Server CD available, yet needed
to check the system from home once the OS was reinstalled?
Have you ever needed to run a DBCC check_ident while you were
in a long and important meeting with no time to get back to your
workstation or the computer room without dropping out of the meeting?
Did one of these questions cause you to recall another situation
where you we not able to get to an Enterprise Manager, but you
desperately needed access to your SQL Servers?
If so, here is a solution. Virtually every PC has a browser. If you
set up a utility that will give you the ability to check the status
of your SQL servers and add, modify or run tasks in a documented SQL Server
network from a browser then you will be better able to administer your
system from anywhere at anytime as long as you have a browser available.
I use the admin subsystem utility in a distributed environment. While
you could make full use of the tool in a one database environment, the prevalence
of distributed SQL Server environments leads me to the conclusion that
the materials offered here will be most relevant if presented in the context of
the distributed environment. It should be relatively easy for you to determine which
processes and concepts to use in a single SQL Server environment.
Everything I will show you is in use in a hybrid (OLTP/Data Warehouse) system
with a remote hot site using SQL 6.5 SP4, IIS 4.0, and Windows NT 4.0 SP3. I
have made an effort to make sure everything works with the latest Netscape
and Microsoft browsers, but the reality is that this utility will provide too much
power to risk exposing it to the internet. This means you can build your subsystem to work
with only one browser product if you only use one in your enterprise. This is an
important consideration for you because once you start using the architecture you
build with the admin subsystem you will want to extend the functionality as
appropriate to your system.
The first step will be to establish communication from the browser population you
wish to include to the SQL Server where your scheduled tasks live. This will enable
you to check the tasks from any of these browsers. Take a look
at a small demonstration of the Active Tasks Status Inquiry. The buttons on the main menu
have been abbreviated in the demo so that you can see a couple of possible scenarios. The opening
scenario (the "Normal" button) is what you could expect to see when the administration tasks and
the servers in the network are running normally. A second scenario (the "Broken" button) depicts a network with some problems. This should
give you a good idea of how easy it is to keep an eye on the servers with this tool.
(The demo represents screen shots of a system. No live system is connected to this site.)
If you want this for your system, follow the instructions.
Once the first step posted and there is some feedback that people want more, you can expect to
see details on documenting the SQL Server network in an admin database, scheduling SQL Server tasks
from the browser, and complete source for scripts used to administer the SQL Server network.
I guess that's the catch. You gotta let me know if you want more.
Populate the admin database servers and databases tables from isql/w
While the browser interface is a convenience that I would not want to do without,
there are folks who cannot use the browser interface. Even these folks will
greatly benefit from the centralized maintenance capabilities of the admin subsystem
Toward that end, here is a procedure to follow to populate the maintenance control database.
- Add servers to the database
- Use the add_server stored procedure
- Use 'sp_help add_server' to get length and datatype info for all parameters
- parameter descriptions:
- @newServer - network name of the server you are adding
- @description - a meaningful description of the server in your organization
- @IPaddr - the primary IPAddr of the server
- @dumpShare - The name of the share on this server where the subsystem will use for
output file storage. This will include database dumps, log dumps, zips of dumps, maintenance results, debug
information, and scripts
- @dumpDrive - The logical drive name of the @dumpShare
- @dumpFolder - The folder (full path less drive/share) on the dumpshare that the aforementioned files will be placed
- @loadShare - The name of the share on this server where the subsystem will use for
dump/load replication output file storage. This will include database dumps, log dumps, zips of dumps, and maintenance results.
- @loadDrive - The logical drive name of the @loadShare
- @loadFolder - The folder (full path less drive/share) on the loadshare that the aforementioned files will be placed. This must be
a different location than the @dumpFolder
- @saPassword - sa password on the admin server
- @newPassword - sa password on the server being added. the get_password must be in place for this password
to be properly implemented
- @debug - enables all results to be returned to the process running the procedure.
- Use remove_server to cleanly drop a server from the subsystems control.
- Add databases to the subsystem's database
- Spelling is critical here, as no validation is done when the ASP front end is not used
- Use 'sp_help databases' to get length and datatype info for all parameters
- Use an INSERT statement to add databases to the subsystem:
INSERT databases (name,
VALUES ("[database name]",
"[a good description of the database]",
"[consistency check table name]",
"[server where database exists]")
- Column descriptions:
- name - The name of the database to be added
- description - a meaningful description of the database in your organization
- compareTableName - Used to verify dump/load replication consistency. This should be a
table with a constantly changing number of rows as the consistency check is simply a row count
on the source -vs- target server. Very large tables should be avoided as the row count
operation may become lethargic.
- primaryServer - The name of the server where the table exists. If it exists on more than
one server, it is suggested that you use the primary (production) server name.
- Complete any remaining steps described in the article on installing the admin database and you
will have full capability to schedule admin tasks to run against any database
enrolled in your admin subsystem