Use this Knowledge Script to check the ability to query a Microsoft SQL Server using ADO and advanced connection parameters.
This script generates the following data streams:
Response time
Overall response time. The information returned by this data stream is also saved with the data point, and can be viewed by double-clicking the data point in the Graph Pane or Chart Console.
Response-time Breakdown. If enabled as separate parameters, up to three response-time breakdown data streams. These are individual data points for the different parts of the Knowledge Script transaction that are timed. See Setting Parameter Values below for more information.
Availability--Returns one of two values:
1 or 100 = transaction was successful
0 = transaction was not successful.
The Availability data point is an indication of whether the transaction succeeded or failed.
An event is raised whenever one of the following occurs:
A threshold that you have specified as an event parameter is exceeded.
The SQL-RT engine cannot be initialized. An initialization error is raised, but neither an Availability nor a Response Time data stream is generated.
The job transaction does not complete successfully. A transaction error is raised. Only an Availability data stream is generated, with a value of 0.
You can select where some of the possible events are displayed in the Operator Console TreeView or Control Center Console Server view. This event proxying feature is useful in Control Center Service Map views. It is not supported for jobs that are started in the Operator Web Console.
The SQL response time ADO client
The default interval for this Knowledge Script is Every 15 minutes.
Be sure to set the Integrated security? parameter according to the security model you want to use:
For SQL authentication: Clear the Yes check box to disable integrated security, then specify the SQL Logon Username and Password. Also specify the Run As account information to supply a valid account under which to run the Knowledge Script.
For Windows NT authentication: Select the Yes check box for integrated security, and leave the SQL Logon Username and Password parameters blank. Specify a valid user account under which to run the Knowledge Script for the Run As Knowledge Script parameters.
Set the following parameters as needed:
Description |
How to Set It |
---|---|
Availability |
|
Collect data for availability? |
Select Yes to collect data for graphs and reports. If enabled, data collection returns:
The default is Yes. |
Data stream format |
Select the data stream format for the Availability data stream. You can use a 0 (“not available”) or 100 (“available”) format. The default value is 0-100. |
Raise event if transaction fails? |
Select Yes to raise an event when the server cannot be contacted. The default is Yes. |
Event severity when transaction fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the server cannot be contacted. The default is 5. |
Response Time |
|
Collect data for response time? |
Select Yes to collect response-time data for graphs and reports. By default, data is collected. If you enable data collection, you also have the option to see a breakdown in the response times for the component parts of the query, such as the time taken to connect to the SQL Server. |
Threshold -- Maximum response time (seconds) |
Specify the maximum response time in seconds. When response time exceeds this value, an event is raised. The default is 15 seconds. |
Raise event if threshold is exceeded? |
Select Yes to raise an event when the threshold is exceeded. The event message contains a breakdown of the total response time. The default is Yes. |
Event severity when threshold is exceeded |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the response time threshold is exceeded. The default is 15. |
Response Time Breakdown |
|
Collect data for connecting to SQL Server? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to establish a connection to the SQL Server. The default is unselected. |
Collect data for executing SQL statement? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to execute the SQL statement. The default is unselected. |
Collect data for fetching data? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to perform a fetch of the query data. The default is unselected. |
Server and instance name |
Provide the names of the server and instance where the transaction will be run. Use the following syntax: Server/Instance. If you set the Event on parameter, this parameter lets you select the server where the event will appear in your console. The instance name is stripped out. Provide the name of the server and instance, or click Browse [...] to select from a list of available servers. The server you select must already be in the TreeView. |
Database name |
Provide the database name on the SQL Server. This is the “Initial Catalog” part of the Connection Properties collection. If the provider does not support this property, use the default database. |
Attributes |
Specify the ADODB::Connection attributes as follows:
|
Cursor location |
Specify the location of the cursor service:
|
Isolation level |
Specify the level of transaction isolation for a Connection object as follows:
|
Mode |
Set the available permissions for modifying data in a connection, as follows:
|
SQL Statement |
Provide a SQL statement (128-character maximum) that is compatible with the provider. The format is execute stored_procedure. |
Number of rows per fetch |
Specify any positive integer or -1. Use an appropriate value according to the size of the result and of a single row. The default value is 1. If the SQL statement is a select, the engine uses the GetRows() method to retrieve the data, so you can fetch thousands or records at once. This could mean a huge performance improvement in production. A value of -1 attempts to retrieve all rows on a single fetch. Although this may show interesting results on a small database, it can easily become catastrophic if the result is large and the client machine has limited memory. You should change this value only if the GetRows() method is used in production with a value different than 1. |
Integrated security? |
Select Yes to specify whether the authentication should be done on the Windows integrated security model. The default is unselected. |
Event on |
Select the TreeView location where events should be displayed. Select either:
Notes This setting does not apply to events related to the Knowledge Script itself, such as Knowledge Script failure or initialization problems. Such events are always displayed on the computer where the job ran. You must select Agent when starting jobs in the Operator Web Console. If you select Server, no events are generated. If you select Both, an event is only shown on the agent. |
SQL Logon |
|
Username |
Set this value when you do not use integrated security. This is the User ID part of the Connection Properties collection. |
Password |
Set this value when you do not use integrated security. This is the Password part of the Connection Properties collection. Hard encryption is always used. |
Run As |
|
Username |
Provide the username associated with a specific user who has the required permissions to run this application. Required. Interactive User is a possible value. Leave the Password and Domain parameters blank if you specify "Interactive User". Interactive User requires a user to be physically logged into the computer for the test to run. You might want to do this in environments where a firewall is preventing access to an Active Directory domain controller, or where the test computer is part of a workgroup and not part of a domain. With this feature, the user is not validated, so the test can proceed despite the lack of access to the domain. |
Password |
Provide the password associated with this user that is required to log on to the network and run the application. |
Domain |
Provide the domain associated with this user that is the domain name you are logging onto. Required. |
Administrators group on managed client |
Enter the name of the Administrators Group on the managed client. Typically, this name is “Administrators”, except on some foreign-language operating systems. The default is “Administrators”. |
Timeouts |
|
Command timeout |
Specify the number of seconds to wait while establishing a connection before terminating the event and generating an error. The default is 30 seconds. |
Connection timeout |
Specify the number of seconds to wait while executing a command before terminating the attempt and generating an error. The default is 15 seconds. |