Question: Read-only connection to SQL Server

[khristenkovladimir]

Hi there,

I have a two-node MS SQL cluster with Always On availability group.
I configured Read-Only Routing and checked that it worked by standard sqlcmd util.

As I understand, the TPROC-H benchmark is a read-intensive workload.
So I ​want to read from the secondary replica but can read only from Primary replica.

How can I add the option ApplicationIntent=ReadOnly to hammerdb connection string?
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver15#ConnectToSecondary

Which of the 22 tests in the TPROC-H benchmark generates only read-only workload?

Thank you.

[sm-shaw]

Hi,
As a question, Issue converted to a Discussion topic.

So all the driver scripts are scripts that you can edit. I would recommend using the GUI script editor to make any changes and then saving the file. If using the CLI you can use the customscript command to load an edited file.
Firstly, navigate to the connect_string section. The best example to add a change is similar to the existing one for azure as shown below.

proc connect_string { server port odbc_driver authentication uid pwd tcp azure db } {
set read_only "true"
if { $tcp eq "true" } { set server tcp:$server,$port }
if {[ string toupper $authentication ] eq "WINDOWS" } {
set connection "DRIVER=$odbc_driver;SERVER=$server;TRUSTED_CONNECTION=YES"
} else {
if {[ string toupper $authentication ] eq "SQL" } {
set connection "DRIVER=$odbc_driver;SERVER=$server;UID=$uid;PWD=$pwd"
        } else {
puts stderr "Error: neither WINDOWS or SQL Authentication has been specified"
set connection "DRIVER=$odbc_driver;SERVER=$server"
        }
}
if { $read_only eq "true" } { append connection ";" "ApplicationIntent=ReadOnly" }
if { $azure eq "true" } { append connection ";" "DATABASE=$db" }
return $connection
}

The 22 tests are all queries so are read-only by nature, however query 15 does create and drop a view. If you look in proc ordered_set in tpchcommon-1.0.tm in the modules directory you can see the order that queries are run.
The refresh option does insert and delete data so this would not be able to run against a read-only database.

[khristenkovladimir]

Hi @sm-shaw

Thank you very much for your reply.

I checked the read-only connection and all works fine only when I use my secondary replica in the server parameter set server {my-secondary-replica-server};# Microsoft SQL Server Database Server.

When I use my listener ip address set server {my-listener};# Microsoft SQL Server Database Server I "go" to my primary node.
Where could be the problem?

[sm-shaw]

This is getting a bit out of scope for HammerDB support as it looks like something at the SQL Server configuration side. HammerDB from the perspective of the SQL Server is an ODBC application written in C so you should be able to set any of the options that ODBC supports.