ISA Server & SQL Server – Brothers in Arms – Part 1

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:

  • You have access to a SQL Server
  • You have been granted db_owner permissions to ISA Server logging database
  • SQL Server has been setup to make use of mail features for both SQL service and SQL Server Agent service
  • 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:

  • Changing the recovery model of our ISA Server database
  • Setting up some recommended security-related features
  • Creating an operator to be notified whenever this alert is fired
  • Creating an alert that will be triggered when a specific error occurs (in our case, log full and hard disk full)
  • Creating a job to be executed by the alert as a response to the selected error
  • 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

  • Transaction log
  • – a special place where SQL Server keeps trace of *all* transactions issued against it

  • Checkpoints
  • – an internal housekeeping process, started automatically by SQL Server at defined intervals. During this process, various tasks are performed depending on various settings

  • Completed transactions
  • – transactions that are completed at the time when a checkpoint is started

  • Incomplete transactions
  • – transactions that are not completed at the time when a checkpoint is started

  • Bulk-operations
  • – massive data transfer from an external data source into a specific table

  • Roll-forward
  • – completed transactions are applied to the relevant databases and transactions are deleted from the transaction log

  • Roll-back –
  • 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:

  • The recovery model is setup according to local specific needs
  • The database is setup to grow automatically as it needs
  • An alert is setup to monitor the transaction log (preventing log full error)
  • An alert is setup to monitor the disk space usage (preventing disk full error)
  • 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:

  • Simple
  • – the transaction log is cleared at checkpoints

  • Full recovery
  • – the transaction log is not cleared at checkpoints and large-scale operations and bulk load operations are fully logged

  • Bulk-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:

  • Start Enterprise Manager (Start, Programs, Microsoft SQL Server, Enterprise Manager)
  • Expand Microsoft SQL Servers, then expand SQL Server Group (please note that these containers appears in a typical installation of SQL Server. Your DBA might have changed this to suite his/her specific needs. Please contact your local DBA if your Enterprise Manager does not shows these containers or you have doubts).
  • Expand your server
  • Expand Databases container
  • Right-click on ISA Server database
  • Click on Properties
  • Click on the Options tab
  • Select your desired 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:

  • Start Enterprise Manager (Start, Programs, Microsoft SQL Server, Enterprise Manager)
  • Expand Microsoft SQL Servers, then expand SQL Server Group (please note that these containers appears in a typical installation of SQL Server. Your DBA might have changed this to suite his/her specific needs. Please contact your local DBA if your Enterprise Manager does not shows these containers or you have doubts).
  • Expand your server
  • Expand Databases container
  • Right-click on ISA Server database
  • Click on Properties
  • Checking automatic growth for data files

  • Click on the Data files tab

  • 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.

    Leave a Comment

    Your email address will not be published.

    This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

    Scroll to Top