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