New answers tagged availability-groups
1
In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?
No, it wont as documented in BOL(for azure but still applies to sql server on prem):
Attempting to enable Automatic tuning via T-SQL on the read-only secondary will result in a failure as ...
5
You're not alone on seeing sync latency with AGs when doing index rebuilds. From Sean Gallardy's blog post SQL Server Index Maintenance – You’re Doing It Wrong (emphasis mine):
In the worst of the cases, which does seem to happen quite often, it can bring the entire server to its knees, causing long blocking waits, filling up the log, causing remote AG ...
0
In our case, it looks like some kind of bug when log fills up on systems with cdc/replication and AlwaysON. This issue happened again and the solution was to enable and disable CDC again. The events were as below
Transaction log of the published db full because of distributor db running out of space and log reader failing. log_reuse_wait_desc shows ...
-2
In case you need a failover to happen if your primary SQL Server instance experiences a slow disk read or something, you can try the approach explained here: Configure a flexible automatic failover policy for an AAG. Exactly, you can set the FAILURE_CONDITION_LEVEL AAG parameter higher than default (3, corresponds to "any critical server error"), ...
0
A reboot of a cluster node that has a resource group that's configured to automatically start forces the WSFC to start that resource on a surviving node, implementing HA feature of SQL Server AAG. This action also triggers SQL Server instance to promote an existing synchronous secondary replica to primary. I think you can avoid automatic failover by setting ...
3
Check with your storage admin if there's any firmware updates recently or your storage subsystem needs a newer firmware update. I've seen this messages on SQL environment multiple times and one thing you can look at is the storage layer configuration (this is the common cause). Also, one thing to monitor your disk performance using perfmon. disk counters you ...
4
Switch to indirect checkpoints (generally, set recovery interval time to 60 seconds, at least for user databases).
I wrote about this issue here and here, and you should read those posts in full before making the change, though it’s pretty much a no-brainer IMHO.
The impact in our environment was drastic and immediate, and it was simple to prove the change ...
1
Availability Groups are built upon Windows Failover Cluster. As such, it won't (probably?) be a supported scenario to run long term with different OS versions in the same cluster. Assuming it lets you set it up in the first place.
You might ask in System Administration if it's possible to setup a windows cluster between a OS 2016 and 2019. If you can and ...
1
I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply.
That's actually the main thing that drives the reply. If you don't sharply limit each primary instance it will acquire an unfair share of the server memory, and you may have severely degraded performance of the other ...
0
Depends on your version of SQL Server.
From SQL Server 2012 to SQL Server 2017 CU5, you can't setup AG for distributor database.
Starting SQL Server 2017 CU6 and on SQL Server 2016 SP2-CU3, you can setup AG for distributor database and it is required you have a remote distributor. This is only supported for Transactional Replication. If you have merge, bi-...
1
Sure. The docs have plenty of detail on how, and specific details that might affect your implementation.
I'd recommend configuring your distributor as a third, dedicated AG (assuming 2017+).
0
As you've already found out, ApplicationIntent=READONLY; isn't a standardized keyword when it comes to connection strings. Most drivers don't support it and generally, you'd either connect to a read-only replica node explicitly or just point to the primary node and resign yourself to not using a read-only replica. There is another option though, and once ...
Top 50 recent answers are included
