I want to configure my IIS server to send log information to my Microsoft SQL Server 7.0 machine. I created a system Data Source Name (DSN) to the SQL Server machine and IIS so I could use ODBC logging, but how do I create the table structure in the database file?
ODBC logging lets you configure IIS to write logs to a database such as SQL Server. ODBC logging presents some interesting possibilities. The chief benefit is that it can act as a central store for multiple servers and sites. You can store all your Web sites' logs in one database, which makes running queries on the set of logs for the entire IIS server easy.
For example, if you've identified an intruder's IP address, you can quickly search for that address to see what mischief the intruder has been up to on any of your sites. In addition, you can run programs to monitor the log database for suspicious activity. Monitoring log files becomes more complicated when you store the logs for each Web site in various folders, each containing many files.
To centralize your log files, you can set up ODBC logging to record all IIS activity in one database. However, the setup isn't quite as simple as pointing to the SQL Server database in the logging properties. You must first properly configure the SQL Server machine by setting up authentication in SQL Server, establishing a DSN on the IIS server that connects to the SQL Server machine, and creating a SQL Server table with a specific format to receive the logging information.
To begin setting up ODBC logging, create a table in SQL Server to contain the logs. Unfortunately, IIS controls the fields and their lengths internally. You can't modify or add optional fields as you can in the World Wide Web Consortium (W3C) format, which is one of the main drawbacks of ODBC logging. Table 1 provides the SQL Server field names with their data sources and types. Fortunately, the \winnt\system32\inetsrv folder contains a file called logtemp.sql, which contains a query that will create the correct table.
The Microsoft article "Configuring ODBC Logging in IIS 4.0" (http://support.microsoft.com/support/kb/articles/q245/2/43.asp) provides step-by-step instructions for creating the DSN and setting up logging properties in IIS. Although this article states that the length of the IP address field is 255 characters, the script sets the field to a length of 50 characters. The script works well, and you don't need to have a field length of 255 characters for an IP address. Be advised that you need to know how to set up SQL Server before proceeding. Also, be careful regarding usernames, passwords, and field names that you create, because they're case sensitive.
Microsoft literature is replete with references to the performance penalty required for a server hosting IIS with ODBC logging enabled. In addition, logging sometimes stops if the SQL Server machine becomes temporarily unavailable. My advice is to try to achieve the same result with a different method.
SQL Server 7.0 offers several useful importing and exporting capabilities, including Data Transformation Services (DTS). With DTS, you can easily and quickly import your logs into a SQL Server database. After you define the transfer in the DTS UI, you can save the transfer in a package. You can then schedule the package to run at any time. (You can find a good article titled "Data Import/Export" about this topic at http://www.microsoft.com/technet/sql/dataimp.asp.) Each time you want to transfer your logs to SQL Server, you need to
- Run iisreset.exe to stop IIS.
- Copy the logs to a folder for import to SQL Server.
- Copy the logs to a second folder for backup.
- Delete the logs from the original folder.
- Run iisreset.exe to restart IIS.
- Run the SQL Server import package.
- Delete the imported logs.
You could easily write a script that automates this process.
This process works well for sites that have fewer than 100 Web sites and can afford to be down for a few minutes at night. However, this process doesn't work well for sites with hundreds of Web sites because startup delays will occur when you restart IIS.