8 secondaires

3 réplicas synchrones, dont 2 en failover

Disponibilité augmentée pour les réplicas en lecture

coupure réseau / perte de quorum

Intégration Azure

Réplicas asynchrone en IaaS

Ouverts en lecture

Plus de fonctionnalités de diagnostic

3 réplicas synchrones, dont 3 en failover

Support de DTS (Distributed Transaction Coordinator) : Windows Server 2016 ou Windows Server 2012 Release 2 patch KB3090973.

Failover en cas d’indisponibilité d’une base (<> ONLINE)

Réplicas en load-balancing (round robin)

Support de gMSA (Group-Managed Service Accounts)

Transport de journal optimisé

Parallel redo

Support des solutions de haute disponibilité sur Linux (avec Pacemaker et Corosync en couche cluster OS).

Support d’un groupe de disponibilité sans cluster.

Support de réplicas hétérogènes (Windows – Linux).

Reconstruction d’index en ligne pouvant être reprise, pour éviter l’explosion du journal de transactions.

Support des transactions cross bases de données impliquées dans un groupe de disponibilité.

Jusqu’à 5 réplicas synchrones.

Support des configurations de haute-disponibilité en container.

Redirection de la connexion entre un réplica secondaire et un réplica principal, même si la chaîne de connexion mentionne le réplica directement13https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/secondary-replica-connection-redirection-always-on-availability-groups.

En cas de problème, ou pour couper et rétablir la communication, vous pouvez arrêter et redémarrer un endpoint :

Cela peut être utile dans le cas d’une erreur de ce genre :

C’est la commande à essayer si un réplica est dans un état déconnecté14https://docs.microsoft.com/fr-FR/troubleshoot/sql/database-mirroring/mirrored-databases-disconnected.

Dans le cas où vous souhaitez utilise AlwaysOn pour mettre à jour votre version de SQL Server, vous pourriez tomber sur une erreur de chiffrement des endpoint, si l’algorithme de chiffrement par défaut du endpoint change entre les versions. Ce fut notamment un problème sur un CU de SQL Server 2016, ou l’algorithme RC4 a été remplacé.

Vous pourriez obtenir une erreur 8474, comme suivant.

Interrogez les erreurs sur le réplica, à l’aide de la requête suivante :

En cas d’erreur de chiffrement, vous devriez obtenir quelque chose comme ceci dans la colonne last_connect_error_description :

Vérifiez les algorithmes de chiffrement (encryption_algorithm_desc) :

Vous pouvez changer l’algorithme de chiffrement de votre endpoint :

Il peut arriver que la présence d’un disque partagé trouble le cluster WSFC. En AlwaysOn AG, nous utilisons en général un témoin de partage de fichiers (FSW, File Share Witness).

Si nous créons le cluster WSFC avec l’assistant, il existe une coche qui indique « ajouter tous les disques disponibles au cluster » qui est cochée par défaut. Cela peut casser le cluster si les disques en questions ne sont pas destinés à être partagés dans le cluster.

Pour supprimer après coup un disque de cluster, vous pouvez utilise Powershell15https://docs.microsoft.com/en-us/powershell/module/failoverclusters/remove-clustersharedvolume.

Exemple :

Vous pouvez définir un réplica de configuration uniquement (SQL Server 2017), ce qui est utile dans les clusters externes de type Pacemaker, qui ne supportent pas les témoins. Un troisième réplica de configuration est alors nécessaire pour former un quorum.

Doivent être en mode complet (et pas pseudo-simple).

Auto-close interdit.

La base peut être chiffrée en TDE : Ajout manuel avant 2016, et, selon documentation, intégré dans l’assistant depuis 2016 – copie de la clé de chiffrement sur le réplica par l’assistant. Si vous arrivez à le faire marcher, je suis preneur.

Toutes les machines doivent être dans le même domaine

Toutes les bases doivent être en mode de récupération COMPLET

⇒ faites des sauvegardes de journaux

L’unité de basculement est le groupe

Vous pouvez utiliser des bases contenues pour simplifier les connexions

Attention aux travaux de l’agent

Les différentes cmdlet sont documentées ici : https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/create-an-availability-group-sql-server-powershell.

La procédure est décrite dans cette documentation : « Use PowerShell or Az CLI to configure an availability group for SQL Server on Azure VM » : https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-az-commandline-configure.

Par assistant – dossier partagé, sauvegarde / restauration prise en charge

Manuellement – application des restaurations sur le réplica, complète + journaux, NORECOVERY

Automatic seeding (2016)

Si DB_FAILOVER = ON sur un groupe de disponibilité, le groupe basculera si une base appartenant au groupe se trouve dans n’importe quel état autre qu’ONLINE.

Spécifie si les transactions distribuées sont activées pour ce groupe de disponibilité. Les transactions distribuées sont prises en charge depuis SQL Server 2016.

PER_DB promeut automatiquement les transactions entre bases de données impliquant des bases de données dans le groupe de disponibilité dans des transactions distribuées.

NONE empêche la promotion automatique des transactions entre bases de données en transactions distribuées, et n’inscrit pas la base de données avec un RMID stable dans DTC.

Les transactions distribuées ne sont pas bloquées quand le paramètre NONE est utilisé, mais le basculement et la récupération automatique de base de données risquent d’échouer16https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring .

Depuis SQL Server 2017. Définit un nombre minimal de réplicas secondaires synchrones nécessaires à la validation avant que le réplica principal ne valide une transaction.

Garantit que la transaction SQL Server attend que les journaux des transactions soient mis à jour avec le nombre minimal de réplicas secondaires. La valeur par défaut est 0, ce qui donne le même comportement que SQL Server 2016. La valeur maximale est le nombre de réplicas moins 1.

CONFIGURATION_ONLY17https://docs.microsoft.com/fr-fr/sql/linux/sql-server-linux-availability-group-ha spécifie que le réplica principal valide de façon synchrone les métadonnées de configuration de groupe de disponibilité dans la base de données master de ce réplica. Un réplica en configuration seule :

Il m’est arrivé d’obtenir cette erreur à la création du groupe de disponibilité, dans un environnement où la machine avait été jointe à un domaine après l’installation de SQL Server. Je ne sais pas si c’est la source du problème, mais voici une solution si cela se produit pour vous.

L’erreur dans le log d’erreur de SQL Server est semblable à ceci :

On peut trouver des erreurs dans le log du cluster, de ce type :

Apparemment, cela provient d’un problème de communication entre SQL Server et le service de cluster. Le service de cluster tourne sous le compte de service NT AUTHORITY\SYSTEM.

Vous verrez également des signes de ce problème dans le journal du cluster (que vous récupérez avec Get-ClusterLog). Il sera indiqué une erreur d’exécution de la procédure sp_server_diagnostics par manque de permission.

Il faut ajouter des privilèges dans SQL Server pour ce compte de service :

Pour effectuer un seeding automatique, SQL Server effectue en interne une sauvegarde complète de la base de données, et l’envoie sur les secondaires à l’aide d’un flux VDI (Virtual Device Interface).

Les secondaires effectuent une restauration de la sauvegarde. La base est ajoutée au groupe de disponibilité lorsque la restauration est complète.

Le journal de transaction ne peut pas se vider durant une opération de seeding.

Le seeding automatique est un processus monothread qui peut gérer jusqu’à cinq bases de données.

Vous pouvez suivre la sauvegarde sur le principal avec la requête suivante.

Sur le secondaire, le type de requête est un REDO. Vous pouvez suivre la restauration avec la commande suivante :

Après la fin des opérations de seeding automatique, la session reste ouverte, autant que je sache, jusqu’au redémarrage de l’instance.

Pour le seeding automatique, l’idéal est que les répertoires existent à l’identique sur les réplicas secondaires. Le seeding automatique n’a pas d’option de déplacement des fichiers à la restauration. C’est absolument le cas en SQL Server 2016.

Le chemin des fichiers par défaut, défini dans l’instance, n’est pas pris en compte.

SQL Server 2017 prend en charge le seeding automatique sur des serveurs Linux, Avec une utilisation des chemins par défaut. Le comportement est documenté18https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas, et il peut être changé avec un drapeau de trace.

Il est possible de combiner la sauvegarde, la copie et la restauration traditionnelles avec l’amorçage automatique. Dans ce cas, restaurez d’abord la base de données sur un réplica secondaire, y compris tous les journaux de transactions disponibles. Ensuite, activez l’amorçage automatique lors de la création du groupe de disponibilité pour « rattraper » la base de données du réplica secondaire, comme si une sauvegarde de la fin du journal était restaurée (consultez Sauvegardes de la fin du journal (SQL Server)).

Des évènements étendus existent pour le seeding automatique. Ils ne sont pas visibles par défaut dans l’assistant, parce qu’ils appartiennent au canal debug, que vous devez cocher dans l’assistant.

Les évènements sont les suivants :

Vous trouvez un requête de création de session d’évènement pour le seeding automatique à l’adresse suivante : https://github.com/rudi-bruchez/tsql-scripts/blob/master/hadr/direct-seeding-monitoring.sql.

Le seeding automatique n’est pas très loquace. Notamment, si le seeding ne réussit pas, vous n’avez pas d’avertissement graphique, ou très évident. Vous devez vous en référer aux journaux d’erreur de SQL Server et à la session d’événements étendus alwayson_health.

Vous trouvez des requêtes du suivi du seeding automatique à l’adresse suivante : https://github.com/rudi-bruchez/tsql-scripts/blob/master/hadr/direct-seeding-monitoring.sql .

Name Description

hadr_db_manager_seeding_request_msg Message de demande d’amorçage.

hadr_physical_seeding_backup_state_change Modification d’état côté sauvegarde d’amorçage physique.

hadr_physical_seeding_restore_state_change Modification d’état côté restauration d’amorçage physique.

hadr_physical_seeding_forwarder_state_change Modification d’état côté redirecteur d’amorçage physique.

hadr_physical_seeding_forwarder_target_state_change Modification d’état côté cible du redirecteur d’amorçage physique.

hadr_physical_seeding_submit_callback Événement de rappel de soumission d’amorçage physique.

hadr_physical_seeding_failure Événement d’échec d’amorçage physique.

hadr_physical_seeding_progress Événement de progression d’amorçage physique.

hadr_physical_seeding_schedule_long_task_failure Événement d’échec de tâche longue de planification d’amorçage physique.

hadr_automatic_seeding_start Se produit quand une opération d’amorçage automatique est soumise.

hadr_automatic_seeding_state_transition Se produit quand une opération d’amorçage automatique change d’état.

hadr_automatic_seeding_success Se produit quand une opération d’amorçage automatique aboutit.

hadr_automatic_seeding_failure Se produit quand une opération d’amorçage automatique échoue.

hadr_automatic_seeding_timeout Se produit quand une opération d’amorçage automatique expire.

Spécifie si les bases de données d’un réplica secondaire peuvent accepter des connexions clientes.

NO – Aucune connexion utilisateur n’est autorisée aux bases de données secondaires de ce réplica. Elles ne sont pas disponibles pour l’accès en lecture. Il s’agit du comportement par défaut.

READ_ONLY – Seules sont autorisées les connexions aux bases de données dans le réplica secondaire où la propriété d’intention de l’application est définie sur ReadOnly. Pour plus d’informations sur cette propriété, consultez le lien d’aide suivant : https://docs.microsoft.com/fr-fr/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client.

ALL – Toutes les connexions sont autorisées aux bases de données dans le réplica secondaire pour un accès en lecture seule.

SELECT

ag.name as [Availability Group],

ar.replica_server_name as [When Primary Replica Is],

rl.routing_priority as [Routing Priority],

ar2.replica_server_name as [RO Routed To],

ALTER AVAILABILITY GROUP [MONAG]

Load Balancing sur la redicrection en lecture, à partir de SQL Server 2016 :

La copie des données sur un secondaire en lecture implique une opération de REDO

Elle doit respecter le niveau d’isolation par défaut

Les lectures pourraient donc bloquer les REDO

Les réplicas en lectures passent automatiquement les REDO en isolation snapshot

Attention à tempdb

Le secondaire en lecture peut créer des statistiques temporaires dans tempdb.

On peut aussi configurer le comportement lorsque le réplica est primaire, avec l’option PRIMARY_ROLE suivante :

READ_WRITE – Les connexions avec ApplicationIntent = ReadOnly ne sont pas autorisées.

ALL – Toutes les connexions sont autorisées. Valeur par défaut.

Nouveauté SQL Server 2019.

Redirection automatique vers le réplica principal, même si la connexion est effectuée directement sur un réplica secondaire (sans passer par le listener).

https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/secondary-replica-connection-redirection-always-on-availability-groups

Par drapeau de trace (TF).

Pour activer ces drapeaux de trace, faites-le en global

La commande CREATE LOGIN accepte l’option SID pour passer, sur les réplicas, le SID récupéré sur le réplica où vous avez créé le login SQL. C’est en général le réplica principal, car vous devez avoir accès à la base en lecture-écriture pour y créer le user.

Pour récupérer le SID après création du login, vous pouvez utiliser la fonction SUSER_SID().

Le résultat est converti en représentation hexadécimale par SSMS. Si vous récupérez cette information différemment, vous pourrez la convertir en hexadécimal à partir de sa valeur en VARBINARY à l’aide de la fonction CONVERT().

Lorsque le réplica est ajouté, vous devez ensuite, à partir de ce réplica, dans SQL Server, joindre ce réplica au groupe de disponibilité23https://docs.microsoft.com/fr-fr/sql/database-engine/availability-groups/windows/join-a-secondary-replica-to-an-availability-group-sql-server.

Pour activer l’ajout du réplica sur l’instance SQL Server de ce réplica, on utilise la commande JOIN :

ALTER AVAILABILITY GROUP Pacha JOIN;

à lancer donc sur le réplica secondaire.

Fonction scalaire. Retourne 1 si on se trouve sur le réplica principal pour la base de données passée en paramètre.

Permission VIEW SERVER STATE.

Indique, pour la base de données passée en paramètre, si le réplicas est le réplica préféré pour effectuer les sauvegardes.

Exemple d’utilisation chez un client, dans une procédure d’archivage automatique :

La fonctionnalité de reconstruction d’index avec reprise souffre des limitations suivantes :

PAUSE – mettre en pause une opération de reconstruction d’index en ligne pouvant être reprise.

ABORT – abandonne une opération d’index en cours d’exécution ou en pause qui a été déclarée comme pouvant être reprise. Vous devez exécuter explicitement une commande ABORT pour mettre fin à une opération de reconstruction d’index pouvant être reprise. L’échec ou la mise en pause d’une opération d’index pouvant être reprise n’arrête pas son exécution, mais la laisse dans un état de pause indéterminée.

Reconstruire un index avec possibilité de reprise.

ALTER INDEX index_name ON table

REBUILD WITH (

ONLINE = ON , RESUMABLE ON, MAX_DURATION = X MINUTES

);

Reprendre une reconstruction interrompue.

ALTER INDEX index_name ON table

RESUME WITH (MAX_DURATION = X MINUTES );

Mettre en pause une reconstruction d’index.

ALTER INDEX index_name ON table PAUSE;

Annuler totalement une opération.

ALTER INDEX index_name ON table ABORT;

ALTER DATABASE SCOPED CONFIGURATION

{

{ [ FOR SECONDARY] SET <set_options>}

}

| CLEAR PROCEDURE_CACHE

| SET < set_options >

[;]

< set_options > ::=

{

MAXDOP = { <value> | PRIMARY}

| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}

| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}

| IDENTITY_CACHE = { ON | OFF }

| OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

| XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF }

| XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF }

| ELEVATE_ONLINE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

| ELEVATE_RESUMABLE = { OFF | WHEN_SUPPORTED | FAIL_UNSUPPORTED }

| GLOBAL_TEMPORARY_TABLE_AUTODROP = { ON | OFF }

}

FOR SECONDARY – Spécifie les paramètres pour les bases de données secondaires AlwaysOn ouverts en lecture seule. Toutes les bases de données secondaires auront la même valeur, on ne peut pas attribuer une valeur à un secondaire spécifique.

Si vous utilisez l’option FOR SECONDARY, la valeur de configuration peut aussi être PRIMARY, Cela signifie que la valeur pour les réplicas secondaires suivra dynamiquement la valeur du réplica principal.

https://www.sqlshack.com/max-worker-threads-for-sql-server-always-on-availability-group-databases/

La latence des REDO peut se mesurer sur les valeurs présentes dans la DMV sys.dm_hadr_database_replica_states. Les colonnes suivantes sont utiles :

Au besoin, vous pouvez augmenter le niveau de détail (de verbosité) de cette journalisation pour un groupe de disponibilité.

Vous pouvez le faire dans le gestionnaire de cluster, dans les propriétés de la ressource du groupe de disponibilité, sur l’onglet Propriétés.

L’option s’appelle VerboseLogging et sa valeur par défaut est 0.

Pour que l’option soit prise en compte, il faut mettre la ressource hors connexion, et en ligne à nouveau.

Messages de ce type :

Source : https://techcommunity.microsoft.com/t5/sql-server-support-blog/dbmgrpartnercommitpolicy-setsyncstate-may-seem-more-mysterious/ba-p/334007

In the example above the value is 00000026BD96D330.

It certainly remains a mystery though which partner it is. This is not the GUID replica ID that is stored in many DMVs like sys.dm_hadr_database_replica_states. It is a memory address, so it is not particularly useful.

Here are the possible states:

0 - Not Joined to AG

1 - Not Synchronized

2 - Suspended

4 - Synchronized

8 - Redo (redoing log)

Informations sur le cluster WSFC.

SELECT

c.cluster_name,

c.quorum_type_desc as quorum_type,

c.quorum_state_desc as quorum_state

FROM sys.dm_hadr_cluster c;

État du cluster.

SELECT

cm.member_name,

cm.member_type_desc as member_type,

cm.member_state_desc as member_state,

cm.number_of_quorum_votes

FROM sys.dm_hadr_cluster_members cm;

sp.state,

SUSER_NAME(SP.grantor_principal_id) as grantor,

sp.type as permission,

SUSER_NAME(SP.grantee_principal_id) as grantee

g.failure_condition_level,

g.health_check_timeout,

g.automated_backup_preference_desc as [automated_backup_preference],

gs.primary_recovery_health_desc as [primary_health],

gs.primary_replica,

gs.secondary_recovery_health_desc as [secondary_health],

gs.synchronization_health_desc as [synchronization_health]

SELECT

g.name as AG,

ar.replica_server_name,

ar.availability_mode_desc as [availability_mode],

ar.failover_mode_desc as [failover_mode],

ar.session_timeout,

ar.primary_role_allow_connections_desc as [primary_role_allow_connections],

ar.secondary_role_allow_connections_desc as [secondary_role_allow_connections],

CAST(ar.create_date as datetime2(0)) as create_date,

CAST(ar.modify_date as datetime2(0)) as modify_date,

ar.backup_priority,

ar.read_only_routing_url

SELECT

g.name as AG,

agl.dns_name,

agl.is_conformant,

agl.port,

agl.ip_configuration_string_from_cluster,

aglip.ip_address,

aglip.ip_subnet_mask,

aglip.is_dhcp,

aglip.network_subnet_ip,

aglip.network_subnet_ipv4_mask,

aglip.network_subnet_prefix_length,

aglip.state_desc

SELECT

g.name as AG,

ar.replica_server_name as [Replica],

ar2.replica_server_name as ReadOnly_Replica,

rorl.routing_priority

dc.database_name,

dc.truncation_lsn

ar.replica_server_name as [Replica],

rcs.database_name,

rcs.is_database_joined,

rcs.is_failover_ready,

rcs.is_pending_secondary_suspend,

rcs.truncation_lsn

apr.file_id,

apr.page_id,

apr.error_type,

apr.page_status,

apr.modification_time

Donne l’état des réplicas.

Retourne une ligne pour chaque base de données qui participe à un groupe de disponibilité pour lequel l’instance locale de SQL Server héberge un réplica.

Documentation : https://docs.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql

last_sent_time – le dernier bloc de log envoyé sur le principal. Début de la synchro

last_received_time – le dernier bloc de log reçu par le secondaire

last_hardened_time – le dernier bloc de log écrit par le secondaire depuis le cache sur le disque

last_redone_time – le temps du dernier REDO sur le secondaire

last_commit_time – dernier commit du secondaire reporté au principal

Requête disponible à l’adresse suivante : https://github.com/rudi-bruchez/tsql-scripts/blob/master/hadr/database-replica-states.sql

ar.replica_server_name as [Replica],

rs.is_local,

rs.role_desc as role,

rs.operational_state_desc as operational_state,

rs.connected_state_desc as connected_state,

rs.recovery_health_desc as recovery_health,

rs.synchronization_health_desc as synchronization_health

JOIN sys.dm_hadr_availability_replica_cluster_states cs

ON cn.replica_server_name = cs.replica_server_name;

Donne le statut des groupes de disponibilité.

Donne le statut des réplicas.

According to MSDN documentation, suspend_reason in sys.dm_hadr_database_replica_states has below values

0 = User action

1 = Suspend from partner

2 = Redo

3 = Apply

4 = Capture

5 = Restart

6 = Undo

7 = Revalidation

8 = Error in the calculation of the secondary-replica synchronization point

was confirmed from other resource that The doc has "capture" and "apply" flipped, it should be:

0=USER

1=PARTNER

2=REDO

3=CAPTURE

4=APPLY

5=RESTART

6=UNDO

7=REVALIDATION

8=Error in calculation

and suspend_reason_desc

SUSPEND_FROM_USER = A user manually suspended data movement

SUSPEND_FROM_PARTNER = The database replica is suspended after a forced failover

SUSPEND_FROM_REDO = An error occurred during the redo phase

SUSPEND_FROM_APPLY = An error occurred when writing the log to file (see error log)

SUSPEND_FROM_CAPTURE = An error occurred while capturing log on the primary replica

SUSPEND_FROM_RESTART = The database replica was suspended before the database was restarted (see error log)

SUSPEND_FROM_UNDO = An error occurred during the undo phase (see error log)

SUSPEND_FROM_REVALIDATION = Log change mismatch is detected on reconnection (see error log)

SUSPEND_FROM_XRF_UPDATE = Unable to find the common log point (see error log)

Donne le statut des listeners.

Si la recherche DNS du VNN retourne plusieurs adresses IP, comme c’est le cas dans une configuration de sous-réseaux, le client va essayer une adresse IP à la fois en round robin. Si vous spécifiez l’option MultiSubnetFailover=True dans la chaîne de connexion, les adresses IP sous toutes essayées simultanément.

MultiSubnetFailover :

https://blogs.lessthandot.com/index.php/datamgmt/dbadmin/asynchronous-replicas-and-transactional-replication/

La commande CREATE AVAILABILTY GROUP est enrichie de la façon suivante.

CREATE AVAILABILITY GROUP group_name

WITH ( CLUSTER_TYPE = { WSFC | EXTERNAL | NONE } )

La vue de catalogue sys.availability_groups est enrichie de deux nouvelles colonnes :

SELECT

name,

cluster_type,

cluster_type_desc

FROM sys.availability_groups;