Sur une solution SQL Server Basic Always On Availability Groups, toutes les bases de données portées sur les replicas secondaires ne sont pas accessibles, car aucun accès en lecture seule n’est possible sur le replica secondaire pour le Basic Always On AG.

Voici une petit descriptif de la solution :

Always On Availability Group est une solution de haute disponibilité et de disaster recovery qui nécessite une licence SQL Server Enterprise Edition. Mais Microsoft propose la solution Basic Always On AG, qui nous permet de configurer le cluster Always On avec une licence Standard Edition, néanmoins cette solution n’est pas à l’abri des limitations :

• Limite de deux replicas (primaire et secondaire)
• Exploitation un peu plus complexe
• Aucun accès en lecture sur le replica secondaire
• Aucune sauvegarde sur le replica secondaire
• Aucun contrôle d’intégrité sur le replica secondaire
• Supporté sur SQL Server 2016 SP1 et plus
• Prise en charge d’une seule base de données de disponibilité

Donc, je reviens sur l’inconvénient qu’aucun accès en lecture sur le replica secondaire n’est possible, et comment on peut gérer cette situation surtout quand il s’agit d’un Job applicatif ou d’un Job de maintenance sur les deux replicas.

Comme vous le savez, l’agent SQL Server ne fait pas partie du groupe de disponibilité, donc, si un Job applicatif importe les données sur une base de données de disponibilité (Availability Database), il sera créé et le planifié sur quel replica ? Vous allez me dire qu’il faut le créer sur les deux replicas et le planifier en même temps.

Dans ce cas-là, on aura toujours un échec de Job qui s’exécute sur le replica secondaire, car la base de données n’est pas accessible en écriture, et même si elle est accessible en écriture (ce qui n’est pas vrai) , on va avoir des données dupliquées sur notre bases de données de disponibilité si on a pas des contraintes d’intégrité sur les tables.

Voilà donc comment on peut gérer cette situation :

Avant de passer un script ou une tâche sur une base de données de disponibilité, on vérifie d’abord si la base de données est accessible en écriture ou pas.

Si tel est le cas, on poursuit le reste des étapes, sinon on n’exécute aucun script ou tâche sur cette base de données. Dans ce cas on évitera des message d’erreurs qui vont être signaler aux utilisateurs ou aux administrateurs.

Pour cela, nous pouvons utiliser la fonction SQL Server DATABASEPROPERTYEX () pour extraire les métadonnées de base de données, et la valeur de la propriété  « Updateability » pour savoir si la base de données en question est accessible ou pas.

La fonction SQL Server DATABASEPROPERTYEX () nécessite deux arguments :

• Nom de la base de données : spécifiez le nom de la base de données pour laquelle nous voulons récupérer les informations
• Nom de la propriété : spécifiez le nom de la propriété pour obtenir sa valeur configurée dans la base de données spécifiée dans le premier argument

Le nom de la propriété qui nous intéresse dans notre cas est :

Updateability

https://docs.microsoft.com/fr-fr/sql/t-sql/functions/databasepropertyex-transact-sql?view=sql-server-ver15

Et comme le Basic Always On AG ne prend pas en charge l’accès en lecture sur le replica secondaire, on ne pourra pas avoir la valeur READ_ONLY sur une base de donnée de disponibilité sur une Basic Always On AG, et on va avoir a la place NULL.

Avec Management Studio :

Voici le message d’erreur si on essaye de faire un backup sur le replica secondaire :

Msg 976, Level 14, State 1, Line 1
The target database, 'DB010', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information,
see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Voici un script complet pour l’implémenter sur les deux replicas pour planifier un backup full de toutes les base de données :

--##########################################################################
-- SCRIPT
-- Backup All Database 
--
-- DESCRIPTION
-- Backup all database in the writeable replica Always ON
--
--##########################################################################
-- Version User Date 
-- 1.0 S.SACI 2020/08/27 
--##########################################################################
DECLARE @path VARCHAR(500)
DECLARE @dbname VARCHAR(50)
DECLARE @BackupName VARCHAR(MAX)
DECLARE @fileName VARCHAR(MAX)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
DECLARE @command VARCHAR(MAX)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('model','tempdb') -- exclude these databases

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0 
BEGIN 
IF (DATABASEPROPERTYEX(@dbname, 'Updateability') = 'READ_WRITE')
BEGIN
SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))
-- specify database backup directory
SET @path= 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup' + @dbname+'\' ;
print @path
EXEC xp_create_subdir @path
SET @BackupName = @Path+ @dbname + '_' + @year + @month + @day + @hour + @minute + @second + '.BAK'
SET @command = 'BACKUP DATABASE '+QUOTENAME(@dbname)+' TO DISK='''+@BackupName+''' WITH COMPRESSION'
EXEC (@command)
END
else 
Print 'This is not a writeable replica'

FETCH NEXT FROM db_cursor INTO @dbname
END 

CLOSE db_cursor 
DEALLOCATE db_cursor