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

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.
   • You should not see any errors.
   • Run the drop all tables script (4K) if necessary.
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...