Articles

SQLShack

by admin

Le volume de données conservées, gérées et consultées aujourd’hui est sans précédent. Les entreprises s’attendent à ce que le service informatique maintienne les données en ligne et accessibles indéfiniment, ce qui exerce une pression intense sur les bases de données nécessaires pour les stocker et les gérer. Pour répondre aux besoins actuels, nous devons remplacer les processus hérités obsolètes et inefficaces par de nouvelles techniques plus agiles. La réplication SQL Server est l’une des techniques permettant de répondre à de telles demandes.

  • Remarque: Pour en savoir plus sur la réplication de bases de données, veuillez lire l’article Different Stakeholders, Different Views: Why Database Management Requires a Systematic Approach.

Dans cet article, nous allons vous aider à comprendre la topographie complète de la réplication de SQL Server, y compris les composants, les internes et le SQL, afin de tout lier ensemble. Après avoir terminé la lecture de cet article, vous comprendrez:

  • Réplication SQL Server, en général
  • Composants de la réplication transactionnelle SQL Server, en particulier
  • Comment obtenir les propriétés du distributeur
  • Comment trouver l’éditeur en utilisant le même distributeur
  • Quelles sont les bases de données utilisées pour la réplication SQL Server
  • La topologie générale d’un environnement de réplication
  • Quels sont les articles qui sont mappés au type de modèle de réplication SQL Server
  • Comment obtenir détails de la publication
  • Comment obtenir les détails de l’abonnement
  • Agents de réplication SQL Server
  • Et plus

Réplication

La réplication SQL Server est une technologie permettant de copier et de distribuer des données et des objets de base de données d’une base de données à une autre, puis de synchroniser entre les bases de données pour maintenir la cohérence et l’intégrité des données. Dans la plupart des cas, la réplication est un processus de reproduction des données aux cibles souhaitées. La réplication SQL Server est utilisée pour copier et synchroniser les données en continu ou elle peut également être planifiée pour s’exécuter à des intervalles prédéterminés. Il existe plusieurs techniques de réplication différentes qui prennent en charge diverses approches de synchronisation des données, unidirectionnelles, un-à-plusieurs, plusieurs-à-un et bidirectionnelles, et permettent de synchroniser plusieurs ensembles de données les uns avec les autres.

Composants de réplication SQL Server transactionnels

Le diagramme suivant représente les composants de la réplication SQL Server transactionnelle.

Y compris la réplication SQL Server …

  • Éditeur
  • Base de données de publication
  • Publication
  • Articles
  • Distributeur
  • Base de données de distribution
  • Abonné
  • Base de données d’abonnement
  • Abonnement
  • Agents de réplication

Diagramme de réplication SQL Server

Article

Un article est l’unité de base de la réplication SQL Server. Un article peut être constitué de tables, de procédures stockées et de vues. Il est possible de mettre à l’échelle l’article, horizontalement et verticalement à l’aide d’une option de filtre. Nous pouvons également créer plusieurs articles sur le même objet avec certaines restrictions et limitations.

À l’aide de l’assistant de nouvelle publication, l’article peut être navigué. Il nous permet d’afficher les propriétés d’un article et de fournir des options pour définir les propriétés des articles. Dans certains cas, les propriétés peuvent être définies au moment de la création de la publication et c’est une propriété en lecture seule.

Après la création d’une publication de réplication SQL Server, par exemple, si une propriété nécessite une modification, elle nécessitera à son tour la génération d’un nouvel instantané de réplication. Si la publication a un ou plusieurs abonnements, la modification nécessite la réinitialisation de tous les abonnements. Pour plus d’informations, consultez Comment ajouter/supprimer des articles vers/depuis une publication existante dans un article SQL Server.

Pour lister tous les articles publiés, exécutez le T-SQL suivant

1
2
3
4
5
6
7
8
9
10
11
12
SÉLECTIONNEZ
Pub.
, art.
, art.
, art.
, art.
DE la
.. Art
JOINTURE INTÉRIEURE.. Pub
SUR L’art. = Pub.
ORDER BY
Pub., Art.

To get the details of articles in transactional or merge SQL Server replication in a published database, run the following T-SQL.

1
2
3
4
5
6
7
8
SELECT st.name , st.schema_id, st.is_published , st.is_merge_published, is_schema_published
DE sys.tables st OÙ st.is_published = 1 ou st.is_merge_published = 1 ou st.is_schema_published= 1
UNION
SÉLECTIONNER sp.name , sp.schema_id, 0, 0, sp.is_schema_published
DE sys.procédures sp OÙ sp.is_schema_published = 1
UNION
SÉLECTIONNEZ sv.name , sv.schema_id, 0, 0, sv.is_schema_published
DE sys.vues sv OÙ sv.is_schema_published = 1;

To get detailed information about an article in the listed publisher, run the following T-SQL

1
2
3
4
5
6
7
DECLARE @publication AS sysname;
SET @publication = N’PROD_HIST_Pub’;
USE MES_PROD_AP
EXEC sp_helparticle
@publication = @publication;
GO

To get column level details, run the following T-SQL

1
2
3
USE MES_PROD_AP
GO
sp_helparticlecolumns @publication = N’PROD_HIST_Pub’ , @article = ‘tb_Branch_Plant’

iv pour lister les colonnes publiées dans la réplication transactionnelle dans la base de données de publication, exécutez le T-SQL suivant

1
SÉLECTIONNEZ object_name(object_id), nom DE sys.colonnes sc OÙ sc.is_replicated = 1;

Publications

Une publication est une collection logique d’articles d’une base de données. L’entité nous permet de définir et de configurer les propriétés de l’article au niveau supérieur afin que les propriétés soient héritées de tous les articles de ce groupe.

1
EXEC sp_helppublication;

Base de données Publisher

L’éditeur est une base de données qui contient une liste d’objets désignés comme réplication SQL Server les articles sont connus sous le nom de base de données de publication. L’éditeur peut avoir une ou plusieurs publications. Chaque éditeur définit un mécanisme de propagation des données en créant plusieurs procédures stockées de réplication internes.

1
2
3
USE Distribution
GO
select * from MSpublications

Publisher

The Publisher is a database instance that makes data available to other locations through SQL Server replication. L’Éditeur peut avoir une ou plusieurs publications, chacune définissant un ensemble d’objets et de données logiquement liés à répliquer.

Distributeur

Le Distributeur est une base de données qui sert d’entrepôt pour la réplication de données spécifiques associées à un ou plusieurs éditeurs. Dans de nombreux cas, le distributeur est une base de données unique qui agit à la fois en tant qu’Éditeur et en tant que Distributeur. Dans le contexte de la réplication de SQL Server, il s’agit généralement d’un  » distributeur local « . D’un autre côté, s’il est configuré sur un serveur séparé, il est connu sous le nom de « distributeur distant ». Chaque Éditeur est associé à une base de données unique appelée  » base de données de distribution  » ou  » Distributeur « .

La base de données de distribution identifie et stocke les données d’état de réplication SQL Server, les métadonnées relatives à la publication et, dans certains cas, sert de file d’attente pour les données transférées de l’Éditeur aux Abonnés.

Selon le modèle de réplication, le Distributeur peut également être responsable d’informer les Abonnés abonnés à une publication qu’un article a été modifié. En outre, la base de données de distribution maintient l’intégrité des données.

Bases de données de distribution

Chaque Distributeur doit avoir au moins une base de données de distribution. La base de données de distribution se compose de détails d’articles, de méta-données de réplication et de données. Un Distributeur peut détenir plus d’une base de données de distribution ; cependant, toutes les publications définies sur un seul Éditeur doivent utiliser la même base de données de distribution.

Pour savoir si …

  • un serveur est un distributeur ou non ?

    1
    SELECT @@ServerName Servername, case when is_distributor=1 then ‘Yes’ else ‘No’ end status FROM sys.servers WHERE name=’repl_distributor’ AND data_source=@@servername;

  • a distribution database installed or not?

    1
    SELECT name FROM sys.databases WHERE is_distributor = 1

  • a Publisher is using this Distributor or not?

    1
    EXEC sp_get_distributor

  • or just to interrogate various Distributor and Distribution database properties?

    1
    2
    3
    EXEC sp_helpdistributor;
    EXEC sp_helpdistributiondb;
    EXEC sp_helpdistpublisher;

Subscriber

A database instance that consumes SQL Server replication data from a publication is called a Subscriber. L’abonné peut recevoir des données d’un ou plusieurs éditeurs et publications. L’abonné peut également transmettre les modifications de données à l’éditeur ou republier les données à d’autres abonnés en fonction du type de conception et de modèle de réplication.

1
EXEC sp_helpsubscriberinfo;

Abonnements

Un abonnement est une demande de remise d’une copie d’une publication à un Abonné. L’abonnement définit quelles données de publication seront reçues, où et quand.

Il existe deux types d’abonnements : les abonnements push et les abonnements pull

  • Abonnement push : Le distributeur met directement à jour les données dans la base de données des abonnés
  • Abonnement Pull: l’Abonné doit vérifier régulièrement chez le distributeur si de nouvelles modifications sont disponibles, puis met à jour les données dans la base de données d’abonnement elle-même.
1
EXEC sp_helpsubscription;

Bases de données d’abonnement

Une base de données cible d’un modèle de réplication est appelée base de données d’abonnement.

Agents de réplication

La réplication SQL Server utilise un ensemble prédéfini de programmes autonomes et les événements sont appelés agents, pour effectuer les tâches associées aux données. Par défaut, les agents de réplication SQL Server s’exécutent en tant que tâches planifiées sous l’agent SQL Server. Les agents de réplication peuvent également être exécutés à partir de la ligne de commande et par des applications qui utilisent des objets de gestion de réplication (RMO). Les agents de réplication SQL Server peuvent être surveillés et administrés à l’aide de Replication Monitor et de SQL Server Management Studio.

Agent d’instantané de réplication

L’Agent d’instantané de réplication est utilisé avec tous les types de technologie de réplication SQL Server car il fournit l’ensemble de données requis pour effectuer la synchronisation initiale des données de la base de données de publication avec la base de données d’abonnement. Il prépare le schéma et les données initiales des articles publiés, des fichiers d’instantanés et enregistre des informations sur le type de synchronisation dans la base de données de distribution.

Agent de lecture de journaux

L’Agent de lecture de journaux est utilisé uniquement avec la réplication transactionnelle. Il déplace les transactions de réplication du journal des transactions en ligne de la base de données de publication vers la base de données de distribution.

Agent de distribution

L’Agent de distribution est utilisé uniquement avec l’instantané de réplication et la réplication transactionnelle de SQL Server. Cet agent applique l’instantané de réplication initial à la base de données d’abonnement et, ultérieurement, les modifications de données sont suivies et enregistrées dans la base de données de distribution et appliquées à la base de données d’abonnement.

Agent de fusion

L’Agent de fusion est utilisé avec le modèle de réplication de fusion. Par défaut, l’Agent de fusion télécharge les modifications de l’Abonné vers l’Éditeur, puis télécharge les modifications de l’Éditeur vers l’Abonné. Chaque abonnement a son propre agent de fusion qui se connecte à la fois à l’Éditeur et à l’Abonné et met à jour les deux. L’agent de fusion s’exécute au distributeur pour les abonnements push ou à l’Abonné pour les abonnements pull. Ici, la synchronisation est bidirectionnelle. Les conflits de données sont gérés par un ensemble de déclencheurs qui prend en charge l’ensemble du processus

Résumé

Jusqu’à présent, nous avons vu un aperçu de certains des concepts importants de la réplication SQL Server. En outre, les scripts T-SQL sont présentés pour interroger les tables système et les procédures stockées de réplication pour répondre à la plupart des questions fréquemment posées sur la réplication de SQL Server.

Je discuterai plus en détail de la réplication SQL Server dans les prochains articles. Si vous pensez que quelque chose peut être amélioré dans cet article, n’hésitez pas à laisser votre commentaire ci-dessousTable

Table des matières

Réplication SQL Server: Aperçu des composants et de la topographie

Réplication SQL: Configuration et configuration de base

Comment Ajouter / supprimer des articles de publications existantes dans SQL Server

Comment comparer rapidement les données dans deux grandes bases de données SQL Server pour voir si elles sont égales

Réplication transactionnelle SQL Server: Comment réinitialiser un abonnement à l’aide d’une sauvegarde de base de données SQL Server

Comment configurer un modèle de réplication de transaction SQL Server personnalisé avec un abonné central et plusieurs bases de données d’éditeurs

Comment configurer une réplication transactionnelle SQL Server personnalisée avec un éditeur central et plusieurs bases de données d’abonnés

Comment configurer une solution de réplication transactionnelle de base de données SQL Server DDL et DML

Comment configurer une réplication transactionnelle multi-plateforme SQL Server pour les rapports de base de données sous Linux

Migrations de bases de données SQL Server sans perte de données et zéro temps d’arrêt

Utilisation de la réplication de données transactionnelles pour rejouer et tester les charges de production sur un serveur intermédiaire

Comment configurer la réplication de base de données SQL Server pour un serveur de rapports

Réplication transactionnelle SQL Server: Comment réinitialiser un abonnement à l’aide d’un « support de réplication uniquement » – TBA

Surveillance de la réplication SQL Server et définition d’alertes à l’aide de PowerShell–TBA

  • Auteur
  • Messages récents
Prashanth Jayaram
Je suis un technologue de base de données ayant plus de 11 ans d’expérience pratique et riche sur les technologies de base de données. Je suis un Professionnel certifié Microsoft et soutenu par un diplôme en Master d’Application Informatique.
Ma spécialité réside dans la conception de & implémentant des solutions haute disponibilité et une migration de base de données multiplateforme. Les technologies sur lesquelles travaillent actuellement sont SQL Server, PowerShell, Oracle et MongoDB.
Voir tous les messages de Prashanth Jayaram

Prashanth Jayaram
Derniers messages de Prashanth Jayaram (tout voir)
  • Un aperçu rapide de l’audit de base de données en SQL – 28 janvier 2021
  • Comment configuration de la synchronisation des données Azure entre les bases de données SQL Azure et SQL Server sur site – 20 janvier 2021
  • Comment effectuer des opérations d’importation/exportation de bases de données SQL Azure à l’aide de PowerShell – 14 janvier 2021