ISA Server & SQL Server – Brothers in Arms Part 3 In this article we’ll go further into the tasks of monitoring space usage and allocation for our ISA Server database as well as few methods of investigating data consistency for our database. I’ve dug up in the ISA Server forums archive and found a lot of posts where people was asking what to do about the space claimed by the database and very often they gave up in logging to a database because they found this method very “hungry” in terms of space. We’ll discuss the following: IMPORTANT!
Available tools & Database Space Monitoring
by Alin Salicean
Some of the exercises in this article assumes that the SQL Server is configured to send email notifications. See http://forums.isaserver.org/ultimatebb.cgi?ubb=get_topic;f=2;t=009846 topic for a brief description on how to set up SQL Server to use a mail server to send email notifications.
Available tools
There are several tools available for an ISA admin to accomplish all its tasks. These tools includes (but are not limited to): SQL Query Analyzer, Enterprise Manager and osql command-line utility. Of course, there might be some other 3rd party tools to connect and manage a SQL Server. The ones we are going to use and discuss a little about are SQL Query Analyzer and osql. Not always an ISA admin will have Enterprise Manager installed, for various reasons: company policy, paranoid DBA (I’m a DBA as well 🙂 ), don’t want to, segregation of duties, etc… So knowing what other tools are available might prove useful in certain situations. An invaluable tool is BOL, which contains all the documentation related to SQL an ISA admin will require.
IMPORTANT!
Installing the tools
These tools can be installed from any SQL Server CD. The most significant steps are detailed below:
Point and click on SQL Server 2000 Components
Point and click on Install Database Server. Wait until InstallShield launches.
Intermediary step
Click Next
Make sure Local Computer is selected and click Next.
Select Create a new instance of SQL Server, or install Client Tools option and click Next.
Type in your name and company name and click Next then click Yes to accept EULA.
Select Client Tools Only and then click Next.
Play around with the available selections and please note the descriptive text displayed in the Description box. Make the selections as shown above. These should be enough for every ISA admin needs. Click Next.
After this step, Setup will also install MDAC (MS Data Access Components). These components are also subject to frequent security issues and patches, so *never* install these tools on your ISA box.
This message should appear when everything’s done. Click Finish.
You should find your newly installed software under Start, Programs, Microsoft SQL Server.
Using the tools – very brief description
SQL Query Analyzer is very powerfull tool available to query, manage and update data. It provides great features like: syntax color-coding, connection-level settings, various output options, displaying execution plans and many more.
Osql is a command-line utility which allow a user to run a query against a database in a simple fashion. It has a lot of command-line params, like depicted below:
==================
C:\>osql /?
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q “cmdline query”] [-Q “cmdline query” and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
==================
Using these tools, an ISA admin can issue various Transact-SQL (aka T-SQL) against a target database (in our case, ISA logging database). T-SQL statements can be stored as scripts in .sql files, developed with plain text editors (like Notepad).
The results of the scripts can then be saved to plain text files and then imported in Excel for processing and reporting purposes, as a quick tip. Another useful application of these scripts is to quickly investigate a specific user activity.
Access, on the other hand, is well-known (I guess anyone played around with Access at least for fun) and needs no further introduction. What we’ll discuss about Access is its Access Data Project or ADP feature. This feature allow a programmer to directly access a SQL database and use data stored on it as its primary data and develop forms and other business logic components to suite specific needs. But enough talking, let’s get down to work…
Explaining T-SQL language syntax is beyond the scope fo this article, so we’ll jump to discussing the methods available for monitoring space usage and allocation for our ISA database.
When investigating space allocation and usage, system stored procedures prove to be our best friends. What is a stored procedure ? A stored procedure is simply a collection of T-SQL statements which might contain one or more transactions and/or batches (see http://www.isaserver.org/tutorials/isasqldbpart1.html for a brief explanation of these terms or consult BOL). Usually a stored procedure is designed to accomplish a very well defined task. There are 3 types of stored procedures: system stored procedures, extended stored procedures and user defined stored procedures. System stored procedures usually have their names starting with the sp_ prefix. Extended stored procedures resides outside the SQL Server (usually in a DLL file) and can be invoked programmatically. Usually, these extended stored procedures are prefixed with xp_. Now back to our task, there are several sp’s and xp’s that will help us out in monitoring space allocation and usage for our database.
Using sp_spaceused system stored procedure
The syntax of this stored procedure is:
sp_spaceused [[@objname=]’objname’] [,[@updateusage=]’updateusage’]
where objname is the object name being investigated (i.e. table name) and updateusage instructs the store procedure to update the information about space usage for that particular object. Updateusage parameter can be either True or False.
Full syntax example is shown below:
USE ISAServer
GO
EXEC sp_spaceusage ‘FirewallLog’, ‘True’
The result will be similar to the one below:
The meanings of the columns:
Name = The name of the object being investigated
Rows = Number of rows currently existing in the object being investigated
Reserved = Amount of total space reserved for the object (SQL Server automatically allocates additional space for objects when required)
Data = Amount of space used by data in object being analyzed
Index Size = Amount of space used by index(es) in object being analyzed
Unused = Amount of unused space
If no parameter is passed (I commented the params in my previous example), the result is similar to this one:
The output now refers to the database itself and consists of two sets of details.
First set of details explained:
Database_name = the name of the currrent database
Database_size = the size of the current database, including the transaction log
Unallocated_space = available space in the database
The second set of details:
Reserved = Total amount of reserved space for the current database
Data = Total amount of space occupied by the data in the current database, by all its objects
Index_size = Total space used by all indexes in the database
Unused = Unused space in the database
NOTE: In a SQL Server database, objects are comprised of tables, indexes, views, stored procedures, etc… Each of these consumes storage space because their definitions are stored in several system tables, local to that specific database. There are also some server-level system tables, which holds information about the whole SQL Server. These are beyond the scope of our article.
Now, let’s consider the following script:
USE ISAServer
GO
EXEC sp_spaceused
EXEC sp_spaceused ‘FirewallLog’, ‘True’
EXEC sp_spaceused ‘WebProxyLog’, ‘True’
EXEC sp_spaceused ‘PacketFilterLog’, ‘True’
This will produce the following result:
As you can see, in one step, we have all the details we need. But running this script on a regular basis, might prove sometimes a dificult task. Why ? Sometimes you might be out of the office but want to know how your database is doing, sometimes you might be in vacation, sometimes you simply forgot to run the script (we’re humans)… How can you be notified with these details ?
The answer is a multi-step procedure that will do it together.
Step 1 – Creating user-defined stored procedure
We’ll use a user-defined stored procedure for creating the output as it is easy to maintain and develop in case we gonna need any additional details about our database.
Creating user-defined stored procedure using QA
USE ISAServer
GO
CREATE PROCEDURE usp_SpaceMonitor
AS
EXEC sp_spaceused
EXEC sp_spaceused ‘FirewallLog’, ‘True’
EXEC sp_spaceused ‘WebProxyLog’, ‘True’
EXEC sp_spaceused ‘PacketFilterLog’, ‘True’
Creating user-defined stored procedure using osql
USE ISAServer
GO
CREATE PROCEDURE usp_SpaceMonitor
AS
EXEC sp_spaceused
EXEC sp_spaceused ‘FirewallLog’, ‘True’
EXEC sp_spaceused ‘WebProxyLog’, ‘True’
EXEC sp_spaceused ‘PacketFilterLog’, ‘True’
NOTE:
If unsure about what authentication method to use, please contact your DBA. Replace <loginid>, <password> and <saved-file-name> with the coresponding values indicated by your DBA and the name of the file saved in Notepad. If required, specify the full path for that file.
After running the above piece of code, you should see your newly created stored procedure under Stored Procedures node, in your ISA Server database, as in the next image.
Step 2 – Creating the job that will create text report file
The job that will create the text report file can be done in two ways: using SQL Enterprise Manager or by using the Scheduled Tasks, in Windows.
Method 1 – SQL Enterprise Manager (EM)
NOTE:
If you do not see a green triangle near the SQL Server Agent indicating that the service is running, rightclick on SQL Server Agent and select Start. If the service is configured properly, it should start in few seconds. If the green triangle doesn’t appears, right click after few seconds on SQL Server agent and select Refresh.
Right click on Jobs an select New Job…
Here are the details for the job:
Step 1
Make sure you select the user account used to start the SQL services and it has write/modify rights on the folder you need to save the output.
Step 2 – Creating execution steps (or commands to be executed)
Specifying the options for the command entered:
Set the job to output the results to the desired file. Also, for easier tracking, set the job to append the results.
The final result after setting the steps (or commands).
Step 3 – Scheduling the job
Click on the Schedules tab.
Click on New Schedule…
Enter a name for this schedule. Click then on Change button to set the schedule to suite your needs.
You can enter an end date, if desired.
This is how the new schedule should look like.
And this is how the job should look like, after finishing setting up the schedule. You can set multiple schedules if you need to, using different patterns. For instance you might want to have one report generated every Monday morning and one every first day of ech month (Mondays are not always the first day in month 🙂 ). You can make sure this way that every month start you’ll have a snapshot of the space allocation for your ISA Server database.
Step 4 – Notifications
Click on the Notifications tab and select the desired notification method and operator from the lists provided. Having a notification only when the job fails should be more than enough. Make sure you leave the Write to Windows application event log option checked. This is useful when SQL is not able to send notifications for whatever reason and you need to see what caused a job to fail.
Here’s how the output will be generated (output wrapped for publishing purposes):
========================================================================================
Job ‘Generate space usage statistics’ : Step 1, ‘Execute stored procedure usp_SpaceMonitor’ : Began Executing 2003-09-20 22:24:51
database_name database_size unallocated space
——————————————————————————————————————————– —————— ——————
ISAServer 4.00 MB 2.45 MB
reserved data index_size unused
—————— —————— —————— ——————
560 KB 160 KB 296 KB 104 KB
name rows reserved data index_size unused
——————————————————————————————————————————– ———– —————— —————— —————— ——————
FirewallLog 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
——————————————————————————————————————————– ———– —————— —————— —————— ——————
WebProxyLog 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
——————————————————————————————————————————– ———– —————— —————— —————— ——————
PacketFilterLog 0 0 KB 0 KB 0 KB 0 KB
=========================================================================================
This might not look very usefull or friendly, but it’s intended to only provide a quick insight about the space allocation and nothing more.
Method 2 – Using Windows’ Scheduled Tasks
Scheduled Tasks is very common tool for many admins. So, I’m pretty sure everyone’s familiar with it, so let’s roll the ball.
One quick note, though. As the methods are totally different, there will be some differences in how the output will be generated. When using EM to generate the output, there was an explicit option to append the output to the desired file. On the opposite, when using Scheduled Tasks and hence, command (or batch files), other methods for appending the results must be used, as other rules are governing the process (in this case, the OS).
Use any text editor to create a batch file with the following content:
@ECHO OFF
CD “C:\User\Private\ISA Stats”
REN SpaceMonitor.txt sm.txt
DATE /t >> start.txt
TIME /t >> start.txt
osql -E -Q “EXEC usp_SpaceMonitor” -d ISAServer -o monitor.txt
COPY start.txt+monitor.txt+sm.txt SpaceMonitor.txt
DEL start.txt
DEL sm.txt
DEL monitor.txt
What these commands will do is as follows:
- Save the existing reports
- Mark the start of the job (DATE /T and TIME /T)
- Run the script to generate the current status report
- Copy all the outputs: job start, current status, previous reports in one file (COPY)
- Delete temporary files
Few words about step 2. There are some params that needs to be discussed a little bit.
Here’s the command we’ve used:
osql -E -Q “EXEC usp_SpaceMonitor” -d ISAServer -o monitor.txt
-E instructs osql to use integrated security, that is to try to authenticate using current network credentials. If SQL server is not setup to use integrated security, this attempt will fail. Use –U and –P instead.
-Q tells osql to execute-and-quit the command passed immediately after –Q param, enclosed in doube quotes. This param is different than –q which will not terminate osql and will wait undefinitely for a quit command.
-d tells osql which database to be used as default for the script execution context, that is on which database the script is to be executed. If this param is not passed or the database name is misspelled, the script will fail.
-o redirects the output of the script to the specified file. The file is overwritten if one with the same name exists.
Now, that the command-file is created, what we’ll have to do is to schedule it to run as we need. I will not insist on this part, as I’m confident that all of you have used Scheduled Tasks and still using it.
Some final considerations
On some setup scenarios, an ISA Admin might not have access to EM or to run scripts under a local account (Windows account) not speaking about running the scripts under a domain account. When this is the case, than method 2 is the right (and only) method to use as it only requires a valid SQL login and having osql installed on the desired computer.
On the other hand, if ISA Admin has access to SQL via EM, than method 1 is the preferred method, because it also provides a very easy-to-use, built-in notification mechanism along with integration with Windows Application event log.
So, pay attention on how your SQL Server is setup in terms of authentication and use the appropriate parameters for osql.
Feel free to drop me PM on the message board if having any questions regarding this article.
Until next article, peace on your logs…
