Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups

Writer: Simon Su Technical Reviewer: Pam Lahoud, Sourabh Agarwal, Tejas Shah Applies to: SQL Server 2014 SP2, SQL Server 2016 SP1, SQL Server 2017 RTM  In synchronous-commit mode AG nodes sometimes you may observe your transactions are pending on HADR_SYNC_COMMIT waits. HADR_SYNC_COMMIT waits indicate that SQL server is waiting for the signal from the remote…

0

SQL Server : large RAM and DB Checkpointing

SQL Server : large RAM and DB Checkpointing   Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory and when applicable, how to best address them. SQL Server 2016 improves…


MultiSubnet = TRUE Is Now Default Behavior

I get to be the a good new messenger today.    We have made changes to the SQL Server Client Provider.  The provider detects when multiple IP addresses are present for a listener.   The links below detail the behavior making it easier for your multi-subnet AlwaysOn deployments. Improved MultiSubnet Listener Behavior With Newly Released SQL Client…

1

VSS backup of AlwaysOn Secondaries

Hi Everyone, Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases. Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our…

5

AlwaysON – HADRON Learning Series: lock_redo_blocked/redo worker Blocked on Secondary Replica

The topic I received most in my inbox this week was redo blocked on a secondary while attempting to acquire SCH-M (schema modify) lock. First of all, this is expected behavior and you can monitor for this with your standard blocking activities (sys.dm_exec_requests, blocked process TRC event, blocked process threshold configuration setting(s) and the log_redo_blocked…

0

AlwaysON – HADRON Learning Series: HADR_SYNC_COMMIT vs WRITELOG wait

The distinction between these two wait types is subtle but very helpful in tuning your Always On environment. The committing of a transaction means the log block must be written locally as well as remotely for synchronous replicas.   When in synchronized state this involves specific waits for both the local and remote, log block, harden…

2

How It Works: Always On–When Is My Secondary Failover Ready?

I keep running into the question: “When will my secondary allow automatic failover?”   Based on the question I did some extended research and I will try to summarize in is blog post.  I don’t want to turn this post into a novel so I am going to take some liberties and assume you have read…

9

AlwaysOn ERROR: Log backup for database "MyDB" on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.

This error surprised me when it showed up in the error log so I decided to dig into it a bit more. “Log backup for database “MyDB” on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.  This is an informational message only.  Preserve…

1

How It Works: SQL Server AlwaysOn Lease Timeout

Nov 2013 Based in recent cases I needed to extend and clarify this post. The LeaseTimeout controls the lease mechanism and when the lease expires there is a very high probability of a system wide event taking place.   Let me explain more and why I make this statement. The SQL Server resource DLL is responsible…

5

PowerShell and AlwaysOn – Gotcha – Exception setting "ConnectionString": "Keyword not supported: ‘applicationintent’."

Here is the an issue I saw come across an alias that is a gotcha! I’m running into a problem connecting to an AlwaysOn read-intent secondary and I was wondering if someone could help me out.  I have the .Net Framework 4.5 installed and the newest SQL Client install for SQL Server 2012.  Running this…

1