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…


SQL Server 2016 AlwaysOn Availability Group Enhancements: Initial Data Synchronization Without Database and Log Backup

Overview In SQL Server 2012 and 2014, initializing a secondary replica is cumbersome to do manually, and requires backup and restore of your database and transaction log from the primary to a network share, and then a restore of the database and log backup files to the secondary replica. The availability group wizard can automate…


SQL Server 2016 AlwaysOn Availability Group Enhancements: Basic Availability Group on Standard Edition

Basic Availability Group on Standard Edition Historically, SQL Server database mirroring offered high availability of a SQL Server database. AlwaysOn availability groups has supplanted database mirroring as the future of high availability in SQL Server, meanwhile, database mirroring has been marked for deprecation. As database mirroring is deprecated, SQL Server 2016 introduces AlwaysOn Basic Availability…


Improved AlwaysOn Availability Group Lease Timeout Diagnostics

When your AlwaysOn availability group is configured for automatic failover, you may find your availability group failed over, or if configured for manual failover, you may observe your availability group transition from the PRIMARY role to the RESOLVING role, during which users cannot access the availability group databases. Checking the SQL Server error log, you…


Troubleshooting Availability Group Listener in Azure

Configuring an availability group listener in Azure has additional steps involved when compared to creating an availability group listener on premises. This topic helps you troubleshoot your availability group listener, whether your AlwaysOn Availability Groups deployment is in Azure only or in a hybrid IT environment using a site-to-site VPN. Some steps in the listener configuration…


Enhance AlwaysOn Failover Policy to Test SQL Server Database Data and Log Drives

In SQL Server 2012 and 2014, AlwaysOn health diagnostics detect the health of the SQL Server process in several ways. However, no health detection is performed on the accessibility or viability of the databases defined in AlwaysOn availability groups. If the disk hosting the availability group database or log files is lost, AlwaysOn health does…


Replication Agents fail to connect to listener in a multisite cluster

A listener (network name) for a multisite cluster will be dependent on more than 1 ip address.  When connecting to a listener for a multisite cluster, it is recommended to add the parameter multisubnetfailover to the connection string for the application.  Legacy applications may not be able to use the latest version of Microsoft SQL…