Concepts liés aux procédures stockées système de réplication
S’applique à :
SQL Server Azure SQL Managed Instance
Dans SQL Server, l’accès programmatique à toutes les fonctionnalités configurables par l’utilisateur dans une topologie de réplication est fourni par les procédures stockées système. Bien que les procédures stockées puissent être exécutées individuellement à l’aide de SQL Server Management Studio ou de l’utilitaire de ligne de commande sqlcmd, il peut être utile d’écrire des fichiers de script Transact-SQL qui peuvent être exécutés pour effectuer une séquence logique de tâches de réplication.
La génération de scripts pour des tâches de réplication offre les avantages suivants :
conservation d'une copie définitive des étapes utilisées pour déployer votre topologie de réplication ;
utilisation d'un même script pour configurer plusieurs abonnés ;
formation rapide des nouveaux administrateurs de base de données en leur permettant d'évaluer, de comprendre et de modifier le code, ou de résoudre des problèmes liés au code.
Important
Les scripts peuvent causer des failles de sécurité ; ils peuvent appeler des fonctions système sans que l'utilisateur le sache ou intervienne. En outre, ils sont susceptibles de contenir des informations d'identification de sécurité sous forme de texte brut. Examinez les scripts pour détecter d'éventuels problèmes de sécurité avant de les utiliser.
Création de scripts de réplication
Du point de vue de la réplication, un script est une série d’une ou plusieurs instructions Transact-SQL où chaque instruction exécute une procédure stockée de réplication. Les scripts sont des fichiers texte, souvent dotés d'une extension de fichier .sql, qui peuvent être exécutés à l'aide de l'utilitaire sqlcmd. Lorsqu'un fichier de script est exécuté, l'utilitaire exécute les instructions SQL stockées dans le fichier. De même, un script peut être stocké en tant qu’objet de requête dans un projet SQL Server Management Studio.
Les méthodes suivantes peuvent être employées pour créer des scripts de réplication :
création manuelle du script ;
utilisation des fonctionnalités de génération de script fournies dans les Assistants de réplication ou
SQL Server Management Studio. Pour plus d'informations, voir Scripting Replication.
utilisation de Replication Management Objects pour générer le script par programme et créer un objet RMO.
Lorsque vous créez des scripts de réplication manuellement, gardez à l'esprit les points suivants :
Les scripts Transact-SQL ont un ou plusieurs lots. La commande GO signale la fin d'un lot. Si un script Transact-SQL n’a pas de commandes GO, il est exécuté en tant que lot unique.
Lors de l'exécution de plusieurs procédures stockées de réplication dans un lot unique, après la première procédure, toutes les procédures suivantes du lot doivent être précédées du mot clé EXECUTE.
Toutes les procédures stockées d'un lot doivent être compilées pour que le lot puisse être exécuté. Toutefois, lorsque le lot a été compilé et qu'un plan d'exécution a été créé, une erreur d'exécution peut éventuellement se produire.
Lorsque vous créez des scripts pour configurer la réplication, vous devez utiliser l'authentification Windows de sorte que les informations d'identification de sécurité ne soient pas stockées dans le fichier de script. Si vous devez enregistrer les informations d'identification dans un fichier de script, vous devez sécuriser le fichier pour empêcher un accès non autorisé.
Exemple de script de réplication
Le script suivant peut être exécuté pour configurer la publication et la distribution sur un serveur.
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks2022';
-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;
-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB,
@security_mode = 1;
GO
-- Create a Publisher and enable AdventureWorks2022 for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher,
@distribution_db=@distributionDB,
@security_mode = 1;
GO
Ce script peut ensuite être enregistré localement sous le nom instdistpub.sql afin d'être exécuté ou réexécuté en cas de besoin.
Le script précédent inclut des variables de script sqlcmd , qui sont utilisées dans de nombreux exemples de code de réplication dans la documentation en ligne de SQL Server. Les variables de script sont définies à l’aide de la syntaxe $(MyVariable). Les valeurs des variables peuvent être passées à un script sur la ligne de commande ou dans SQL Server Management Studio. Pour plus d'informations, consultez la section suivante de cette rubrique, « Exécution de scripts de réplication ».
Exécution de scripts de réplication
Une fois qu'un script de réplication a été créé, il est possible d'utiliser l'une des méthodes suivantes pour l'exécuter.
Création d'un fichier de requête SQL dans SQL Server Management Studio
Un fichier de script Transact-SQL de réplication peut être créé en tant que fichier sql Query dans un projet SQL Server Management Studio. Après l'écriture du script, une connexion à la base de données peut être établie pour ce fichier de requête, et le script peut être exécuté. Pour plus d’informations sur la création de scripts Transact-SQL à l’aide de SQL Server Management Studio, consultez Les éditeurs de requête et de texte (SQL Server Management Studio).
Pour utiliser un script qui inclut des variables de script, SQL Server Management Studio doit s’exécuter en mode sqlcmd . En mode sqlcmd, l’éditeur de requête accepte une syntaxe supplémentaire spécifique à sqlcmd, par exemple :setvar, qui est utilisée pour fournir une valeur à une variable. Pour plus d’informations sur le mode sqlcmd, consultez Modifier des scripts SQLCMD à l’aide de l’Éditeur de requête. Dans le script suivant, la syntaxe :setvar est utilisée afin de fournir une valeur pour la variable $(DistPubServer).
:setvar DistPubServer N'MyPublisherAndDistributor';
-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
--
-- Additional code goes here
--
Utilisation de l'utilitaire sqlcmd à partir de la ligne de commande
L’exemple suivant montre comment la ligne de commande est utilisée pour exécuter le fichier de script instdistpub.sql à l’aide de l’utilitaire sqlcmd:
sqlcmd.exe -E -S sqlserverinstance -i C:\instdistpub.sql -o C:\output.log -v DistPubServer="N'MyDistributorAndPublisher'"
Dans cet exemple, le commutateur indique que l’authentification -E Windows est utilisée lors de la connexion à SQL Server. Avec l'authentification Windows, il est inutile de stocker un nom d'utilisateur et un mot de passe dans le fichier de script. Le nom et le chemin du fichier de script sont spécifiés par le -i commutateur et le nom du fichier de sortie est spécifié par le -o commutateur (la sortie de SQL Server est écrite dans ce fichier au lieu de la console lorsque ce commutateur est utilisé). L’utilitaire sqlcmd vous permet de passer des variables de script à un script Transact-SQL au moment de l’exécution à l’aide du -v commutateur. Dans cet exemple, sqlcmd remplace chaque instance de $(DistPubServer) dans le script par la valeur N'MyDistributorAndPublisher' avant l’exécution.
Remarque
Le commutateur -X désactive les variables de script.
Automatisation des tâches dans un fichier de commandes
Le recours à un fichier de commandes permet d'automatiser les tâches d'administration et de synchronisation de la réplication, entre autres, dans le même fichier de commandes. Le fichier de commandes suivant utilise l’utilitaire sqlcmd pour supprimer la base de données d’abonnement et la recréer, ainsi que pour ajouter un abonnement de fusion par extraction de données (pull). Le fichier appelle ensuite l'agent de fusion pour synchroniser le nouvel abonnement :
REM ----------------------Script to synchronize merge subscription ----------------------
REM -- Creates subscription database and
REM -- synchronizes the subscription to MergeSalesPerson.
REM -- Current computer acts as both Publisher and Subscriber.
REM -------------------------------------------------------------------------------------
SET Publisher=%computername%
SET Subscriber=%computername%
SET PubDb=AdventureWorks
SET SubDb=AdventureWorksReplica
SET PubName=AdvWorksSalesOrdersMerge
REM -- Drop and recreate the subscription database at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE master IF EXISTS (SELECT * FROM sysdatabases WHERE name='%SubDb%' ) DROP DATABASE %SubDb%"
sqlcmd /S%Subscriber% /E /Q"USE master CREATE DATABASE %SubDb%"
REM -- Add a pull subscription at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription @publisher = %Publisher%, @publication = %PubName%, @publisher_db = %PubDb%"
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription_agent @publisher = %Publisher%, @publisher_db = %PubDb%, @publication = %PubName%, @subscriber = %Subscriber%, @subscriber_db = %SubDb%, @distributor = %Publisher%"
REM -- This batch file starts the merge agent at the Subscriber to
REM -- synchronize a pull subscription to a merge publication.
REM -- The following must be supplied on one line.
"\Program Files\Microsoft SQL Server\130\COM\REPLMERG.EXE" -Publisher %Publisher% -Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PubDb% -SubscriberDB %SubDb% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 1 -Output -SubscriberSecurityMode 1 -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3
Génération de scripts pour les tâches de réplication courantes
Les tâches de réplication les plus courantes pour lesquelles il est possible de générer des scripts à l'aide de procédures stockées système sont les suivantes :
Configuration de la publication et de la distribution
Modification des propriétés d'un serveur de distribution et d'un serveur de publication
Désactivation de la publication et de la distribution
Création de publications et définition d'articles
Suppression de publications et d'articles
Création d'un abonnement par extraction de données
Modification d'un abonnement par extraction de données
Suppression d'un abonnement par extraction de données
Création d'un abonnement par émission de données
Modification d'un abonnement par émission de données
Suppression d'un abonnement par émission de données
Synchronisation d'un abonnement par extraction de données
Voir aussi
Concepts de programmation en matière de réplication
Procédures stockées de réplication (Transact-SQL)
Création de scripts de réplication