Help for Foxhound 1.1.3832a

Introduction and Setup

Foxhound is two products in one: it is both a database monitor and a schema troubleshooter for SQL Anywhere.

A database monitor is a computer program that measures the activity of a database management system and displays those measurements in a meaningful way, so that you can easily see that everything's OK or quickly learn about problems and threats to performance and availability. A monitor can also send email alerts when something goes wrong.

A schema troubleshooter is a computer program that performs a static analysis of the tables, columns, indexes, foreign key relationships and other objects in the database, displays those objects in a way that's easy to understand and easy to navigate, together with various facts, figures and curiosities which may be problems that need attention.

Foxhound Design Decisions

Foxhound Highlights

Requirements

Foxhound Setup

Customizing the Setup

Running Foxhound as a Service

Connecting to Target Databases

Backup and Restore

Controlling Growth

SQL Anywhere is a trademark of Sybase, Inc.

Foxhound Design Decisions [Top]

Straightforward all-the-facts interface for professional developers.
Monitor shows important statistics and value-added calculations, not graphs or waveforms.
Email alerts include automatic "all clear" messages when problems are resolved.
Schema display shows physical attributes, not an abstract or logical view; e.g., VARCHAR ( 10 ) NOT NULL DEFAULT '' as opposed to "String".
Support for all viable versions of SQL Anywhere: 5.5, 6, 7, 8, 9, 10, 11 and 12.
Support matches features of each version, not some lowest common denominator.
Runs out-of-the-box, just connect and go.

Foxhound Highlights [Top]

See what's happening now
The Monitor page is automatically refreshed every 10 seconds to show the last 10 samples, plus details of the first 100 current connections.
See what happened yesterday
The History page lets you scroll through all the old samples and all the connections for each sample.
Get email alerts
The Alerts Criteria lets you choose which conditions you want Foxhound to monitor and send email alerts when those conditions are met; e.g., Foxhound can't connect to the database, the database disk free space has sunk below 1G, the server's been using more than 90% of the CPU for the past 10 sampling periods, there's been an HA failover and so on. There are 28 different kinds of alerts you can ask for, and most of them allow you to fine-tune the thresholds.
See blocked connections
Foxhound answers all your questions: "Who's blocked? What are they waiting for? How long have they been waiting? Who's holding the locks? What kind of locks are they? What tables? What rows? How long has it been since they last did a commit?"
Persistent connections
The monitor tries to stay connected to the target database 24 by 7; it automatically re-connects after an outage, even when Foxhound itself is shut down and restarted.
Persistent data
The monitor data is preserved in the Foxhound database as long as you want, all instantly available in the History view.
Peak highlighting
Peak values are displayed as links into the History view, and values that reach 80% or 50% of the peaks are color highlighted.
Schema at-a-glance
Each table is shown as a formatted CREATE TABLE statement, plus CREATE INDEX statements and ALTER TABLE statements for the foreign keys. They're all syntactically correct for easy copy and paste, and up to date at the time of display.
Follow the foreign keys
For each table, there are hypertext links to every parent and child.
How much space?
For each table, you see how much disk space is used for data, extension pages, all the indexes... and for each index too. Bytes per row measurements include all disk storage so they can be used to extrapolate future requirements.
Find those columns
All the column names appear in one alphabetic list, with hypertext links into the table definitions. Plus you can see at a glance where different data types are used for the same column name in different tables.
Facts & Figures
This section tells you all the names your database goes by: DSN, server, database, machine, plus the connection string used by dbping. It tells you where all your database files are located, how much free space they contain, and how badly each file fragmented. Plus, it tells you how big your database is: how many rows, how many bytes, how many tables, columns, indexes, even how many times each different data type is used.
What's unusual about this database?
If your database is heavily fragmented, or some of your database options have been changed from the defaults, or you're running with a different page size, you'll see it at a glance in the "Database Curiosities" section.
What's unusual about this table?
A "curiosity" is an interesting fact; it may or may not be a problem. The Table-level Curiosities section shows dozens of interesting facts about individual tables; e.g., no primary key, most bytes per row, large number of secondary indexes, and large amount of index space relative to table space,

Requirements [Top]

1. Windows - Foxhound works with target databases running on other operating systems but the Foxhound engine itself only runs on Windows, and has been tested on Microsoft Windows XP SP3, Vista SP1 and Windows 7.

2. ODBC - Foxhound itself uses ODBC to connect to your target databases. You don't necessarily need ODBC installed on the servers running your target databases, and you can specify DSN-less connections for Foxhound by using the "String" tab on the main menu page.

3. Firefox or IE - The Foxhound client has been tested using Mozilla Firefox 3 and Microsoft Internet Explorer 8.

4. JavaScript - Foxhound needs "JavaScript" or "Active scripting" to be enabled in the browser; here's how:

Firefox 3: Tools - Options - Content - check "Enable JavaScript"
IE 8: Tools - Internet Options - Security - Local intranet - Custom level... - Scripting - Active scripting - check "Enable"

5. SQL Anywhere version 5.5.5.2787 through version 12 for target databases - If you have any target databases running on SQL Anywhere 5.5, you may have to upgrade them to 5.5.5.2787 for Foxhound to work properly with them.

6. SQL Anywhere 32-bit version 11.0.1.2276 or later for Foxhound - Foxhound works with target databases using 5.5 to 12 of SQL Anywhere including 64-bit versions, but the Foxhound engine itself needs the x86 version of SQL Anywhere 11.0.1.2276 or later to run.

7. SQLANY11 - The delivered *.bat files expect that the SQLANY11 environment variable points to SQL Anywhere 11. If that is not the case you may have to modify the *.bat files. Here is the default setting on Windows XP, Vista and Windows 7:

SET SQLANY11=C:\Program Files\SQL Anywhere 11

8. FOXHOUND1 - The delivered *.bat files expect that the FOXHOUND1 environment variable will be created by the Foxhound installation (which will happen by default). If that is not the case you may have to modify the *.bat files. Here is the default setting for Windows XP:

SET FOXHOUND1=C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1\

and for Windows Vista and Windows 7 it is this:

SET FOXHOUND1=C:\ProgramData\RisingRoad\Foxhound\

9. Disk space - When re-installing or upgrading Foxhound, 60% or more extra disk space may be required during the post-setup process. The "60% or more" figure applies to the amount of disk space occupied by the existing Foxhound installation, not the total used disk space on the drive.

Foxhound Setup [Top]

Foxhound consists of server and client components.

The Foxhound server (or "engine") runs as a single SQL Anywhere database in a Version 11 engine configured to run as an HTTP (web) server.

The Foxhound client is browser based, with the default URL being this:

http://localhost/

The standard Foxhound installation creates Windows Start menu shortcuts to batch files that start both the Foxhound engine and the Foxhound client on the same computer:

Start - All Programs

- Foxhound

- Start Foxhound via Firefox

- or -

- Start Foxhound via IE

You can make Foxhound part of the home page row in Firefox. For example, here's how to set the Firefox home page tabs to Google and Foxhound:

Tools

- Options

- Main

- Startup

- Home Page:

http://www.google.com/|http://localhost/

Here's how to do the same thing with Internet Explorer:

Tools

- Internet Options

- General

- Home Page

http://www.google.com/
http://localhost/

Customizing the Setup [Top]

By default the Foxhound batch files are installed in this location on Windows XP:

C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1

and on Vista and Windows 7 it is here:

C:\ProgramData\RisingRoad\Foxhound1

These are the command files which start Foxhound:

$backup_foxhound1.bat
$start_foxhound1_engine.bat
$start_foxhound1_firefox.bat
$start_foxhound1_firefox_debug.bat
$start_foxhound1_ie.bat
$start_foxhound1_ie_debug.bat

You can customize those batch files, or create new ones as you see fit. Each one contains the following command or a variation thereof: CD /D "%FOXHOUND1%" "%SQLANY11%\Bin32\dbspawn.exe"^ -f^ "%SQLANY11%\Bin32\dbsrv11.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gn 120^ -n foxhound1^ -o foxhound1_debug.txt^ -oe foxhound1_debug_startup.txt^ -on 10M^ -qn^ -sb 0^ -x tcpip^ -xd^ -xs http(port=80;maxsize=0;to=600;kto=600)^ foxhound1.db^ -n f

Here is a description of each part of those commands:

CD /D "%FOXHOUND1%" -- relies on the FOXHOUND1 environment variable to change the current directory
"%SQLANY11%\Bin32\dbspawn.exe"^ -- launchs dbsrv11.exe indirectly so the batch file will continue running after SQL Anywhere starts
-f^ -- force dbspawn to start another dbsrv11.exe even if one is already running
"%SQLANY11%\Bin32\dbsrv11.exe"^ -- change this to dbeng11.exe if you want to prevent network access to Foxhound
Note: The Foxhound application resides inside the foxhound.db database file, and that database must be run on its own engine (dbeng11.exe or dbsrv11.exe). In other words, you cannot start the foxhound.db database on an engine that is already running some other database, and you cannot start another database on an engine that is already running Foxhound. You can run other databases on the same computer, of course; they just need their own engine(s).
-c 25p^ -- set the initial RAM cache size to 25% of available memory
-ch 50p^ -- set the maximum RAM cache size to 50% of available memory
-cr-^ -- disable cache warming to speed Foxhound startup
-gk all^ -- enable Foxhound batch files to execute dbstop.exe
-gn 120^ -- enable Foxhound to collect samples from 100 separate target databases
-n foxhound1 -- set the Foxhound runtime server name. The Extended Edition of Foxhound also allows foxhound1b through foxhound1j to be specified here
-o foxhound1_debug.txt^ -- record Foxhound console log messages in a text file
-oe foxhound1_debug_startup.txt^ -- record Foxhound server startup and other error messages in a text file./I>

-on 10M^ -- rename and restart the console log text file when it grows to this size

-qn^ -- do not minimize the server window on startup

-sb 0^ -- do not listen for UDP broadcasts

-x tcpip^ -- enable the TCPIP protocol for ad-hoc queries via network communications

-xs http(...)^ -- enable HTTP communications

(port=80; -- set the HTTP port to 80; use a different value like 8080 or 12347 if there is some other HTTP server already running on the same machine and using port 80

maxsize=0; -- allows unlimited size HTTP requests

to=600; -- increases the HTTP idle timeout to 10 minutes

kto=600) -- increases the HTTP keep-alive timeout to 10 minutes

foxhound1.db^ -- the Foxhound database file, which contains all the Foxhound executable code

-n f -- set the Foxhound runtime database name

\\\\\
Running Foxhound as a Service    [Top]
Foxhound is often used to monitor heavily-loaded target databases, and in such an environment some care must be taken to ensure Foxhound's own CPU and disk requirements don't adversely affect overall performance. The simplest solution is to run the Foxhound engine on its own computer that is:

separate from the computer running the target database server,

separate from heavily loaded application or MobiLink servers, and

located physically close to the target database server and communicates over a LAN rather than WAN.

A centrally-located Foxhound engine often implies remote client access across the LAN or WAN as well as the need for easy administration. All these requirements can be met by running Foxhound as a Windows service using the dbsrv11.exe network server.
You can use Sybase Central or the dbsvc.exe utility to create Windows service; both techniques are described in the SQL Anywhere 11 Help.
Note: If you run Foxhound as a service, you may have to create ODBC DSNs as System DSNs instead of User DSNs if you want them to appear on the Foxhound Menu page.
Here is an example of the command line parameters for running Foxhound as a service on Windows 7:

-c 25p -ch 50p -cr- -gk all -gn 120 -n foxhound1 -oe "C:\ProgramData\RisingRoad\Foxhound1\foxhound1_debug_startup.txt" -sb 0 -x tcpip -xd -xs "http(log=C:\ProgramData\RisingRoad\Foxhound1\foxhound1_http_log.txt;lf=@T - @I - @U - @R - @L - @W - @E;lsize=1M;dbn=f;port=80;maxsize=0;to=600;kto=600)" "C:\ProgramData\RisingRoad\Foxhound1\foxhound1.db" -n f

Here is a description of those parameters:

-c 25p -- set the initial RAM cache size to 25% of available memory

-ch 50p -- set the maximum RAM cache size to 50% of available memory

-cr- -- disable cache warming to speed Foxhound startup

-gk all -- enable Foxhound batch files to execute dbstop.exe

-gn 120 -- enable Foxhound to collect samples from 100 separate target databases

-n foxhound1 -- set the Foxhound runtime server name. The Extended Edition of Foxhound also allows foxhound1b through foxhound1j to be specified here

-oe "C:\ ... \foxhound1_debug_startup.txt" -- the full file specification for recording the Foxhound server startup and other error messages in a text file./I>

-sb 0 -- do not listen for UDP broadcasts

-x tcpip -- enable the TCPIP protocol for ad-hoc queries via network communications

-xs http(...) -- enable HTTP communications

(log=C:\ ... \foxhound1_http_log.txt; -- the full file specification for recording the HTTP request log in a text file

lf=@T - @I - @U - @R - @L - @W - @E; -- specifies the fields and format of the HTTP request log

lsize=1M; -- renames and restarts the HTTP request log when it grows to 1M in size

dbn=f; -- the database name for processing HTTP requests

port=80; -- set the HTTP port to 80; use a different value like 8080 or 12347 if there is some other HTTP server already running on the same machine and using port 80

maxsize=0; -- allows unlimited size HTTP requests

to=600; -- increases the HTTP idle timeout to 10 minutes

kto=600)" -- increases the HTTP keep-alive timeout to 10 minutes

"C:\ ... \foxhound1.db" -- the full file specification for the Foxhound database file

-n f -- set the Foxhound runtime database name

Connecting to Target Databases    [Top]
Foxhound uses ODBC to connect to the target databases. If you have an ODBC DSN, you can choose it on the DSN tab of the Foxhound Menu page.
If you don't have a DSN, or don't want to use one, you can specify a DSN-less connection by specifying the connection string on the String tab of the Menu page. The main difference between using the String tab and setting up a DSN in the ODBC Administrator is that the String tab needs to know what driver to use, as in:
DRIVER=SQL Anywhere 11;
For best results, Foxhound needs DBA privileges when it connects to the target database. Without DBA privileges, the Foxhound schema display will be unable to show DBSPACE information in the Facts & Figures section.
For next-best results, Foxhound needs the RESOURCE privilege. Without it, the Foxhound Monitor will not be able to create the following procedures on the target database:
rroad_connection_properties
rroad_database_properties
rroad_engine_properties
Without those procedures, Monitor performance will suffer, especially if the target database has a lot of connections. The Foxhound Monitor page displays SPs OK: YYY if finds the three procedures, and SPs OK: NNN if it doesn't.
Note: If you want to monitor a secondary (read only) database in a high availability environment, start a monitor session on the primary (updatable) database first so the procedures described above can be stored on the primary and then reach the secondary database via the high availability synchronization process. Foxhound can't send the procedures directly to the secondary database because it's read only.

Here is how you can let Foxhound create those procedures without giving it DBA privileges:

Create a user id to be used by Foxhound on the target database; e.g.:
GRANT CONNECT TO FOXHOUND IDENTIFIED BY SQL;

Give that user id the RESOURCE privilege:
GRANT RESOURCE TO FOXHOUND;

If the Foxhound Monitor is already connected to the target database, click on Stop Sampling, then Start Sampling.

Check to make sure "SPs OK: YYY" appears on the Foxhound Monitor page.

If you don't want to let Foxhound to have either RESOURCE or DBA privileges, but you are willing to install those three procedures on the target database yourself, here's how:
(Note: If you choose to use this method, you will have to repeat these steps for each target database you wish to monitor, and again each time you upgrade to a new version or build of Foxhound.)

Create a user id to be used by Foxhound on the target database; e.g.:
GRANT CONNECT TO FOXHOUND IDENTIFIED BY SQL;

Find the following three text files in the Foxhound folder (which defaults to C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1):
rroad_connection_properties.sql
rroad_database_properties.sql
rroad_engine_properties.sql

Edit each file to specify the user id you chose in step 1; e.g., change {OWNER} to FOXHOUND:
CREATE PROCEDURE FOXHOUND.rroad_connection_properties()
CREATE PROCEDURE FOXHOUND.rroad_engine_properties()
CREATE PROCEDURE FOXHOUND.rroad_database_properties(d INTEGER)

Use dbisql to run those files against the target database.

If the Foxhound Monitor is already connected to the target database, click on Stop Sampling, then Start Sampling.

Check to make sure "SPs OK: YYY" appears on the Foxhound Monitor page.

Backup and Restore    [Top]
It is a good idea to take regular backups of the Foxhound database, for two reasons: To protect against loss, and to control the growth of the Foxhound transaction log file.
Foxhound doesn't take backups automatically, but it does come with a backup batch file called $backup_foxhound1.bat which can be run manually or according to a regular schedule.
Here is how to run the backup manually using the shortcuts created by the Foxhound installation:
Start - All Programs
- Foxhound
- Tools
- Backup Foxhound Database
Here is what the output looks like:
C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1\backup
>"C:\Program Files\SQL Anywhere 11\Bin32\dbbackup.exe" -c "ENG=foxhound1;DBN=f;U
ID=BACKER;PWD=SQL" -o generation3\backup_log.txt -x -y generation3
SQL Anywhere Backup Utility Version 11.0.1.2250
(15565 of 15554 pages, 100% complete)
Transaction log truncated
Database backup completed
You can also use the Windows Scheduled Tasks feature to run the backup on a regular schedule:
Start - All Programs
- Accessories
- System Tools
- Scheduled Tasks
Doubleclick on "Add Scheduled Task"
Follow the instructions to launch C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1\$backup_foxhound1.bat on a regular basis.
Here is a description of the tasks performed by $backup_foxhound1.bat:

Creates a full online backup of both the Foxhound database file foxhound1.db and the transaction log foxhound1.log. This process can be run even while Foxhound is busy monitoring databases, and the backup files it creates can be used to restore the Foxhound database after a problem occurs.

Deletes and recreates the active transaction log.

Deletes and renames subfolders containing old backups before creating a new one.

Keeps three generations of backups:
C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1 -- the default Foxhound folder created by the Foxhound installation
\backup -- the backup subfolder
\generation1 -- the oldest backup subfolder, deleted before a new backup is created
\generation2 -- the previous backup subfolder, renamed to "generation1" before a new backup is created
\generation3 -- the latest backup subfolder, renamed to "generation2" and recreated before a new backup is created.

Here is how to perform a full restore from a backup:

Delete or move the Foxhound database and transaction log files from the main Foxhound folder:
C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound1\
foxhound1.db
foxhound1.log

Choose which generation of backup files you're going to use: backup\generation3 (the newest), backup\generation2 or backup\generation1 (the oldest).

Copy the foxhound1.db and foxhound1.log files from the backup subfolder to the main Foxhound folder.

Start Foxhound.

Controlling Growth    [Top]
The Foxhound Monitor process will cause the Foxhound database and transaction log files to grow in size. This growth can be quite rapid, as much as several gigabytes per day, if target databases have hundreds or thousands of connections.
Foxhound transaction log growth can be controlled by regularly using the backup process described in the previous section. That backup process deletes old backup copies of the transaction log as well as deleting and restarting the current transaction log.
Foxhound automatically controls database file growth with an internal purge process that runs every hour. By default the purge process deletes data when it reaches 1 month old. You can speed up or slow down this process by changing the "Purge sample data:" setting on the Foxhound Options page.
[Top]

[Top]