October 21, 2019 at 8:04 pm
I have question, i am configuring always on AG, so one of the replica would be used for reporting server(async mode). Looking at storage space, keeping the same space as primary replica would be good option, since primary replica server has only one DB and that would be used to replicate to the secondary replica? Please advise?
October 21, 2019 at 8:12 pm
Also would see any issue with reducing the resources on secondary replica, i would not think this has any impact on Primary server. Please advise?
October 22, 2019 at 1:32 pm
Your question is a little bit confusing.
Do you have a primary , and multiple secondaries in the AG? One of which is being used for reporting?
You can certainly reduce the resources allocated to the secondary. What happens if it fails over to the secondary? Will performance suffer? As the data is being synchronized to the secondary, you may see a slowdown on the primary depending upon the the availability mode. Asynchronous may be better than synchronous.
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/
October 22, 2019 at 2:52 pm
In addition to what Michael L John has already covered, if you use a secondary replica for reporting, there's a requirement to license that replica as well as the primary.
Also do test it carefully before hitting Production, because long-running read-queries on a readable replica can block the log redo writer, which can spell trouble if your AG is in synchronous mode.
October 25, 2019 at 4:53 pm
Thanks. So we are thinking to have 3 secondary replicas, one is for reporting (async mode), second one is for sync replicas and third one is for DR(async mode). Would i still need licenses for DR?
October 25, 2019 at 9:12 pm
Thanks. So we are thinking to have 3 secondary replicas, one is for reporting (async mode), second one is for sync replicas and third one is for DR(async mode). Would i still need licenses for DR?
It's not the mode that matters. It whether the secondaries are read-only.
The primary and any read-only secondaries need to be licensed for all cores, as well as software assurance. The DR secondaries, not read only, only need software assurance.
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/
October 25, 2019 at 9:29 pm
Thanks Michael. So you saying for DR Secondaries in Always On the databases secondaries should not be in read only so that way Licenses are not needed. Did i understood right?
October 25, 2019 at 10:03 pm
Thanks Michael. So you saying for DR Secondaries in Always On the databases secondaries should not be in read only so that way Licenses are not needed. Did i understood right?
No, I’m saying they should be whatever you need them to be.
if you need you secondary’s to be read only, such as for reporting, then they need to be licensed.
if you do not need them to be read only, then you do save on licensing.
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/
January 3, 2020 at 8:38 pm
Thanks Michael John. So keeping in considering with license cost and performance benefit. Would below setup sounds reasonable to you?
Primary Replica - Full, Diff and Log backups & Maintenance Jobs etc.
Secondary Replica 1(Async mode read only) - Reporting Purposes and Data transfer.
Secondary Replica 2 (same location Sync mode for fail over) - Allow some users to do select queries so no one should run any queries in Primary, would this cause and performance issue or blocking? Would i save license cost if it's not read only mode?
Secondary Replica 3 (Asyc mode) - For DR
I am sure you can argue why some users would still need to run queries against SR2 instead of SR1? I agree, but Async mode could be behind some times ,so if in case someone needs the live data. Would you see any issues with the above setup? Any thoughts and advise would be appreciated.
January 6, 2020 at 8:59 pm
Any thoughts? Thanks in advance!
January 6, 2020 at 9:39 pm
Secondary Replica 2 (same location Sync mode for fail over) - Allow some users to do select queries so no one should run any queries in Primary, would this cause and performance issue or blocking? Would i save license cost if it's not read only mode?
I would not recommend setting up or allowing users the ability to access the HA instance for 'some' reports. This system should be solely available to support your HA requirements. It is also possible to impact the primary node because of the synchronous commit mode - which could get blocked by running queries on this secondary.
If users need to have reports with real-time data - then those reports should be reviewed and verified, placed in production and controls put in place to limit access. These should only pull a small amount of data - just the real-time requirements and can possibly be mitigated by using snapshot isolation just for those procedures.
Everything else can and should be run on the read-only secondary.
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
January 7, 2020 at 2:31 pm
Thanks Michael John. So keeping in considering with license cost and performance benefit. Would below setup sounds reasonable to you?
Primary Replica - Full, Diff and Log backups & Maintenance Jobs etc.
Secondary Replica 1(Async mode read only) - Reporting Purposes and Data transfer.
Secondary Replica 2 (same location Sync mode for fail over) - Allow some users to do select queries so no one should run any queries in Primary, would this cause and performance issue or blocking? Would i save license cost if it's not read only mode?
Secondary Replica 3 (Asyc mode) - For DR
I am sure you can argue why some users would still need to run queries against SR2 instead of SR1? I agree, but Async mode could be behind some times ,so if in case someone needs the live data. Would you see any issues with the above setup? Any thoughts and advise would be appreciated.
Primary Replica = OK.
Secondary Replica 1(Async mode read only) - Reporting Purposes and Data transfer. No. This should be probably be synchronous mode. Do you understand, or have you looked into, "Read-only Routing"? If this is set up properly, and the connection strings are set properly, queries will automatically be re-directed to the read-only secondary. You will still need to fully license this node.
Secondary Replica 2 (same location Sync mode for fail over) There should be no blocking, unless you have some really bad queries as the secondaries are read-only. Why would you want two different places to do reporting? See above, read-only routing, and this will also need to be fully licensed.
You are creating a situation where users need to connect to a specific server, not the listener. Why happens in a fail over? You will then be scrambling to fail back to the primary so that everything works properly. This kind of defeats the purpose of an availability group. Regardless of which node is the primary, it should all be transparent to the users and applications.
In my setup, the nodes fail over and back. This occurs normally when we patch servers. There is little disruption to the applications, and everything, as well as users, connects to the listener. It does not matter which node is primary. All reports use "applicationintent=readonly" in the connection strings, and the developers have configured multiple connections depending upon the operation being performed. End-users who have to perform ad-hoc queries are instructed to connect with applicationintent=readonly.
Secondary replica 3 for DR makes sense. This only needs to be licensed with SA.
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/
January 28, 2020 at 9:01 pm
Secondary Replica 1 is on VM. So Always- On can be configured for Virtual server right?
January 29, 2020 at 3:47 pm
Secondary Replica 1 is on VM. So Always- On can be configured for Virtual server right?
Virtual or physical, it does not matter.
All of my servers are virtualized, I have had nothing physical for more than 4 years.
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/
January 29, 2020 at 8:28 pm
Secondary Replica 2 (same location Sync mode for fail over) There should be no blocking, unless you have some really bad queries as the secondaries are read-only. Why would you want two different places to do reporting? See above, read-only routing, and this will also need to be fully licensed.
Be very careful with this type of configuration. If the goal is to setup a secondary server to offload reads for the application and reports executed from within the application - then this could be a valid configuration. If the goal is to offload reporting (ex: SSRS/Tableau/etc.) and data extracts then this configuration could cause substantial performance issues on your production system.
It appears to me that you want a read-only secondary available for reporting and data extracts. In that case - this secondary node should only be accessible directly and not through the listener. This way you can grant users access to the reporting system without having to give them access to the primary database(s) - nor can they accidentally run something on production (forgot to include ApplicationIntent on their connection, read-only secondary down at this time, etc...).
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
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply