JLION.COM
SQLAutoDoc
8/4/2007

SQLAutoDoc SQL Server database dictionary & monitoring utility v2.0.0.0

How to obtain the latest version of SQLAutoDoc
Installing and Configuring SQLAutoDoc
Viewing SQLAutoDoc Schema Reports
The SQLAutoDoc status reports

Known Issues

Version Issue
2.0.0.0 Documentation incomplete

Installing SQLAutoDoc

I've created a setup program that installs SQL AutoDoc with the default options. This setup program has been tested with Windows XP and Windows Server 2003.

The setup program has these prerequesites:

  • SQL Express 2005
  • IIS (if you're installing to a Windows XP Desktop, you'll need to use the add/remove software wizard to add IIS. It's a windows component)
  • The DotNet Runtime v2.0. Note that you may need to use the ASP_REGIIS.exe utility to configure IIS to use DotNet 2.0. More information available at www.microsoft.com

SQLAutoDoc has three core components. These components are:

  • A SQL Server database. This database does not at present use any 2005-specific functionality and by changing two configuration files, you can, once SQLAutoDoc is installed, point it at a database on a non-local 2000 or 2005 server. The configuration files are web.config in the SAD web root directory, and SQLAutoDocSVC.exe.config in the SAD bin directory. I'll describe how to update these configuration files later on, if anyone asks.
  • A Windows Service. This service is called SQLAutoDocSVC and after the setup program is run this service will appear in the service list. This service is responsible for the polling of databases for schema and status information. After you have installed configured SQLAutoDoc you will need to manually start the service by going to services in the windows control panel.
  • A web administration interface. The web interface is where all administration takes place.

The installation program creates a new web application called SAD and installs a windows service called SQLAutoDocSVC in the bin directory of this new web application. It also creates a program group with an internet shortcut to the default page of the SQLAutoDoc web application. Another such shortcut is placed on the desktop. Finally, permissions are adjusted on the app_data and content subdirectories of the web application. For this reason you must be logged in as administrator of the computer on which you are installing SQLAutoDoc.

After installation is complete, click on the internet shortcut to SQLAutoDoc that you'll find on your desktop (or in your programs menu). SQLAutoDoc will detect that this is the first time that you have used it, and that the current database (which by default is a SQL Express 2005 database called METADATA) has no tables. It will prompt you to create the database schema in the METADATA database by showing you this prompt:

The Install Screen

installation screen

Click on the "Install" button to automatically create the schema. When SQLAutoDoc has completed, it will automatically forward you to the administration interface where you can set up your SMTP mail settings, define default polling intervals and add databases to be automatically documented.

How to configure SQLAutoDoc to use SQL Server Standard or Enterprise

By default SQLAutoDoc uses its own SQL Express 2005 database. If you have SQL Server Standard or Enterprise edition version 2000 or 2005 then you can configure SQLAutoDoc to use it as the host for its MetaData database.

To make this change, you will need to edit the web.config file in the BIN directory of the SAD web application on your server. This config file contains these two configuration entries:

<add key="METADATA" value="Server=.\SQLExpress;integrated security=true;AttachDbFilename=|DataDirectory|\metadata.mdf;User Instance=True;"/>
<!-- <add key="METADATA" value="SERVER=?;DATABASE=metadata;UID=?;PWD=?"/>-->

The second entry is there for your convenience. Simply create a METADATA database on your database server and enter the server name, and the user id and password that SQLAutoDoc should use when connecting to the server. Then comment out the top line, which points SQLAutoDoc at SQLExpress, and uncomment out the second line so that the two entries look something like this in your web.config file:

<!-- <add key="METADATA" value="Server=.\SQLExpress;integrated security=true;AttachDbFilename=|DataDirectory|\metadata.mdf;User Instance=True;"/>-->
<add key="METADATA" value="SERVER=myserver;DATABASE=metadata;UID=myuid;PWD=mypwd"/>

Next, you will also need to update the configuration file for the windows service. The configuration file for the windows service is called SQLAutoDocSVC.exe.config and it also is located in the SAD bin directory.

In this configuration file, simply find the line that looks like this:

<add key="mode" value="web"/>

Change the line to SVC to tell the windows service that the database is located on a remote database server. The updated configuration file entry should look like this:

<add key="mode" value="web"/>

Once you've made the change, restart the SQLAutoDocSVC service using the windows services control panel.

The SMTP Settings Screen

When the database schema has been initialized the main SQLAutoDoc control page will be displayed, as shown below. The The next step in configuring SQLAutoDoc is to set up your SMTP server. Why is this necessary? Well, SQLAutoDoc can send out a variety of email reports. It also will send out error alerts if (for example) it is unable to connect to a database.

SMTP screen

Enter your SMTP settings here, and send a test message to verify that they are correct. Once the SMTP settings have been entered correctly, click on the Save icon to save your changes.

Configuring SQLAutoDoc

After SMTP settings have been entered, the next step is configure SQLAutoDoc by setting up databases to monitor and by setting the interval at which email reports should be sent out.

The "Settings" tab controls how frequently and to whom comprehensive status report should be sent, as well as the server path that should be used by the email reports to link back to SQLAutoDoc for charts and drill-down reporting.

The Settings Screen

settings screen

The comprehensive report includes a section for each monitored database and reports on size, connections, blocking, wait-states and schema changes. The email address and server path is also used by individual email reports that can be set up on a database-by-database level and which include only one type of data (only schema changes, for example, or only connection trends).

Once you have configured the autodoc settings appropriately, click on the Save icon to save your changes.

The Server Properties Screen

Databases in SQLAutoDoc are grouped by server, so before adding a database you must first add the server on which it is located. I have tested SQLAutoDoc with SQL Server 2000 and SQL Server 2005 databases. It may work with other versions of SQL Server. If you try it and it does, please let me know!

To add a new server, click on the "Servers" link in the navigation tree. Then click on the Add icon. to add a new server. Enter the name of the server and, if you wish, a description. Then click on the Save icon to save your changes. Note: The default name of the SQL 2005 Express server is ".\SQLExpress". Use this server if you wish to monitor a SQL Express database such the MetaData database used by SQLAutoDoc by default.

After you have successfully added a server, the server name will appear under the "servers" link in the navigation tree.

Adding databases

Once you've added a server, you can add databases located on that server. To monitor a database, click on the link for the server where the database is located then on the add icon.

SQLAutoDoc supports connecting to SQL Server databases in any of four ways:

  1. Standard. This is a standard user id and password.
  2. Trusted. This is a trusted connection. To use this, you must change the login account used by SQLAutoDocSVC to an account recognized by the database.
  3. FileName. This is a connection directly to a SQL Express database.
  4. TCP/IP. This is connecting to SQL Server by IP address instead of name.

The "IsActive" checkbox allows you to configure the database but not monitor it. The description field allows you to describe the purpose of the database.

Once you've configured the connection to the database, use the save icon to save your changes.

The Add Database Screen

Server Properties

Configuration settings

After the database connection has been set up, you can configure how often it will be monitored and whether individual email reports will be sent with the various kinds of information.

A full scan on the database can be forced by clicking on the scan now icon.

The Database Properties Screen

Database Properties

Clicking on the schema link in the navigation tree will display the schema report. This report shows schema changes to tables, views, stored procedures and functions that were detected during the last seven days.

By changing the dates and clicking on the refresh view, schema changes detected during other date ranges can be viewed, and if you wish to print the report, click on the view in seperate window icon to display the report in a stand-alone window.

Clicking on any of the links in the schema report will zoom appropriately. For example, clicking on the tables link will take you to the list of tables detected in the current database. Clicking on the link for a specific table will show you the fields for the current version of that table and allow you to view information for previous detected versions of the table. Similar capability exists for views, stored procedures and functions.

The Schema Report Screen

Schema Report

Use the search screen to search for any occurances of text in the name of a table, column, view, stored procedure or function, or in the body text of a view, stored procedure or function. You can search only current revisions or all revisions, and you can include schema elements that were detected at some point in the past but not during the last scan (deleted schema elements).

You can also restrict your search to changes made in a specific period.

The Search Screen

Search Screen

The compare screen is intended to allow you to compare the schema of either two different databases or of different revisions of the same database. The report lists detected schema differences. When the source schema is newer than the target, then a link to the source schema element is displayed in the left-most column. When the two schema elements are the same, then a link to the source schema element is displayed in the center. If the target schema element is newer, then a link to it is displayed on the right.

Note that most changes to the schema of a database will cause the database to have a new revision date in the compare database screen. The kinds of schema changes that can cause a database revision are as follows:

  1. Addition or removal of a login
  2. Addition or removal of a table
  3. Addition or removal of a table column, or change in size or data type of a column
  4. Addition or removal of a table index.
  5. Addition or removal of a view, or change in the source of a view
  6. Addition or removal of a stored procedure, or change in the source of a stored procedure
  7. Addition or removal of a function, or change in the source of a function

The Compare Screen

Compare Screen

The login list shows logins permitted for the current database and when the login was first detected. Updating the description of the login in this screen will cause the extended properties associated with the login to also be updated. IE: The description will appear in the management studio or enterprise manager properties of the login.

The Login Screen

Login Screen

The table screen shows a list of tables detected in the database, with the date a modification to the table was last detected and the number of total modifications detected since sqlautodoc started watching (the table rev).

The list can be sorted in either ascending or descending order by modication date, count of revs or name. Note that the table list can be refeshed by clicking on the refresh icon, or displayed in a separate window for printing by clicking on the display in separate window icon.

The Table Screen

Table Screen

Clicking on the question-mark icon causes a field list and the table description to be displayed. Changes to the field description or table description are saved to extended properties where they appear in the management studio or enterprise manager properties for the table and fields.

The Table Update Screen

Table Update Screen

Like the table screen, the function screen shows a list of functions detected in the database, with the date a modification to the function was last detected and the number of total modifications detected since sqlautodoc started watching (the function rev).

The list can be sorted in either ascending or descending order by modication date, count of revs or name. Note that the view list can be refeshed by clicking on the refresh icon, or displayed in a separate window for printing by clicking on the display in separate window icon.

The Function Screen

Function Screen

Clicking on the question mark icon to the left of each row will display the text of the function. On this screen, a description can be entered for the function that will appear in the management studio or enterprise manager properties for the function. Also on this screen, text for prior versions of the function can be displayed.

The Function Update Screen

Function Update Screen

Like the function screen, the view screen shows a list of views detected in the database, with the date a modification to the view was last detected and the number of total modifications detected since sqlautodoc started watching (the view rev).

The list can be sorted in either ascending or descending order by modication date, count of revs or name. Note that the view list can be refeshed by clicking on the refresh icon, or displayed in a separate window for printing by clicking on the display in separate window icon.

Clicking on the question mark icon to the left of each row will display the text of the view. On this screen, a description can be entered for the view that will appear in the management studio or enterprise manager properties for the view. Also on this screen, text for prior versions of the view can be displayed.

The View Screen

View Screen

Like the function screen, the stored procedure screen shows a list of stored procedures detected in the database, with the date a modification to the stored procedure was last detected and the number of total modifications detected since sqlautodoc started watching (the stored procedure rev).

The list can be sorted in either ascending or descending order by modication date, count of revs or name. Note that the view list can be refeshed by clicking on the refresh icon, or displayed in a separate window for printing by clicking on the display in separate window icon.

Clicking on the question mark icon to the left of each row will display the text of the stored procedure. On this screen, a description can be entered for the stored procedure that will appear in the management studio or enterprise manager properties for the stored procedure. Also on this screen, text for prior versions of the stored procedure can be displayed.

The Stored Procedure Screen

Stored Procedure Screen

The size report is intended to alert you to trends in the growth rate of your database so that you will be better able to plan for future hardware requirements.

Clicking on the size link in the navigation tree displays a chart showing average size for the database for the last twelve weeks, if available. Note that the SQLAutoDoc uses the sp_spaceused system stored procedure to record table sizes. While the sizes recorded may not match exactly the size of the datafiles on disk (because of logging and other issues), they are an accurate indicator of growth trends.

Clicking on an individual week in the size trends chart will display the average sizes of the individual tables as recorded during that week, with a sparkline chart that shows 12 week trends for that individual table.

The Size Report

Size Report

The blocking report is intended to allow you to track blocking trends. It can be quite difficult to identify the cause of intermittent blocking issues, especially when they are sporadic. When SQLAutoDoc detects blocking during one of its period scans it records connection information bouth about the process being blocked as well as about the process doing the blocking.

The default view shows blocking trends for the last 12 weeks. Clicking on a specific week shows a trend shart for that week, clicking on a specific day shows trends by hour and clicking on a specific hour shows the blocking detail report.

The Blocking Report

Blocking Report

The blocking detail report lists blocks that were detected at any point during a specific hour. For each block, information about the connection being blocked is shown as well as information about the connection doing the blocking.

The Blocking Detail Report

Blocking Detail Report

The connections report tracks connection trends and is useful in identifying surges in the number of connections to a database or server. Connections use server resources and greater numbers of connections than expected can negatively impact performance.

By default the connections report shows connections in the current day, beginning at midnight and ending at 11:59 pm. Both connections to the current database (blue) and connections to the server but not to the database (green) are displayed.

Clicking on the greent back button will zoom out to the connections by day report, and from there zooming out will display connection trends for the last twelve weeks.

The Connections Report

Connections Report

A wait state is a delay experienced by SQL Server when it is attempting to respond to a request. There are lots of reasons why such delays can occur. Wait states are caused by, among other things, network issues that constrain how quickly data can be sent, contention issues related to locking, and hardware issues such as slow drives or not enough memory.

SQLAutoDoc tracks waitstate trends. In the wait state report, wait states are displayed that have occurred during the last 30 days. Sparkline charts show trends for each of the wait states.

Drilling in by clicking on a wait state shows more information about the frequency of occurance of that wait state during the last 30 days.

The WaitStates Report

WaitStates Report