Wednesday, November 15, 2017

Top 10 Cool New Features In Foxhound 4

Version 4 of the Foxhound Database Monitor has been around for a while, long enough for five patches and a new build... long enough for a Top 10 listicle about the best new features:

1. Foxhound Is The Book

Once upon a time (2004) there was a book about SQL Anywhere 9.

Then the book got old and the questions started:
Q: When are you going to write a book about SQL Anywhere Version 11? I really like the Version 9 book.
Eventually (2009) the questions were answered:
A: This blog is that book.
Now, the answer has changed: Foxhound is that book... Foxhound is where the effort's going, especially in the Help.

For example,
  • dozens of new "Performance Tips" have been added,

  • links between related Monitor and Monitor Options topics have been added; for example, between the Cache statistic and Alert #19 Cache size, and

  • the source of each performance statistic is documented; e.g., "The Cache and % of Max fields are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level CurrentCacheSize and MaxCacheSize properties."

2. It's Easy To Open And Close The Book.

In Foxhound 3 the Help was always on display.

The bad news was, the Help was always on display, always taking up room on the screen.

Sure, you could turn off the Help, but that was hard, and once you turned it off it stayed invisible until you turned it on again.

In Foxhound 4 it's easy to close the Help by clicking X, and easy to open again by clicking any of the context-sensitive ? icons... no more checking/unchecking Show Help over on the Foxhound Options page and then having to click refresh.
The Help content is a big part of Foxhound (see Foxhound Is The Book above), now the Help interface is a bit easier to use.

3. Banner Warnings About Unintended Consequences

Every target database has its own full set of Monitor Options in Foxhound, making "options management" the hardest part of using Foxhound.

Couple that with the unintended consequences of "getting it wrong" when you set up conflicts between different options, and "hardest" becomes "really hard". For example, there are no less than five different ways you can accidentally disable the Alert Email Schedule feature.

To combat this problem Foxhound 4 now displays white-on-black banner warnings to immediately flag potential conflicts:
 This Alert Email Schedule has no effect when the Enable Emails checkbox is unchecked (see 1. Global Overrides). 

 This Alert Email Schedule has no effect when the Enable Schedules checkbox is unchecked (see 1. Global Overrides). 

 This Alert Email Schedule has no effect when the Send Alert Emails checkbox is unchecked (see 7. Email Setup).  

 This Alert Email Schedule has no effect when the Alert Email Address(es) field is empty (see 7. Email Setup). 

 Only Alert #1 emails are sent when Ping-Only Sampling is performed (see 16. Ping Settings). 

4. Drop-Down List Boxes Everywhere

It used to be a real chore to switch from one target database to another on the Monitor Options page. Foxhound 4 fixed that with drop-down list boxes spread all over the Monitor Options page that let you switch from target to target.

...but that's not the only place these drop-down list boxes appear.
They let you quickly change from one target to another on the Monitor page itself, and on the Sample History page:
That's what makes this a Cool New Feature: Those list boxes were originally designed just for the Monitor Options page, but it made perfect sense to add them to other pages as well.

5. Disable All Alerts

Sometimes it makes sense to enable only one single Alert when looking for a problem. The new Disable All Alerts buttons on the Monitor Option page make that easy: Turn off all 34 alerts, all at once, and then turn one back on.

Two other buttons were added as well: Enable All Alerts to turn them all back on, and Undo All Alerts to recover from an "Oops!"

6. Support Continues For Adhoc Queries

Unlike the builtin SQL Anywhere Monitor, Foxhound lets you run ISQL queries against all the historical data pertaining to your databases: "It's your data, you own it" is one of the Hallmarks of Foxhound.

Foxhound 4 introduced a whole new Adhoc Queries chapter in the Help, and has added the ID column to the Monitor tab on the Foxhound Menu page so you can see which sampling_id value to use in your queries.

7. Faster Patches

Patches are easier to build for Foxhound 4, and that has made it possible to publish five patches since February.
Patches are popular because (a) you can pick and choose which patch(es) to apply depending of your needs, and (b) patches are fast because you don't have to reinstall Foxhound and upgrade the database.

8. Disk-Versus-Cache Usage

Disk/Cache statistics are displayed for both table and index data, with anything over 1% for extended periods of time indicating a possible performance bottleneck.
These percentages are new to Foxhound 4, and are calculated from the DiskReadTable, CacheReadTable and four other SQL Anywhere statistics that are [cough] less-than-humanly readable.

9. Monitor The Ability To Accept New Connections

Sometimes a SQL Anywhere database loses the ability to accept new connections. Foxhound 3 didn't detect that problem because once it was connected to the target database, it stayed connected.

Foxhound 4 introduced a separate "ping" process that tests the target database's ability to accept new connections.

Also, the time required to make each new ping connection has been added as a third measurement of response time, along with Heartbeat and Sample times.

10. Monitor The Arbiter Server

Foxhound's ability to monitor the Arbiter server in a SQL Anywhere High Availability setup was an unexpected new feature of version 4. It was made possible by the new Ping-Only Sampling feature, and is documented in this blog post.

Sunday, October 29, 2017

Monitor The Arbiter Server With Foxhound

Question: How do I monitor a SQL Anywhere High Availability Arbiter server with Foxhound 4?

Answer: Gosh, how hard can that be?

After all, dbping has no problem connecting to an arbiter server:

"%SQLANY17%\bin64\dbping.exe" -c "SERVER=ARBITER_PROD; HOST=localhost:55501;"

SQL Anywhere Server Ping Utility Version
Ping server successful.
Foxhound 4 uses ODBC to connect, so all we have to do is add a DRIVER= to the connection string, right?
SERVER=ARBITER_PROD; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:10:36 PM  2m 21s  -- Invalid user ID or password -- 
2:09:11 PM          Alert #1: Database unresponsive.  Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:08:15 PM    5.2s  -- Invalid user ID or password at -- 
OK, so Foxhound can't just ping a server, it needs a database, so let's specify DBN=utility_db in the connection string.
Tip: You have to specify the -su option when starting the arbiter server if you want to connection via the utility database: e.g., dbsrv16 -su sql or dbsrv17 -su sqlsql. Why sqlsql? Because passwords have to be 6 characters long in SQL Anywhere 17.
But! ... it still doesn't work!
SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:19:13 PM  1m .2s  -- Permission denied: you do not have permission to execute a statement of this type -- 
2:19:09 PM          Alert #1: Database unresponsive.  Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:18:13 PM    5.1s  -- Permission denied: you do not have permission to execute a statement of this type at -- 
One last change is needed: Use the Monitor Options page to tell Foxhound to use the new Ping-Only Sampling feature:
Finally! Foxhound connects to the arbiter server and displays "Ping OK":
SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:32:53 PM  20.2s  -- Ping-only sampling -- Ping OK 
2:32:33 PM            All Clear  - Alert #1: Database unresponsive. Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:32:33 PM    10s  -- Ping-only sampling at -- 
When you directly monitor the arbiter server with a separate Foxhound sampling session, you can get Alert #1 messages sent to you when the arbiter goes down:
The other High Availability sampling sessions (primary, secondary, partner) also tell you about troubles with the arbiter, but that's not the same as Alert #1:

Overview - How To Monitor The Arbiter Server With Foxhound

  • Start the arbiter server with the -su option to enable connections via the utility database; e.g., dbsrv16 -su sql or dbsrv17 -su sqlsql

  • Specify the utility database in the Foxhound connection string; e.g., SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native;

  • Check Perform Ping-Only Sampling on the Foxhound Monitor Options page for the arbiter server.
Tip: If you are using the original Foxhound 4 build 4729, consider upgrading to build 4740, or at least applying Patch 8 to build 4728, in order to fix problems affecting Ping-Only Sampling.

Monday, October 9, 2017

How To Shrink Foxhound

Question: How do I shrink and reorganize the Foxhound 4 database?

Answer: Foxhound uses SQL Anywhere for its database, and the only way to shrink the size of a SQL Anywhere database file is to unload, recreate and reload the file from scratch. The good news is, that whole process is automatic when you reinstall Foxhound on an existing installation:

  • First, Foxhound creates a new, empty copy of the database file.

  • Second, Foxhound finds the old database file,

  • then it asks you how much of the old data you want to copy to the new database: some, all, none, just the options.

  • Finally, Foxhound copies and loads the data, using just enough space to hold it all.
In this context, "shrink" means "don't copy the empty space, and don't allocate space for data that isn't copied", and "reorganize" means the copy process implicitly organizes the data as it is loaded.

Here's the step-by-step process:

Step 1: Start Foxhound 4

...if it isn't running already, that is.

In this example, the Foxhound 4 database has grown quite large:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/02/2017  07:31 PM    37,521,506,304 foxhound4.db

Step 2: Decide How Much Data To Copy

Let's say you want to save half the data, thus shrinking the file by 50%.

Foxhound doesn't understand "half", but it does understand "only copy samples recorded after yyyymmdd".

To convert "half" into "yyyymmdd", start ISQL and run this query:
All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL

SELECT CAST ( SYSTAB.count * 0.50 AS INTEGER ) AS half
 WHERE SYSTAB.table_name = 'rroad_sample_set';


-- The space used by the foxhound4.db file is more-or-less determined by the number 
-- of rows in the rroad_sample_set table, which contains one row for each sample
-- sample recorded by the Foxhound Monitor.

-- In other words, the "half" calculated here is "half the samples", not "half the bytes".
Now use "half" as the START AT value in this query:
       DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd
  FROM sample_header
 ORDER BY sample_header.sample_set_number;


-- The sample_finished_at column is used to turn the row number 867871 into a yyyymmdd date.
-- The sample_header view is used because Foxhound only allows SELECT statements on the 
-- adhoc query views (sample_header), not the underlying tables (rroad_sample_set).

Step 3: Run Foxhound's "Unsetup"

You don't have to stop Foxhound to reinstall the software, but you do have to run the special "unsetup" process to prepare for the reinstallation:
All Programs - Foxhound4 - Tools - Unsetup Foxhound

-- The "unsetup" process gets rid of the Windows shortcuts and other items, 
-- but it leaves the Foxhound database alone... and even leaves it running.

Step 4: Run The Foxhound 4 Setup Up To The PLEASE READ THIS

In this example, the new Foxhound 4 build 4740 is used... run it all the way to the PLEASE READ THIS window:

Step 5: Enter The FOXHOUND4UPGRADE=yyyymmdd Value

You can copy and paste the FOXHOUND4UPGRADE=yyyymmdd value, and press Enter twice to continue:

Step 6: Wait... and Wait... and Wait Some More

It takes quite a while for 35G of data to be copied and loaded, even half of 35G.

Eventually, the upgrade process will finish, and shut down, and Foxhound 4 will be restarted with the newer, smaller, reorganized database:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/06/2017  07:25 PM    21,720,817,664 foxhound4.db
That's 42% smaller that before... not exactly half, but it meets the "more-or-less" standard :)

Thursday, October 5, 2017

New Foxhound 4 Build 4740

A new build of Foxhound 4 is available here.

  • You can use it to upgrade an existing copy of Foxhound 4 for free.

  • You can also use it to install a new copy of Foxhound 4, or

  • to upgrade an existing copy of Foxhound 1, 2 or 3.
If you are already using Foxhound 4, here's why you should consider upgrading to build 4740:
  • Build 4740 "rolls up" all five patches that were previously released for the original Foxhound 4 build 4729.

  • Several performance problems have been fixed, making it less likely that Foxhound will become unresponsive when the database grows very large.

  • If you want to use the "Reinstall Foxhound" method to reorganize and shrink the Foxhound database, the new build 4740 will let you do that... unlike the previous Patch 10 which made it impossible to preserve any data if you subsequently reinstalled the original build 4729.

  • Foxhound's own purge process has been improved, making it more likely the purge will keep database growth under control... and this fix was not included in the previous patches.
On the other hand...
  • If you don't need any of changes in build 4740, there's no need to upgrade.

  • If you only need one or two of the fixes, applying a patch to to build 4729 is a lot quicker than upgrading to build 4740.
Either way, here's a tip...
Tip: Set the Purge Speed to 10 Fastest on section 6. Purge of the Foxhound Options page.

Do this whether or not you upgrade to build 4740, if your Foxhound database is growing rapidly.