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 17.0.7.3399
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
  FROM SYSTAB
 WHERE SYSTAB.table_name = 'rroad_sample_set';

       half 
----------- 
     867871 

-- 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:
SELECT TOP 1 START AT 867871
       DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd
  FROM sample_header
 ORDER BY sample_header.sample_set_number;

yyyymmdd 
-------- 
20170808 

-- 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:
Foxhound-Version-4-0-4740-setup.exe


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.