Score:0

SQL Availability Group Failover Automation through PowerShell - Slow response

cn flag

I am trying to automate the process of failing over AGs on SQL Server 2017 and I run the below command remotely through PowerShell over remoting (port 5985)

Invoke-Sqlcmd -ServerInstance .\INSTANCE01 -Query "ALTER AVAILABILITY GROUP AG1 FAILOVER;" The AG is the backend to a time sensitive financial application. What we are noticing is that when the AG is failed over manually through SQL Server Management Studio, it takes less than 10 seconds worth of blip in financial transactions where as when I do it through PowerShell it can take up to 75 seconds.

Please note, the SQL command above returns in under 10 seconds but the transactions team tell me there was blip of at east 70 seconds. That makes me think there is something missing in the failover SQL query above than what happens when done manually via SSMS.

So just wanted to know your thoughts as to what could be slowing things down. Thanks.

Ben Thul avatar
cn flag
I'd check the AlwaysOn_Health session on both nodes (i.e. the former and current primary). While the failover itself might only take a couple of seconds, there are things like crash recovery that need to happen. That should all be logged in the aforementioned session. Also, just to rule it out, are both nodes in the same subnet? If not, are you running a multsubnet AG listener?
Steve avatar
cn flag
Thanks Ben. It looks like the failover in my automation was targeting the cross site node hence the issue. We are testing further.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.