Tuesday, November 22, 2016

Foxhound's New Ping Process

Foxhound 4 is the first database monitor for SQL Anywhere that tests the target database's ability to accept new connections.

Foxhound does this with a special ping process that opens a new connection to the target database via the embedded SQL interface, issues a SELECT @@SPID command and then immediately disconnects.

This is different from the regular Foxhound sampling process which connects to the target database via ODBC and keeps that connection open while it collects multiple samples. It is also different from the SQL Anywhere dbping.exe utility; the Foxhound ping is a low-overhead process that's built in to Foxhound.

The Foxhound ping process comes in three flavors:

  1. As an addition to the Foxhound Monitor sampling process.

    The separate ping process tests the target database's ability to accept new connections as well as providing data for a third measure of response time: ping response time.


  2. As a complete alternative to Foxhound's sampling process.

    Ping-only sampling may be used to check for Alert #1 Database unavailable without storing a lot of data in the Foxhound database.


  3. As an addition to Y/N Sample Schedule settings.

    "P" for ping-only may be specified at various times of the day. For example, ping-only sampling might be scheduled during the overnight hours

    • when a large connection pool is mostly idle and/or

    • when a heavy load from a few connections is expected, and

    • folks care more about availability than about connection-level performance.

The Monitor Options page lets you specify the ping settings for each target database:


With Conflict Comes Confusion


The ping-only sampling option overrides (turns off, disables) several other Foxhound features including
  • Alerts and Alert Email Schedule, except for Alert # 1 Database unavailable,

  • AutoDrop and AutoDrop Schedule,

  • Sample Schedules, even if P-for-ping-only is specified, and

  • Connection Sample Schedules.
Conflicts like those lead to confusion, and confusion leads to Frequently Asked Questions like:

  • "Why aren't Alert emails being sent?"

  • "Why isn't the Sample Schedule working?"

  • "Why aren't blocked connections being dropped?"
The new  Banner Warnings  feature has been added to Foxhound 4 to help deal with confusion caused by conflicts among different options; here's what' you see when you specify ping-only sampling:

Banner warnings appear all over the place in Foxhound 4. That is especially true for ping-only sampling because it affects so many other options; for example, here's what the Sample Schedule section looks like on the Monitor Options page:



Tuesday, November 15, 2016

Friday, November 4, 2016

Foxhound Version 4 Is Now Available

Version 4 of the Foxhound database monitor for SAP® SQL Anywhere® is now available for purchase and download here.

You can see What's New in the FAQ; here are the Top 6 Release-Defining Features:

1. A new custom "ping" process tests separate connections to the target database.


The Foxhound ping process opens a new connection to the target database via the embedded SQL interface, issues a SELECT @@SPID command and then immediately disconnects. This is different from the Foxhound Monitor process which connects to the target database via ODBC and keeps that connection open while it collects multiple samples.

The new ping process comes in three flavors:

  1. As an addition to the Foxhound Monitor sampling process.

    The separate ping process tests the target database's ability to accept new connections as well as providing data for a third measure of response time: ping response time.


  2. As a complete alternative to Foxhound's sampling process.

    Ping-only sampling may be used to check for Alert #1 Database unavailable without storing a lot of data in the Foxhound database.


  3. As an addition to Y/N Sample Schedule settings.

    "P" for ping-only may be specified at various times of the day. For example, ping-only sampling might be scheduled during the overnight hours

    • when a large connection pool is mostly idle, or

    • when a heavy load is expected and nobody much cares about performance.

The Monitor Options page lets you specify the ping settings for each target database:



2. Foxhound now supports SQL Anywhere 17.


Foxhound now supports target databases that run on any version of SQL Anywhere 6 through 17, including 17.0.4.

Foxhound itself will now run on SQL Anywhere 17, or SQL Anywhere 16 build 2127 or later.

Here are some other new features specific to SQL Anywhere 17:

  • Incomplete Reads, Writes columns have been reintroduced for SQL Anywhere 17 target databases

  • Alert #15 - Incomplete I/Os has been reintroduced for SQL Anywhere 17 target databases

  • The Foxhound shortcuts choose SQL Anywhere 17 over 16, and 64-bit over 32-bit, by default

  • Mutex and semaphore locks are now included in the connection-level Current Req Status and Block Reason: fields



3. Context-sensitive Performance Tips have been added throughout the Help.

The Help has been redesigned and expanded to include
  • Performance Tips by the dozen, all over the place, exactly where you need them,

  • details about where the data's coming from (which SQL Anywhere properties) and

  • statements of support (which versions of SQL Anywhere provide which performance measurements).

Here's an example:


Plus, now it's easy to open and close the Help frame any time you want.

  • Each new Foxhound page opens without the Help frame showing,

  • any of the context-sensitive (?) icons opens the Help frame, and

  • the [X] closes it again.

  • When you resize the Help frame before closing it, Foxhound remembers how wide it was when you open it again.

4.  No more pink!  Black and white and grey are now used for highlighting.


Foxhound has always used colors  like this  and  this  to highlight important information.

Foxhound 4 now uses  white-on-black  and  grey  for these reasons:

  • White-on-black text is just as dramatic as any color,

  • monochrome printers do a really bad job with colors, and

  • folks with color vision deficiency might have an easier time with the black-grey-white color scheme.


Foxhound 4 also uses white-on-black to highlight important information about Foxhound settings and status values; for example:

  •  Sampling stopped  when the Monitor is not gathering any data.

  • SPs  NNN  when the Monitor can't call these three stored procedure on the target database: rroad_connection_properties, rroad_database_properties and rroad_engine_properties.

  • Favorable?  YNY  when one or more of the RememberLastPlan, RememberLastStatement and RequestTiming server options are not set on the target server.

  • Purge  Off  when the Foxhound purge isn't deleting anything.

Also, alternating row colors are used for the Monitor, Sample History and other multi-row displays:



5.  Banner warnings  now expose conflicts among different Monitor Options page settings.


Individual settings on the Monitor Options page might be simple to understand and easy to use, but . . . conflicts among different settings aren't so simple or easy.

. . . and these conflicts lead to the most Frequently Asked Questions:

  • Why aren't Alert emails being sent?

  • Why isn't the Sample Schedule working?

  • Why aren't blocked connections being dropped?

Foxhound now warns about these conflicts on the Monitor Options page:



6. let you switch among multiple target databases.


If you're dealing with dozens of databases, you might like this new feature best of all!

It works on the Monitor and Sample History pages, and on the Monitor Options page as well.




   



...see more new features here.



Friday, August 26, 2016

Downgrading A Database From SQL Anywhere 17 to 16

Question: How do I switch to SQL Anywhere 16 after upgrading my database to version 17?

Answer: The bad news is, there's no dbunload -downgrade option.

The good news is, it might not be very difficult, at least according to a preliminary test using the SQL Anywhere 17 demo database:

  • Step 1. Start the current database with V17 dbsrv17.exe

  • Step 2. Unload the current database with V17 dbunload.exe

  • Step 3. Copy the V17 reload file to the V16 folder

  • Step 4. Manually edit the V16 reload file

  • Step 5. Create the new database with V16 dbinit.exe

  • Step 6. Start the new database with V16 dbsrv16.exe

  • Step 7. Open an ISQL session with V16 dbisql.com

  • Step 8. Run the edited V16 reload file in ISQL

  • Repeat as required, possibly starting at Step 4

"Can I try this at home?"

This demo does not address the following questions...
  1. "How do I undo the changes I had to make when upgrading to SQL Anywhere 17?"

  2. "Will the new objects (e.g., roles) cause problems in SQL Anywhere 16?"

  3. "Will the column statistics work properly?"

  4. "What about the MobiLink stuff?"

    "Have I forgotten anything? Oh, yeah..."

  5. "Will this work for my giant production database?"
Here's the batch file used for testing, followed by some comments and another batch file to clean up before starting over:
ECHO OFF
REM  ******************************** 
ECHO Step 0. Set up folders and files
C:
CD C:\
MD TEMP
CD C:\TEMP
MD data
CD C:\TEMP\data
MD V16
MD V17
COPY /B /V /Y "%SQLANYSAMP17%\demo.db" "C:\TEMP\data\V17\demo.db"
COPY /B /V /Y "%SQLANYSAMP17%\demo.log" "C:\TEMP\data\V17\demo.log"
PAUSE

REM  ******************************************************* 
ECHO Step 1. Start the current database with V17 dbsrv17.exe
"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -n demo17^
  -o "C:\TEMP\data\V17\dbsrv17_log_demo17.txt"^
  "C:\TEMP\data\V17\demo.db"
PAUSE

REM  ********************************************************* 
ECHO Step 2. Unload the current database with V17 dbunload.exe
"%SQLANY17%\Bin64\dbunload.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -r "C:\TEMP\data\V17\reload17.sql"^
  -up^
  "C:\TEMP\data\V17\data17"
PAUSE

REM  ************************************************** 
ECHO Step 3. Copy the V17 reload file to the V16 folder
COPY /V /Y "C:\TEMP\data\V17\reload17.sql" "C:\TEMP\data\V16\reload16.sql"
PAUSE

REM  ***************************************** 
ECHO Step 4. Manually edit the V16 reload file
ECHO [edit reload16.sql]
PAUSE

REM  *************************************************** 
ECHO Step 5. Create the new database with V16 dbinit.exe
"%SQLANY16%\bin64\dbinit.exe"^
  -p 4096^
  "C:\TEMP\data\V16\demo.db"
PAUSE

REM  *************************************************** 
ECHO Step 6. Start the new database with V16 dbsrv16.exe
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -n demo16^
  -o "C:\TEMP\data\V16\dbsrV16_log_demo16.txt"^
  "C:\TEMP\data\V16\demo.db" 
PAUSE

REM  ************************************************ 
ECHO Step 7. Open an ISQL session with V16 dbisql.com
"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"
PAUSE

REM  **********************************************
ECHO Step 8. Run the edited V16 reload file in ISQL
ECHO [copy, paste and run reload16.sql]
PAUSE

REM  *************************** 
ECHO Repeat as required
ECHO [you might be able to start at Step 4]
ECHO All done...
PAUSE
The dbunload -up option in Step 2 is new with SQL Anywhere 17:
New -up option for the Unload utility (dbunload) and 
   the Extract utility (dbxtract) 

This option allows the unloading of passwords. There are  
   behavior changes associated with its use. See UNLOAD  
   utility, and Extract Utility (dbxtract).

-up 

Unloads user passwords (hashed value). You do not need to 
   specify this option if you are performing an unload with  
   reload (-ac, -an, or -ar option).
If you don't specify dbunload -up in Step 2 the following text in the reload SQL file will stop Step 8 from working:
****************** WARNING *******************************
*                                                        *
* This file contains user definitions with removed       *
* password values.                                       *
* It should not be used to create a new database.        *
*                                                        *
****************** WARNING *******************************
If you want to avoid this error in Step 8:
Could not execute statement.

User ID 'SYS_RUN_PROFILER_ROLE' does not exist
SQLCODE=-140, ODBC 3 State="28000"
Line 157, column 1
You can continue executing or stop.

GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION
...you will have to remove these lines in Step 4:
GRANT DELETE ANY TABLE TO "SYS_RUN_PROFILER_ROLE" WITH NO ADMIN OPTION
go
...or you can just choose "Continue" after each error in Step 8 :)

The -p 4096 in Step 5 is the default... it's just a reminder that when you run the "Old School" method using separate dbunload and dbinit steps, you are on your own when it comes to dbinit options.

Here's the batch file to clean up after running the demo:
ECHO OFF
ECHO Are you sure you want to delete all the files?
PAUSE 

"%SQLANY16%\bin64\dbstop.exe"^
  -c "ENG=demo16; DBN=demo; UID=dba; PWD=sql;"^
  -y

"%SQLANY17%\bin64\dbstop.exe"^
  -c "ENG=demo17; DBN=demo; UID=dba; PWD=sql;"^
  -y

ECHO Wait until the databases are stopped, then
PAUSE 

RD /S /Q C:\TEMP\data\V16
RD /S /Q C:\TEMP\data\V17

ECHO All done...
PAUSE