Analyze Network Latency Impact on Remote Availability Group Replica

Multisite availability groups allow customers to deploy copies of business data across multiple sites, for disaster recovery and/or for reporting purposes, offering near real-time changes available to the copies of the production data at remote locations. If secondary replicas are hosted greater distances from the primary replica, network latency can begin to impact availability group…


Availability Group Database Reports Not Synchronizing / Recovery Pending After Database Log File Inaccessible

  You may find that one or more availability group databases is reported ‘Not Synchronizing / Recovery Pending’ on the primary replica or ‘Not Synchronizing’ on one of the secondary replicas. Despite this, your availability group replicas report they are in the primary role or secondary role, This may occur if SQL Server is unable…


Avoid Availability Group Database Data Loss: Do not Deploy File Share Witness From DFS Namespace

Cluster Quorum File Share Witness Should Not be Part of a DFS Namespace When deploying AlwaysOn availability groups you may decide to add a vote to Windows Cluster quorum by configuring a File Share Witness. A requirement when configuring that File Share Witness is that it is not part of a Distributed File System (DFS)…


Troubleshooting Internal Load Balancer Listener Connectivity in Azure

Problem Unable to Connect to Azure availability group listener Creating an availability group in order to make your application highly available when running on Azure virtual machines (IaaS) is very popular. The availability group listener requires special configuration steps in Azure as opposed to running on premise.  For more information on step by step configuration…


How to Apply Transaction Logs to Secondary When it is Far Behind

  Problem Large Send Queue You discover that the log send queue for a given availability database has grown very large, which threatens your Recovery Point Objective (RPO) and the transaction log has grown very large on the primary replica, possibly threatening to fill the drive. The cause may have been for various reasons: you…


Making Service Broker Application Highly Available With AlwaysOn

  If you have a Service Broker (SSB) application connecting to SQL Server using an AlwaysOn availability group listener, in the event of an unexpected failover, some messages may be lost or stuck in the transmission queue on the old primary (new secondary) after the failover. This could be an automatic failover or manual failover…


AlwaysOn Readable Secondaries Can Display Misleading Data & Log File Paths

Written By: Grant Carter, Senior Premier Field Engineer Reviewed By: Mark Weber – Principal Premier Field Engineer Norm Eberly – Senior Premier Field Engineer Charles Allard – Senior Premier Field Engineer Nick Schueler – Senior Premier Field Engineer Curt Matthews – Senior Escalation Engineer   Problem You may discover on a readable secondary database that is…


SQL Server 2016 AlwaysOn Availability Group Enhancements: Support for Encrypted Databases

Overview In SQL Server 2012 and SQL Server 2014, encrypted databases could be added to an AlwaysOn availability group, but they could not be added using the New Availability Group wizard. Additionally, in the event of a failover, the encrypted database data could not be accessed. This is because the database master key in the…


SQL Server 2016 AlwaysOn Availability Group Enhancements: Multiple Automatic Failover Targets

Overview SQL Server 2104 and SQL Server 2012 can have two synchronous secondary replicas. One synchronous secondary replica can serve the role as automatic failover partner with the primary replica. In SQL Server 2016 both synchronous secondary replicas can be configured as automatic failover partners with the primary replica. Benefits This will allow following benefits This increases the chances…


SQL Server 2016 AlwaysOn Availability Group Enhancements: Load Balance Read-Only Routing

Overview SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it would direct the connection to the next replica in the routing list. When you have multiple secondary replicas available for read, it is not possible to spread…