Partager via

  • Article

Concepts liés aux procédures stockées système de réplication

Dans SQL 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. Bien que les procédures stockées puissent être exécutées individuellement à l'aide de SQL Server Management Studio ou de l'utilitaire en ligne de commande sqlcmd, il peut être judicieux 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.

    Remarque relative à la sécuritéRemarque relative à la sécurité

    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 la réplication, un script consiste en une série d'instructions Transact-SQL, chacune exécutant 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 :

Lorsque vous créez des scripts de réplication manuellement, gardez à l'esprit les points suivants :

  • Les scripts Transact-SQL comportent un ou plusieurs lots. La commande GO signale la fin d'un lot. Si un script Transact-SQL ne comporte pas de commande GO, il est exécuté comme un lot isolé.

  • 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, placez ce fichier dans un endroit sécurisé de manière à empêcher tout 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'AdventureWorks'; 

-- 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 AdventureWorks 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, lesquelles 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 transmises à 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

Il est possible de créer un fichier de script Transact-SQL de réplication en tant que fichier de requête SQL 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 Écriture, analyse et modification de scripts à l'aide de SQL Server Management Studio.

Si vous voulez 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 la 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 Modification de 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 illustre le mode d'utilisation de la ligne de commande 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 -E indique que l'authentification Windows est employé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 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 Server est écrite dans ce fichier et non dans la console lorsque ce commutateur est utilisé). L'utilitaire sqlcmd vous permet de passer des variables de script à un script Transact-SQL lors de l'exécution, à l'aide du commutateur -v. 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 emploie l'utilitaire sqlcmd pour supprimer la base de données d'abonnement et la recréer, et 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\100\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