February 26, 2018 at 8:05 am
Hi,
I've been working on setting up AOAG for the first time. I see a lot of benefits to having this in place. However, I'm a little bit fuzzy on a couple of things and I'm hoping someone can help clarify my understanding.
I have the simplest of setups:
-1 Availability Group.
-2 replicas (primary, secondary read only).
-1 availability database.
-1 AOAG listener (AG_LISTENER).
I noticed in SSMS that I can successfully connect to either the Windows Cluster that I created for this purpose (AG_CLUSTER) or the AOAG listener (AG_LISTENER). Which is the correct one to use for applications so that a successful failover can occur if one of the replicas is inaccessible? It seems like the listener is what I want to be using, but the fact that the cluster name also connects makes me question myself.
Separate from that, I went into this with the understanding that the secondary server in the AG would be read only and that I could simply use that instance directly for reporting, etc. However, it seems that when I want to access this secondary server for read-only purposes I should also route through the AOAG listener--just with a slightly different connection string (e.g., "Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly") ? I think whether I access it directly or through the listener (if that's the right way), the database is still read only. So what's "correct"?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 26, 2018 at 8:58 am
Mike Scalise - Monday, February 26, 2018 8:05 AMHi,I've been working on setting up AOAG for the first time. I see a lot of benefits to having this in place. However, I'm a little bit fuzzy on a couple of things and I'm hoping someone can help clarify my understanding.
I have the simplest of setups:
-1 Availability Group.
-2 replicas (primary, secondary read only).
-1 availability database.
-1 AOAG listener (AG_LISTENER).I noticed in SSMS that I can successfully connect to either the Windows Cluster that I created for this purpose (AG_CLUSTER) or the AOAG listener (AG_LISTENER). Which is the correct one to use for applications so that a successful failover can occur if one of the replicas is inaccessible? It seems like the listener is what I want to be using, but the fact that the cluster name also connects makes me question myself.
Separate from that, I went into this with the understanding that the secondary server in the AG would be read only and that I could simply use that instance directly for reporting, etc. However, it seems that when I want to access this secondary server for read-only purposes I should also route through the AOAG listener--just with a slightly different connection string (e.g., "Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly") ? I think whether I access it directly or through the listener (if that's the right way), the database is still read only. So what's "correct"?
Thanks in advance,
Mike
I think you should use AG listener. The biggest benefit is, when failover, you don't have to change anything on application side. It means less down/offline time.
GASQL.com - Focus on Database and Cloud
February 26, 2018 at 9:35 am
In an availability group, one has nothing to do with the other.
The cluster resource may be active on either node. So, at this point. the cluster and listener resources are active on the same node. They can be active on different nodes. If you attempt to connect to the cluster if it's active on the secondary(read-only) node, then that may me an issue.
You users and applications should be connecting using the listener. That will ensure that they will be hitting the correct server/databases.
If the secondary is read-only, you will need to configure read-only routing. Configuring that, and adding "applicationIntent=Read_Only" to the connection strings, will automatically execute any queries using this string on the secondary.
This article is a simple, easy to follow set of instructions: https://blog.rdx.com/how-to-configure-read-only-routing-for-an-availability-group/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 26, 2018 at 11:49 am
So let's say I have a listener called AG_LISTENER and then two servers that are part of the AG (primary "A" and secondary read-only "B").
If I configure my reporting software to connect through the AG_LISTENER (using the "applicationIntent=Read_Only") and it gets routed to "B". What happens if "B" is inaccessible? It becomes the primary and is therefore read/write as far as the database goes, but to the reporting tool that's connecting to it using the read-only connection string, is it still read only? Is the primary serving both purposes now--transactions and reporting? That's why I wasn't sure if I should be connecting the reporting tool directly to "B".
Also, regarding your comment
The cluster resource may be active on either node. So, at this point. the cluster and listener resources are active on the same node. They can be active on different nodes. If you attempt to connect to the cluster if it's active on the secondary(read-only) node, then that may me an issue.
Should the cluster resource not be active anywhere? Should it only be the listener that's active?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 26, 2018 at 12:15 pm
Mike Scalise - Monday, February 26, 2018 11:49 AMSo let's say I have a listener called AG_LISTENER and then two servers that are part of the AG (primary "A" and secondary read-only "B").If I configure my reporting software to connect through the AG_LISTENER (using the "applicationIntent=Read_Only") and it gets routed to "B". What happens if "B" is inaccessible? It becomes the primary and is therefore read/write as far as the database goes, but to the reporting tool that's connecting to it using the read-only connection string, is it still read only? Is the primary serving both purposes now--transactions and reporting? That's why I wasn't sure if I should be connecting the reporting tool directly to "B".
Also, regarding your comment
The cluster resource may be active on either node. So, at this point. the cluster and listener resources are active on the same node. They can be active on different nodes. If you attempt to connect to the cluster if it's active on the secondary(read-only) node, then that may me an issue.
Should the cluster resource not be active anywhere? Should it only be the listener that's active?
Thanks,
Mike
You cannot shut the cluster resources down. That's part of the availability group.
Do not set the connections to an individual read-only server. Assuming that these are set to automatically fail over (whats the point if it isn't?). If a read only secondary is available, it will connect. If it's not, it will use the primary for both operations.
Take a few minutes to read some of the many articles available on the subject.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 28, 2018 at 6:09 am
Mike Scalise - Monday, February 26, 2018 8:05 AMHi,I've been working on setting up AOAG for the first time. I see a lot of benefits to having this in place. However, I'm a little bit fuzzy on a couple of things and I'm hoping someone can help clarify my understanding.
Please read my stairway to AlwaysOn, starting at the following link
http://www.sqlservercentral.com/stairway/112556/
Mike Scalise - Monday, February 26, 2018 8:05 AM
I have the simplest of setups:-1 Availability Group.
-2 replicas (primary, secondary read only).
-1 availability database.
-1 AOAG listener (AG_LISTENER).I noticed in SSMS that I can successfully connect to either the Windows Cluster that I created for this purpose (AG_CLUSTER) or the AOAG listener (AG_LISTENER). Which is the correct one to use for applications so that a successful failover can occur if one of the replicas is inaccessible? It seems like the listener is what I want to be using, but the fact that the cluster name also connects makes me question myself.
Do not use the Windows Server Failover Cluster Client Access Point to connect to the instance it is the CAP for the WSFC administration only.
Use the listener if you created one, if not then the instance name, i'm assuming the replicas are standalone instances and not clustered instances of sql server?
Mike Scalise - Monday, February 26, 2018 8:05 AM
Separate from that, I went into this with the understanding that the secondary server in the AG would be read only and that I could simply use that instance directly for reporting, etc. However, it seems that when I want to access this secondary server for read-only purposes I should also route through the AOAG listener--just with a slightly different connection string (e.g., "Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly") ? I think whether I access it directly or through the listener (if that's the right way), the database is still read only. So what's "correct"?Thanks in advance,
Mike
By default the secondary will be unavailable, you can set the readable secondary option but be aware of what this does and the ramifications of licencing and performance to name just a couple
You can connect straight to the secondary if you wish but the listener is designed to handle this, you may also need to configure your read only routing too.
Read the stairway first then come back if you have questions
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 28, 2018 at 9:03 am
Please read my stairway to AlwaysOn, starting at the following link
Perry, I did actually read an article you had written that made me realize that I was missing the routing rules. Once I established those, I was able to use the ApplicationIntent parameter and it routed me correctly, so thank you. Regardless, I'm going to keep this link handy for the next time I set up an AOAG.
Do not use the Windows Server Failover Cluster Client Access Point to connect to the instance it is the CAP for the WSFC administration only.
Use the listener if you created one, if not then the instance name, i'm assuming the replicas are standalone instances and not clustered instances of sql server?
Good to know. Thank you. I'll use the listener exclusively. Correct, the replicas are standalone and not clustered instances of SQL Server.
By default the secondary will be unavailable, you can set the readable secondary option but be aware of what this does and the ramifications of licencing and performance to name just a couple
You can connect straight to the secondary if you wish but the listener is designed to handle this, you may also need to configure your read only routing too.
Read the stairway first then come back if you have questions
I have a few questions here:
1) You said by default the secondary will be unavailable. Are you saying that its only purpose by default is to be a standby for failover and not also a read-only database? One of the biggest wins for us in making the secondary read-only is to offload real-time reporting from the production server to this secondary. Regardless, I think the licensing situation is the same--the secondary needs to be licensed, unless I'm missing something.
2) I am interested to hear about the performance ramifications. Is that discussed in your series? I wonder if that's the lesser of two evils compared to production reporting and our production application both contending for resources on the same database...
Again, thanks!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 28, 2018 at 9:19 am
Mike Scalise - Wednesday, February 28, 2018 9:03 AMPlease read my stairway to AlwaysOn, starting at the following link
Perry, I did actually read an article you had written that made me realize that I was missing the routing rules. Once I established those, I was able to use the ApplicationIntent parameter and it routed me correctly, so thank you. Regardless, I'm going to keep this link handy for the next time I set up an AOAG.
Do not use the Windows Server Failover Cluster Client Access Point to connect to the instance it is the CAP for the WSFC administration only.
Use the listener if you created one, if not then the instance name, i'm assuming the replicas are standalone instances and not clustered instances of sql server?Good to know. Thank you. I'll use the listener exclusively. Correct, the replicas are standalone and not clustered instances of SQL Server.
By default the secondary will be unavailable, you can set the readable secondary option but be aware of what this does and the ramifications of licencing and performance to name just a coupleYou can connect straight to the secondary if you wish but the listener is designed to handle this, you may also need to configure your read only routing too.
Read the stairway first then come back if you have questions
I have a few questions here:
1) You said by default the secondary will be unavailable. Are you saying that its only purpose by default is to be a standby for failover and not also a read-only database? One of the biggest wins for us in making the secondary read-only is to offload real-time reporting from the production server to this secondary. Regardless, I think the licensing situation is the same--the secondary needs to be licensed, unless I'm missing something.
2) I am interested to hear about the performance ramifications. Is that discussed in your series? I wonder if that's the lesser of two evils compared to production reporting and our production application both contending for resources on the same database...
Again, thanks!
Mike
By default, the secondary is not available for queries. They would only be accessible in the event of a fail-over, so in that setup they do not need to be licensed.
If you set any of the secondaries to read only, then you will need to license the secondary in addition to the primary.
The most common thing that I have found that affects performance in an AG is if a secondary is set to synchronous commit, and latency between the primary and secondary causes slowdowns until it completes.
And read Perry's article! Memorize it!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 28, 2018 at 9:49 am
Mike Scalise - Wednesday, February 28, 2018 9:03 AMI have a few questions here:1) You said by default the secondary will be unavailable. Are you saying that its only purpose by default is to be a standby for failover and not also a read-only database? One of the biggest wins for us in making the secondary read-only is to offload real-time reporting from the production server to this secondary. Regardless, I think the licensing situation is the same--the secondary needs to be licensed, unless I'm missing something.
The default when you create the group is for secondary to be standby mode, you have to specify you would like the secondary readable or read intent.
Depending on your license a standby may not necessarily need to be licensed
Mike Scalise - Wednesday, February 28, 2018 9:03 AM
2) I am interested to hear about the performance ramifications. Is that discussed in your series? I wonder if that's the lesser of two evils compared to production reporting and our production application both contending for resources on the same database...Again, thanks!
Mike
there are many things to consider, firstly your mirror traffic. If you have a busy system and you're pushing your client access traffic, your mirror traffic and the cluster traffic over the same network you'll encounter issues. you may want to offload the mirror traffic to a segregated network.
The redo thread on the secondarys is single threaded and can be slow in a busy system
The windows cluster and the AG both have various settings that come with default values, based on how your system is built up you may almost certainly have to change these to something realistic, classic case is session timeout on the AG, the default of 10 secs may be way too low
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 28, 2018 at 11:23 am
Perry Whittle - Wednesday, February 28, 2018 9:49 AMMike Scalise - Wednesday, February 28, 2018 9:03 AMI have a few questions here:1) You said by default the secondary will be unavailable. Are you saying that its only purpose by default is to be a standby for failover and not also a read-only database? One of the biggest wins for us in making the secondary read-only is to offload real-time reporting from the production server to this secondary. Regardless, I think the licensing situation is the same--the secondary needs to be licensed, unless I'm missing something.
The default when you create the group is for secondary to be standby mode, you have to specify you would like the secondary readable or read intent.
Depending on your license a standby may not necessarily need to be licensedMike Scalise - Wednesday, February 28, 2018 9:03 AM
2) I am interested to hear about the performance ramifications. Is that discussed in your series? I wonder if that's the lesser of two evils compared to production reporting and our production application both contending for resources on the same database...Again, thanks!
Mikethere are many things to consider, firstly your mirror traffic. If you have a busy system and you're pushing your client access traffic, your mirror traffic and the cluster traffic over the same network you'll encounter issues. you may want to offload the mirror traffic to a segregated network.
The redo thread on the secondarys is single threaded and can be slow in a busy system
The windows cluster and the AG both have various settings that come with default values, based on how your system is built up you may almost certainly have to change these to something realistic, classic case is session timeout on the AG, the default of 10 secs may be way too low
Thank you for detailing those considerations. Those are really good things to know.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
March 5, 2018 at 8:29 am
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply