Remote Deployment of MS SQL Server Agents
By Ant Mico
Introduction
Installing the Tivoli Monitoring for Databases: Microsoft SQL Server Agent remotely can at first appear to be a rather daunting task.
This is especially the case for agents used for monitoring MS SQL Server 2005 as additional steps are required to enable the support for this version.
This tip covers the remote install of a Windows OS and MS SQL Server agent onto a server running a default instance of MS SQL Server 2005.
The following assumptions have been made:
- the patch 6.1.0-TIV-ITM_SQL-LA0023 (or later, available from IBM support) has been installed on all the relevant ITM servers (TEMS, TEPS etc)
- the remote deployment bundles from this patch have been added to the depot
- an appropriately permissioned database user has been created
Deploy the OS Agent
Before deploying the OS Agent, we must first login into the TEMS:
C:>tacmd login -s team1itm -u sysadmin
Password?
Validating user...
KUIC00007I: User sysadmin logged into server on https://team1itm:1062.
The OS Agent is deployed using the tacmd createnode command. The server on which the agent will be installed is called MSSQL and the install will be performed by the user Administrator.
C:>tacmd createnode -h MSSQL -u Administrator
KUICCN001I Initializing required services...
KUICCN005I Enter the password for Administrator.
KUICCN039I Attempting to connect to host mssql ...
KUICCN050I Distributing file 95 of 95 (112.8 MB / 112.8 MB)...
KUICCN002I Beginning the installation and configuration process...
KUICCN057I The node creation on host mssql was successful.
KUICCN065I The node creation operation was a success.
Use the tacmd listsystems command to confirm that the new agent has registered correctly, NT being the product type for Windows OS Agents.
C:>tacmd listsystems -t NT
Managed System Name Product Code Version Status
Primary:TEAM1ITM:NT NT 06.10.04.01 N
Primary:TEAM1TEC:NT NT 06.10.04.01 N
Primary:MSSQL:NT NT 06.10.05.01 Y
Deploy the MS SQL Server Agent
With the OS Agent installed, we can now deploy the MS SQL Server Agent.
However, there are a couple of things to note.
Firstly, the documentation refers to a DBSETTINGS.db_sid property which is used to identify the SQL Server instance to be monitored. This property doesn’t actually exist (it appears to be a duplicate of the INSTANCE property) and attempting to deploy the agent referencing it will result in an error.
The actual properties available can be determined by running the following command:
C:>tacmd describesystemtype -t OQ -p WINNT
Key Name : INSTANCE
Required : Y
Restricted Values:
Description : Enter the database server instance name of the database server to monitor
Key Name : DBSETTINGS.db_login
Required : Y
Restricted Values:
Description : Enter the user id that the monitor will use to connect to the
database server
Key Name : DBSETTINGS.db_password
Required : Y
Restricted Values:
Description : Enter the user id's password that the monitor will use to connect to the database server
Key Name : DBSETTINGS.db_ver
Required : Y
Restricted Values:
Description
: <html>Enter the numeric database server version.<br>The
supported values
are:<table><tr><th>Value</th><th>SQL
Server</th><tr><td>7</td><td>SQL Server
7.0</td><tr><td>8</td><td>SQL Server
2000</td><tr><td>9</td><td>SQL Server
2005</td></table></html>
Key Name : DBSETTINGS.db_home
Required : Y
Restricted Values:
Description
: <html>Enter the database server instance's home directory
path.<br>For example, the default home directory path for SQL
Server 2000 is<br>C:Program FilesMicrosoft SQL
ServerMSSQL</html>
Key Name : DBSETTINGS.db_errorlog
Required : Y
Restricted Values:
Description
: <html>Enter the fully qualified file name of database server
instance's error log file.<br>For example, the default error log
path for SQL Server 2000 is<br>C:Program FilesMicrosoft SQL
ServerMSSQLLOGERRORLOG</html>
Secondly, as indicated in the introduction, the base version of the Agent does not initially support MS SQL Server 2005 (referred to as version 9.X). Therefore, in order to deploy the Agent, we need to configure the Agent with a previous version and then correct it later. In this example, the version will initially be set to 8.0.
The tacmd addsystem command is used to deploy the MS SQL Server Agent (designated product type OQ).
C:>tacmd addsystem -t OQ -n Primary:MSSQL:NT -p
INSTANCE=MSSQL DBSETTINGS.db_login=tivoli DBSETTINGS.db_password=orbdata
DBSETTINGS.db_ver=8.0 DBSETTINGS.db_home="C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQL" DBSETTINGS.db_errorlog="C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLLOGERRORLOG"
KUICAR010I: The agent type OQ is being deployed.
KUICAR006I: The product type OQ on https://team1itm:1062 is now being managed.
Note the use of double quotes around the directory names with embedded spaces.
Again, use tacmd listsystems to confirm the agent has been registered correctly:
C:>tacmd listsystems -t OQ
Managed System Name Product Code Version Status
MSSQL:MSSQL:MSS OQ 06.10.00.00 Y
Note the base version of the Agent.
Update the Agent
Now the agent needs to be updated with the patch, which is done via the tacmd updateagent command.
C:>tacmd updateagent -t OQ -n Primary:MSSQL:NT -v061000023
KUICUA012I: Are you sure you want to update the OQ agents
that manage MSSQL:MSSQL:MSS to version 061000023? Updating these agents
stops any that are running, applies the changes, and restarts them.
Enter Y for yes or N for no:y
KUICUA011I: Updating the OQ agents.
KUICUA009I: The OQ agents were updated.
Confirm the agent is now showing the correct version:
C:>tacmd listsystems -t OQ
Managed System Name Product Code Version Status
MSSQL:MSSQL:MSS OQ 06.10.00.23 Y
Reconfigure the Agent
With the agent now at a level at which it can support MS SQL 2005, we can re-configure it with the correct version details using tacmd configuresystem:
C:>tacmd configuresystem -m MSSQL:MSSQL:MSS -p
INSTANCE=MSSQL DBSETTINGS.db_login=tivoli DBSETTINGS.db_password=orbdata
DBSETTINGS.db_ver=9.0.3042.000
KUICCR014I: Configuring agent MSSQL:MSSQL:MSS.
KUICCR008I: The management configuration for MSSQL:MSSQL:MSS was updated.
At this point the agent should be fully functional and all workspaces in the TEP should be correctly populated with data.
Visits: 196