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:
Let’s take a look at the system view to check the availability group configuration:
Let’s verify that the collation of the SQL Server instance is case sensitive …
… and the backup preference policy is configured to “primary”
Finally, let’s verify the database inside the availability group:
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:
Concerning the primary:
Concerning the 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