SQL Server 2019 - ASP Classic - ASPDOTNET
I followed the information from this page. ReadOnly Routing from
sqlservercentral, and have everything set up and all tests passed in SQL Server for the AOAG.
With the ASP Classic ConnectionString test.
Once I get the asp classic ConnectionString working, I will implement the aspDOTnet version. With the below code, if I run the SQLListener,2433, the ConnectionString works.
Makes testing easier, creating the Variable and going between them instead of changing it in the ConnectionString.
'getSerInstance = "SQLListener,2433" ' Works
'getSerInstance = "sql1.domain.local,1433" ' Does not Work
'getSerInstance = "SQLListener,1433" ' Does not Work
Set siteconn = CreateObject("ADODB.Connection")
siteconn.ConnectionString = "Provider=MSOLEDBSQL;Server=tcp:"&getSerInstance&";
Database=dbname;ApplicationIntent=ReadOnly;IntegratedSecurity=SSPI;
MultiSubnetFailover=True;User ID=USERNAME;Password=PASSWORD;Max Pool Size=1024;"
siteconn.Open
Running SQLListener,2433 works, but once I run either of the other two, I receive the error.
'80004005' Invalid connection string attribute.
Added: User ID=USERNAME; Password=PASSWORD; was the only way to make "SQLListener,2433" work. So it is left in, in hopes that it would also help to make one of the others work as well.
In each of the SQL Instances, the following Firewall rules are implemented.
New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Listener instance" -Direction Inbound -LocalPort 2433 -Protocol TCP -Action Allow
Thank you
Wayne
1 Like
Why do you have 2 different IP addresses? If SQL Server was installed as a default instance - and you did not modify the port settings, then the listener would also be using the same port.
Either way, the only way ApplicationIntent works is if you are connecting through the listener. It makes no sense to pass that to parameter in the connection string for a direct connection. It also does not make sense to be passing the username/password in a connection string using integrated security (windows authentication).
Further - if using a default instance then including the port isn't required. If using a named instance - and having SQL Server Browser disabled, then including the port is required.
Finally, what is the requirement for using read intent?
Not sure I follow what you mean by 2 different IP Addresses.
This is the default listener SQLListener,2433
Everywhere I have read states to use port 1433 in ReadOnly.
So I tested both SQLListener,2433 and SQLListener,1433
2433 works, and 1433, gives the error.
I've used the port since I set the listener up two years ago.
Why am needing to use Read Intent?
I want to spread the load between all available instances so the Primary Instance does not get the bulk of the hits. This is all in preparation for a site we are looking to release by the beginning to middle of May.
What port is SQL Server listening on? You must have changed that port to 2433 - which is why port 1433 isn't working.
I don't know where you have read that read-only should use port 1433. Regardless of how you get to the instance, you need to use the port that instance is listening for - and if you have different ports assigned to each instance then failover won't work.
Assuming you modified the port assignment to 2433 for both instances - then just use that port number to access each instance as needed. Port 1433 won't work because the instance isn't listening on that port number.
To be able to use read-intent, then the connection string must reference the listener - must specify a database in the AG and must only issue SELECT statements. If you try to select data from one connection - then use another connection to update that data, then almost certainly you are going to run into concurrency issues that will be far harder to troubleshoot.
Read-Intent is meant to offload read only connections - not load balance read operations. A good example is offloading reports from the primary instance. That way a query that is pulling a month's worth of data isn't impacting the primary node.
1 Like
Thanks for this information.