Continuité d’activité et récupération de base de données - SQL Server

Article
04/26/2023

S’applique à : SQL Server 2016 (13.x) et versions ultérieures

Cet article fournit une vue d’ensemble des solutions de continuité d’activité dans le cadre de la haute disponibilité et de la récupération d’urgence dans SQL Server, sous Windows et Linux.

Quand vous déployez SQL Server, vous devez toujours vérifier que toutes les instances SQL Server critiques et les bases de données qu’elles contiennent sont disponibles pour l’entreprise et les utilisateurs finaux, quels que soient l’heure ou le jour. L’objectif est de maintenir l’activité avec un minimum d’interruption voire sans interruption. Ce concept est également connu sous le nom de continuité d’activité.

SQL Server 2017 (14.x) a introduit plusieurs nouvelles fonctionnalités ou améliorations de celles existantes, dont certaines concernent la disponibilité. La nouveauté la plus importante de SQL Server 2017 (14.x) était la prise en charge de SQL Server sur les distributions Linux. Pour obtenir la liste complète des nouvelles fonctionnalités de SQL Server, consultez les articles suivants :

Cet article traite en particulier des scénarios de disponibilité dans SQL Server 2017 (14.x) et les versions ultérieures, ainsi que des fonctionnalités de disponibilité nouvelles et améliorées. Les scénarios étudiés sont notamment les scénarios hybrides qui couvrent les déploiements de SQL Server sur Windows Server et Linux, et les scénarios capables d’augmenter le nombre de copies accessibles en lecture d’une base de données.

Cet article n’aborde pas les options de disponibilité externes à SQL Server, comme celles fournies par la virtualisation, mais tous les sujets traités ici s’appliquent aux installations de SQL Server sur une machine virtuelle invitée, qu’elle se trouve dans le cloud public ou soit hébergée par un serveur hyperviseur local.

Scénarios SQL Server utilisant les fonctionnalités de disponibilité

Les groupes de disponibilité Always On, les instances de cluster de basculement Always On et la copie des journaux de transaction peuvent être utilisés de différentes manières, et pas nécessairement uniquement à des fins de disponibilité. Les fonctionnalités de disponibilité sont utilisées dans quatre contextes principaux :

Haute disponibilité
Récupération d'urgence
Les migrations et les mises à niveau
La mise à plus haute échelle des copies accessibles en lecture d’une ou plusieurs bases de données

Les sections suivantes traitent des fonctionnalités appropriées pour un scénario en particulier. La seule fonctionnalité non abordée ici est la Réplication SQL Server. Même si elle n’est pas officiellement désignée comme une fonctionnalité de disponibilité dans le contexte AlwaysOn, elle est souvent utilisée pour la redondance des données dans certains scénarios. La réplication de fusion n’est pas prise en charge pour SQL Server sur Linux. Pour plus d’informations, consultez Réplication SQL Server sur Linux.

Important

Les fonctionnalités de disponibilité de SQL Server ne remplacent pas le besoin d’une stratégie de sauvegarde et de restauration robuste et correctement testée, qui est le fondement de toute solution de disponibilité.

Haute disponibilité

Il est important de s’assurer que les instances ou la base de données SQL Server soient disponibles en cas de problème local dans un centre de données ou dans une région du cloud. Cette section décrit comment les fonctionnalités de disponibilité de SQL Server peuvent aider à atteindre cet objectif. Toutes les fonctionnalités décrites sont disponibles aussi bien sur Windows Server que sur Linux.

Groupes de disponibilité

Les groupes de disponibilité (AG) ont été introduits dans SQL Server 2012 (11.X) pour fournir une protection au niveau de la base de données en envoyant chaque transaction de base de données à une autre instance (un réplica) qui contient une copie de cette base de données dans un état particulier. Un groupe de disponibilité peut être déployé sur les éditions Standard ou Entreprise. Les instances qui font partie d’un groupe de disponibilité peuvent être autonomes ou des instances de cluster de basculement (FCI, décrites dans la section suivante). Les transactions étant envoyées à un réplica à mesure qu’elles se produisent, les groupes de disponibilité sont recommandés quand il est nécessaire de baisser les objectifs de point de récupération et de délai de récupération. Le déplacement de données entre réplicas peut être synchrone ou asynchrone. L’édition Entreprise autorise jusqu'à trois réplicas synchrones (y compris le réplica principal). Un groupe de disponibilité contient une copie complète de la base de données accessible en écriture et en lecture qui se trouve sur le réplica principal. Les réplicas secondaires ne peuvent pas recevoir de transactions directement des utilisateurs finaux ou des applications.

Remarque

Always On est un terme général qui désigne les fonctionnalités de disponibilité dans SQL Server et inclut les groupes de disponibilité et les instances FCI. Always On n’est pas le nom de la fonctionnalité de groupe de disponibilité.

Avant SQL Server 2022 (16.x), les groupes de disponibilité fournissent uniquement une protection au niveau de la base de données et non une protection au niveau de l’instance. Tout ce qui n’est pas capturé dans le journal des transactions ou configuré dans la base de données doit être synchronisé manuellement sur chaque réplica secondaire. Exemples d’objets devant être synchronisés manuellement : connexions au niveau de l’instance, serveurs liés et travaux de SQL Server Agent.

À compter de SQL Server 2022 (16.x), vous pouvez gérer les objets de métadonnées, notamment les utilisateurs, les connexions, les autorisations et les travaux SQL Server Agent au niveau du groupe de disponibilité en plus du niveau de l’instance. Pour plus d’informations, consultez Groupe de disponibilité autonome.

Un groupe de disponibilité a également un autre composant qui est l’écouteur. Il permet aux applications et aux utilisateurs finaux de se connecter sans avoir besoin de connaître l’instance de SQL Server qui héberge le réplica principal. Chaque groupe de disponibilité dispose de son propre écouteur. Bien que les implémentations de l’écouteur soient légèrement différentes sur Windows Server et Linux, la fonctionnalité qu’il fournit et son utilisation sont identiques. Le diagramme ci-dessous montre un groupe de disponibilité Windows Server qui utilise un cluster de basculement Windows Server (WSFC). La disponibilité repose sur un cluster sous-jacent au niveau de la couche du système d’exploitation, aussi bien sur Linux que sur Windows Server. L’exemple montre une configuration simple à deux serveurs, ou nœuds, où le cluster sous-jacent est un cluster WSFC.

Diagramme d’un groupe de disponibilité simple.

Les éditions Standard et Entreprise prennent chacune en charge un nombre maximal de réplicas différent. Un groupe de disponibilité dans l’édition Standard, appelé groupe de disponibilité de base, prend en charge deux réplicas (un réplica principal et un secondaire) et une seule base de données dans le groupe de disponibilité. L’édition Entreprise permet non seulement de configurer plusieurs bases de données dans un seul AG, mais prend en charge également jusqu'à neuf réplicas au total (un réplica principal et huit secondaires). L’édition Entreprise fournit d’autres avantages comme des réplicas secondaires accessibles en lecture, la possibilité d’effectuer des sauvegardes d’un réplica secondaire, et bien plus encore.

Notes

La mise en miroir de bases de données, dépréciée dans SQL Server 2012 (11.x), n’est pas disponible sur la version Linux de SQL Server et ne sera pas ajoutée. Les clients qui utilisent encore la mise en miroir de bases de données doivent planifier la migration vers les AG, qui remplacent la mise en miroir de bases de données.

En matière de disponibilité, les groupes de disponibilité peuvent fournir un basculement automatique ou manuel. Un basculement automatique peut se produire si un déplacement de données synchrone est configuré et que la base de données est synchronisée sur les réplicas principal et secondaire. Si l’écouteur est utilisé et que l’application utilise une version plus récente du .NET Framework (version 3.5 avec une mise à jour, ou version 4.0 ou ultérieure), le basculement doit être géré avec peu ou aucun effet sur les utilisateurs finaux. Le basculement vers un réplica secondaire pour en faire un réplica principal peut être automatique ou manuel, et est généralement mesuré en secondes.

La liste ci-dessous souligne les différences entre les groupes de disponibilité sur Windows Server et Linux :

En raison du fonctionnement différent du cluster sous-jacent sur Linux et Windows Server, tous les basculements (manuels ou automatiques) des groupes de disponibilité sont effectués via le cluster sur Linux. Pour les déploiements de groupes de disponibilité de base Windows Server, les basculements manuels doivent être effectués via SQL Server. Les basculements automatiques sont gérés par le cluster sous-jacent sur Windows Server et Linux.
Pour SQL Server sur Linux, la configuration recommandée pour les groupes de disponibilité est un minimum de trois réplicas. Cela est lié au fonctionnement du clustering sous-jacent.
Sur Linux, le nom commun utilisé par chaque écouteur est défini dans le système DNS et non dans le cluster comme sur Windows Server.

À compter de SQL Server 2017 (14.x), les groupes de disponibilité bénéficient de nouvelles fonctionnalités et d’améliorations :

Types de cluster
REQUIRED_SECONDARIES_TO_COMMIT
Amélioration de la prise en charge de Microsoft Distributor Transaction Coordinator (DTC) pour les configurations basées sur Windows Server
Ajout de scénarios de scale-out pour les bases de données en lecture seule (décrits plus loin dans cet article)

Types de cluster des groupes de disponibilité

La forme de disponibilité intégrée de clustering dans Windows Server est activée via une fonctionnalité nommée Clustering de basculement. Cela vous permet de créer un cluster WSFC à utiliser avec un groupe de disponibilité ou une instance FCI. L’intégration des groupes de disponibilité et des instances FCI est fournie par des DLL de ressource adaptées aux clusters inclus dans SQL Server.

SQL Server sur Linux prend en charge plusieurs technologies de clustering. Microsoft prend en charge les composants SQL Server, tandis que nos partenaires prennent en charge la technologie de clustering appropriée. Par exemple, avec Pacemaker, SQL Server sur Linux prend en charge HPE Serviceguard et DH2i DxEnterprise en tant que solution de cluster.

Un cluster de basculement Windows et une solution de cluster Linux sont plus similaires que différents. Tous deux permettent de combiner des serveurs individuels dans une configuration pour assurer la disponibilité et utilisent des concepts comme les ressources, les contraintes (même si elles sont implémentées différemment), le basculement, etc.

Par exemple, pour prendre en charge Pacemaker dans les configurations de groupes de disponibilité et d’instances FCI (y compris le basculement automatique), Microsoft fournit le package mssql-server-ha pour Pacemaker, qui est similaire, mais pas exactement identique, aux DLL de ressource dans un cluster WSFC. Un cluster WSFC et Pacemaker se distinguent notamment par le fait qu’aucune ressource de nom de réseau n’est incluse dans Pacemaker, car celui-ci récupère le nom de l’écouteur (ou le nom de l’instance de cluster de basculement) sur un cluster WSFC. Le système DNS fournit cette résolution de nom sur Linux.

Parce que la pile de cluster est différente, les groupes de disponibilité sont modifiés, car SQL Server doit gérer une partie des métadonnées qui sont gérées en mode natif par un cluster WSFC. Un des changements importants est l’introduction d’un type de cluster pour un groupe de disponibilité. Il est stocké dans sys.availability_groups dans les colonnes cluster_type et cluster_type_desc. Il existe trois types de cluster :

WSFC
Externe
None

Tous les groupes de disponibilité nécessitant une haute disponibilité doivent utiliser un cluster sous-jacent, ce qui, dans le cas de SQL Server 2017 (14.x) et des versions ultérieures, signifie WSFC ou un agent de clustering Linux. Pour les groupes de disponibilité de base Windows Server qui utilisent un cluster WSFC sous-jacent, le type de cluster par défaut est WSFC et ne doit pas nécessairement être défini. Pour les groupes de disponibilité basés sur Linux, lors de la création du groupe de disponibilité, le type de cluster doit être défini sur Externe. L’intégration à une solution de cluster externe sous Linux est configurée après la création du groupe de disponibilité, tandis que sur un cluster WSFC, elle est effectuée au moment de la création.

Le type de cluster Aucun peut être utilisé avec les groupes de disponibilité Windows Server et Linux. Quand vous définissez le type de cluster sur Aucun, le groupe de disponibilité n’a pas besoin de cluster sous-jacent. Cela signifie que SQL Server 2017 (14.x) est la première version de SQL Server qui prend en charge les groupes de disponibilité sans cluster. Toutefois, cette configuration n’est pas prise en charge comme solution de haute disponibilité.

Important

À compter de SQL Server 2017 (14.x), vous ne pouvez pas modifier un type de cluster pour un groupe de disponibilité après sa création. Cela signifie qu’un groupe de disponibilité ne peut pas passer du type Aucun à Externe ou WSFC, et inversement.

Pour ceux qui veulent simplement ajouter des copies de base de données en lecture seule, ou qui sont intéressés par les options de migration ou de mise à niveau qu’offrent les groupes de disponibilité, mais ne veulent pas assumer la complexité de gérer un cluster sous-jacent ni même la réplication, un groupe de disponibilité avec le type de cluster Aucun est la solution idéale. Pour plus d’informations, consultez les sections Migrations et mises à niveau et Échelle lecture.

La capture d’écran ci-dessous montre la prise en charge des différents types de cluster dans SSMS (SQL Server Management Studio). Vous devez exécuter la version 17.1 ou ultérieure. La capture d’écran ci-dessous concerne la version 17.2.

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

Dans SQL Server 2016 (13.x), la prise en charge du nombre de réplicas synchrones est passée de deux à trois dans l’édition Entreprise. Toutefois, si un réplica secondaire était synchronisé, mais que l’autre rencontrait un problème, il n’existait aucun moyen de contrôler le comportement pour indiquer au réplica principal d’attendre le réplica concerné ou de continuer. Cela signifie que le réplica principal à un moment donné continue de recevoir du trafic en écriture, même si le réplica secondaire n’est pas synchronisé, ce qui implique une perte de données sur le réplica secondaire. À compter de SQL Server 2017 (14.x), vous pouvez contrôler le comportement de ce qui se passe lorsqu’il existe des réplicas synchrones avec REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT. Cette option fonctionne de la façon suivante :

Il existe trois valeurs possibles : 0, 1 et 2
La valeur est le nombre de réplicas secondaires qui doivent être synchronisés, ce qui a des implications sur la perte de données, la disponibilité du groupe de disponibilité et le basculement
Pour les clusters WSFC et un type de cluster Aucun, la valeur par défaut est 0 et peut être définie manuellement sur 1 ou 2
Pour un type de cluster Externe, par défaut, le mécanisme de cluster définit cette valeur qui peut être remplacée manuellement. Pour les trois réplicas synchrones, la valeur par défaut est égale à 1.

Sur Linux, la valeur de REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT est configurée sur la ressource de groupe de disponibilité dans le cluster. Sur Windows, elle est définie via Transact-SQL.

Une valeur supérieure à 0 garantit une protection de données plus élevée, car si le nombre de réplicas secondaires nécessaires n’est pas disponible, le réplica principal n’est pas disponible tant que le problème n’est pas résolu. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT affecte également le comportement de basculement, car le basculement automatique ne peut pas se produire si le nombre nécessaire de réplicas secondaires n’est pas dans l’état approprié. Sur Linux, la valeur 0 n’autorise pas le basculement automatique, par conséquent, quand vous utilisez des réplicas synchrones avec basculement automatique, la valeur doit êtes supérieure à 0 pour obtenir le basculement automatique. 0sur Windows Server est le comportement dans SQL Server 2016 (13.x) et versions antérieures.

Amélioration de la prise en charge de Microsoft Distributed Transaction Coordinator

Avant SQL Server 2016 (13.x), la seule façon d’assurer la disponibilité dans SQL Server pour les applications qui nécessitent des transactions distribuées utilisant DTC en arrière-plan était de déployer des instances FCI. Une transaction distribuée peut être effectuée de deux manières :

Une transaction qui s’étend sur plusieurs bases de données dans la même instance de SQL Server
Une transaction qui s’étend sur plusieurs instances de SQL Server ou qui implique éventuellement une source de données non-SQL Server

SQL Server 2016 (13.x) a introduit la prise en charge partielle de DTC avec les groupes de disponibilité (2ème scénario). SQL Server 2017 (14.x) complète la prise en charge dans les deux scénarios avec DTC.

Dans SQL Server 2017 (14.x) et les versions ultérieures, la prise en charge de DTC peut être ajoutée à un groupe de disponibilité également après sa création. Dans SQL Server 2016 (13.x), l’activation de la prise en charge de DTC à un groupe de disponibilité ne pouvait être effectuée qu’une fois le groupe de disponibilité créé.

Instances de cluster de basculement

Les installations en cluster sont une fonctionnalité de SQL Server depuis la version 6.5. Les instances FCI sont une méthode testée qui permet d’assurer la disponibilité d’une installation entière de SQL Server, c’est-à-dire une instance. Cela signifie que tout ce que contient l’instance, y compris les bases de données, les travaux de SQL Server Agent, les serveurs liés, etc., est déplacé sur un autre serveur si le serveur sous-jacent rencontre un problème. Toutes les instances FCI nécessitent une sorte de stockage partagé, même s’il est fourni sur un réseau. Les ressources d’une instance FCI peuvent uniquement être exécutées et détenues par un seul nœud à un moment donné. Dans le diagramme ci-dessous, le premier nœud du cluster possède l’instance FCI, ce qui signifie également qu’il possède les ressources de stockage partagé associées, représenté par la ligne continue vers le stockage.

Diagramme d'une instance de cluster de basculement.

Après un basculement, la propriété change, comme illustré dans le diagramme ci-dessous.

Diagramme d'une instance de cluster de basculement, après le basculement.

Il n’y a pas de perte de données avec une instance FCI, mais le stockage partagé sous-jacent est un point d’échec unique, car il n’y a qu’une seule copie des données. Les instances FCI sont souvent associées à une autre méthode de disponibilité, comme un groupe de disponibilité ou la copie des journaux de transaction, pour avoir des copies redondantes des bases de données. La méthode supplémentaire déployée doit utiliser un stockage séparé physiquement de l’instance FCI. Quand l’instance FCI bascule sur un autre nœud, elle s’arrête sur un nœud et démarre sur l’autre, ce qui a le même effet que si vous éteignez un serveur et le rallumez. Une instance FCI suit le processus de récupération normal, ce qui signifie que toutes les transactions qui doivent être restaurées par progression le sont, et toutes les transactions incomplètes sont annulées. Par conséquent, la base de données est cohérente par rapport à un point de données jusqu’au moment de l’échec ou du basculement manuel, il n’y a donc aucune perte de données. Les bases de données sont disponibles uniquement après la récupération, le temps de récupération dépend donc de plusieurs facteurs et est généralement plus long que le basculement sur un AG. L’inconvénient est que quand vous basculez un AG, des tâches supplémentaires peuvent être nécessaires pour que la base de données soit utilisable, comme l’activation d’un travail de SQL Server Agent.

Tout comme les AG, les instances FCI récupèrent le nœud du cluster sous-jacent qui les héberge. Une instance FCI conserve toujours le même nom. Les applications et les utilisateurs finaux ne se connectent jamais aux nœuds, c’est le nom unique attribué à l’instance FCI qui est utilisé. Une instance FCI peut participer à un groupe de disponibilité sous la forme d’une instance hébergeant un réplica principal ou secondaire.

La liste ci-dessous souligne les différences entre les instances FCI sur Windows Server et Linux :

Sur Windows Server, une instance FCI fait partie du processus d’installation. Une instance FCI sur Linux est configurée après l’installation de SQL Server.
Linux prend uniquement en charge une seule installation de SQL Server par hôte, toutes les instances FCI représentent donc l’instance par défaut. Windows Server prend en charge jusqu'à 25 instances FCI par cluster WSFC.
Le nom commun utilisé par les instances FCI dans Linux est défini dans le système DNS, et doit être identique à la ressource créée pour l’instance FCI.

Copie des journaux de transaction

Si les objectifs de point de récupération et de délai de récupération sont plus flexibles, ou que les bases de données ne sont pas considérées comme très critiques, la copie des journaux de transaction est une autre fonctionnalité de disponibilité qui a fait ses preuves dans SQL Server. Basé sur les sauvegardes natives de SQL Server, le processus de copie des journaux de transaction génère automatiquement des sauvegardes de fichier journal, les copie dans une ou plusieurs instances appelées secours semi-automatique, et applique automatiquement les sauvegardes du fichier journal à ce secours. La copie des journaux de transaction utilise les travaux de SQL Server Agent pour automatiser le processus de sauvegarde, de copie et d’application des sauvegardes du fichier journal.

Diagramme de la copie des journaux de transaction.

Le principal avantage de l’utilisation de la copie des journaux de transaction dans une capacité est sans doute qu’elle prend en compte l’erreur humaine. L’application des journaux de transactions peut être différée. Par conséquent, si un utilisateur envoie une commande de type UPDATE sans clause WHERE, le serveur de secours peut ne pas avoir pris en compte le changement et vous pouvez donc l’utiliser pendant que vous réparez le système principal. Bien que la copie des journaux de transaction soit facile à configurer, le basculement du réplica principal sur un secours semi-automatique, appelé changement de rôle, est toujours manuel. Un changement de rôle est lancé via Transact-SQL et, tout comme pour un AG, tous les objets qui ne sont pas capturés dans le journal des transactions doivent être synchronisés manuellement. Par ailleurs, la copie des journaux de transaction doit être configurée pour chaque base de données, tandis qu’un seul groupe de disponibilité peut contenir plusieurs bases de données.

Contrairement à un groupe de disponibilité ou à une instance FCI, la copie des journaux de transaction n’a pas d’abstraction pour un changement de rôle, que les applications doivent être en mesure de gérer. Des techniques comme l’alias DNS (CNAME) peuvent être utilisées, mais il existe des avantages et des inconvénients, par exemple, le temps que prend le système DNS pour l’actualisation après le basculement.

Récupération d'urgence

Quand votre emplacement de disponibilité principal subit un événement catastrophique comme un tremblement de terre ou une inondation, l’entreprise doit être préparée à mettre ses systèmes en ligne ailleurs. Cette section décrit comment les fonctionnalités de disponibilité de SQL Server peuvent aider à assurer la continuité de l’activité.