Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 7, 1999

Centralized Administration in a Distributed Environment (ASP and SQL Server) - Page 6

By Bill Wunder

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.
  1. 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.
  2. 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,
                        description,
                        compareTableName,
                        primaryServer)
      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.
  3. 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


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM