Concepts liés aux procédures stockées système de réplicationReplication System Stored Procedures Concepts

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database (Managed Instance uniquement) nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

Dans SQL ServerSQL Server, l'accès par programme à toutes les fonctionnalités d'une topologie de réplication configurables par l'utilisateur est opéré par les procédures stockées système.In SQL ServerSQL Server, programmatic access to all of the user-configurable functionality in a replication topology is provided by system stored procedures. Bien que les procédures stockées puissent être exécutées individuellement à l'aide de SQL Server Management StudioSQL Server Management Studio ou de l'utilitaire en ligne de commande sqlcmd, il peut être judicieux d'écrire des fichiers de script Transact-SQLTransact-SQL qui peuvent être exécutés pour effectuer une séquence logique de tâches de réplication.While stored procedures may be executed individually using the SQL Server Management StudioSQL Server Management Studio or the sqlcmd command-line utility, it may be beneficial to write Transact-SQLTransact-SQL script files that can be executed to perform a logical sequence of replication tasks.

La génération de scripts pour des tâches de réplication offre les avantages suivants :Scripting replication tasks provides the following benefits:

  • conservation d'une copie définitive des étapes utilisées pour déployer votre topologie de réplication ;Keeps a permanent copy of the steps used to deploy your replication topology.

  • utilisation d'un même script pour configurer plusieurs abonnés ;Uses a single script to configure multiple Subscribers.

  • 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.Quickly educates new database administrators by enabling them to evaluate, understand, change, or troubleshoot the 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.Scripts can be the source of security vulnerabilities; they can invoke system functions without user knowledge or intervention and may contain security credentials in plain text. Examinez les scripts pour détecter d'éventuels problèmes de sécurité avant de les utiliser.Review scripts for security issues before you use them.

Création de scripts de réplicationCreating Replication Scripts

Du point de la réplication, un script consiste en une série d'instructions Transact-SQLTransact-SQL, chacune exécutant une procédure stockée de réplication.From the standpoint of replication, a script is a series of one or more Transact-SQLTransact-SQL statements where each statement executes a replication stored procedure. 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.Scripts are text files, often with a .sql file extension, that can be run using the sqlcmd utility. Lorsqu'un fichier de script est exécuté, l'utilitaire exécute les instructions SQL stockées dans le fichier.When a script file is run, the utility executes the SQL statements stored in the file. De même, un script peut être stocké sous la forme d’un objet de requête dans un projet SQL Server Management StudioSQL Server Management Studio.Similarly, a script can be stored as a query object in a SQL Server Management StudioSQL Server Management Studio project.

Les méthodes suivantes peuvent être employées pour créer des scripts de réplication :Replication scripts can be created in the following ways:

  • création manuelle du script ;Manually create the script.

  • utilisation des fonctionnalités de génération de script fournies dans les Assistants de réplication ouUse the script generation features that are provided in the replication wizards or

  • SQL Server Management StudioSQL Server Management Studio.. Pour plus d'informations, voir Scripting Replication.For more information, see Scripting Replication.

  • utilisation de Replication Management Objects pour générer le script par programme et créer un objet RMO.Use Replication Management Objects (RMOs) to programmatically generate the script to create an RMO object.

Lorsque vous créez des scripts de réplication manuellement, gardez à l'esprit les points suivants :When you manually create replication scripts, keep the following considerations in mind:

  • Les scripts Transact-SQLTransact-SQL comportent un ou plusieurs lots.Transact-SQLTransact-SQL scripts have one or more batches. La commande GO signale la fin d'un lot.The GO command signals the end of a batch. Si un script Transact-SQLTransact-SQL ne comporte pas de commande GO, il est exécuté comme un lot isolé.If a Transact-SQLTransact-SQL script does not have any GO commands, it is executed as a single batch.

  • 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.When executing multiple replication stored procedures in a single batch, after the first procedure, all subsequent procedures in the batch must be preceded by the EXECUTE keyword.

  • Toutes les procédures stockées d'un lot doivent être compilées pour que le lot puisse être exécuté.All stored procedures in a batch must compile before a batch will execute. 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.However, once the batch has been compiled, and an execution plan has been created, a run-time error may or may not occur.

  • 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.When creating scripts to configure replication, you should use Windows Authentication to avoid storing security credentials in the script file. 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é.If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

Exemple de script de réplicationSample Replication Script

Le script suivant peut être exécuté pour configurer la publication et la distribution sur un serveur.The following script can be executed to setup publishing and distribution on a server.

-- 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'AdventureWorks2012';   
  
-- 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 AdventureWorks2012 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.This script can then be saved locally as instdistpub.sql so that it can be run or rerun when needed.

Le script précédent inclut des variables de script sqlcmd, lesquelles sont utilisées dans de nombreux exemples de code de réplication dans la documentation en ligne de SQL ServerSQL Server.The previous script includes sqlcmd scripting variables, which are used in many of the replication code samples in SQL ServerSQL Server Books Online. Les variables de script sont définies à l’aide de la syntaxe $(MyVariable).Scripting variables are defined by using $(MyVariable) syntax. Les valeurs des variables peuvent être transmises à un script sur la ligne de commande ou dans SQL Server Management StudioSQL Server Management Studio.Values for variables can be passed to a script at the command line or in SQL Server Management StudioSQL Server Management Studio. Pour plus d'informations, consultez la section suivante de cette rubrique, « Exécution de scripts de réplication ».For more information, see the next section in this topic, "Executing Replication Scripts."

Exécution de scripts de réplicationExecuting Replication Scripts

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.Once created, a replication script can be executed in one of the following ways:

Création d'un fichier de requête SQL dans SQL Server Management StudioCreating a SQL Query File in SQL Server Management Studio

Il est possible de créer un fichier de script Transact-SQLTransact-SQL de réplication sous la forme d’un fichier de requête SQL dans un projet SQL Server Management StudioSQL Server Management Studio.A replication Transact-SQLTransact-SQL script file can be created as a SQL Query file in a SQL Server Management StudioSQL Server Management Studio project. 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é.After the script is written, a connection can be made to the database for this query file and the script can be executed. Pour plus d’informations sur la manière de créer des scripts Transact-SQLTransact-SQL à l’aide de SQL Server Management StudioSQL Server Management Studio, consultez Éditeurs de texte et de requête (SQL Server Management Studio).For more information about how to create Transact-SQLTransact-SQL scripts by using SQL Server Management StudioSQL Server Management Studio, see Query and Text Editors (SQL Server Management Studio).

Pour utiliser un script qui inclut des variables de script, SQL Server Management StudioSQL Server Management Studio doit s’exécuter en mode sqlcmd.To use a script that includes scripting variables, SQL Server Management StudioSQL Server Management Studio must be running in sqlcmd mode. 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.In sqlcmd mode, the Query Editor accepts additional syntax specific to sqlcmd, such as :setvar, which is used to a value for a variable. Pour plus d’informations sur le mode sqlcmd, consultez Modifier des scripts SQLCMD à l’aide de l’Éditeur de requête.For more information about sqlcmd mode, see Edit SQLCMD Scripts with Query Editor. Dans le script suivant, la syntaxe :setvar est utilisée afin de fournir une valeur pour la variable $(DistPubServer).In the following script, :setvar is used to provide a value for the $(DistPubServer) variable.

: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 commandeUsing the sqlcmd Utility from the Command Line

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:The following example shows how the command line is used to execute the instdistpub.sql script file using the sqlcmd utility:

sqlcmd.exe -E -S sqlserverinstance -i C:\instdistpub.sql -o C:\output.log -v DistPubServer="N'MyDistributorAndPublisher'"  

Dans cet exemple, le commutateur -E indique que l'authentification Windows est employée lors de la connexion à SQL ServerSQL Server.In this example, the -E switch indicates that Windows Authentication is used when connecting to SQL ServerSQL Server. Avec l'authentification Windows, il est inutile de stocker un nom d'utilisateur et un mot de passe dans le fichier de script.When using Windows Authentication, there is no need to store a username and password in the script file. Le nom et le chemin d'accès du fichier de script sont spécifiés par le commutateur -i, et le nom du fichier de sortie est spécifié par le commutateur -o (la sortie de SQL ServerSQL Server est écrite dans ce fichier et non dans la console lorsque ce commutateur est utilisé).The name and path of the script file is specified by the -i switch and the name of the output file is specified by the -o switch (output from SQL ServerSQL Server is written to this file instead of the console when this switch is used). L'utilitaire sqlcmd vous permet de passer des variables de script à un script Transact-SQLTransact-SQL lors de l'exécution, à l'aide du commutateur -v.The sqlcmd utility enables you to pass scripting variables to a Transact-SQLTransact-SQL script at runtime using the -v switch. Dans cet exemple, sqlcmd remplace chaque instance de $(DistPubServer) dans le script par la valeur N'MyDistributorAndPublisher' avant l’exécution.In this example, sqlcmd replaces every instance of $(DistPubServer) in the script with the value N'MyDistributorAndPublisher' before execution.

Notes

Le commutateur -X désactive les variables de script.The -X switch disables scripting variables.

Automatisation des tâches dans un fichier de commandesAutomating Tasks in a Batch File

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.By using a batch file, replication administration tasks, replication synchronization tasks, and other tasks can be automated in the same batch file. 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).The following batch file uses the sqlcmd utility to drop and recreate the subscription database and add a merge pull subscription. Le fichier appelle ensuite l'agent de fusion pour synchroniser le nouvel abonnement :Then the file invokes the merge agent to synchronize the new subscription:

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 courantesScripting Common Replication Tasks

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 :The following are some of the most common replication tasks can be scripted using system stored procedures:

  • Configuration de la publication et de la distributionConfiguring publishing and distribution

  • Modification des propriétés d'un serveur de distribution et d'un serveur de publicationModifying Publisher and Distributor properties

  • Désactivation de la publication et de la distributionDisabling publishing and distribution

  • Création de publications et définition d'articlesCreating publications and defining articles

  • Suppression de publications et d'articlesDeleting publications and articles

  • Création d'un abonnement par extraction de donnéesCreating a pull subscription

  • Modification d'un abonnement par extraction de donnéesModifying a pull subscription

  • Suppression d'un abonnement par extraction de donnéesDeleting a pull subscription

  • Création d'un abonnement par émission de donnéesCreating a push subscription

  • Modification d'un abonnement par émission de donnéesModifying a push subscription

  • Suppression d'un abonnement par émission de donnéesDeleting a push subscription

  • Synchronisation d'un abonnement par extraction de donnéesSynchronizing a pull subscription

Voir aussiSee Also

Concepts de programmation en matière de réplication Replication Programming Concepts
Procédures stockées de réplication (Transact-SQL) Replication Stored Procedures (Transact-SQL)
Création de scripts de réplicationScripting Replication