lpar2rrd MS SQL monitoring: Always-On configuration

Elix

Dears,

the smallest configuration of an Always-On HA configuration depends on two servers, one active and one passive node. Client connections to the node which is the active one are allowed and usually, connections to the passive node are being rejected. There's one exception: If the client connects with read-intent, such connections will be marked as a read-only connections and can be permitted on passive nodes too. Do you see some chance to add read-intent to your connection string?

Offload workload to secondary availability group replica - SQL Server Always On | Microsoft Docs

Many thanks,
Elix

damerva

Hello Elix,

there is a toggle in perl module we use that sounds like the thing you want. "causes the connection attribute SQL_ATTR_ACCESS_MODE to be set to SQL_MODE_READ_ONLY"


Can you apply this patch and test if it works as expected? It seems to not have any side effects on the current solution so we will use it as a default if it does work.

https://download.lpar2rrd.com/patch/7.21-30-1-gd3ac/sqlserver-db2json.pl.gz

Gunzip it and copy to /home/lpar2rrd/lpar2rrd/bin (755, lpar2rrd owner)

-rw-rw-r-- 1 lpar2rrd lpar2rrd 23038 Oct 25 16:03 sqlserver-db2json.pl

If your web browser gunzips it automatically then just rename it: mv sqlserver-db2json.pl.gz sqlserver-db2json.pl

Assure that file size is the same as on above example

Elix

Hi damerva,

thank you for the swift support! File already downloaded and deployed. I'm now waiting for results and will come back to you asap.

BR,
Elix

Elix

Hi again,

unfortunately, unsuccessful. No changes with the patched file, neither the DB config nor any database attributes are being read. From the SQL Server log I can see, that there were still numerous denied connection attempts indicating they came in without read-intent. After checking the available connection parameters of the ODBC driver for SQL Server, the required parameters are:

ApplicationIntent=ReadOnly
MultiSubnetFailover=Yes

The second parameter just indicates, that the application which connects with this string is explicitly aware of Always-On HA clusters.

I've modified line 348 quick and dirty as follows in order to give it a try:

my $dbh = DBI->connect("DBI:ODBC:driver=$_driver;Server=$_ip;database=$_database;Port=$_port;ApplicationIntent=ReadOnly;MultiSubnetFailover=Yes",
                       $_user, $_password,{PrintError => 0, RaiseError => 0, ReadOnly => 1});

After five minutes, all values have been read from the DB properly and I were not able to find any interruptions in reading from other ("normal") servers.

BR,
Markus

damerva

Hello,

I see, thank you Markus. We tested in our lab too and it seems to work fine so we will include it in the future versions and run it as a default.

Here is a patch for anyone else with this issue:

https://download.lpar2rrd.com/patch/7.21-30-1-gd3ac/sqlserver-db2json.pl.gz

Gunzip it and copy to /home/lpar2rrd/lpar2rrd/bin (755, lpar2rrd owner)

-rw-rw-r-- 1 lpar2rrd lpar2rrd 23117 Oct 26 16:08 sqlserver-db2json.pl

If your web browser gunzips it automatically then just rename it: mv sqlserver-db2json.pl.gz sqlserver-db2json.pl

Assure that file size is the same as on above example