ISA Server & SQL Server – Brothers in Arms – Part 1
By Alin Selicean
You are responsible for your corporate ISA Server. You have a spare server and want to have SQL store ISA server logs. You have done all as depicted in Nathan’s article http://www.isaserver.org/tutorials/How_to_setup_SQL_Logging_in_ISA_Server.html.
You also thought of having a backup solution for your logs and have done all as per Jens’s article located at http://www.isaserver.org/tutorials/Fix_for_ISA_stall_when_ODBC_data_source_is_unavailable.html
What next ? Well, as an MCDBA I can tell you that one of DBA’s nightmare is to end up with one of the transaction logs filled up unexpectedly. If the log fills up, nothing and nobody can work on that database. So your ISA Server logs will stall.
The actions in this article assume the following:
The main purpose of this article is to provide a hands-on experience for ISA Admins who has access to a SQL server without a DBA to assist them in automating some of the tasks a DBA usually performs.
We’ll cover the following:
I’ll start with very few basic terms used throughout this article, to avoid any misunderstandings.
- Transactions
– any command sent to SQL Server that affects the data, like an INSERT statement that appends one or more records to a specific table in a specific database
– a special place where SQL Server keeps trace of *all* transactions issued against it
– an internal housekeeping process, started automatically by SQL Server at defined intervals. During this process, various tasks are performed depending on various settings
– transactions that are completed at the time when a checkpoint is started
– transactions that are not completed at the time when a checkpoint is started
– massive data transfer from an external data source into a specific table
– completed transactions are applied to the relevant databases and transactions are deleted from the transaction log
incomplete transactions recorded in the transaction log are ignored and transactions are deleted from the transaction log
Our Story for the Show
Let’s use the following scenario, for our article purpose: your firewall is being heavily scanned and you have an IDS alert set to be fired when a port scanning occurs. Having your logs on a SQL Server, ISA will very likely generate a *massive* load on firewall log table. The checkpoint process is usually fired when the system goes idle, so that’s the case and the transaction log will eventually became full. If the transaction log is full, no activity takes place on the SQL Server and your firewall will stall.
What can we do to avoid this ? Well, there is a solution and with various implementations in terms of sequence. Basically, what an ISA Admin has to do is to make sure that:
Recovery Model
Recovery model informs the SQL Server how to track transactions and how long to keep the transaction log. Depending on how this option is set, when SQL Server starts, it performs an automatic recovery for every database. This step reads the transaction log and all completed transactions are rolled-forward and all incomplete transactions are rolled-back.
For recovery model, there are 3 options:
– the transaction log is cleared at checkpoints
– the transaction log is not cleared at checkpoints and large-scale operations and bulk load operations are fully logged
– similar to Full Recovery except the large-scale operations and bulk load operations are recorded only as occurrence, not fully logged
At this point, a conclusion can be drawn: the recovery model dictates at some extent the space requirements for our database. Simple recovery requires the least space (only transactions between 2 checkpoints exist at any given time in the transaction log), Full recovery model requires the most. Bulk Logged lays in between, because no bulk operation details are recorded.
For the time being, simple recovery model should be enough for a typical ISA Server scenario. Also, this setting together with your business scenario, rules and requirements will affect your backup strategy for this particular strategy.
How to check / set the recovery model of your database
Here’s how to do check/ set the recovery model:
DB Settings – Setting the Recovery model
After selecting your desired recovery model, click on OK to close the window. That should be all for setting the Recovery Model.
Other Recommended Security Settings
We’ll continue with 2 recommended settings related to security. First is to have auditing enabled at least for failed authentications. A good practice is to have auditing enabled for both successful and failed authentication, as seen in the graphic below. Also, make sure you have the option Allow cross-database ownership chaining un-checked if you don’t require it for some other reasons.
NOTE:
If you don’t have the Allow cross-database ownership chaining option means that your SQL Server is not running SP3.
To check or set these settings, right-click on your server in Enterprise Manager and then click on Properties. The following dialog box will popup and select the Security tab. Check or set your options in the corresponding areas and then click OK. If you change any of the above options, SQL Server will ask you that its services needs to be restarted. Answer Yes and wait until services are restarted.
Server settings – Authentication & Audit level
NOTE
:
If there are any services that depend on MSSQLServer service these will be stopped as well. You may have to restart these services manually.
Automatic growth feature
Automatic growth feature was implemented starting with version 7 of SQL Server. Before this, DBAs had to closely monitor space usage for every database they had it on their SQL Servers. It offers a very convenient way of monitoring and managing space usage for individual database. Automatic growth can be setup for both database data files and associated transaction log. In combination with alerts, jobs and operators, it offers a powerful tool to closely monitor, control and manage space usage for your database.
How does SQL Server manage this? The following diagram briefly depicts what is happening when SQL Server is tries to grow a database.
Of course, there are some other tests performed, at the OS level, when SQL Server tries to grow the database and one of this might fail and this we’re trying to avoid: disk full.
How to check / setup Automatic Growth values
For checking / setting up automatic growth for your ISA Server database, follow these steps:
Checking automatic growth for data files
To avoid full disk errors, you should always set up the Restrict file growth in the Maximum file size group to a specific size. The values depicted in the image are not realistic and serves only as examples. You should evaluate the space need for your database using this simple formula:
Space_Reqd = Number of daily records * Record length
Where [Number of daily records] can be estimated by investigating a week’s logs and Record length is calculated by adding the length of every column in the table. The formula must be run against every table in your database (Firewall, Web Proxy and Packet Filter) and you’ll get an estimate for your daily needs. The files will be grown in increments as specified in the File growth group of values.
In an OLTP environment, the transaction log usually requires approximately 25% from the data file size. Considering our figures above, the maximum size to which transaction log is allowed to reach is 125MB. You will create the same settings (different values, of course J ) for transaction log. For this, move to the Transaction Log tab to check or adjust the settings.
Please keep in mind that the values used in this section are serving only as examples and an evaluation of your daily space needs should be performed in order to achieve best performance.
In Part 2 of this article we’ll discuss database monitoring in detail and creating an alert. Then we will create an alert response job to control what actions should take place after the alert is triggered. See you then! –Alin.
