Help for Foxhound 5.0.5516a

Table of Contents     [RisingRoad]

search engine by freefind	advanced

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 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.

Windows Start Menu Hallmarks of Foxhound Requirements Limitations Foxhound Setup Starting Foxhound Customizing the Setup

Running Foxhound as a Service Connecting to Target Databases Backup and Restore Controlling Growth Safe Mode Startup Starting Multiple Copies of Foxhound

Offline Foxhound Sampling Service (OFSS) Environment Variables Scripts Subfolder End User License Agreement Support and Consulting How to CALL unload_samples()

SAP® SQL Anywhere® is a trademark of SAP AG.

Here's what the Windows - Start - Foxhound5 menu looks like:

1 Start Foxhound	See Starting Foxhound
2 Adhoc Query via ISQL	See Adhoc Queries
3 Backup Database	See Full Backup and Restore
4 Help	Browse this Help on your local hard drive
5 FAQ	Browse the Foxhound Frequently Asked Questions on the Web
6 Debug Start Foxhound	See Starting Foxhound Method 2: Debug Startup
7 Start Foxhound Engine	See Starting Foxhound Method 3: Do-It-Yourself
8 Stop Foxhound Engine	Shut down the dbsrv17.exe that is running Foxhound
9 Mini-Restore	See Mini-Restore
10 Unsetup Foxhound	Use this before reinstalling Foxhound or upgrading to a new build

Here's a list of Foxhound characteristics you can take advantage of:

1. Functionality out of the box  Foxhound just requires a connection string or DSN to get started; the next thing you see is your database in the Foxhound Monitor page; there are no widgets or wizards to deal with, and no need to pick and choose which statistics to display.

2. Guidance  Foxhound provides on-screen guidance for all the data it displays and all the options it offers. The column titles, tooltip text, field descriptions, context-sensitive Help and performance tips are all designed to help you understand what you are looking at and what you can do about it.

3. Information at a glance  Foxhound presents a straightforward interface for professional developers with no graphs, waveforms or "data visualization" artwork. All the relevant data and value-added calculations are gathered together on single pages with peak highlighting and hypertext links to scroll through history and open different views in new browser tabs.

4. Open access  Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it. The Foxhound Help contains sample queries, and to make your own queries easier to write important internal primary key values are displayed together with the data on the Monitor and other pages; e.g., sampling_id, sample_set_number and the new connection_id_string.

5. Legacy support  Foxhound supports target databases running all versions of SQL Anywhere from 6 to 17. And while it's true that older versions of SQL Anywhere don't provide (and Foxhound doesn't display) modern performance statistics, statistics that are available do get displayed, not lowest common denominators.

6. Robustness  The Foxhound Monitor keeps on trying to connect to the target database during an outage. Foxhound also silently handles invalid performance statistics, and it keeps working after it throws an exception, thus meeting the definition of robustness: "The degree to which a system or component can function correctly in the presence of invalid inputs or stressful environmental conditions."

7. Dogfooding  Foxhound is regularly run against the Foxhound database itself; to test new features, sure, but primarily to look for performance bottlenecks. Foxhound is also the first tool of choice on client performance and tuning assignments; that's how the Foxhound Monitor was created in the first place, on assignment in 2003 as a Blocked Connection Display for a SQL Anywhere 9 database at UNO Money Transfers. The Schema Display feature was created even earlier, in 1998, for an immense Oracle database at Princess Margaret Hospital... which makes 2018 the twentieth anniversary of Foxhound!

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 the 64-bit version of Microsoft Windows 10.

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 - The Foxhound client has been tested with the latest versions of Chrome and Firefox.

4. JavaScript - Foxhound needs "JavaScript" or "Active scripting" to be enabled in your browser.

5. SQL Anywhere 6, 7, 8, 9, 10, 11, 12, 16 and 17 for target databases - Foxhound has also been tested with target databases created with SQL Anywhere 5.5 and running on SQL Anywhere 6 software.

6. SQL Anywhere 17 for Foxhound - The Foxhound database must run on its own instance of the 32-bit or 64-bit SQL Anywhere 17 network server build 4882 or later. This restriction does NOT apply to your target databases which can run on any version of SQL Anywhere 6, 7, 8, 9, 10, 11, 12, 16 and 17.

7. SQLANY17 - The delivered Foxhound shortcuts expect that the SQLANY17 environment variable will be created by the SQL Anywhere 17 installation (which will happen by default). If that is not the case you may have to modify the corresponding *.bat files. Here is the default setting for Windows 10:

   SET SQLANY17=C:\Program Files\SQL Anywhere 17 

8. FOXHOUND5 - The FOXHOUND5 environment variable is created by the Foxhound installation to contain the drive and path of the folder where Foxhound is installed. It is no longer required by the delivered Windows shortcuts or by any of the code inside Foxhound. It is used by several of the optional Windows command files in the scripts subfolder, and by the $post_setup.bat file which is launched by the Foxhound installation process. Here is the default setting for Windows 10:

   SET FOXHOUND5=C:\ProgramData\RisingRoad\Foxhound5\

9. Disk space - The Foxhound database file may grow in size at a rate of 100K per hour per target database connection... or more; your mileage may vary.

10. OFSS - The Offline Foxhound Sampling Service (OFSS) is only supported for subject databases running on SQL Anywhere 12, 16 and 17.

11. Data Upgrade - The Foxhound 5 installation process will copy and upgrade the data from an existing Foxhound Version 3, 4 or 5 database, but it will ignore a Version 1 or 2 database.

Here are the limitations of the Rental, Basic and Extended Editions of Foxhound:

* This limit on connections applies to all versions and editions of SQL Anywhere target databases and servers. The actual limit is 101 connections when you count the connection from Foxhound itself. Internal connections (e.g., connections made by events and web services) are counted in the total.

** This limit on physical CPUs does not apply to SQL Anywhere 5.5, 6, 7, 8 or 9 target databases and servers. Also, it applies to the number of physical processors as given by the NumPhysicalProcessorsUsed server property, not the number of logical processors shown by "Using n CPUs" value in the Foxhound Database Monitor. For example, a single Intel Pentium 4 processor counts as 1 physical processor even though the separate HyperThreading units may show up as "Using 2 CPUs". Similarly, a single Intel Core2 Quad processor also counts as only 1 physical processor even though it may show up as "Using 4 CPUs".

*** This restriction on SQL Anywhere editions does not apply to SQL Anywhere target databases and servers earlier than version 11.0.1, which is when SQL Anywhere introduced the notion of different editions.

**** The Rental and Basic Editions allow only one instance of Foxhound to be started on the same local network, and the SQL Anywhere server name must be "foxhound5". The Extended Edition allows an unlimited number of Foxhound instances to be started on the same local network, with no restrictions on the SQL Anywhere server names; however, the License Agreement requires that a separate Extended Edition registration key be purchased for each multiple of 10 instances started.

Foxhound consists of server and client components.

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

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

http://localhost:80/

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.

Starting Foxhound is a two-step process: Starting the Foxhound database in a SQL Anywhere engine, and connecting to the Foxhound HTTP server from your browser.

Method 1: Normal Startup

This Windows shortcut does both steps, start the database and open the browser:

Foxhound5 - 1 Start Foxhound

Method 2: Debug Startup

The following "debug" shortcut is also available:

6 Debug Start Foxhound

It does exactly the same thing as the Foxhound5 - 1 Start Foxhound shortcut, except:

• it stops the Foxhound database if it is running, before starting it again,

• it displays any diagnostic messages produced if the Foxhound database fails to start, and

• it PAUSEs at the end so you can see what happened.

Method 3: Do-It-Yourself

You can also start the Foxhound database and open the browser in separate steps:

Foxhound5 - 7 Start Foxhound Engine

Specify http://localhost in your browser.

By default the Foxhound shortcuts use the 64-bit of SQL Anywhere if it exists, otherwise they use the 32-bit version.

You can override those choices with the FOXHOUND5BIN environment variable.

This applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.

Here's how those choices are made by the command files used by the Foxhound shortcuts:

IF EXIST "!SQLANY17!\Bin32" (SET BIN=Bin32)
IF EXIST "!SQLANY17!\Bin64" (SET BIN=Bin64)
IF /I "%FOXHOUND5BIN%z"=="Bin32z" (SET BIN=Bin32)
IF /I "%FOXHOUND5BIN%z"=="Bin64z" (SET BIN=Bin64)

Here's the list of Foxhound command files which contain the dbsrv17.exe command:

C:\ProgramData\RisingRoad\Foxhound5\ $backup_foxhound5.bat $start_foxhound5_default_browser_debug.bat $start_foxhound5_default_browser.bat $start_foxhound5_engine.bat C:\ProgramData\RisingRoad\Foxhound5\scripts $create_SQL_Anywhere_17_bin64_service.bat $MULTI_Step_2_dbsrv_all.bat $MULTI_Step_2b_dbsrv_one.bat $run_most_recent_mini_restore.bat $TEN_INSTANCES_2_dbsrv.bat

C:\ProgramData\RisingRoad\Foxhound5\ $backup_foxhound5.bat $start_foxhound5_default_browser_debug.bat $start_foxhound5_default_browser.bat $start_foxhound5_engine.bat C:\ProgramData\RisingRoad\Foxhound5\scripts $create_SQL_Anywhere_17_bin64_service.bat $MULTI_Step_2_dbsrv_all.bat $MULTI_Step_2b_dbsrv_one.bat $run_most_recent_mini_restore.bat $TEN_INSTANCES_2_dbsrv.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:

"!SQLANY17!\!BIN!\dbspawn.exe"^ -f "!SQLANY17!\!BIN!\dbsrv17.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gl all^ -gn 220^ -gna 0^ -m^ -n foxhound5^ -o foxhound5_debug.txt^ -oe foxhound5_debug_startup.txt^ -on 1M^ -qn^ -sb 0^ -ufd restart^ -x tcpip^ -xd^ -xs http(port=80;maxsize=0;to=600;kto=600)^ foxhound5.db^ -n f

Here is a description of each part of the command:

• "!SQLANY17!\!BIN!\dbspawn.exe"^

  Launch the SQL Anywhere server indirectly so the batch file will continue running after SQL Anywhere starts.

• -f "!SQLANY17!\!BIN!\dbsrv17.exe"^

  Force dbspawn to start another SQL Anywhere server even if one is already running.

  The Foxhound application resides inside the foxhound5.db database file, and that database must be run on its own SQL Anywhere engine. In other words, you cannot start the foxhound5.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.

• -gl all^

  Enable Foxhound adhoc queries to UNLOAD data.

• -gn 220^

  Enable Foxhound to collect samples and/or perform ping-only sampling on up to 100 separate target databases.

  If you never sample more than a few target databases at the same time, you can safely reduce this value.

• -gna 0^

  Prevent SQL Anywhere's automatic tuning process from changing the -gn value.

• -m^

  Limit the transaction log file size by truncating the log on each checkpoint.

• -n foxhound5

  Set the Foxhound runtime server name.

  The -n option must be set to foxhound5 for the Rental and Basic editions of Foxhound because only one copy of the Foxhound engine may be run at one time on one network.

  The -n option may be set to any acceptable value for the Extended Edition of Foxhound because multiple copies of the Foxhound engine may be run at one time on one network.

• -o foxhound5_debug.txt^

  Record Foxhound console log messages in a text file.

• -oe foxhound5_debug_startup.txt^

  Record Foxhound server startup and other serious error messages in a text file.

• -on 10M^

  Rename the console log text file foxhound5_debug.txt to foxhound5_debug.old and restart it when it grows to this size.

  If foxhound5_debug.old exists it is overwritten.

• -sb 0^

  Do not listen for UDP broadcasts.

• -ufd restart^

  If a database-level assertion error occurs in the Foxhound database, this option tells SQL Anywhere to shut the database down and attempt to restart it.

• -x tcpip^

  Enable the TCPIP protocol for adhoc queries via network communications.

• -xd^

  Prevent the Foxhound database server from becoming the default server for other connection attempts.

• -xs http(...)^

  Enable HTTP communications.

• (port=80;

  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/

• maxsize=0;

  Allow unlimited size HTTP requests.

• to=600;

  Increase the HTTP idle timeout to 10 minutes.

• kto=600)

  Increase the HTTP keep-alive timeout to 10 minutes.

• foxhound5.db^

  The Foxhound database file, which contains all the Foxhound executable code.

• -n f

  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:

• separate from the computer running the target database server,

• separate from heavily loaded application and/or MobiLink servers, and

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

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 response time (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 dbsrv17.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.

The Foxhound Samples folder contains a command file that deletes and create a Windows service for running Foxhound using the 64-bit version of SQL Anywhere 17. It also displays the service parameters after it is created, and then lists all the SQL Anywhere-related services that exist:

C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat

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 the String tab needs to know what driver to use, as in:

DRIVER=SQL Anywhere Native;

GRANT CONNECT to Foxhound

Foxhound needs to connect to the target database in order to collect Monitor statistics and display schema information. For most purposes, a user id with CONNECT permission is sufficient:

GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';

   -- or --

CREATE USER FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';

That user id can then be used to connect to a SQL Anywhere database using a connection string like this:

Name: ddd

String: ENG=ddd; DBN=ddd; UID=FOXHOUND; PWD=ZAdt5Yq8; DRIVER=SQL Anywhere Native; 

Debugging ODBC Connections

Tip: To debug problems with Foxhound's ODBC connection to your database, try enabling the ODBC LOG file:

Name: ddd

String: ENG=ddd; DBN=ddd; UID=FOXHOUND; PWD=ZAdt5Yq8; DRIVER=SQL Anywhere Native;  LOG=C:\TEMP\LOG.TXT;

GRANT Other Permissions to Foxhound

A user id with only CONNECT permission does have some limitations, depending on the version of SQL Anywhere being used to run the target database.

Tip: For a SQL Anywhere Version 12 or earlier target database, here's how to let the FOXHOUND user id do everything EXCEPT the following:

1. Perform AutoDrop processing shown in section 12. AutoDrop Criteria of the Monitor Options page.
   Message: Permission denied: you do not have permission to disconnect "xxx"

2. Use any actions in section 14. Change Target Settings of the Monitor Options page
   Message: Permission denied: you do not have permission to execute the procedure "sa_server_option"

3. Set the DEDICATED_TASK option in section 15. Monitor Connection Settings of the Monitor Options page.
   Message: Only the DBA can set the option 'dedicated_task' SQLCODE=-204, ODBC 3 State="42000"

   GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8'; GRANT RESOURCE TO FOXHOUND; GRANT EXECUTE ON dbo.sa_locks TO FOXHOUND; GRANT EXECUTE ON dbo.sa_disk_free_space TO FOXHOUND;

Tip: For a SQL Anywhere Version 16 or later target database, here's how to let the FOXHOUND user id do everything with no exceptions:

CREATE USER FOXHOUND IDENTIFIED BY 'ZAdt5Yq8'; GRANT RESOURCE TO FOXHOUND; GRANT MANAGE ANY DBSPACE TO FOXHOUND; GRANT MONITOR TO FOXHOUND; GRANT DROP CONNECTION TO FOXHOUND; GRANT SET ANY SYSTEM OPTION TO FOXHOUND; GRANT SERVER OPERATOR TO FOXHOUND;

Tip: If you are willing to GRANT DBA TO FOXHOUND then all the problems go away.

If not, here's a table introducing the symptoms and solutions:

Target SQL Anywhere Version Symptom Reason Solution(s) ================================================================================================ GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8'; -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound Monitor displays Foxhound is not GRANT RESOURCE TO FOXHOUND; "SPs: NNN" and may allowed to CREATE perform slowly. three special -- or -- stored procedures on the target CREATE the procedures database. manually using SQL files delivered with Foxhound (see below). -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound doesn't display Foxhound is not GRANT EXECUTE ON free space on the target allowed to call the dbo.sa_disk_free_space database disk drives, sa_disk_free_space() TO FOXHOUND; and doesn't issue Alerts procedure on the 5, 6, 7 or 8 about low target database. -- or -- disk space. GRANT DBA TO FOXHOUND; -- this solves all the other problems too. -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound doesn't display Foxhound is not GRANT EXECUTE ON detailed information allowed to call the dbo.sa_locks about why a connection is sa_locks() procedure TO FOXHOUND; blocked. on the target target database. -- or -- GRANT DBA TO FOXHOUND; -- this solves all the other problems too. -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound can't AutoDrop Foxhound is not GRANT DBA TO FOXHOUND; any connections on the allowed to execute target database. the DROP CONNECTION -- this solves all the statement on the other problems too. target database. -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound can't set the Foxhound is not GRANT DBA TO FOXHOUND; DEDICATED_TASK option on allowed to execute the target database. the SET TEMPORARY -- this solves all the OPTION DEDICATED_TASK other problems too. statement on the target database. -------------- ------------------------- --------------------- --------------------------- 12 and earlier Foxhound can't make the Foxhound is not GRANT DBA TO FOXHOUND; changes specified by the allowed to call the Change Target Settings sa_server_option() -- or -- section of the Monitor procedure on the Option page. target database. Use dbsrv12 -zp -zl -zt ================================================================================================ CREATE USER FOXHOUND IDENTIFIED BY 'ZAdt5Yq8'; -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound Monitor displays Foxhound is not GRANT RESOURCE TO FOXHOUND; "SPs: NNN" and may allowed to CREATE perform slowly. three special -- or -- stored procedures on the target CREATE the procedures database. manually using SQL files delivered with Foxhound (see below). -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound doesn't display Foxhound is not GRANT MANAGE ANY DBSPACE anything in Free Disk allowed to call the TO FOXHOUND; Space Sys, Temp, Log, sa_disk_free_space() Other, and doesn't issue procedure on the Alerts 5, 6, 7 or 8 about target database. low disk space. -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound Monitor doesn't Foxhound is not GRANT MONITOR TO FOXHOUND; gather information about allowed to call the any connections other CONNECTION_PROPERTY() than Foxhound's own function on the connection to the target target database for database. any other connections. Foxhound is also not allowed to call the sa_locks() procedure on the target database. -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound can't AutoDrop Foxhound is not GRANT DROP CONNECTION any connections on the allowed to execute TO FOXHOUND; target database. the DROP CONNECTION statement on the target database. -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound can't set the Foxhound is not GRANT SET ANY SYSTEM OPTION DEDICATED_TASK option on allowed to execute TO FOXHOUND; the target database. the SET TEMPORARY OPTION DEDICATED_TASK statement on the target database. -------------- ------------------------- --------------------- --------------------------- 16 and later Foxhound can't make the Foxhound is not GRANT SERVER OPERATOR changes specified by the allowed to call the TO FOXHOUND; Change Target Settings sa_server_option() section of the Monitor procedure on the -- or -- Option page. target database. Use -zp -zl -zt ================================================================================================

All the solutions use simple GRANT statements shown above, except one; here's the full story on the special Foxhound stored procedures:

How To Install The Foxhound SPs On Non-OFSS Target Databases Three stored procedures are delivered with Foxhound for installation on your target databases. These procedures are optional but highly recommended: rroad_connection_properties greatly improves Foxhound performance when the target database has a large number of client connections. rroad_database_properties improves Foxhound performance when gathering database-level properties, as well as enabling these Foxhound display fields: Log Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'translog' ) Log Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'translog' ) Temp Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'temp' ) Temp Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'temp' ) Temp Frags - based on DB_EXTENDED_PROPERTY ( 'DBFileFragments', 'temp' ) rroad_engine_properties improves Foxhound performance when gathering server-level properties. Foxhound will automatically install and call these stored procedures if it can. The phrase "if it can" means "the user id with which Foxhound connects to the target database has the RESOURCE authority" as in: GRANT RESOURCE TO FOXHOUND; Foxhound will keep trying to install those procedures each time it connects to the target database; e.g., each time sampling is stopped and started, or each time Foxhound is started. Performance Tip: Here's how to force Foxhound to re-install those procedures on your target database: Stop Foxhound sampling on your database. Run these commands via ISQL on your database: DROP PROCEDURE rroad_connection_properties; DROP PROCEDURE rroad_database_properties; DROP PROCEDURE rroad_engine_properties; You might have to specify the user id that owns the procedures; i.e., the user id that Foxhound uses to connect to your database: DROP PROCEDURE FOXHOUND.rroad_connection_properties; DROP PROCEDURE FOXHOUND.rroad_database_properties; DROP PROCEDURE FOXHOUND.rroad_engine_properties; Start sampling on your target database; Foxhound should re-send those procedures when it discovers they don't exist. As mentioned above, Monitor performance will suffer without those procedures, especially if the target database has a lot of connections. The Foxhound Monitor page displays SPs YYY if finds the three procedures, and SPs NNN if it doesn't. . . . but Wait, There Is Another Way! If you don't want to let the Foxhound user id to have either RESOURCE or DBA privileges on the target database, 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 'ZAdt5Yq8'; Find the following three text files in the Foxhound folder (which defaults to C:\ProgramData\RisingRoad\Foxhound5 on Windows 7): 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 YYY" appears on the Foxhound Monitor page.

Tip: One of the reasons to run Foxhound on the 32-bit version of SQL Anywhere is to make it easier to use the 32-bit version of ODBC to connect to your target database... but that may not be necessary!

For example, if you have an Adaptive Server Anywhere 9.0 target database you may be able to connect to it using a more recent SQL Anywhere ODBC driver (DRIVER=SQL Anywhere 10 through 16).

It's easiest to do this with Foxhound Menu - String tab rather than the DSN tab... you don't have to mess around with the ODBC Administrator.

The following connection string worked for Foxhound connecting to an Adaptive Server Anywhere 9.0.2.3951 database:

Name:   ddd9
String: ENG=ddd9; DBN=ddd9; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

This also works for SQL Anywhere 6.0.3 database running on a 6.0.4 (3594) server:

Name:   ddd6
String: ENG=ddd6; DBN=ddd6; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

However, neither DRIVER=SQL Anywhere 17 nor DRIVER=SQL Anywhere Native appear to work properly, when trying to connect to an Adaptive Server Anywhere 9.0 or earlier target; you may see this error message: The remote table 'pxxx..SYS.DUMMY' could not be found.

Mini-Backup Mini-Restore

Full Backup Full Restore

Transaction Log Backup Transaction Log Restore

The Mini-Backup process is a new feature in Foxhound 5 that automatically backs up all the changes you make to Foxhound options and settings:

• A Mini-Backup is a compressed encrypted text file that saves space by leaving out sample data.

• A new Mini-Backup file is automatically created by Foxhound shortly after you make any changes to the activation, options and settings tables.
  • You can change the lag time before a Mini-Backup files is created; see section 4 Mini-Backup on the Foxhound Options page.

  • You can't stop Mini-Backup files from being created,

  • but you can force the creation of a new Mini-Backup file is by clicking on a Save button on the Foxhound Options or Monitor Options page.

• Mini-Backup files are small... they are tiny when compared with a full backup of the Foxhound database... they don't take long to create, and they don't take long to restore.

• Mini-Backup files are written to the C:\ProgramData\RisingRoad\Foxhound5\backup folder.

• The file name format is foxhound5-bbbb-mini-backup-yyyy-mm-dd-Thh-nn-ss.txt where bbbb is the build number and yyyy-mm-dd-Thh-nn-ss is when the file was created.

• Mini-Backup files with build number bbbb can be used in the Mini-Restore process to restore activation, options and settings to an empty foxhound5.db file with the same build number bbbb.

• Old Mini-Backup files are deleted when new files are created.
  • You can change the retention count for old Mini-Backup files; see section 4 Mini-Backup on the Foxhound Options page.

A Mini-Restore is the process whereby an empty Foxhound database is copied, activated, restored and restarted using a previously-created Mini-Backup file.

Mini-Restore Method 1: All Programs - Foxhound5 - 9 Mini-Restore Mini-Restore Method 2: Run Most Recent Mini-Restore Mini-Restore Method 3: Do It Yourself

The simplest way to launch a Mini-Restore is to use this Windows shortcut:

All Programs 
   - Foxhound5 
      - 9 Mini-Restore

C:\ProgramData\RisingRoad\Foxhound5>ECHO OFF
Foxhound5 - 9 Mini-Restore
****************************************
CAUTION:
A Mini-Restore will restore an EMPTY
Foxhound database, with ONLY your
options and settings, but NO samples.
Are you sure you want to proceed?
Press any key to continue . . .

• This method will start by renaming the existing foxhound.db and foxhound.log files.

  It will then copy and rename foxhound5.db.5.0.bbbb.ORIGINAL_COPY to replace the existing foxhound.db file, and then run dbsrv17.exe to start it.

• The Foxhound Activation page will appear in a browser - Don't use Option 1!

  Option 1: Activate Foxhound with a Registration Key This installation of Foxhound 5.0.nnnn requires one of the following registration keys: ... Option 2: Activate Foxhound with a Mini-Restore Select a mini-backup file to restore: foxhound5-bbbb-mini-backup-yyyy-mm-dd-Thh-nn-ss.txt foxhound5-bbbb-mini-backup-yyyy-mm-dd-Thh-nn-ss.txt foxhound5-bbbb-mini-backup-yyyy-mm-dd-Thh-nn-ss.txt and click on

• Use Option 2 instead: Select a file and click on Run Mini-Restore.

• Be patient, it may take little while for the Mini-Restore to
  • activate the empty copy of the foxhound5.db file,

  • restore all the settings on the Foxhound Options and Monitor Options pages, and

  • restart all the sampling sessions that were running at the time of the Mini-Backup...

  • ...but it does not restore any of the old data on the Sample, History and Connection History pages.

If there were any sampling sessions running when the Mini-Backup file was originally created, they will start sampling again when you restart Foxhound... but only new samples will be stored.

Administrative Tip: Any Alert #1 Database unavailable that was in effect before the Mini-Restore was run will not immediately re-appear after the Mini-Restore is run.

This behavior is a side-effect of the way Alert #1 is handled: "...no Alert #1 will be produced for a brand-new sampling session that hasn't recorded any successful samples yet no matter how much time has passed, but checking for Alert #1 starts as soon as a successful sample is recorded or sampling is stopped and started."

When you run a Mini-Restore, the current foxhound5.db and foxhound5.log files are renamed:

C:\ProgramData\RisingRoad\Foxhound5\ 

   foxhound5.db.RENAMED-AT-yyyy-mm-dd-Thh-nn-ss

   foxhound5.log.RENAMED-AT-yyyy-mm-dd-Thh-nn-ss

Old renamed files are automatically deleted after new ones are renamed.

You can change the retention count for these old files; see section 5 Mini-Restore on the Foxhound Options page.

If you already know that you want to restore the most recent Mini-Backup file, a script has been provided that skips the Foxhound Activation page:

C:\ProgramData\RisingRoad\Foxhound5\scripts$run_most_recent_mini_restore.bat C:\ProgramData\RisingRoad\Foxhound5\scripts>ECHO OFF Foxhound5 - Run Most Recent Mini-Restore **************************************** CAUTION: A Mini-Restore will restore an EMPTY Foxhound database, with ONLY your options and settings, but NO samples. Are you sure you want to proceed?

Administrative Tip: This script depends on the FOXHOUND5 environment variable to determine where Foxhound resides, which is C:\ProgramData\RisingRoad\Foxhound5 by default.

That means you may have to copy and edit this script if you want it work for multiple copies of Foxhound stored in different locations, as described in Starting Multiple Copies of Foxhound.

Other scripts (such as the Windows shortcuts for starting Foxhound, etc.) no longer depend on FOXHOUND5.

If you want to customize the Mini-Restore process, the scripts for Method 1 and Method 2 contain all the code you need to copy and paste:

Method 1: C:\ProgramData\RisingRoad\Foxhound5\$mini_restore_foxhound5.bat

Method 2: C:\ProgramData\RisingRoad\Foxhound5\scripts\$run_most_recent_mini_restore.bat

The main difference between the two is that Method 2 runs a SQL script before launching the browser, and that SQL script automates Method 1's "Select a file and click on Run Mini-Restore":

"!SQLANY17!\!BIN!\dbisql.com"^
  -c "ENG=foxhound5; DBN=f; UID=ADHOC; PWD=SQL; CON=Mini-Restore"^
  CALL rroad_run_most_recent_mini_restore()

Note: Don't try to CALL rroad_run_most_recent_mini_restore() without first replacing foxhound5.db with a copy of foxhound5.db.5.0.bbbb.ORIGINAL_COPY.

If you do, the CALL will be ignored with an error message like this:

CALL rroad_run_most_recent_mini_restore()
-- A Mini-Restore is starting.
-- Mini-Restore bypassed because the sampling tables were not empty
-- Procedure completed

The Start - Foxhound5 - 3 Backup Database shortcut runs the SQL Anywhere dbbackup.exe utility to create a full backup of the foxhound5.db and foxound.log files.

The Full Backup saves three generations (copies) of the backup files before it starts deleting older copies. The most recent backup is always saved in the generation3 subfolder, and the oldest in generation1.

Here's what the backup folders contain after the Full Backup has been run three or more times:

Directory of C:\ProgramData\RisingRoad\Foxhound5\backup 07/03/2019 07:27 AM <DIR> generation1 07/03/2019 09:03 AM <DIR> generation2 07/03/2019 10:21 AM <DIR> generation3 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation1 07/03/2019 07:27 AM 535 backup_log.txt 07/03/2019 07:27 AM 41,435,136 foxhound5.db 07/03/2019 07:27 AM 344,064 foxhound5.log Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation2 07/03/2019 09:03 AM 535 backup_log.txt 07/03/2019 09:03 AM 42,795,008 foxhound5.db 07/03/2019 09:03 AM 352,256 foxhound5.log Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3 07/03/2019 10:27 AM 2,442 backup_log.txt 07/03/2019 10:20 AM 45,072,384 foxhound5.db 07/03/2019 10:20 AM 2,940,928 foxhound5.log

Here's how to restore the Foxhound database from one of the full backups (in this case, the most recent one):

1. Stop Foxhound (if it is still running).

2. Delete or move the Foxhound files (if they still exist):

   C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log

3. Copy the backup files:

   from: C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.db C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.log to: C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log

4. Start Foxhound.

 Caution: Foxhound 5 does not support transaction log backups... unless you make some changes. 

If you want to make transaction log backups in between full backups, you will first have to stop Foxhound from truncating the transaction log every time it takes a checkpoint.

To do that, you have to remove the dbsrv17 -m option from the Windows command files you will be using to start Foxhound; here's a list of all those files delivered with Foxhound:

C:\ProgramData\RisingRoad\Foxhound5\ $backup_foxhound5.bat $start_foxhound5_default_browser_debug.bat $start_foxhound5_default_browser.bat $start_foxhound5_engine.bat C:\ProgramData\RisingRoad\Foxhound5\scripts $create_SQL_Anywhere_17_bin64_service.bat $MULTI_Step_2_dbsrv_all.bat $MULTI_Step_2b_dbsrv_one.bat $run_most_recent_mini_restore.bat $TEN_INSTANCES_2_dbsrv.bat

Here's the change you'll have to make:

"!SQLANY17!\!BIN!\dbspawn.exe"^ -f "!SQLANY17!\!BIN!\dbsrv17.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gl all^ -gn 220^ -gna 0^ -m^ <== remove this line -n foxhound5^ -o foxhound5_debug.txt^ -oe foxhound5_debug_startup.txt^ -on 1M^ -sb 0^ -ufd restart^ -x tcpip^ -xd^ -xs http(port=80;maxsize=0;to=600;kto=600)^ foxhound5.db^ -n f

Once Foxhound is preserving the transaction log (no more dbsrv17 -m) you can run this script file every time you want to create an incremental transaction log file backup:

C:\ProgramData\RisingRoad\Foxhound5\scripts\ $backup_foxhound5_log.bat

The term "incremental log file backup" means that each log backup contains the changes made only since the previous log backup. In turn, that means a restore will have to apply every single log backup to the restored database file.

The $backup_foxhound5_log.bat script runs the dbbackup.exe utility with these options:

dbbackup.exe"^ -c "ENG=foxhound5;DBN=f;UID=BACKER;PWD=SQL"^ -o backup\generation3\backup_log.txt^ -n^ - yymmddxx.log naming convention -t^ - create a transaction log backup only -x^ - delete and restart the current transaction log backup\generation3\logs - where to put the log backup files

Note that the dbbackup -x option has the same effect as dbsrv17 -m; i.e., it truncates the current log file, only a lot less frequently.

Here's what the backup folders look like after the most recent full backup has been followed by five runs of the $backup_foxhound5_log.bat script:

Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3 07/03/2019 10:27 AM 2,442 backup_log.txt 07/03/2019 10:20 AM 45,072,384 foxhound5.db 07/03/2019 10:20 AM 2,940,928 foxhound5.log Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs 07/03/2019 10:21 AM 483,328 190703AA.log 07/03/2019 10:23 AM 73,728 190703AB.log 07/03/2019 10:24 AM 81,920 190703AC.log 07/03/2019 10:26 AM 1,024,000 190703AD.log 07/03/2019 10:27 AM 1,064,960 190703AE.log

If you have run a Full Backup followed by one or more Transaction Log Backups, you can run a Transaction Log Restore; here's an example.

1. Stop Foxhound (if it is still running).

2. Delete or move the Foxhound files (if they still exist):

   C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log

3. Copy the Full Backup database files:

   from: C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.db to: C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db

4. Choose (or create) an empty temporary folder; e.g., c:\temp.

5. Copy the Full Backup transaction log file, plus one or more Temporary Log Backup files, to the temporary folder.

   In this case, only the first two Temporary Log Backup files are copied because the changes made after that point are no longer desired:

   from: C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.log C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs\190703AA.log C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs\190703AB.log to: C:\temp\foxhound5.log C:\temp\190703AA.log C:\temp\190703AB.log

6. Create, edit and run this Windows command file to run the restore:

   REM c:\temp\restore.bat "%SQLANY17%\bin64\dbsrv17.exe"^ -gn 220^ -o C:\ProgramData\RisingRoad\Foxhound5\foxhound5_debug.txt^ -oe C:\ProgramData\RisingRoad\Foxhound5\foxhound5_debug_startup.txt^ -on 1M^ C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db^ -ad C:\temp PAUSE

   The dbsrv17 -ad C:\temp option tells SQL Anywhere to apply all the transaction log files in C:\temp to the foxhound5.db file, and then stop the engine.

   The foxhound5_debug.txt file shows that SQL Anywhere is smart enough to know which order to apply the log files in:

   I. 07/03 10:31:12. SQL Anywhere Network Server Version 17.0.9.4882I. 07/03 10:31:12. Developer edition, not licensed for deployment. ... I. 07/03 10:31:12. Starting database "foxhound5" (C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:12. Database recovery in progress I. 07/03 10:31:12. Last checkpoint at Wed Jul 03 2019 10:19 I. 07/03 10:31:12. Checkpoint log... I. 07/03 10:31:13. Transaction log: C:\temp\foxhound5.log... I. 07/03 10:31:13. Parallel recovery enabled I. 07/03 10:31:13. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:13. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:13. Transaction log: C:\temp\190703AA.log... I. 07/03 10:31:13. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:13. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Transaction log: C:\temp\190703AB.log... I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:16. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:16. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:16. Checkpointing... I. 07/03 10:31:16. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:16. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31 I. 07/03 10:31:16. Recovery complete I. 07/03 10:31:16. Database server shutdown automatically after log applied I. 07/03 10:31:18. Database server stopped at Wed Jul 03 2019 10:31

7. Start Foxhound.

The Foxhound Monitor process will cause the Foxhound database to grow in size by as much as several gigabytes per day, if target databases have hundreds or thousands of connections.

There are several ways you can limit the growth of the Foxhound database:

• Use the Sample Schedule feature to specify ping-only sampling during off-peak hours.

• Use the Sample Schedule and Connection Sample Schedule features to disable connection-level sampling during off-peak hours.

• Use Ping-Only Sampling if you don't need to gather performance statistics at all.

• Use the Purge Process to delete old sample data on a regular basis.

The growth of the Foxhound transaction log is limited by the dbsrv17 -m option which tells SQL Anywhere to truncate the transaction log whenever a checkpoint is performed.

Performance Tip: If you want to start all the Monitor sampling sessions over again, try getting rid of all the old sample data with the Mini-Restore feature; it will

• stop and rename the current foxhound5.db file (which you can delete later on),

• create an empty foxhound5.db file that uses much less space,

• restore all your Foxhound options and settings (which is why it's called "Mini-Restore"), and

• restart all your Monitor sampling sessions that were running.

Performance Tip: If you are in the habit of creating and deleting sample sessions, but you don't want to purge data "after [xxx] day(s)", try this: enable the automatic purge schedule but disable purging of all data after [xxx] days.

Here's how:

Foxhound Options - section 6. Purge 
   check    Enable automatic purge schedule 
   uncheck  Enable purging of all data after [xxx] day(s)
   click    Save

Here's why it works: the Purge process will always remove orphan data (those rows that remain after you delete a sampling session), even when purging of all data after [xxx] days is disabled.

Orphan data is useless, and it only hangs around because the "delete sampling session" process is designed to respond quickly rather than actually delete all the rows right away.

Performance Tip: The quickest way to shrink the foxhound5.db file is to re-install Foxhound and carefully choose the value for FOXHOUND5UPGRADE; here's how:

Go through the setup until it asks this question:

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=

The default ALL will copy all the data and leave some free space, so this might not be what you want... it uses ALTER DBSPACE on the new file to allocate the free space.

The most thorough is OPTIONS which throws away all the samples BUT keeps all your settings, so when you start Foxhound it will immediately start gathering samples again; the database file will be verrrrry small. This is pretty much the same as running a Mini-Restore.

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=OPTIONS

The coolest FOXHOUND5UPGRADE value 100000, as in "upgrade the settings plus all the samples recorded in the last 100000 days" which is effectively ALL the samples... but ALTER DBSPACE is NOT run; the database file will be no bigger than necessary.

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=100000

For more information about these and other values see FOXHOUND5UPGRADE.

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".

Safe mode startup, also known as "safe startup", stops sampling of all target databases by the Foxhound Monitor when Foxhound is started, and then temporarily disables most Monitor functionality. This sometimes helps if Foxhound is unresponsive when it starts.

Here's how you can turn safe mode startup on and off to "reset" Foxhound:

1. Stop Foxhound.

2. Turn on safe mode startup by creating a text file named startup.txt containing these exact 4 characters

   safe

   and placing that file in the Foxhound installation folder.

   By default, Foxhound is installed in this location on Windows 10:

   C:\ProgramData\RisingRoad\Foxhound5

3. Start Foxhound. All the Monitor sessions will now be stopped, and most Monitor functionality will be disabled (the History page will still work, however).

   Administrative Tip: Safe mode startup can be used to prevent the Foxhound purge process from immediately deleting data when you start (or upgrade) an old Foxhound database.

   For example, if you start an old Foxhound database that hasn't been running for six months, and that database is set to purge data that's older than 30 days, the very first purge will try to delete all the samples.

   However, safe mode startup disables the purge process before it can start, so you can change the purge settings before you lose any data.

   If you are going to upgrade an old Foxhound 4 database, you can create the C:\ProgramData\RisingRoad\Foxhound5 folder and startup.txt file ahead of time to disable the purge process.

4. Stop Foxhound.

5. Turn safe mode startup off by deleting or renaming the startup.txt file described above.

6. Start Foxhound. All the Monitor sessions will still be stopped, but all Monitor functionality will be available again.

You can use the Extended Edition of Foxhound to start multiple copies of the Foxhound database, each running in a separate SQL Anywhere database server and monitoring a separate set of target databases.

Here's what the End User License Agreement has to say...

In the case of the Extended Edition, an unlimited number of copies of the Foxhound database may be created and started using separate instances of SQL Anywhere on the same local network, with the requirement that a separate Extended Edition registration key be purchased for each multiple of 10 copies of Foxhound started.

In particular,

• if you start 1 through 10 copies of the Foxhound database, you must purchase one Extended Edition registration key,

• if you start 11 through 20 copies, you must purchase 2 keys,

• if you start 21 through 30 copies, you must purchase 3 keys,

• and so on.

• Also, at some point you may have to use more than one computer to run all these copies of Foxhound.

Here's how to create and customize multiple copies of Foxhound on one computer, using the following names and values:

Folders: C:\ProgramData\RisingRoad\Foxhound5\a, \b, \c, ... SQL Anywhere server names: fh5_a, fh5_b, fh5_c, ... HTTP ports: 50201, 50202, 50203, ... Windows service names: fh5_a, fh5_b, fh5_c, ...

1. Create a new folder or subfolder for each new copy of Foxhound.

   MD C:\ProgramData\RisingRoad\Foxhound5\a MD C:\ProgramData\RisingRoad\Foxhound5\b MD C:\ProgramData\RisingRoad\Foxhound5\c

2. If the master copy of Foxhound is running, stop it so the foxhound5.db file can be copied.

3. Three files are required for each new copy of Foxhound, all the others are optional:

   CD C:\ProgramData\RisingRoad\Foxhound5\a COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll CD C:\ProgramData\RisingRoad\Foxhound5\b COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll CD C:\ProgramData\RisingRoad\Foxhound5\c COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll

4. If you didn't stop Foxhound cleanly before copying foxhound5.db, you will have to copy this file as well; otherwise, it will be created when you start foxhound5.db for the first time.

   foxhound5.log

   You don't have to run the dblog.exe utility to tell Foxhound where to find foxhound5.log; it knows to look in the current folder.

5. You may want to copy and customize some additional command files:

   CD C:\ProgramData\RisingRoad\Foxhound5\a COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat CD C:\ProgramData\RisingRoad\Foxhound5\b COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat CD C:\ProgramData\RisingRoad\Foxhound5\c COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat

6. Here's how to create a custom Windows service for each new copy of Foxhound;
   • start with the $create_SQL_Anywhere_17_bin64_service.bat file copied from the scripts subfolder, and

   • make the changes shown in yellow:

   ECHO Create SQL Anywhere 17 Bin64 fh5_a Service ECHO To delete existing fh5_a service... PAUSE "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -d fh5_a -y REM Tip: To avoid SQLCODE -602, -1066 and other exceptions when using UNC REM and other file specifications in Adhoc and OFSS SQL statements, try REM changing the dbsvc -as option to dbsvc -a and -p to provide the name REM and password for a Microsoft Windows account you have created: REM -a youraccount^ REM -p yourpassword^ ECHO To create the fh5_a service... PAUSE "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe"^ -o "C:\ProgramData\RisingRoad\Foxhound5\a\dbsvc_log.txt"^ -y^ -as^ -s Automatic^ -sn fh5_a^ -sd "fh5_a Database Monitor Bin64 Service"^ -t Network^ -w fh5_a "C:\Program Files\SQL Anywhere 17\Bin64\dbsrv17.exe"^ -c 25p^ -ch 50p^ -cr-^ -gk all^ -gl all^ -gn 220^ -gna 0^ -m^ -n fh5_a^ -o "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5_debug.txt"^ -oe "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5_debug_startup.txt"^ -on 1M^ -qn^ -sb 0^ -ufd restart^ -x tcpip^ -xd^ -xs http(port=50201;maxsize=0;to=600;kto=600)^ "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5.db"^ -n f ECHO To display the fh5_a service definition... PAUSE "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -g fh5_a ECHO To list all the SQL Anywhere service names... PAUSE "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -l ECHO All done... PAUSE

7. Here's how to start, browse and stop the three new copies of Foxhound:

   "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_a "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_b "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_c START http://localhost:50201/foxhound START http://localhost:50202/foxhound START http://localhost:50203/foxhound "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_a "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_b "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_c

8. Here's how to customize the other command files:

   $adhoc_query_foxhound5.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a -c "ENG=foxhound5; -c "ENG=fh5_a; CON=Foxhound5 CON=fh5_a

   $admin_update_foxhound5.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a -c "ENG=foxhound5; -c "ENG=fh5_a; CON=Foxhound5 CON=fh5_a

   $backup_foxhound5.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a %SERVER% fh5_a -xs http(port=80; -xs http(port=50201;

   $start_foxhound5_default_browser.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a %SERVER% fh5_a -xs http(port=80; -xs http(port=50201; localhost:80 localhost:50201

   $start_foxhound5_engine.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a %SERVER% fh5_a -xs http(port=80; -xs http(port=50201; localhost/ localhost:50201/

   $stop_foxhound5_engine.bat

   Change... To... ECHO Foxhound5 ECHO fh5_a %SERVER% fh5_a

Introducing OFSS OFSS Terminology OFSS Limitations Step 1: Set up OFSS on your database: OFSS_1_setup.sql Step 2: Run the OFSS Monitor on your database: CALL start_OFSS_monitor() Step 3: Run the OFSS Load component on Foxhound: CALL load_OFSS_samples()

How to use OFSS on multiple databases How to choose an Air Gap technique for OFSS How to customize OFSS How to debug OFSS code on your database

The Offline Foxhound Sampling Service (OFSS) provides an alternative method for Foxhound to gather and display performance statistics from a subject database when Foxhound cannot (or must not) establish a direct client server connection to that database.

OFSS is supported for subject databases running on SQL Anywhere 12, 16 and 17.

Figure 1: OFSS Architecture

1. The Capture component of the OFSS Monitor gathers and inserts one OFSS Sample in your Subject Database every 10 seconds.

2. The Unload component of the OFSS Monitor periodically writes a batch of OFSS Samples to an external OFSS Batch text file.

3. Some unspecified method is used to transport the OFSS Batch files across the Air Gap from your Subject Server environment to the Foxhound Server environment.

4. The OFSS Load component of Foxhound periodically reads one or more OFSS Batch files and stores the data as Foxhound Samples for display on the Sample History page.

The word "Offline" in Offline Foxhound Sampling Service emphasizes that Foxhound receives the samples in files long after they have been recorded by an entirely separate process.

The word "Service" in Offline Foxhound Sampling Service is a general term meaning "supplier" or "provider". In particular, OFSS is not implemented as a Windows or HTTP service.

The phrase "Subject Database" is used in OFSS documentation instead of the Foxhound term "Target Database" to draw attention to the fact that while the database may be the subject of Foxhound performance monitoring it is not the target of any direct connection from Foxhound.

The "OFSS Monitor" is an open source collection of SQL Anywhere tables and stored procedures that you install on your Subject Database via the delivered script OFSS_1_setup.sql and invoke via CALL start_OFSS_monitor().

The word "Batch" refers to a consecutive set of samples gathered, compressed, optionally encrypted and then passed from the OFSS Monitor across the Air Gap to Foxhound as a single text file.

The term "Air Gap" is a network security term for the way your subject database is isolated from the computer running Foxhound. For security reasons this document doesn't suggest (or even discuss) methods for implementing or bridging a real air gap; the examples use a shared folder which effectively means "no air gap".

Some of the limitations of OFSS are a result of the way it works, and others (like AutoDrop) are simply omitted features:

• Client-server response times are not measured; i.e., the Heartbeat, Sample and Ping times are not displayed

• Alert criteria are checked after the fact, as the OFSS batches are loaded into the Foxhound database, so Alert emails are not available.

• The AutoDrop feature is not available.

• Schedules are not supported; i.e., there are no Sample, Connection or AutoDrop schedules.

• The Change Target Settings feature is not available; you cannot dynamically change the subject RememberLastPlan, RememberLastStatement and RequestTiming options from the Monitor Options page.

• There is no connection from Foxhound to the subject database, so the Monitor Options - Monitor Connection Settings feature doesn't apply.

1(a) Connect to your database via ISQL

1(b) Run this script: OFSS_1_setup.sql

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_1_setup.sql SQL - Execute Owner: OFSS OK

Administrative Tip: You can choose any user id you want as Owner, including DBA. If you choose a user id that doesn't exist, it will be created for you.

1(c) Observe that the script ran OK

... OFSS_1_setup.sql done OK.

Administrative Tip: You can re-run the setup script at any time, and when you do, all the OFSS-related objects inside your subject databases are dropped and recreated.

This has the additional effect of telling Foxhound to treat the subsequent OFSS batches as coming from a different subject database.

For more information on how unique Foxhound display names are assigned to OFSS subject databases, see 8. Adhoc Queries » 8.5 OFSS Connection String Names.

Administrative Tip: OFSS doesn't work on read-only subject databases because the OFSS scripts can't modify the database. This limitation affects High Availability secondary (mirror) and read-only scale-out databases in particular.

Administrative Tip: When using OFSS on a High Availability setup, run the OFSS setup and start_OFSS_monitor() on the primary, not the underlying partner. After a failover, you can then rerun the CALL start_OFSS_monitor() on the primary because it will be updatable.

You can also run OFSS on the arbiter server if it has a database.

2(a) Connect to your database via ISQL

2(b) Load this script: OFSS_2_run_the_OFSS_monitor.sql

Note: The sample scripts shown here use a shared folder to transport the OFSS batches; they do not show how to implement an "air gap".

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_2_run_the_OFSS_monitor.sql

2(c) Edit the script as desired

For example, change the path specification for the batch files, and the duration of each batch, and then start the OFSS Monitor:

-- Run this code on your OFSS subject database, not the Foxhound database. PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSS\\'; -- default is 'C:\\temp\\' UPDATE "{OWNER}".OFSS_settings SET max_batch_duration = '5m'; -- default is '10m', range is '1m' to '1h' CALL "{OWNER}".start_OFSS_monitor();

Administrative Tip: If the CALL start_OFSS_monitor() fails with "Cannot access file ... Permission denied SQLCODE=-602", and

• you are using a UNC file specification like SET unload_path = '\\\\Xps\\c\\DATA\\OFSSremote\\'

• with a subject database being run as a Windows service,

• try using the dbsvc -a and -p options to run the service with the account name and password for a Microsoft Windows account you have created.

Administrative Tip: If you stop and then re-execute the CALL start_OFSS_monitor(), there will be a gap in the batch numbering.

Whether one or two batch numbers are skipped depends on the timing of the interruption.

See also...

How to customize OFSS

2(d) Run the script OFSS_2_run_the_OFSS_monitor.sql

SQL - Execute Owner: OFSS OK

Administrative Tip: You can stop and rerun the CALL start_OFSS_monitor() at any time, and when you do, the current batch of samples being gathered will be discarded and a new batch of samples will be started.

This may create a gap in the samples displayed by Foxhound, but it does have the benefit of letting you change the OFSS settings that control how the batch files are created. For more information see How to customize OFSS.

2(e) Observe the OFSS sampling progress on your subject database

Use a separate ISQL session to run this query:

SELECT subject_name, batch_number, updated_at FROM OFSS.OFSS_settings; subject_name batch_number updated_at -------------------------------------------------------------------------------- ------------ ----------------------- ddd12 10 2019-02-27 13:45:38.192

Administrative Tip: Be sure to use the right Owner name in the query above, in this case "OFSS". It's quite easy to accidentally run Step 1 with different owners, and get multiple different sets of OFSS objects set up; e.g., one set owned by OFSS and another by DBA.

2(f) Observe the new OFSS batch files being created

Administrative Tip: You'll have to wait a while (by default 10 minutes) before a DIR command will show any batch files... and if you run Step 3 right away, the files may be deleted before you get to see them.

Directory of C:\DATA\OFSS 02/27/2019 01:52 PM <DIR> . 02/27/2019 01:52 PM <DIR> .. 02/27/2019 01:45 PM 118,024 OFSS-ddd12-batch-----10-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 12:14 PM 121,005 OFSS-ddd12-batch------1-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 12:24 PM 116,979 OFSS-ddd12-batch------2-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 12:34 PM 117,231 OFSS-ddd12-batch------3-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 12:44 PM 117,417 OFSS-ddd12-batch------4-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 12:54 PM 117,661 OFSS-ddd12-batch------5-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 01:05 PM 117,810 OFSS-ddd12-batch------6-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 01:15 PM 117,041 OFSS-ddd12-batch------7-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 01:25 PM 117,614 OFSS-ddd12-batch------8-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 02/27/2019 01:35 PM 117,365 OFSS-ddd12-batch------9-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt 12 File(s) 1,178,163 bytes 2 Dir(s) 337,448,173,568 bytes free

Administrative Tip: OFSS samples are temporarily stored inside your subject database by the OFSS Monitor until they have been written to the OFSS batch files, at which point they are automatically deleted from your database.

As far as the OFSS batch files are concerned, it is up to your implementation of the air gap to delete those files when they are no longer needed.

However, if the OFSS Monitor and Foxhound Load processes both use the same shared folder to write and read the files, the OFSS Load component on Foxhound will take care of deleting the files; see the next section Step 3: Run the OFSS Load component on Foxhound: CALL load_OFSS_samples().

In particular, if the OFSS Load component on Foxhound is running at the same time as the OFSS Monitor on your source database, and they are both using exactly the same physical folder, the batch files may be deleted almost as fast as they are written and the folder may be empty most of the time.

3(a) Run: Foxhound5 - 1 Start Foxhound

Don't connect to Foxhound to your database; i.e., do not click on Monitor Database.

3(b) Run: Foxhound5 - 2 Adhoc Query Via ISQL

3(c) Load this script: OFSS_3_load_OFSS_batches_into_Foxhound.sql

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_3_load_OFSS_batches_into_Foxhound.sql

3(d) Edit the script as desired

For example, change the path specification for the batch files and start the load process:

-- Run this code on the Foxhound database, not your OFSS subject database... -- use the Foxhound5 shortcut "2 Adhoc Query via ISQL" CALL load_OFSS_samples ( 'C:\\DATA\\OFSS\\' );

Note: The sample scripts shown here use a shared folder to receive the OFSS batches; they do not show how to implement an "air gap".

Administrative Tip: You can use the same folder to receive the batches from multiple different OFSS subject databases.

In that case, you must run only one instance of the script to CALL load_OFSS_samples(), and Foxhound will use OFSS batch file names to determine which database is which.

Administrative Tip: If the CALL load_OFSS_samples() fails with "File system error: ... SQLCODE=-1066", and

• you are using a UNC file specification like CALL load_OFSS_samples ( '\\\\Inspiron\\c\\DATA\\InspironOFSSremote\\' )

• with a copy of Foxhound being run as a Windows service,

• try using the dbsvc -a and -p options to run the service with the account name and password for a Microsoft Windows account you have created.

Administrative Tip: If you stop and start Foxhound, you will have to re-run the script to CALL load_OFSS_samples().

3(e) Run the script OFSS_3_load_OFSS_batches_into_Foxhound.sql

SQL - Execute

3(f) View the samples in Foxhound

The OFSS subject database will automatically appear on the Foxhound Menu page. You can then open the History page (not the Monitor page), and then click on the Newest button to see new OFSS batches as they are added.

See also...

How to customize OFSS

You can use OFSS on multiple subject database and display all the samples in one copy of Foxhound; here's how:

• Set up one single destination folder on your Foxhound computer to receive all the OFSS batch files.

• Run Step 1: OFSS_1_setup.sql on each subject database.

• Run Step 2: CALL start_OFSS_monitor() on each subject database.

• Run Step 3: CALL load_OFSS_samples() once, on the Foxhound computer.

OFSS uses a UUID to identify each subject database, and that UUID appears in the OFSS batch file names so you don't have to worry about overlaps or collisions.

First, the bad news...

By its very definition a network air gap is a security mechanism you will want to keep secret. This Help document is openly available on the internet so any specific details found here would break that secrecy.

Now, the good news...

OFSS writes data to text files into a folder, then Foxhound reads those files from a folder. How those files get from one computer to another is the "secret network air gap technique"... shhh, don't tell anyone, but a diskette or USB drive may be all you need.

More good news...

If you have more than one OFSS subject database on one computer, you can put all the files in one folder. Their file names are all unique, and Foxhound keeps the data separate.

The same applies to sending OFSS files from several computers to one folder on one Foxhound computer: the file names are globally unique and Foxhound keeps everything straight.

For testing purposes...

A single network shared drive works fine.

OFSS can be customized by using one or more of the following techniques when calling start_OFSS_monitor() and load_OFSS_samples() on Foxhound:

A. Continue processing until a specific time. B. Specify the database name to be displayed in Foxhound. C. Specify the maximum batch duration and storage size. D. Disable capturing of server messages

E. Use a different folder to store the OFSS batch files. F. Enable encryption by providing a non-empty key. G. Change multiple settings. H. Use different folders to use different encryption keys.

[Top]

-- A. Continue processing until a specific time. -- On your subject database: PARAMETERS OWNER; CALL "{OWNER}".start_OFSS_monitor ( @stop_sampling_after = DATEADD ( DAY, 10, CURRENT TIMESTAMP ) ); -- On Foxhound: CALL load_OFSS_samples ( @stop_loading_after = DATEADD ( DAY, 10, CURRENT TIMESTAMP ) );

Set @stop_sampling_after to the future timestamp when you want start_OFSS_monitor() to stop recording samples from your subject database.

Set @stop_loading_after to the future timestamp when you want load_OFSS_samples() to stop loading samples into the Foxhound database.

The defaults for @stop_sampling_after and @stop_loading_after are both '9999-01-01' which means "run forever" (until you stop the process manually).

Administrative Tip: To ensure all the samples are loaded into Foxhound, use a @stop_loading_after value that is slightly larger than @stop_sampling_after... or just wait a while before calling load_OFSS_samples().

[Top]

-- B. Specify the database name to be displayed in Foxhound. -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET subject_name = 'Inventory - Cleveland'; CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound: CALL load_OFSS_samples();

Foxhound displays the name of each OFSS subject database in two locations:

• The list of databases on Monitor tab of the Foxhound Menu page, and

• the database title on Sample History page.

By default, the name is set to the first 80 characters of your subject database name; i.e., LEFT ( DB_PROPERTY ( 'Name' ), 80 ).

If you want to change the default, do it before you call start_OFSS_monitor() for the first time.

When you call load_OFSS_samples(), Foxhound appends the suffix "(offline)" to create the display name; e.g., "Inventory - Cleveland (offline)".

Note: It's OK to use the same name for multiple subject databases. For example, three different SQL Anywhere servers could all be running databases with the same name "Inventory". In this case Foxhound will assign different display names as follows:

Inventory (offline)
Inventory(02) (offline)
Inventory(03) (offline)

Foxhound uses a different column to tell different OFSS subject databases apart; i.e., the UUID value in the OFSS_subject_uuid column is used to tell which OFSS batch file belongs to which subject database.

For more information about how Foxhound handles OFSS display names, see OFSS Connection String Names section in the Adhoc Queries topic.

Administrative Tip: If you change OFSS_settings.subject_name on-the-fly and then stop and rerun the CALL start_OFSS_monitor(), the new subject_name will not affect the display name in Foxhound. The new subject_name will be used in the OFSS batch file names, and the Foxhound load_OFSS_samples() procedure will read those files OK, but the samples will be displayed under the old name.

[Top]

-- C. Specify the maximum batch duration and storage size. -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET max_batch_duration = '1h'; -- range 1m to 1h UPDATE "{OWNER}".OFSS_settings SET max_batch_size = '512M'; -- range 10k to 512M CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound: CALL load_OFSS_samples();

The default values are '10m' and '512k' respectively. There are several factors to consider when choosing the maximum size of OFSS batch files.

• A short duration (e.g., 10 minutes) may be appropriate for demonstration purposes, and

• if batches cross the air gap quickly and are loaded into Foxhound as soon as they are available, a short duration means the samples are promptly displayed.

• Also, large batches take longer to load into Foxhound, further adding to the delay before you see them displayed.

• However, a short duration means more gaps between batches, and gaps are not perfectly well handled by Foxhound.

  For example, some interval-related statistics like Throughput and CPU% are not shown for the first sample in a new batch

• Your decision may also be affected by the technique you choose to implement the air gap.

  For example, if the air gap is difficult to bridge you may choose a longer interval; e.g., 1 hour.

[Top]

-- D. Disable capturing of server messages (don't call sa_server_messages). -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET server_messages_are_to_be_captured = 'N'; CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound: CALL load_OFSS_samples();

The default for server_messages_are_to_be_captured is 'Y'.

[Top]

-- E. Use a different folder to store the OFSS batch files. -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSS\\'; CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound: CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSS\\' );

The default path is 'C:\\temp\\'.

Note: All the examples shown here assume there is no air gap; i.e., start_OFSS_monitor() writes the batch files to exactly the same folder that load_OFSS_samples() reads them from: same folder, same computer.

When a real air gap is implemented,

• the unload_path used by start_OFSS_monitor() is relative to the computer running your subject database, and

• the @load_path used by load_OFSS_samples() is relative to the computer running Foxhound.

• In other words, the relative path specifications may look the same but the actual folders reside on different computers.

Administrative Tip: If the CALL start_OFSS_monitor() fails with "Cannot access file ... Permission denied SQLCODE=-602", and

• you are using a UNC file specification like SET unload_path = '\\\\Xps\\c\\DATA\\OFSSremote\\'

• with a subject database being run as a Windows service,

• try using the dbsvc -a and -p options to run the service with the account name and password for a Microsoft Windows account you have created.

Administrative Tip: If the CALL load_OFSS_samples() fails with "File system error: ... SQLCODE=-1066", and

• you are using a UNC file specification like CALL load_OFSS_samples ( @load_path = '\\\\Inspiron\\c\\DATA\\InspironOFSSremote\\' )

• with a copy of Foxhound being run as a Windows service,

• try using the dbsvc -a and -p options to run the service with the account name and password for a Microsoft Windows account you have created.

[Top]

-- F. Enable encryption by providing a non-empty key. -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e'; -- the values must agree CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound: CALL load_OFSS_samples ( @encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' ); -- the values must agree

The default encryption key is '' which means "no encryption".

[Top]

-- G. Change multiple settings. -- On your subject database: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSS\\'; UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e'; CALL "{OWNER}".start_OFSS_monitor ( @stop_sampling_after = DATEADD ( HOUR, 1, CURRENT TIMESTAMP ) ); -- On Foxhound: CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSS\\', @stop_loading_after = DATEADD ( MINUTE, 70, CURRENT TIMESTAMP ), @encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' );

[Top]

-- H. Use different folders to use different encryption keys.. -- On subject database 1: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd12\\'; -- folder 1 UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e'; -- encryption key 1 CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound Adhoc Query connection 1: CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSSddd12\\', -- folder 1 @encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' ); -- encryption key 1 -- On subject database 2: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd16\\'; -- folder 2 UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'gigd}qh]enUU2Y/-j4)Sdj_uP5T{!CpH'; -- encryption key 2 CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound Adhoc Query connection 2: CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSSddd16\\', -- folder 2 @encryption_key = 'gigd}qh]enUU2Y/-j4)Sdj_uP5T{!CpH' ); -- encryption key 2 -- On subject database 3: PARAMETERS OWNER; UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd17\\'; -- folder 3 UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'GPPSsMsPSFGC:MBl[-+D)P+F3rPYVu0V'; -- encryption key 3 CALL "{OWNER}".start_OFSS_monitor(); -- On Foxhound Adhoc Query connection 3: CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSSddd17\\', -- folder 3 @encryption_key = 'GPPSsMsPSFGC:MBl[-+D)P+F3rPYVu0V' ); -- encryption key 3

It's OK to run multiple CALL load_OFSS_samples() statements at one time on multiple connections to Foxhound if each one reads the OFSS batch files from a different folder...

...and that's what you have to do if you want to use different encryption keys for different subject databases.

I.e., if you want to use a different encryption key for each different subject database, you have to put the OFSS batch files in different folders. The underlying technical reason is this: the load_OFSS_samples() procedure reads all the OFSS batch files from one folder, and uses the same encryption key on all of them.

If you modify the OFSS setup script OFSS_1_setup.sql you may introduce bugs that raise SQL exceptions, and those exceptions may be recorded in this table:

CREATE TABLE "{OWNER}".OFSS_exception ( exception_id BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED, inserted_at TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, diagnostic_text LONG VARCHAR NOT NULL );

Here's a query that shows the most recent 10 exceptions (assuming that you used specified OFSS as the OWNER when you ran the setup script):

SELECT TOP 10 * FROM OFSS.OFSS_exception ORDER BY OFSS_exception.inserted_at DESC;

It is possible to run OFSS and a direct connection from Foxhound at the same time. This can be helpful while you're testing changes to the OFSS setup, but the following scenario may also be helpful in production:

1. Use OFSS to gather high volumes of sample data that is loaded into Foxhound during off hours, and

2. use a direct Foxhound connection to continuously monitor database availability and server messages but not the connection-level detail.

Here's how to disable connection-level samples for a direct connection from Foxhound (not OFSS):

Open the Foxhound Monitor Options page. Check Enable Schedules in section 1. Global Overrides of the Monitor Options page. Select the target database that Foxhound is directly monitoring. Use section 6. Connection Sample Schedule to stop Foxhound from gathering connection-level samples, as follows: Check Enforce the Connection Sample Schedule, click on the Clear button for all the days of the week Mon through Sun, and click Save ( don't forget that! :)...

FOXHOUND5 - optional FOXHOUND5BIN - optional FOXHOUND5UPGRADE - optional SQLANY17 - required

By default FOXHOUND5 is created by the Foxhound InstallShield installation to contain the drive and path of the folder where the original copy of Foxhound is installed; e.g., C:\ProgramData\RisingRoad\Foxhound5\

It is no longer required by the delivered Windows shortcuts or by any of the code inside Foxhound. That's because multiple copies of Foxhound in different folders can be created (see Starting Multiple Copies of Foxhound) and they all need to be able to locate their own folders... which they do by using the new local DBFOLDER environment variable.

It is used by one of the optional command files in the scripts subfolder ($run_most_recent_mini_restore.bat), and by the $post_setup.bat file which is launched by the Foxhound installation process.

Normally you do not have to create the FOXHOUND5 environment variable, but if you do, here's how:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5
                Variable value:  C:\ProgramData\RisingRoad\Foxhound5\

FOXHOUND5BIN is an optional environment variable. It can be used to change the default SQL Anywhere installation subfolder used by Foxhound from Bin32 to Bin64 or vice versa.

By default Foxhound will use Bin32 subfolder unless Bin64 exists in which case Foxhound will use Bin64. To override this rule create FOXHOUND5BIN as follows:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5BIN
                Variable value:  Bin32
                      -- or --   Bin64

After setting FOXHOUND5BIN you must stop and restart the Foxhound database to put the change into effect.

For example, if you have installed both 32-bit and 64-bit versions of SQL Anywhere, you can force the Foxhound 5 shortcuts to use the 32-bit version by setting FOXHOUND5BIN to Bin32.

Note: This discussion of the FOXHOUND5BIN environment variable applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.

Tip: One of the reasons to run Foxhound on the 32-bit version of SQL Anywhere is to make it easier to use the 32-bit version of ODBC to connect to your target database... but that may not be necessary!

For example, if you have an Adaptive Server Anywhere 9.0 target database you may be able to connect to it using a more recent SQL Anywhere ODBC driver (DRIVER=SQL Anywhere 10 through 16).

It's easiest to do this with Foxhound Menu - String tab rather than the DSN tab... you don't have to mess around with the ODBC Administrator.

The following connection string worked for Foxhound connecting to an Adaptive Server Anywhere 9.0.2.3951 database:

Name:   ddd9
String: ENG=ddd9; DBN=ddd9; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

This also works for SQL Anywhere 6.0.3 database running on a 6.0.4 (3594) server:

Name:   ddd6
String: ENG=ddd6; DBN=ddd6; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

However, neither DRIVER=SQL Anywhere 17 nor DRIVER=SQL Anywhere Native appear to work properly, when trying to connect to an Adaptive Server Anywhere 9.0 or earlier target; you may see this error message: The remote table 'pxxx..SYS.DUMMY' could not be found.

FOXHOUND5UPGRADE is an optional environment variable that is very rarely used. It can be used to change the factory setting default behavior of the post-setup data upgrade process when reinstalling Foxhound.

The factory default setting is ALL which means "upgrade all the data."

When you run the data upgrade process you will be asked to change or confirm the setting:

Post-Setup Process for Foxhound Version 5.0
*** Checking for a post-setup path parameter...
*** A post-setup path parameter was provided...
C:\ProgramData\RisingRoad\Foxhound5\
*******************************************************************
***                   Foxhound 5.0.xxxx
***
*** Here's where Foxhound is being installed:
*** C:\ProgramData\RisingRoad\Foxhound5\
***
*** Starting the Foxhound 5.0.xxxx post-setup process...
*** Creating foxhound5.db.4.0.xxxx.ORIGINAL_COPY...
*** Checking for an existing Foxhound5 installation...
***  ...yes, there is an existing Foxhound5 installation.
*** Checking if the existing data should be upgraded...
***  ...yes, the existing Foxhound5 data should be upgraded.
******************************************************************
*** PLEASE READ THIS, AND CONFIRM OR CHANGE **********************
******************************************************************
***
*** "FOXHOUND5UPGRADE" specifies how much data is to be upgraded.
***
*** If you want to CHANGE the setting, type in a new value...
***    ALL       - upgrade all the data
***    OPTIONS   - no samples, just the Foxhound options
***    yyyymmdd  - options plus samples since yyyymmdd
***    nnn       - options plus last nnn days of samples
***    NOTHING   - don't upgrade any data
*** and press Enter to continue.
***
*** If you LIKE the current setting...
***    FOXHOUND5UPGRADE=ALL which means upgrade all the data
*** just press Enter.
******************************************************************
Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=

You can change the default setting ahead of time by creating the FOXHOUND5UPGRADE environment variable:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5UPGRADE
                Variable value:  ALL
                      -- or --   OPTIONS
                      -- or --   yyyymmdd
                      -- or --   nnn
                      -- or --   NOTHING

• ALL    Upgrade all the data.

  If sampling was enabled before the upgrade process was started, sampling will start again as soon as you start Foxhound after the upgrade is finished.

  In this case only, Foxhound will use the ALTER DBSPACE SYSTEM ADD command to expand the new Foxhound database file to accommodate all the data plus 10% before starting to copy the data. The new Foxhound database file won't be much (or any) smaller than the old one, but it will be reorganized by the upgrade process.

  Performance Tip: The most effective way to make the upgrade process faster is to change the FOXHOUND5UPGRADE value from ALL to some other value during the post-update stage of the Foxhound installation process:

  It is also one of the best ways to shrink the size of the Foxhound database file.

• OPTIONS    Upgrade all the Foxhound settings and options, including the Foxhound activation status and the Monitor Options for all the target databases, but don't copy any of the sample data.

  If sampling was enabled before the upgrade process was started, sampling will start again as soon as you start Foxhound after the upgrade is finished.

  In this case, Foxhound will not use ALTER DBSPACE SYSTEM ADD to expand the new database file, with the result being the new Foxhound database file will be quite small.

• yyyymmdd    Upgrade all the Foxhound options plus all the samples recorded since yyyymmdd. You can specify any valid timestamp as long as it at least 8 characters long and in the past, but no more than 100000 days in the past; e.g., 20110801, 2011-08-01 23:59:59.999, 2011-7-7 12 are all valid timestamps.

  Caution: If you specify a timestamp more than 100000 days in the past, Foxhound will use the OPTIONS setting and no samples at all will be copied.

  Foxhound chooses OPTIONS because it runs quickly so you don't have to wait so long if it was a mistake. To correct the error, restore the old copy of the Foxhound database and reinstall Foxhound again.

  If sampling was enabled before the upgrade process was started, sampling will start again as soon as you start Foxhound after the upgrade is finished.

  In this case, Foxhound will not use ALTER DBSPACE SYSTEM ADD to expand the new database file even if yyyymmdd is earlier than the earliest recorded sample. In other words, you can use an old yyyymmdd to copy all the data while at the same time reorganizing and shrinking the Foxhound database.

• nnn    Upgrade all the Foxhound options plus all the samples recorded in the past nnn days. You can specify any positive integer from 1 to 100000.

  Caution: If you specify a number outside the range 1 to 100000, Foxhound will use the OPTIONS setting and no samples at all will be copied.

  Foxhound chooses OPTIONS because it runs quickly so you don't have to wait so long if it was a mistake. To correct the error, restore the old copy of the Foxhound database and reinstall Foxhound again.

  Here is the formula used to convert nnn into a timestamp: DATEADD ( DAY, -nnn, CURRENT TIMESTAMP )

  If sampling was enabled before the upgrade process was started, sampling will start again as soon as you start Foxhound after the upgrade is finished.

  In this case, Foxhound will not use ALTER DBSPACE SYSTEM ADD to expand the new database file even if nnn is earlier than the earliest recorded sample. In other words, you can use a large nnn to copy all the data while at the same time reorganizing and shrinking the Foxhound database.

• NOTHING    Don't upgrade any data, no options or settings, not even the activation status.

  Note: Even in this case, Foxhound will preserve the existing copy of the Foxhound database and transaction log files in the backup\previous_build folder.

• EVERYTHING    Same as ALL.

• SETTINGS    Same as OPTIONS.

• NONE    Same as NOTHING.

• FRESH    Same as NOTHING.

The SQLANY17 environment variables must have a valid value for the Foxhound 5 shortcuts to work properly.

By default SQLANY17 is set by the SQL Anywhere 17 installation process to contain the drive and path of the folder where SQL Anywhere 17 is installed.

Normally you do not have to create the SQLANY17 environment variable, but if you do, here's how:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   SQLANY17 
                Variable value:  C:\Program Files\SQL Anywhere 17

Note: This discussion of the SQLANY17 environment variable applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.

Several optional and supporting SQL scripts and Windows command files are stored in a separate subfolder:

C:\ProgramData\RisingRoad\Foxhound5\scripts

Here's a cross-reference list of the scripts:

File Name	Description	See Also
$backup_foxhound5_log.bat	Backup Foxhound Transaction Log	Transaction Log Backup
$create_SQL_Anywhere_17_bin64_service.bat	Create SQL Anywhere 17 Bin64 Foxhound Service	Running Foxhound as a Service
$run_most_recent_mini_restore.bat	Automatically Restore Most Recent Mini-Backup	Mini-Restore
OFSS_1_setup.sql	The Required OFSS Setup	How to set up OFSS on your database
OFSS_2_sample_loop_to_capture_and_unload_batches.sql	Run the OFSS Capture and Unload components	How to run the OFSS Capture and Unload components on your database
OFSS_3_sample_loop_to_load_batches_into_Foxhound.sql	Run the OFSS Load component	How to run the OFSS Load component on Foxhound

RisingRoad - 3QC Inc. License Terms

Foxhound Version 5

By using the software, you accept the following terms. IF YOU DO NOT ACCEPT THEM, DO NOT USE THE SOFTWARE.

1. No-Nonsense License Agreement

The Foxhound software is protected by United States and Canadian copyright law and international copyright treaty provisions. Therefore, you must treat Foxhound just like a book, except that you may copy it onto a computer to be used and you may make archival copies of Foxhound for the sole purpose of backing-up our software and your data and protecting your investment from loss.

By saying "just like a book," RisingRoad means, for example, that one copy of Foxhound may be executed on any number of computers, and may be freely moved from one computer to another, so long as there is no possibility of it being executed on one computer while it's being executed on another.

By saying "one copy of Foxhound", RisingRoad means a copy of the Foxhound software that has been activated by the application of one single unique registration key obtained from RisingRoad.

Just like one copy of a book can't be read by two different people in two different places at the same time, neither can one copy of Foxhound be executed on two different computers at the same time. (Unless, of course, this License Agreement has been violated.)

2. Use on a Network and the Internet

In the case of the Rental and Basic Editions, one copy of Foxhound may be executed on a computer attached to a local area network and/or the internet, with multiple users accessing the single Foxhound database from browsers running on different computers. The "just like a book" analogy begins to weaken at this point, but it still applies if you think of more than one person reading the same book over someone else's shoulder... it's still one copy of the book, and one copy of Foxhound executing.

In the case of the Extended Edition, an unlimited number of copies of the Foxhound database may be created and started using separate instances of SQL Anywhere on the same local network, with the requirement that a separate Extended Edition registration key be purchased for each multiple of 10 copies of Foxhound started.

3. Further Explanation of Copyright Law and the Scope of This License Statement

You may not download or transmit your copy of Foxhound electronically (either by direct connection or telecommunication transmission) for the purpose of executing it on multiple computers at the same time.

You may transfer all of your rights to use your copy of Foxhound to another person, provided that you transfer to that person (or destroy) all of the software and documentation provided in this package, together with all copies, tangible or intangible, including copies in RAM or installed on a disk, as well as all back-up copies. Remember, once you transfer your copy of Foxhound, it may only be executed on the single computer to which it is transferred and, of course, only in accordance with copyright law and international treaty provisions.

Except as stated in this paragraph, you may not otherwise transfer, rent, lease, sub-license, time-share, or lend the Foxhound software or documentation. Your use of Foxhound is limited to acts that are essential steps in the use of Foxhound on your computer as described in the documentation. You may not otherwise modify, alter, adapt, merge, decompile or reverse-engineer Foxhound, and you may not remove or obscure RisingRoad copyright notices.

6. Disclaimer of Warranty

The software is licensed "as is". You bear the risk of using it. RisingRoad gives no express warranties, guarantees or conditions. RisingRoad excludes the implied warranties of merchantability, fitness for a particular purpose and non-infringement.

Foxhound support is free: Send all your questions about how to set up and use Foxhound to Breck.Carter@gmail.com.

If you have specific questions about how to improve the performance of your database, the first hour of SQL Anywhere performance and tuning consulting is also free, via email.

Here's how it works:

1. You run the Foxhound 5 Database Monitor to gather samples from your SQL Anywhere database,

2. then you send me a copy (or a subset) of your Foxhound database

3. which I look at for one hour

4. and then I send you my observations and suggestions.

Yes, the first hour of consulting is free... and you can send me your entire foxhound5.db file if it fits in an email or dropbox.com.

But . . . if your Foxhound database is huge . . .

If your foxhound5.db file is too big to send by email or dropbox, you can unload and send a smaller subset like this:

1. Run Foxhound5 - 2 Adhoc Query via ISQL

2. Call unload_samples procedure to gather a subset.

   For example, the following statements unload all the samples recorded since May 1 and write the files into C:\temp:

   CALL unload_samples ( '2019-05-01' ); -- see below for more examples

3. Attach the files from C:\temp to a covering email and send it to me: Breck.Carter@gmail.com

That's it! ...you'll hear back from me soon.

-- unload_samples - Unloads some or all of the rows from the Foxhound 4 sample-related views. -- -- Syntax -- -- CALL unload_samples ( from_timestamp, -- to_timestamp, -- sampling_id, -- folder_path ); -- -- from_timestamp The TIMESTAMP value for the earliest rows to be selected from the views -- that contain timestamp columns; e.g., alert but not alerts_criteria. -- A NULL or omitted value means that no earliest limit is placed on these rows. -- -- to_timestamp The TIMESTAMP value for the latest rows to be selected from the views -- that contain timestamp columns; e.g., alert but not alerts_criteria. -- A NULL or omitted value means that no latest limit is placed on these rows. -- -- sampling_id The UNSIGNED INT value for the one sampling_id value to be selected -- from views that are specific to individual target databases; e.g., -- alerts_criteria but not exception_diagnostic. -- A NULL or omitted value means all target databases are included. -- -- folder_path The string specifying the path where the output text files are written. -- A NULL or omitted value is interpreted as C:\temp. -- -- Examples -- -- 1. Unload all the samples recorded since May 1 2019 and write the files to C:\temp. -- CALL unload_samples ( '2019-05-01' ); -- -- 2. Unload the last 7 day's samples to C:\temp. -- CALL unload_samples ( CURRENT TIMESTAMP - 7 ); -- -- 3. Unload all samples for target database 2 to C:\temp. -- CALL unload_samples ( @sampling_id = 2 ); -- -- 4. Unload samples for the first week of April 2019 for target database 5 to C:\data\db5. -- CALL unload_samples ( '2019-04-01', '2019-04-07', 5, 'C:\\data\\db5' ); -- -- 5. Same as Example 5, using named arguments in a different order. -- CALL unload_samples ( @input_folder_path = 'C:\\data\\db5', -- @sampling_id = 5, -- @from_timestamp = '2019-04-01', -- @to_timestamp = '2019-04-07' ); -- -- Remarks -- -- Here are the views that are unloaded: -- -- 1. alert One row per alert. -- 2. alert_cancelled One row per alert cancellation. -- 3. alerts_criteria One row for the Monitor Options page settings for each sampling session. -- 4. all_clear One row per alert all-clear. -- 5. autodropped_connection One row per connection that was dropped by the AutoDrop facility. -- 6. exception_diagnostic One row for each time Foxhound detected an error or other important event. -- 7. peaks One row for each target database, holding various peak values. -- 8. ping_log One row for each time the custom ping process was run. -- 9. purge_run One row for each run of the database purge process. -- 10. sample_connection One row for each connection for each sample holding connection-level properties. -- 11. sample_detail One row for each sample. -- 12. sample_header One row for each sample holding server and database-level properties. -- 13. sampling_options One row for each target database. -- 14. schedule One row for each week-long schedule defined on the Monitor Options page. -- 15. schedule_day_entry One row for each day in one schedule. -- 16. schedule_period_entry One row for each 15-minute period in one day in one schedule. -- 17. server_message One row for sa_server_message() row that was captured.