Qu’est-ce qu’un groupe de disponibilité autonome ?

S’applique à : SQL Server 2022 (16.x)

Un groupe de disponibilité autonome est un groupe de disponibilité Always On qui prend en charge :

• Gestion des objets de métadonnées (utilisateurs, connexions, autorisations, travaux SQL Agent, etc.) au niveau du groupe de disponibilité, ainsi qu’au niveau de l’instance.

• Bases de données système autonomes spécialisées au sein du groupe de disponibilité.

Cet article décrit en détail les similitudes, les différences et les fonctionnalités des groupes de disponibilité autonomes.

Vue d’ensemble

En général, les groupes de disponibilité autonomes se composent d’une ou de plusieurs bases de données utilisateur destinées à fonctionner en tant que groupe coordonné et qui sont répliquées sur un certain nombre de nœuds dans un cluster. En cas d’échec dans le nœud ou dans l’intégrité de SQL Server sur le nœud qui héberge la copie principale, le groupe des bases de données est déplacé comme une seule unité vers un autre nœud de réplica dans le groupe de disponibilité. Toutes les bases de données utilisateur restent synchronisées sur tous les réplicas du groupe de disponibilité, en mode synchrone ou asynchrone.

Cela fonctionne bien pour les applications qui interagissent uniquement avec cet ensemble de bases de données utilisateur, mais des difficultés surviennent lorsque les applications s’appuient également sur des objets tels que les utilisateurs, les connexions, les autorisations, les travaux de l’agent, etc., qui sont stockés dans l’une des bases de données système (master ou msdb). Pour que les applications fonctionnent de manière fluide et prévisible, l’administrateur doit s’assurer manuellement que toute modification apportée à ces objets est dupliquée sur toutes les instances de réplica du groupe de disponibilité. Si une nouvelle instance est introduite dans le groupe de disponibilité, les bases de données peuvent être amorcées automatiquement ou manuellement via un processus simple, mais alors toutes les personnalisations de la base de données système doivent être reconfigurées sur la nouvelle instance pour qu’elle corresponde aux autres réplicas.

Les groupes de disponibilité autonomes étendent le concept du groupe de bases de données répliquées pour inclure des parties pertinentes des bases de données master et msdb. Considérez cela comme le contexte d’exécution pour les applications qui utilisent le groupe de disponibilité autonome. L’idée est que l’environnement du groupe de disponibilité autonome inclut des paramètres qui affecteraient l’application qui dépend d’eux. Par conséquent, l’environnement du groupe de disponibilité autonome concerne toutes les bases de données avec lesquelles l’application interagit, l’authentification qu’elle utilise (connexions, utilisateurs, autorisations), tous les travaux planifiés qu’elle s’attend à exécuter et d’autres paramètres de configuration qui ont un impact sur l’application.

Cela est différent des bases de données autonomes, qui utilisent un mécanisme différent pour les comptes utilisateur, stockant les informations utilisateur au sein de la base de données elle-même. Les bases de données autonomes répliquent uniquement les connexions et les utilisateurs, et l’étendue de la connexion ou de l’utilisateur répliqué est limitée à cette seule base de données (et à ses réplicas).

En revanche, dans un groupe de disponibilité autonome, vous pouvez créer des utilisateurs, des connexions, des autorisations, etc., au niveau du groupe de disponibilité, et ils seront automatiquement cohérents entre les réplicas du groupe de disponibilité et entre les bases de données au sein de ce groupe de disponibilité autonome. Cela évite à l’administrateur d’effectuer manuellement ces modifications.

Différences

Il existe des différences pratiques à prendre en compte lors de l’utilisation de groupes de disponibilité autonomes, comme la création de bases de données système autonomes et forcer la connexion au niveau du groupe de disponibilité autonome, plutôt que d’utiliser la connexion au niveau de l’instance.

Bases de données système autonomes

Chaque groupe de disponibilité autonome possède ses propres bases de données système master et msdb nommées d’après le nom du groupe de disponibilité. Par exemple, le groupe de disponibilité autonome MyContainedAG contient les bases de données nommées MyContainedAG_master et MyContainedAG_msdb. Ces bases de données système sont automatiquement amorcées sur de nouveaux réplicas et les mises à jour sont répliquées sur ces bases de données comme pour n’importe quelle autre base de données dans un groupe de disponibilité. Cela signifie que lorsque vous ajoutez un objet tel qu’une connexion ou un travail de l’agent alors que vous êtes connecté au groupe de disponibilité autonome, lorsque le groupe de disponibilité autonome bascule vers une autre instance, se connectant au groupe de disponibilité autonome, vous voyez toujours les travaux de l’agent et pourrez toujours vous authentifier à l’aide de la connexion créée dans le groupe de disponibilité autonome.

Les bases de données système d’un groupe de disponibilité nouvellement créé ne sont pas des copies effectuées à partir de l’instance où la commande CREATE AVAILABILITY GROUP est exécutée. Ce sont initialement des modèles vides sans aucune donnée. Immédiatement après la création, les comptes d’administrateur de l’instance qui crée le groupe de disponibilité autonome sont copiés dans le groupe de disponibilité autonome master. De cette façon, l’administrateur peut se connecter au groupe de disponibilité autonome et procéder au reste de la configuration.

Si vous créez des utilisateurs ou des configurations locaux dans votre instance, ils ne s’affichent pas automatiquement lorsque vous créez vos bases de données système autonomes et ils ne sont pas visibles lorsque vous vous connectez au groupe de disponibilité autonome. Une fois la base de données utilisateur jointe à un groupe de disponibilité autonome, elle devient immédiatement inaccessible à ces utilisateurs. Vous devez les recréer manuellement dans les bases de données système autonomes dans le contexte du groupe de disponibilité autonome, en vous connectant directement à la base de données ou en utilisant le point de terminaison de l’écouteur. L’exception à cette règle est que toutes les connexions dans le rôle sysadmin de l’instance parente sont copiées dans la nouvelle base de données master spécifique au groupe de disponibilité.

Restaurer une base de données système autonome

Vous pouvez restaurer une base de données système autonome de deux manières.

• Restaurer une base de données autonome à l’aide d’un réplica secondaire :

  1. Restaurez la base de données autonome master et msdb sur une instance de serveur qui héberge le réplica secondaire, en utilisant RESTORE WITH NORECOVERY pour chaque opération de restauration. Pour plus d’informations, consultez l’article Préparer une base de données secondaire pour un groupes de disponibilité Always On.

  2. Joignez chaque base de données autonome au groupe de disponibilité. Pour plus d’informations, consultez Joindre une base de données secondaire à un groupes de disponibilité Always On.

• Restaurer une base de données autonome en supprimant le groupe de disponibilité autonome :

  1. Supprimez le groupe de disponibilité autonome.

  2. Restaurez la base de données autonome master et msdb dans chacune des instances inscrites dans le groupe de disponibilité autonome.

  3. Recréez le groupe de disponibilité autonome à l’aide de nœuds et de noms d’origine, en utilisant la syntaxe WITH (CONTAINED, REUSE_SYSTEM_DATABASES).

Connecter (environnement autonome)

Il est important de faire la différence entre la connexion à l’instance et la connexion au groupe de disponibilité autonome. La seule façon d’accéder à l’environnement du groupe de disponibilité autonome est de se connecter à l’écouteur du groupe de disponibilité autonome ou de se connecter à une base de données qui se trouve dans le groupe de disponibilité autonome.

"Persist Security Info=False;
User ID=MyUser;Password=*****;
Initial Catalog=MyContainedDatabase;
Server=MyServer;"

Où MyContainedDatabase est une base de données du groupe de disponibilité autonome avec laquelle vous souhaitez interagir.

Cela signifie que vous devez créer un écouteur pour le groupe de disponibilité autonome afin d’utiliser efficacement ce dernier. Si vous vous connectez à l’une des instances hébergeant le groupe de disponibilité autonome plutôt que directement au groupe de disponibilité autonome via l’écouteur, vous êtes dans l’environnement de l’instance et pas dans le groupe de disponibilité autonome.

Par exemple, si votre groupe de disponibilité MyContainedAG est hébergé sur le serveur SERVER\MSSQLSERVER et que, au lieu de vous connecter à l’écouteur MyContainedAG_Listener, vous vous connectez à l’instance à l’aide de SERVER\MSSQLSERVER, vous êtes dans l’environnement de l’instance et pas dans l’environnement de MyContainedAG. Cela signifie que vous êtes soumis au contenu (utilisateurs, autorisations, travaux, etc.) qui se trouvent dans les bases de données système de l’instance. Pour accéder au contenu trouvé dans les bases de données système autonomes du groupe de disponibilité autonome, connectez-vous à l’écouteur du groupe de disponibilité autonome (par exemple, MyContainedAG_Listener). Lorsque vous êtes connecté à l’instance via l’écouteur du groupe de disponibilité autonome, lorsque vous interagissez avec master, vous êtes en fait redirigé vers la base de données master autonome (par exemple, MyContainedAG_master).

Routage en lecture seule et groupes de disponibilité autonomes

Si vous configurez le routage en lecture seule pour rediriger les connexions avec l’intention de lecture vers un réplica secondaire (voir Configurer le routage en lecture seule pour un groupe de disponibilité Always On) et que vous souhaitez vous connecter à l’aide d’une connexion créée uniquement dans le groupe de disponibilité autonome, il existe quelques considérations supplémentaires :

• Vous devez spécifier une base de données qui fait partie du groupe de disponibilité autonome dans la chaîne de connexion
• L’utilisateur spécifié dans la chaîne de connexion doit avoir l’autorisation d’accéder aux bases de données du groupe de disponibilité autonome.

Par exemple, dans la chaîne de connexion suivante, où AdventureWorks une base de données dans le groupe de disponibilité autonome qui a MyContainedListener, et où MyUser est un utilisateur défini dans le groupe de disponibilité autonome et aucune des instances participantes :

"Persist Security Info=False;
User ID=MyUser;Password=*****;
Initial Catalog=AdventureWorks;
Server=MyContainedListener;
ApplicationIntent=ReadOnly"

Cette chaîne de connexion vous permet de vous connecter à la base de données secondaire lisible qui fait partie de la configuration du routage ReadOnly, et vous êtes dans le contexte du groupe de disponibilité autonome.

Différences entre la connexion à l’instance et la connexion au groupe de disponibilité autonome

• Lorsqu’ils sont connectés à un groupe de disponibilité autonome, les utilisateurs ne voient que les bases de données dans le groupe de disponibilité autonome, ainsi que tempdb.
• Au niveau de l’instance, les noms du groupe de disponibilité autonome master et msdb sont [contained AG]_master, et [contained AG]_msdb. Dans le groupe de disponibilité autonome, leurs noms sont master et msdb.
• L’ID de la base de données master pour le groupe de disponibilité autonome est 1 à partir du groupe de disponibilité autonome, mais il est différent en cas de connexion à l’instance.
• Bien que les utilisateurs ne voient pas les bases de données externes au groupe de disponibilité autonome dans sys.databases lorsqu’ils sont connectés avec une connexion au groupe de disponibilité autonome, ils peuvent accéder à ces bases de données par un nom en trois parties ou via la commande USE.
• La configuration du serveur via sp_configure peut être lue à partir de la connexion au groupe de disponibilité autonome, mais ne peut qu’être écrite niveau de l’instance.
• À partir de connexions au groupe de disponibilité autonomes, le sysadmin peut effectuer des opérations au niveau de l’instance, comme l’arrêt de SQL Server.
• La plupart des opérations au niveau de la base de données, au niveau du point de terminaison ou au niveau du groupe de disponibilité ne peuvent être effectuées qu’à partir de connexions à l’instance, et non de connexions au groupe de disponibilité autonome.

Interactions avec d’autres fonctionnalités

Il existe des considérations supplémentaires lors de l’utilisation de certaines fonctionnalités avec des groupes de disponibilité autonomes, et certaines fonctionnalités ne sont actuellement pas prises en charge.

Non pris en charge

Actuellement, les fonctionnalités SQL Server suivantes ne sont pas prises en charge avec un groupe de disponibilité autonome :

• Réplication SQL Server de n’importe quel type (transactionnelle, par fusion, via instantané, etc.).
• Groupes de disponibilité distribués.
• Copie des journaux de transaction où se trouve la base de données cible dans le groupe de disponibilité autonome. La copie des journaux de transaction avec la base de données source dans le groupe de disponibilité autonome est prise en charge.

Capture des données modifiées

La capture des changements de données (CDC) est implémentée en tant que travaux SQL Agent, de sorte que SQL Agent doit être en cours d’exécution sur toutes les instances avec des réplicas dans le groupe de disponibilité autonome.

Pour utiliser la capture des changements de données avec un groupe de disponibilité autonome, connectez-vous à l’écouteur du groupe de disponibilité lorsque vous configurez la capture des changements de données afin que les métadonnées de la capture des changements de données soient configurées à l’aide des bases de données système autonomes.

Copie des journaux de transaction

La copie des journaux de transaction peut être configurée si la base de données source se trouve dans le groupe de disponibilité autonome. Toutefois, une cible de copie des journaux de transaction n’est pas prise en charge dans un groupe de disponibilité autonome. En outre, il y a une étape supplémentaire pour modifier le travail de copie des journaux de transaction une fois la capture des changements de données configurée.

Pour configurer la copie des journaux de transaction avec un groupe de disponibilité autonome, procédez comme suit :

1. Connectez-vous à l’écouteur du groupe de disponibilité autonome.
2. Configurez la copie des journaux de transaction comme vous le feriez normalement.
3. Une fois le travail de copie des journaux de transaction configuré, modifiez le travail pour vous connecter à l’écouteur de groupe de disponibilité autonome avant d’effectuer une sauvegarde.

Chiffrement transparent des données (TDE)

Pour utiliser le chiffrement transparent des données (TDE) avec des bases de données dans un groupe de disponibilité autonome, installez manuellement la clé principale de base de données (DMK) dans la base de données master autonome au sein du groupe de disponibilité autonome.

Les bases de données qui utilisent le chiffrement transparent des données s’appuient sur des certificats de la base de données master pour déchiffrer la clé de chiffrement de base de données (DEK). Sans ce certificat, SQL Server ne peut pas déchiffrer les bases de données chiffrées avec le chiffrement transparent des données ou les mettre en ligne. Dans un groupe de disponibilité autonome, SQL Server vérifie la base de données master de la DMK, la base de données master de l’instance, et la base de données master autonome dans le groupe de disponibilité autonome pour déchiffrer la base de données. S’il ne trouve pas le certificat à l’un ou l’autre emplacement, SQL Server ne peut pas mettre la base de données en ligne.

Pour transférer la clé principale de base de données (DMK) de la base de données master de l’instance vers la base de données master autonome, consultez Déplacer une base de données protégée par le chiffrement transparent des données (TDE) vers un autre serveur SQL Server, en vous concentrant principalement sur les parties où la clé principale de base de données (DMK) est transférée de l’ancien serveur vers le nouveau.

Packages SSIS et plans de maintenance

L’utilisation des packages SSIS, y compris les plans de maintenance, n’est pas prise en charge avec les groupes de disponibilité autonomes.

Modifications DDL

Les seules modifications DDL sont dans le flux de travail CREATE AVAILABILITY GROUP. Il existe deux nouvelles clauses WITH :

<with_option_spec> ::=
CONTAINED |
REUSE_SYSTEM_DATABASES

CONTAINED

Cette mention indique que le groupe de disponibilité en cours de création doit être un groupe de disponibilité autonome.

REUSE_SYSTEM_DATABASES

Cette option est valide uniquement pour les groupes de disponibilité autonomes et spécifie que le groupe de disponibilité nouvellement créé doit réutiliser les bases de données système autonomes existantes pour un groupe de disponibilité autonome précédent du même nom. Par exemple, si vous aviez un groupe de disponibilité autonome portant le nom MyContainedAG et que vous souhaitez le supprimer et le recréer, vous pouvez utiliser cette option pour réutiliser le contenu des bases de données système autonomes d’origine.

Modifications DMV

Il y a deux ajouts aux DMV liés aux groupes de disponibilité autonomes :

• La sys.dm_exec_sessions DMV a une colonne ajoutée : contained_availability_group_id
• Une colonne sys.availability_groups a été ajoutée à la vue catalogue : is_contained

Contenu connexe

• CREATE AVAILABILITY GROUP (Transact-SQL)