Help for Foxhound 2.0.4215a
Table of Contents [RisingRoad]
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 and even drop connections 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
Customizing the Setup
Running Foxhound as a Service
Connecting to Target Databases
Backup and Restore
Safe Mode Startup
SQL Anywhere is a trademark of Sybase, Inc.
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 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. Chrome, Firefox, IE - The Foxhound client has been tested with the latest browser versions, currently Google Chrome 21, Firefox 15 and Internet Explorer 9.
5. SQL Anywhere version 220.127.116.1187 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 18.104.22.16887 for Foxhound to work properly with them.
6. SQL Anywhere 22.214.171.12498 or later for Foxhound - Foxhound works with target databases using 5.5 to 12 of SQL Anywhere but the Foxhound engine itself needs the 32-bit or 64-bit version of SQL Anywhere 126.96.36.19998 or later to run.
7. SQLANY12 - The delivered *.bat files expect that the SQLANY12 environment variable points to SQL Anywhere 12. 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 SQLANY12=C:\Program Files\SQL Anywhere 128. FOXHOUND2 - The delivered *.bat files expect that the FOXHOUND2 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 FOXHOUND2=C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound2\and for Windows Vista and Windows 7 it is this:
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 consists of server and client components.
The Foxhound server (or "engine") runs as a single SQL Anywhere database in a Version 12 engine configured to run as an HTTP (web) server.
The Foxhound client is browser based, with the default URL being this:
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. For more information see this Q&A entry:
How do I start Foxhound?
By default the Foxhound batch files are installed in this location on Windows XP:
C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound2and on Vista and Windows 7 it is here:
C:\ProgramData\RisingRoad\Foxhound2These are the command files which start Foxhound:
$backup_foxhound2.bat $start_foxhound2_chrome.bat $start_foxhound2_chrome_debug.bat $start_foxhound2_engine.bat $start_foxhound2_firefox.bat $start_foxhound2_firefox_debug.bat $start_foxhound2_ie.bat $start_foxhound2_ie_debug.bat
You can customize those batch files, or create new ones as you see fit. Each one contains the following commands or a variation thereof:
SET BIN=Bin32 IF EXIST "%SQLANY12%\Bin64\dbsrv12.exe" (SET BIN=Bin64) IF "%FOXHOUND2BIN%"=="Bin32" (SET BIN=Bin32) CD /D "%FOXHOUND2%" "%SQLANY12%\%BIN%\dbspawn.exe"^ -f^ "%SQLANY12%\%BIN%\dbsrv12.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gn 120^ -gna 0^ -n foxhound2^ -o foxhound2_debug.txt^ -oe foxhound2_debug_startup.txt^ -on 1M^ -sb 0^ -x tcpip^ -xd^ -xs http(port=80;maxsize=0;to=600;kto=600)^ foxhound2.db^ -n f
Here is a description of each part of those commands:
Use the 32-bit version of SQL Anywhere 12 to run Foxhound...
...unless the 64-bit version of SQL Anywhere 12 is installed, then use that...
...unless you have SET FOXHOUND2BIN=Bin32 to force the use of the 32-bit version of SQL Anywhere 12.
Use the FOXHOUND2 environment variable to change the current directory.
Launch dbsrv12.exe indirectly so the batch file will continue running after SQL Anywhere starts.
Force dbspawn to start another dbsrv12.exe even if one is already running.
Start Foxhound using the SQL Anywhere Network Server.
Change this to dbeng12.exe if you want to prevent network access to Foxhound.
The Foxhound application resides inside the foxhound.db database file, and that database must be run on its own engine (dbeng12.exe or dbsrv12.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).
Set the initial RAM cache size to 25% of available memory.
Set the maximum RAM cache size to 50% of available memory.
Disable cache warming to speed Foxhound startup.
Enable Foxhound batch files to execute dbstop.exe.
Enable Foxhound to collect samples from 100 separate target databases.
Prevent SQL Anywhere's automatic tuning process from changing the -gn value.
Set the Foxhound runtime server name.
The Extended Edition of Foxhound also allows foxhound2b through foxhound2j to be specified here.
Record Foxhound console log messages in a text file.
Record Foxhound server startup and other error messages in a text file.
Rename the console log text file foxhound2_debug.txt to foxhound2_debug.old and restart it when it grows to this size.
If foxhound2_debug.old exists it is overwritten.
Do not listen for UDP broadcasts.
Enable the TCPIP protocol for ad-hoc queries via network communications.
Enable HTTP communications.
Explicitly set the HTTP port to the default 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. When you specify a different value here, you also have to specify it when you launch Foxhound in a browser window; for example, http://localhost:8080/
Allow unlimited size HTTP requests.
Increase the HTTP idle timeout to 10 minutes.
Increase the HTTP keep-alive timeout to 10 minutes.
The Foxhound database file, which contains all the Foxhound executable code.
Set the Foxhound runtime database name.
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:
It is often more important to locate the Foxhound engine close to the target database, than to locate the client-side web browser close to the Foxhound engine. That's because the communication between Foxhound and the target database is more intense and more susceptible to long-range latency issues than is the HTTP traffic between the browser and Foxhound.
Having said all that, a centrally-located Foxhound engine is easier to set up and administer when it is run as a Windows service using the dbsrv12.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 Help.
Here is an example of a Windows 7 script that deletes and creates a service for running Foxhound using the 64-bit version of SQL Anywhere; it also displays the service parameters after it is created, and then lists all the SQL Anywhere-related services that exist:
ECHO Create Bin64 Foxhound Service "C:\Program Files\SQL Anywhere 12\Bin64\dbsvc.exe" -d Foxhound2 -y PAUSE "C:\Program Files\SQL Anywhere 12\Bin64\dbsvc.exe"^ -o "C:\ProgramData\RisingRoad\Foxhound2\dbsvc_log.txt"^ -y ^ -as ^ -i^ -s Automatic^ -sn Foxhound2^ -sd "Foxhound Version 2 Database Monitor Bin64 Service"^ -t Network^ -w Foxhound2 "C:\Program Files\SQL Anywhere 12\Bin64\dbsrv12.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gn 120^ -gna 0^ -n foxhound2^ -o "C:\ProgramData\RisingRoad\Foxhound2\foxhound2_debug.txt"^ -oe "C:\ProgramData\RisingRoad\Foxhound2\foxhound2_debug_startup.txt"^ -on 1M^ -qn^ -sb 0^ -x tcpip^ -xd^ -xs http(port=80;maxsize=0;to=600;kto=600)^ "C:\ProgramData\RisingRoad\Foxhound2\foxhound2.db"^ -n f PAUSE "C:\Program Files\SQL Anywhere 12\Bin64\dbsvc.exe" -g Foxhound2 PAUSE "C:\Program Files\SQL Anywhere 12\Bin64\dbsvc.exe" -l PAUSE
Note: When you run Foxhound as a service, you will probably have to create System DSNs instead of User DSNs if you want your target databases to appear on the DSN tab of the Foxhound Menu page. You may also have to launch the ODBC Administrator from Control Panel - Administrative Tools rather than from the Foxhound Menu page, and you may have to specify TCP/IP rather than Shared Memory for the Foxhound connections to your target databases even if they are running on the same computer.
Also, those DSNs must be set up on the same computer that is running the Foxhound engine. In other words, if you move the Foxhound engine from your workstation to a server somewhere else, you will have to move or copy the DSNs over there as well.
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 12;
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:
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:
GRANT CONNECT TO FOXHOUND IDENTIFIED BY SQL;
GRANT RESOURCE TO FOXHOUND;
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.)
GRANT CONNECT TO FOXHOUND IDENTIFIED BY SQL;
CREATE PROCEDURE FOXHOUND.rroad_connection_properties()
CREATE PROCEDURE FOXHOUND.rroad_engine_properties()
CREATE PROCEDURE FOXHOUND.rroad_database_properties(d INTEGER)
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 the standard Foxhound installation creates two Windows Start menu shortcuts to batch files that perform full and incremental log backups. For more information see these Q&A entries:
How do I backup my Foxhound database?
How do I backup my Foxhound database on a regular basis?
For more information on how to restore the Foxhound database from a backup, see this Q&A entry:
How do I restore the Foxhound database from a backup?
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 settings on the Foxhound Options page.
For more information see these Q&A entries:
How do I keep the Foxhound database file from growing so large?
How do I keep the Foxhound log file from growing so large?
If Foxhound takes too long to start up, perhaps because there are a large number of Monitor sessions to start or because Foxhound simply becomes unresponsive, you can try starting Foxhound in "safe mode".
For more information see this Q&A entry:
How do I start Foxhound in "safe mode"?