July 26, 2019 at 3:12 pm
Is it possible to use a listener with a read scale availability group?
I have read on some sites you cannot use one but in others that you can.
If so what IP addresse do you use for it? I have tried to assign a new one in the subnet and also using the IP of the VM with the primary replica. I can create the listener but cannot connect to it.
I am interested in using the listener to only connect to the primary replica, I am not planning to use read only routing. I idea to use a read scale availability AG is to provide a limited degree of DR. I understand the listener would have to be redeployed if th AG was failed over.
July 26, 2019 at 6:48 pm
Not sure I understand what a 'read scale' availability group actually is...can you explain further?
A listener is setup and configured to be used so that your applications can connect to the 'primary' system regardless of which node is hosting the databases. If you fail over to the secondary (either automatic or manual) - the application would not require any changes to allow it to connect to the system since it connects through the listener.
The only time a user/connection would be redirected from the active node through the listener is when the connection utilizes 'read-intent' and the secondary is setup with 'read-intent'. Then - reads would be redirected to the secondary and writes would be redirected to the primary.
Final Note: I would *never* configure a secondary for read-only when that secondary is identified as DR. First - if you are actively using that node it must be licensed, whereas a dedicated DR node wouldn't need to be licensed (depends on some other factors - which you should review) and second, if you fail over and the DR node is now primary it will also be utilized in its read-only capacity which could impact performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 26, 2019 at 7:09 pm
I understand a read scale AG to be a clusterless AG primarilly to provide ability to offload reads to a secondary replica. However I wanted to use it to provide a degree of DR to the databases, these databses are lower priority databases.
I thought that a listener could still be used for connection to the AG, even if it needed to be redployed after a failover to the secondary. However Im starting to think that the listener is needed for read only routing (which I wont be implementing) but cannot be used for just connecting as in a full cluster AG.
July 28, 2019 at 4:44 pm
I was not aware of read-scale - interesting concept. What I found from a quick scan:
Connect to read-only secondary replicas
You can connect to read-only secondary replicas in either of two ways:
Applications can connect directly to the SQL Server instance that hosts the secondary replica and query the databases. For more information, see Readable secondary replicas.
Applications can also use read-only routing, which requires a listener. For more information, see Read-only routing.
This seems to indicate you can use a listener - but as read-only routing. However - this does not say anything about using the listener in a normal fashion...
Based on my further reading - as long as your connection string is not using the flag 'ApplicationIntent' or that flag is set to 'read/write' then the listener will route the connection to the primary.
Based on this - I think you can setup a listener and utilize that listener for read-write access only. If you failover to the secondary - the listener should redirect connections appropriately.
I would test that to be sure - but it doesn't appear to be a problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2019 at 2:35 pm
So I created a test setup - two VMs and a read scale Availability group between them, got an entry created in DNS for the Instance IP address and the Listeners name and it seems to work as in the normal fashion.
Of course if this was to failover to the other node the listener would have to be redeployed.
Good to know this though, It wasn't at all clear in the Microsoft Guidance.
July 29, 2019 at 7:07 pm
I am assuming that you would need to redeploy the listener due to IP address changes? If not - then I am not clear why it would have to be redeployed...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2019 at 8:36 pm
Yes its due to the change of IP address to the other box that SQL is failed over to. The IP address used in the Listener is the IP of the box the SQL instance is on.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply