SQL Server 2016: availability groups and the new option DB_FAILOVER

Let’s continue the SQL Server 2016’s discovery with one of my favourite topic: the AlwaysOn availability groups (AAGs). There are some improvements on this area.

First of all, let’s introduce one of them (DB_FAILOVER option) by remembering of the availability groups behaviour with the previous versions of SQL Server. One misconception that exists at different customer places is that an availability group doesn’t detect a database failure. No, this not a joke but the pure reality. AAGs are designed only to detect issues at the SQL Server level instance until the introduction of SQL Server 2016. You can verify by yourself by looking at the different possible failover conditions levels.

Indeed, SQL Server 2016 adds a new layer of failover capabilities with the new DB_FAILOVER option. According to the BOL, a new response will be taken by SQL Server when a database on the primary replica will be offline. Maybe, we must precise here what offline means: any status other than ONLINE for a database in the availability group will trigger an automatic failover. Keep in mind that this new feature is set on the group level. It means that all databases included to this group will be concerned by this option.

During my session at the previous event Journées SQL Server 2014 in Paris, I had the opportunity to show that no action was taken by the availability group for a database that had lost its transaction log. To be more precise, I simulated a scenario where the disk that contains the transaction log file has been lost. This is probably the most common scenario where we may encounter a database file failure. Let’s perform the same today with SQL Server 2016.

Let’s begin with the creation script of the availability group 2016Grp. I put only the interesting part below:

CREATE AVAILABILITY GROUP [2016Grp]
WITH
(
       AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
       DB_FAILOVER = ON,
       DTC_SUPPORT = NONE
)
FOR DATABASE [killerdb], [SSISDB]
REPLICA ON N'SQL161'
WITH
(
       ENDPOINT_URL = N'TCP://SQL161.dbi-services.test:5022',
       FAILOVER_MODE = AUTOMATIC,
       AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
       BACKUP_PRIORITY = 50,
       SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
N'SQL162'
WITH
(
       ENDPOINT_URL = N'TCP://SQL162.dbi-services.test:5022',
       FAILOVER_MODE = AUTOMATIC,
       AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
       BACKUP_PRIORITY = 50,
       SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);
GO

This is a basic availability group with two replicas (SQL161 and SQL162) configured with synchronous replication and automatic failover that includes two databases named killerdb and SSISDB. You may notice the new options at the top of the script: DB_FAILOVER and DTC support that are available from the GUI as well:

Let’s continue by creating a simple table dbo.t in the killerdb database and next, by inserting a bunch of data.

USE killerdb;
GO
 
CREATE TABLE dbo.t
(
       id INT NOT NULL IDENTITY(1,1)
);
GO
 
SET NOCOUNT ON;
GO
 
INSERT dbo.t DEFAULT VALUES;
GO 200
 
SELECT COUNT(*)
FROM dbo.t;

As expected the table contains 200 records.

Now, it’s time to unplug my disk from my computer that contains the killerdb transaction log file. After that, a new insertion of 200 additional rows raises the following error:

And good news! The availability group 2016Grp has switched automatically to the SQL162 replica in my case. Bear in mind that for one database failure, all the group will failover. Let’s take a look at the AlwaysOn _health session:

You may see the response from the availability group against the error 1117 – I/O – related to my unplugged disk event. On the new secondary the killerdb database is no longer online with the recovery pending state.

SELECT
       @@SERVERNAME as server_name,
       name AS database_name,
       state_desc
FROM sys.databases
WHERE name = 'killerdb';
GO

This first improvement is surely a good way to enhance the global availability of the AlwaysOn availability group. We’ll discuss about the other interesting new options in the next blog posts.
Stay connected!

By David Barbarin