A couple of days ago, I faced a weird backup issue with SQL Server AlwaysOn and availability groups at one of my customer (thank to him to point out this issue 🙂 ). After installing our DMK tool (Database Management Kit) about database maintenance for AlwaysOn, my customer noticed that their databases have not backed up. Ouch … what’s going on? I never ran into this issue before… Do the problem comes from our tool?

In fact, our DMK uses the useful DMF sys.fn_hadr_backup_is_preferred_replica() to know which databases are candidate for backup operations on replicas at a given time and this is where our issue starts. Indeed, in a specific situation that includes both a case sensitive server collation and entering the name of replicas in lower case, we found that the result of this function is inconsistent. Let me show with an example.

In my customer’s context, the replica names have been filled out from a PowerShell script form in lower case as follows:

 

blog_37_-_powershell_script

 

Let’s take a look at the system view to check the availability group configuration:

 

SELECT
       replica_server_name,
       availability_mode_desc,
       failover_mode_desc
FROM sys.availability_replicas

 

blog_37_-_availability_replicas_name

 

Let’s verify that the collation of the SQL Server instance is case sensitive …

 

SELECT SERVERPROPERTY(‘Collation’) AS ServerCollation;

 

blog_37_-_server_collation

 

… and the backup preference policy is configured to “primary”

 

SELECT
       name AS group_name,
       automated_backup_preference_desc as backup_preference
FROM sys.availability_groups

 

blog_37_-_backup_preference

 

Finally, let’s verify the database inside the availability group:

 

SELECT
       g.name AS group_name,
       r.replica_server_name AS replica_name,
       dcs.database_name,
       drs.database_.state_desc AS db_state
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS r
       ON drs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
       ON g.group_id = drs.group_id
JOIN sys.dm_hadr_database_replica_cluster_states AS dcs
       ON dcs.group_database_id = drs.group_database_id
             AND dcs.replica_id = drs.replica_id

 

blog_37_-_dummy_database

 

Ok now let’s take a look at the result of the DMF sys.fn_hadr_backup_is_preferred_replica() in this context. I put here a simplified sample portion of the TSQL code used in our DMK:

 

USE master;
GO
 
DECLARE @db_name SYSNAME;
 
SELECT @db_name = name
FROM sys.databases WHERE name = N’DUMMY’;
 
SELECT
       @@SERVERNAME AS server_name,
       @db_name AS database_name,
       sys.fn_hadr_backup_is_preferred_replica(@db_name) AS fn_result;

 

Concerning the primary:

 

blog_37_-_backup_function_result_primaryjpg

 

Concerning the secondary :

 

blog_37_-_backup_function_result_secondary

 

 

If you perform the same by configuring this time the replica names in upper case, you will notice that the issue will disappear. When I think about this issue, it’s true that in almost cases customers prefer to use the assistant wizard to configure availability groups and in this case do you notice that the replica names are always switched in upper case?

There also exists a Microsoft connect item about this problem but unfortunately it seems that it will not be solved by Microsoft … so be careful when you implement availability groups by script.

See you on the next availability group ventury!

By David Barbarin

 

 

Post Views: 935
Thumbnail [60x60]
by
Microsoft Team