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

Tuesday Sep 7th 1999 by Bill Wunder
Share:

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 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.

      There have not been many changes to the original information posted on the vast expanse for over a year in the interest of keeping it simple. At the same time, the admin subsystem has evolved. What you'll find posted here is a distributed SQL Server 6.5 administration utility that plays with ASP, ADO, JavaScript, DHTML, Stored Procedures, TSQL scripts, Triggers, and the NT command line to enable an 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 maintenance administration. 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 organization.

      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.

Installation of The Active Tasks Status Inquiry

  1. Define a new application on an IIS server
    Determine your security requirements
    If you're using IE only then require Windows NT challenge/response authentication. If you may need to use any other browser to connect to the site then specify 'Basic Authentication'. If you have no worry of inside malicious attack, you could even use anonymous access.
    It is suggested that you disable session state.
  2. Copy the web pages to the application directory defined above
    frame document
    navigation side bar (left frame)
    style sheet
    navigation frame
    action frame(clock)
    results side (right frame)
    first open page
    ADO include file
    ASP script that gets status
  3. Define an ODBC datasource on the IIS server
    Create a File DSN using the ODBC manager on the web Server
    Add an Application_onStart event handler in the global.asa that defines an "admin_ConnectionString" that exposes the File DSN.
  4. Open your new site from any browser that can see the web server
  5. What if you have problems?
    Debug ASP using Visual Interdev
    Use SQL Trace to watch the database connection
    Drop Bill an email if you can't figure it out
  6. On to the the next step...

Installation of the admin database

  1. Create a new database named admin.
    • Use all of a 10MB device
    • Place the log on the database device
  2. Run the create tables script (6K) in the new database.
  3. Run the create procedures script (166K) in the new database.
    • You will see a number of warnings that a row will not be added to sysdepends. This is OK.
    • You will see one error like:
      Msg 170, Level 15, State 1
      Line 2: Incorrect syntax near '<'.
      
      This is caused by the '<encrypted>' get_password stored procedure. This message can be ignored at this time. This procedure will be created in a subsequent step.
    • There are four stored procedures with hard-coded values that will be changed in a subsequent step.
    • Run the drop all procedures script (13K) if necessary.
  4. BCP the scripts table outfile (28K) in the new database.
    • Use a BCP command like:
      bcp admin_test.dbo.scripts in \\[server]\[share]\[path]\scripts.out -c -Usa 
      -P[sa password] -S[admin server] -escripts.err
      
  5. BCP the drivers table outfile (2K) in the new database.
    • Use a BCP command like:
      bcp admin_test.dbo.drivers in \\[server]\[share]\[path]\drivers.out -c -Usa 
      -P[sa password] -S[admin server] -edrivers.err
      
  6. Add three rows to the semaphore table.
    • Cut and paste these inserts to isql/w (the query tool).
      INSERT semaphore (name, 
                        status,
                        description)
      VALUES ("nullreader",
               0,
              "alphanumeric parsing container where nulls may occur in the input")
      
      INSERT semaphore (name, 
                        status,
                        description)
      VALUES ("PULISTtext",
               0,
              "alphanumeric parsing container dedicated to a high frequency activity")
      
      INSERT semaphore (name, 
                        status,
                        description)
      VALUES ("textreader",
               0,
              "alphanumeric parsing container where nulls will not occur in the input")
      
  7. add two or three rows the subsystem table. These values are used by subsystem processes to locate file system objects.
    • install path
      INSERT subsystem (name, path)
           VALUES ("install", "[fully qualified path to the install source directory]")
      
    • zip file storage location
      INSERT subsystem (name, path)
           VALUES ("zip", "[fully qualified path where zip exe can always be found]")
      
    • NT Resource Kit location
      INSERT subsystem (name, path)
           VALUES ("ntreskit", "[fully qualified path to the NT Resource Kit Root directory]")
      
  8. Add one row to persons
    • Cut and paste these inserts to isql/w (the query tool).
    • Replace all text in brackets '[text]', including the brackets, with the requested values
      INSERT persons (firstName,
                      lastName,
                      role,
                      homePhone,
                      pager,
                      emailAddress)
      VALUES ("[your first name]",
              "[your last name]",
              "DBA",
              "[your home phone number]",
              "[your pager number]",
              "[your complete email address]")                                          
      
  9. If you read through the logSync_driver, maintenance_driver and check_for_blocks procedures, visiting each procedure called, you will have seen most of the code used by the subsystem
  10. Populate the admin database
    • The best way to populate the rest of the tables in the admin database is to install and run the ASP admin utility.
    • If you do not (or cannot) use the ASP utility, you cannot enjoy the protection against misspelling and other errors that the front end will provide. Use care and caution.
    • A separate page is provided with instructions to populate the database from isql/w.
  11. Once the database is populated, you will have enough information to modify the few hard-coded procedures.
    • Place holders for all hardcoded values are bracketed (i.e. '[place holder]')
    • verify_dump and verify_load - These procedures use a remote procedure call and returns an OUTPUT value. That's a difficult combination for dynamic SQL. Both procedures first check to see if the server where the information is requested is the local server. If it is not, the procedure call needs to be prefixed with the correct remote server's name to access that server. Modify (and add if necessary) an entry for each server added to the servers table. You'll need to modify the procedure to hold hard-coded values for each server under the subsystem's control.
    • set_to_hot_site - This procedure sets database options for all databases on a hot spare server. In the event that any user database(s) on the server should not be set with the hot site options (for example test, development, static, and highly transient data stores), add the name of the database to the conditional statement that will exclude such databases. This procedure will work as is if you have no user databases you wish to exclude from the hot site database options settings applied.
    • get_password - This procedure is a dynamic lookup device that will keep you from having to save the password to each server in your environment in clear text in systasks. Run the get_password_template procedure and follow the instructions to create your initial version of this procedure. Once the initial version is created, the add_server and remove_server procedures will properly maintain the get_password procedure.
    • On to the the next step...

Installation of the admin subsystem front end

  1. Complete step 1.
  2. Complete step 2.
  3. Download the zip file of all admin subsystem ASP pages and place all files in the web folder create in step 1.
  4. Add all servers to be maintained
  5. Add all databases to be maintained
  6. Schedule tasks

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
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved