8/4/2007
SQLAutoDoc SQL Server database dictionary & monitoring utility v2.0.0.0
 |
How to obtain the latest version of SQLAutoDoc |
|
|
|
|
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
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.
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 
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
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 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 
icon.
to add a new server. Enter the name of the server and, if you wish, a description. Then click on the
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:
- Standard. This is a standard user id and password.
- 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.
- FileName. This is a connection directly to a SQL Express database.
- 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
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
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
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
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:
- Addition or removal of a login
- Addition or removal of a table
- Addition or removal of a table column, or change in size or data type of a column
- Addition or removal of a table index.
- Addition or removal of a view, or change in the source of a view
- Addition or removal of a stored procedure, or change in the source of a stored procedure
- Addition or removal of a function, or change in the source of a function
The 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
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
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
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
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
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
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
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
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
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
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
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
