Configurer des instances dupliquées externes

Cette page explique comment configurer une instance Cloud SQL pour publier sur un abonné externe à Cloud SQL ou situé dans Cloud SQL. La réplication externe dans Cloud SQL pour SQL Server utilise la réplication transactionnelle, dans laquelle Cloud SQL agit en tant qu'éditeur pour un abonné.

La réplication transactionnelle permet de publier plusieurs types d'objets, comme documenté par Microsoft. En outre, dans Cloud SQL, les limites de cette fonctionnalité sont similaires à celles documentées par Microsoft.

Voici quelques-uns des objets compatibles :

  • Tables
  • Procédures stockées
  • Vues
  • Vues d'index
  • Fonctions définies par l'utilisateur

Bien que cette page fournisse des exemples de processus de publication, consultez la page Publier des données et des objets de base de données dans la documentation Microsoft pour plus de détails. Pensez à utiliser SQL Server Management Studio (SSMS) dans votre processus de publication, car vos options disponibles peuvent être plus évidentes dans SSMS.

Consultez également la section À propos de la réplication dans Cloud SQL.

Mettre en œuvre la réplication transactionnelle

Une instance Cloud SQL peut servir d'éditeur et de distributeur pour un abonné externe, via la réplication transactionnelle.

Pour configurer la réplication transactionnelle, vous pouvez :

  • Utilisez les procédures stockées fournies par Cloud SQL. Elles sont précédées du préfixe : gcloudsql_transrepl_
  • Affiner la réplication à l'aide des procédures stockées fournies par Microsoft

Limites et prérequis

Consultez cette section au moment de planifier la réplication transactionnelle.

Limites

Les instances qui utilisent la haute disponibilité n'ont pas d'adresse IP sortante cohérente. Ces instances ne peuvent pas être des éditeurs si elles utilisent une connectivité IP publique. Ainsi, si une instance utilise la haute disponibilité, elle doit utiliser une connectivité IP privée.

Pour découvrir comment définir un article, consultez la section Définir un article dans la documentation Microsoft, y compris sur les limites et les restrictions.

Les procédures stockées fournies par Cloud SQL ne sont compatibles qu'avec les abonnements push.

Prérequis

La connectivité réseau bidirectionnelle doit être configurée entre une instance Cloud SQL et l'instance d'abonné. L'abonné peut être externe (par exemple, un abonné sur site) ou interne à Cloud SQL.

Pour les instances Cloud SQL utilisant des adresses IP publiques, Cloud SQL utilise une adresse IP différente sur ses chemins de sortie et d'entrée. L'instance dupliquée doit ajouter l'adresse IP sortante de l'instance principale à la liste d'autorisation, que vous pouvez récupérer à l'aide de la commande gcloud :

gcloud sql instances describe [PRIMARY_NAME] --format="default(ipAddresses)"

Pour utiliser l'adresse IP privée de Cloud SQL avec une instance sur site, vous devez configurer l'accès aux services privés. Cela nécessite un appairage entre le VPC Cloud SQL et le VPC du client sur une plage d'adresses IP personnalisée qui doit être annoncée.

Lors de la connexion à partir d'un environnement sur site, le pare-feu sur site doit autoriser les connexions entrantes et sortantes. Plus précisément, le pare-feu sur site doit autoriser ces connexions sur le port 1433 à la plage de sous-réseau d'accès aux services privés utilisée pour le service Google Cloud spécifique (dans ce cas, pour Cloud SQL). Envisagez d'autoriser une plage de sous-réseaux plutôt qu'une adresse IP spécifique pour chaque instance créée.

Pour en savoir plus, consultez les ressources suivantes :

Autorisations et rôles

Autorisations intégrées

Les procédures stockées fournies par Cloud SQL incluent les autorisations nécessaires pour la réplication transactionnelle. Il s'agit de procédures stockées dans le wrapper qui appellent parfois une ou plusieurs procédures stockées Microsoft. Pour en savoir plus sur les procédures stockées dans Microsoft, consultez la documentation Microsoft.

Rôle requis

Les comptes utilisés (y compris pour l'agent de lecture des journaux) doivent disposer d'un rôle db_owner, comme dans le cas de l'utilisateur sqlserver. Pour en savoir plus, consultez le modèle de sécurité de l'agent de réplication.

Utiliser des procédures stockées pour la publication, la distribution et l'abonnement

Cette section contient des étapes de configuration de la réplication transactionnelle.

L'utilisateur sqlserver a stocké des procédures permettant de configurer votre instance Cloud SQL pour qu'elle agisse en tant qu'éditeur. Pour plus d'informations, consultez la page Procédures stockées dans Cloud SQL.

Préparer la réplication transactionnelle avec une base de données de test

Avant de configurer la réplication transactionnelle pour une base de données de production, vous pouvez configurer votre instance en tant qu'éditeur d'objets de base de données de test. Sur cette page, la base de données de test est appelée base de données pub_demo.

Connectez-vous à votre instance Cloud SQL à l'aide de l'utilisateur sqlserver et créez une base de données à des fins de test. Exemple :

Create Database pub_demo;
GO
USE pub_demo;
CREATE TABLE Employee(employeeId INT primary key);
INSERT INTO Employee([employeeId]) VALUES (1);
INSERT INTO Employee([employeeId]) VALUES (2);

-- Add procedure
CREATE OR ALTER PROCEDURE dbo.p_GetDate
AS
SELECT getdate()

-- Add view
CREATE OR ALTER VIEW dbo.v_GetDbs
AS
SELECT name from sys.databases

-- Function
CREATE OR ALTER FUNCTION dbo.fn_ListDbFiles(@id int)
RETURNS TABLE
AS
RETURN
(
select * from sys.master_files where database_id = @id
)

Configurer la base de données de distribution

Pour la base de données de distribution, vous pouvez utiliser msdb.dbo.gcloudsql_transrepl_setup_distribution, qui est une procédure stockée pour le wrapper pour ces procédures stockées Microsoft :

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_setup_distribution @login='sqlserver', @password='<password>'

Activer une base de données pour la publication

Pour activer ou désactiver l'option de publication d'une base de données, vous pouvez utiliser msdb.dbo.gcloudsql_transrepl_replicationdboption. Cette procédure stockée s'applique à l'option de publication de l'éditeur qui utilise sp_replicationdboption.

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption @db='pub_demo', @value='true'

Ajouter un agent de lecteur de journaux

Vous pouvez configurer un agent de lecteur de journaux pour une base de données qui utilise sp_addlogreader_agent.

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_addlogreader_agent @db='pub_demo', @login='sqlserver', @password='<password>'

Créer la publication pour la base de données

Vous pouvez utiliser msdb.dbo.gcloudsql_transrepl_addpublication afin de créer une publication transactionnelle pour la base de données que vous spécifiez. Cette procédure stockée encapsule sp_addpublication.

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_addpublication @db='pub_demo', @publication='pub1'

Créer un agent d'instantané pour la publication spécifiée

Pour créer un agent d'instantané pour la base de données de l'éditeur, vous pouvez utiliser msdb.dbo.gcloudsql_transrepl_addpublication_snapshot, qui encapsule sp_addpublication_snapshot.

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_addpublication_snapshot @db='pub_demo',  @publication='pub1', @login='sqlserver', @password='<password>'

Créer un article et l'ajouter à la publication

Vous pouvez créer un article à partir de la base de données de l'éditeur et l'ajouter à la publication. En tant qu'utilisateur sqlserver, utilisez sp_addarticle.

Vous pouvez également ajouter des articles utilisant SSMS. Pour en savoir plus, consultez la page Ajouter et supprimer des articles d'une publication.

Exemple :

USE pub_demo;
GO

EXEC sp_addarticle @publication = 'pub1',
                   @article = 'csql_dbo.employee',
                   @source_owner = 'dbo',
                   @source_object = 'Employee',
                   @description = N'cloudsql_article_table',
                   @schema_option = 0x000000000903409D,
                   @identityrangemanagementoption = N'manual',
                   @destination_table = 'Employee',
                   @destination_owner = 'dbo';

-- add function
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'fn_ListDbFiles',
                   @source_owner = N'dbo',
                   @source_object = N'fn_ListDbFiles',
                   @type = N'func schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'fn_ListDbFiles',
                   @destination_owner = N'dbo',
                   @status = 16

-- add procedure
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'p_GetDate',
                   @source_owner = N'dbo',
                   @source_object = N'p_GetDate',
                   @type = N'proc schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'p_GetDate',
                   @destination_owner = N'dbo',
                   @status = 16

-- add view
use [pub_demo]
exec sp_addarticle  @publication = N'pub1',
                   @article = N'v_GetDbs',
                   @source_owner = N'dbo',
                   @source_object = N'v_GetDbs',
                   @type = N'view schema only',
                   @description = N'',
                   @creation_script = N'',
                   @pre_creation_cmd = N'drop',
                   @schema_option = 0x0000000008000001,
                   @destination_table = N'v_GetDbs',
                   @destination_owner = N'dbo',
                   @status = 16

Ajouter l'abonnement à la publication

À partir de la base de données, vous pouvez ajouter l'abonnement à la publication. En tant qu'utilisateur sqlserver, définissez l'état de l'abonné à l'aide de sp_addsubscription.

Exemple :

Use pub_demo;
GO
EXEC sp_addsubscription @publication ='pub1',
                        @subscriber = N'10.10.100.1,1433',
                        @destination_db = pub_demo,
                        @subscription_type = N'Push',
                        @sync_type = N'automatic',
                        @article = N'all',
                        @update_mode = N'read only',
                        @subscriber_type = 0

Se connecter à l'abonné et créer une base de données d'abonnement

Vous pouvez vous connecter à l'abonné et créer une base de données d'abonnements pour que les données répliquées soient renseignées.

Exemple :

 Create Database pub_demo

Ajouter une tâche d'agent planifié pour synchroniser l'abonnement push

Vous pouvez ajouter une tâche d'agent planifié pour synchroniser l'abonnement push avec la publication. Par exemple, sur la base de données de l'éditeur, exécutez une commande semblable à celle ci-dessous. Cette commande utilise msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent, une procédure stockée pour sp_addpushsubscription_agent :

EXEC msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent
@db='pub_demo',
@publication = 'pub1',
@subscriber_db= 'pub_demo',
@subscriber_login='sqlserver',
@subscriber_password='<password>',
@subscriber='11.11.111.1,1433'

Démarrer une tâche d'agent de création d'instantanés

Vous pouvez démarrer une tâche d'agent d'instantané de publication, comme suit :

USE pub_demo;
EXEC sp_startpublication_snapshot
@publication = 'pub1'

Accorder l'accès à un compte pour utiliser l'outil de surveillance des réplications

Utiliser msdb.dbo.gcloudsql_transrepl_addmonitoraccess pour :

  • Fournir l'accès à l'outil de surveillance des réplications dans SSMS
  • Interroger des tables dans la base de données de distribution

Ainsi, cette procédure stockée vous permet d'utiliser l'instruction SELECT sur les tables liées à la réplication de la base de données de distribution, telles que la table MSrepl_errors :

EXEC msdb.dbo.gcloudsql_transrepl_addmonitoraccess
@login = 'sqlserver'

Modifier une propriété de base de données de distribution

Vous pouvez modifier heartbeat_interval. Utilisez la procédure msdb.dbo.gcloudsql_transrepl_changedistributor_property, qui encapsule sp_changedistributor_property.

Pour en savoir plus, consultez la documentation de sp_changedistributor_property. Consultez également cette documentation pour en savoir plus sur la valeur heartbeat_interval.

Exemple :

EXEC msdb.dbo.gcloudsql_transrepl_changedistributor_property
@property = N'heartbeat_interval',
@value = 90

Utiliser l'outil de surveillance des réplications

Effectuez un clic droit sur le nœud de réplication dans SSMS, puis choisissez Lancer l'outil de surveillance des réplications.

Si vous cliquez sur l'onglet "Agents", vous devriez voir une vue semblable à celle-ci :

Dossiers dans l&#39;onglet &quot;Agents&quot;

Utiliser des procédures stockées pour supprimer la réplication

Cette section présente des suggestions de suppression de la réplication transactionnelle.

Supprimer l'abonnement

Pour supprimer l'abonnement, utilisez la procédure stockée sp_dropsubscription.

Voici un exemple de commandes permettant de supprimer l'abonnement :

USE  pub_demo;
GO
EXEC sp_dropsubscription
          @publication = 'csql_pub_pub_demo',
          @article     = N'all',
          @subscriber  = N'11.11.111.1,1433'

Supprimer l'abonné

Pour supprimer l'abonné, utilisez la procédure stockée msdb.dbo.gcloudsql_transrepl_dropsubscriber :

EXEC msdb.dbo.gcloudsql_transrepl_dropsubscriber
 @subscriber  = N'11.11.111.1,1433'

Supprimer la publication

Pour supprimer la publication, utilisez la procédure stockée msdb.dbo.gcloudsql_transrepl_droppublication :

EXEC msdb.dbo.gcloudsql_transrepl_droppublication
  @db = 'pub_demo', @publication='pub1'

Désactiver la base de données de publication

Pour désactiver la base de données de publication, utilisez la procédure stockée msdb.dbo.gcloudsql_transrepl_replicationdboption :

EXEC msdb.dbo.gcloudsql_transrepl_replicationdboption
@db='pub_demo',
@value=N'false'

Supprimer la base de données de distribution

Pour supprimer la base de données de distribution, utilisez la procédure stockée msdb.dbo.gcloudsql_transrepl_remove_distribution :

EXEC msdb.dbo.gcloudsql_transrepl_remove_distribution

Conseils et étapes pour résoudre les problèmes

Exécuter des procédures stockées à partir de la base de données appropriée

Vous pouvez obtenir l'erreur suivante lors de l'exécution de sp_addarticle, sp_addsubscription ou sp_startpublication_snapshot :

Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation.

De plus, vous pouvez obtenir l'erreur suivante lors de l'exécution de sp_dropsubscription :

This database is not enabled for publication.

Si de telles erreurs se produisent, notez que vous devez exécuter ces procédures stockées à partir de la base de données à laquelle elles s'appliquent. Vous pouvez utiliser les éléments suivants pour vérifier qu'ils sont exécutés à partir de la base de données appropriée :

USE <database_name>;
GO
<Run stored proc>

Réplication

Les erreurs de réplication sont fournies dans les journaux d'erreurs SQL et ailleurs. Vous pouvez interroger directement certaines tables de la base de données de distribution pour rechercher les erreurs de réplication. Exemple :

select * from [cloudsql_distribution]..msrepl_errors
select * from [cloudsql_distribution]..MSreplication_monitordata

Microsoft contient d'autres exemples sur la recherche d'erreurs à l'aide de l'outil de réplication de réplication. Par exemple, l'utilisateur sqlserver peut ne pas avoir accès à une commande.

Tâches de l'agent de réplication

Une fois la réplication configurée, les nouvelles tâches de l'agent SQL ne sont pas visibles dans SSMS pour l'utilisateur sqlserver. Toutefois, vous pouvez les afficher en procédant comme suit :

USE msdb
select * from dbo.sysjobs

Éditeurs manquants dans l'outil de surveillance des réplications

Vous pouvez utiliser l'outil de surveillance des réplications pour afficher l'état de la réplication et résoudre les problèmes de réplication.

Par exemple, lorsque vous configurez la réplication et que votre instance Cloud SQL d'éditeur utilise une adresse IP, SSMS risque de ne pas trouver l'éditeur. En effet, il ne connaît pas le mappage entre le nom d'hôte et l'adresse IP.

L'outil de surveillance des réplications contient un onglet "Publications" vide :

Aucune ligne n&#39;apparaît dans l&#39;onglet &quot;Publications&quot;

Pour contourner ce problème, vous pouvez créer des alias dans le gestionnaire de configuration SQL Server entre le nom d'hôte SQL Server de l'éditeur et le Adresse IP utilisée pour se connecter à partir de SSMS :

  1. Démarrez le gestionnaire de configuration SQL Server.

    Gestionnaire de configuration de SQL Server

  2. Recherchez le nœud Alias et sélectionnez-le.

    Le nœud d&#39;alias est sélectionné

  3. Effectuez un clic droit sur la zone située sous Nom de l'alias pour créer un alias. La même procédure s'applique à un alias de 32 bits et à un alias de 64 bits :

    Boîte déroulante sous &quot;Nom de l&#39;alias&quot;

  4. Récupérez le nom d'hôte réel de votre instance d'éditeur en utilisant la requête suivante :

    Demandez le nom d&#39;hôte réel de votre instance d&#39;éditeur

  5. Dans la fenêtre d'alias, saisissez les champs suivants avant de sélectionner OK :

    Nom de l'alias : indiquez le nom du serveur de la requête à l'étape 4.

    Numéro de port : indiquez le port 1433.

    Protocole : conservez la valeur par défaut de TCP/IP.

    Serveur : indiquez l'adresse IP de l'instance de l'éditeur.

    Valeurs du nom d&#39;alias, du serveur, etc.

  6. Connectez-vous à l'aide du nouvel alias et lancez l'outil de réplication des réplications :

    Boîte de dialogue &quot;Se connecter au serveur&quot;

Les informations de publication doivent ressembler à ce qui suit :

Une surveillance de la réplication affiche désormais une ligne dans l&#39;onglet &quot;Publications&quot;

Pour en savoir plus sur le dépannage de la réplication, consultez la page Résoudre les problèmes liés à la réplication transactionnelle de SQL Server.

Estimer la taille des articles nécessaires à la réplication

Lorsque vous utilisez une instance Cloud SQL en tant qu'éditeur, un instantané initial des articles à générer est nécessaire pour commencer la réplication. Cet instantané est stocké localement. Selon le nombre d'articles, leur taille et le type de données, les besoins en stockage peuvent augmenter. La procédure stockée sp_spaceused ne fournit qu'une estimation approximative de l'espace disque nécessaire à un article.

L'instantané inclut des fichiers qui stockent des schémas et des données.

Étape suivante