Replication System Stored Procedures Concepts

  • Artikel

Gilt für: SQL Server Azure SQL Managed Instance

In SQL Server wird der programmgesteuerte Zugriff auf alle vom Benutzer konfigurierbaren Funktionen in einer Replikationstopologie durch gespeicherte Systemprozeduren bereitgestellt. Während gespeicherte Prozeduren mithilfe von SQL Server Management Studio oder dem Befehlszeilenprogramm sqlcmd einzeln ausgeführt werden können, kann es von Vorteil sein, Transact-SQL-Skriptdateien zu schreiben, die ausgeführt werden können, um eine logische Sequenz von Replikationsaufgaben auszuführen.

Skriptreplikationstasks bieten die folgenden Vorteile:

  • Sie behalten eine dauerhafte Kopie der Schritte bei, die zum Bereitstellen der Replikationstopologie verwendet werden.

  • Sie verwenden ein einzelnes Skript, um mehrere Abonnenten zu konfigurieren.

  • Sie bieten neuen Datenbankadministratoren eine schnelle Einführung, da Skripts die Möglichkeiten zur Verfügung stellen, den Code auszuwerten, zu verstehen, zu ändern oder Probleme im Code zu finden und zu beheben.

    Wichtig

    Skripts können Quellen für Sicherheitsbeeinträchtigungen sein. Sie können Systemfunktionen ohne Wissen oder Eingriff des Benutzers aufrufen und Sicherheitsanmeldeinformationen im Nur-Text-Format enthalten. Überprüfen Sie Skripts auf Sicherheitsprobleme, bevor Sie sie verwenden.

Erstellen von Replikationsskripts

Aus der Sicht der Replikation ist ein Skript eine Reihe von einer oder mehreren Transact-SQL-Anweisungen, in denen jede Anweisung eine gespeicherte Replikationsprozedur ausführt. Skripts sind Textdateien, die meist die Dateierweiterung SQL aufweisen und mit dem sqlcmd-Hilfsprogramm ausgeführt werden können. Beim Ausführen einer Skriptdatei führt das Hilfsprogramm die in der Datei gespeicherten SQL-Anweisungen aus. Ebenso kann ein Skript als Abfrageobjekt in einem SQL Server Management Studio-Projekt gespeichert werden.

Replikationsskripts können wie folgt erstellt werden:

  • Erstellen Sie das Skript manuell.

  • Verwenden Sie die Skriptgenerierungsfunktionen, die in den Replikations-Assistenten bereitgestellt werden.

  • SQL Server Management Studio. Weitere Informationen finden Sie unter Scripting Replication.

  • Verwenden Sie Replikationsverwaltungsobjekte (RMO), um das Skript programmgesteuert zu generieren und ein RMO-Objekt zu erstellen.

Beachten Sie bei der manuellen Erstellung von Replikationsskripts die folgenden Punkte:

  • Transact-SQL-Skripts verfügen über einen oder mehrere Batches. Der GO-Befehl signalisiert das Ende eines Batches. Wenn ein Transact-SQL-Skript keine GO-Befehle enthält, wird es als einzelner Batch ausgeführt.

  • Beim Ausführen mehrerer gespeicherter Replikationsprozeduren in einem einzelnen Batch muss nach der ersten Prozedur allen folgenden Prozeduren das EXECUTE-Schlüsselwort vorangestellt werden.

  • Alle gespeicherten Prozeduren in einem Batch müssen kompiliert werden, bevor ein Batch ausgeführt wird. Nachdem der Batch kompiliert und ein Ausführungsplan erstellt wurde, kann ggf. jedoch ein Laufzeitfehler auftreten.

  • Beim Erstellen von Skripts zur Konfiguration der Replikation sollten Sie die Windows-Authentifizierung verwenden, um zu vermeiden, dass Sicherheitsanmeldeinformationen in der Skriptdatei gespeichert werden. Wenn Anmeldeinformationen in einer Skriptdatei gespeichert werden müssen, muss die Datei an einem sicheren Ort gespeichert werden, um unberechtigten Zugriff zu vermeiden.

Beispiel für ein Replikationsskript

Das folgende Skript kann zum Einrichten der Veröffentlichung und Verteilung auf einem Server ausgeführt werden.

-- 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

Dieses Skript kann dann lokal unter dem Namen instdistpub.sql gespeichert werden, sodass es bei Bedarf wiederholt ausgeführt werden kann.

Das vorherige Skript enthält sqlcmd-Skriptingvariablen , die in vielen Replikationscodebeispielen in SQL Server Books Online verwendet werden. Skriptvariablen werden mit der $(MyVariable)-Syntax definiert. Werte für Variablen können an ein Skript in der Befehlszeile oder in SQL Server Management Studio übergeben werden. Weitere Informationen finden Sie im nächsten Abschnitt dieses Themas, "Ausführen von Replikationsskripts".

Ausführen von Replikationsskripts

Sobald ein Replikationsskript erstellt wurde, kann es wie folgt ausgeführt werden:

Erstellen einer SQL-Abfragedatei in SQL Server Management Studio

Eine Replikations-Transact-SQL-Skriptdatei kann als SQL-Abfragedatei in einem SQL Server Management Studio-Projekt erstellt werden. Nachdem das Skript geschrieben wurde, kann für diese Abfragedatei eine Verbindung mit der Datenbank hergestellt und das Skript ausgeführt werden. Weitere Informationen zum Erstellen von Transact-SQL-Skripts mithilfe von SQL Server Management Studio finden Sie unter Abfrage- und Text-Editoren (SQL Server Management Studio).

Um ein Skript zu verwenden, das Skriptvariablen enthält, muss SQL Server Management Studio im sqlcmd-Modus ausgeführt werden. Im sqlcmd-Modus lässt der Abfrage-Editor zusätzliche sqlcmd-spezifische Syntax zu, wie :setvar zum Festlegen eines Werts für eine Variable. Weitere Informationen zum sqlcmd-Modus finden Sie unter Bearbeiten von SQLCMD-Skripts mit dem Abfrage-Editor. Im folgenden Skript wird :setvar verwendet, um einen Wert für die $(DistPubServer)-Variable bereitzustellen.

: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  
--

Verwenden des sqlcmd-Hilfsprogramms über die Befehlszeile

Das folgende Beispiel veranschaulicht, wie die Befehlszeile zur Ausführung der instdistpub.sql-Skriptdatei mit dem sqlcmd Hilfsprogramm verwendet wird:

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

In diesem Beispiel gibt die -E Option an, dass die Windows-Authentifizierung beim Herstellen einer Verbindung mit SQL Server verwendet wird. Bei Verwendung der Windows-Authentifizierung entfällt das Speichern des Benutzernamens und Kennworts in der Skriptdatei. Der Name und Pfad der Skriptdatei wird durch den -i Switch angegeben, und der Name der Ausgabedatei wird durch den -o Switch angegeben (die Ausgabe von SQL Server wird in diese Datei geschrieben, anstatt in die Konsole, wenn dieser Schalter verwendet wird). Mit dem sqlcmd Hilfsprogramm können Sie Skriptvariablen mithilfe der -v Option an ein Transact-SQL-Skript zur Laufzeit übergeben. In diesem Beispiel ersetzt sqlcmd vor der Ausführung jede Instanz von $(DistPubServer) im Skript durch den N'MyDistributorAndPublisher'-Wert.

Hinweis

Der -X-Schalter deaktiviert Skriptvariablen.

Automatisieren von Tasks in einer Batchdatei

Mit einer Batchdatei können Replikationsverwaltungstasks, Replikationssynchronisierungstasks und andere Tasks in der gleichen Batchdatei automatisiert werden. Die folgende Batchdatei verwendet das sqlcmd-Hilfsprogramm, um die Abonnementdatenbank zu löschen und neu zu erstellen und ein Mergepullabonnement hinzuzufügen. Anschließend startet die Datei den Merge-Agent, um das neue Abonnement zu synchronisieren:

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

Skripterstellung für allgemeine Replikationstasks

Im Folgenden sind einige der häufigsten Replikationstasks aufgeführt, für die mit gespeicherten Systemprozeduren ein Skript erstellt werden kann:

  • Konfigurieren der Veröffentlichung und Verteilung

  • Ändern von Verleger- und Verteilereigenschaften

  • Deaktivieren von Veröffentlichung und Verteilung

  • Erstellen von Veröffentlichungen und Definieren von Artikeln

  • Löschen von Veröffentlichungen und Artikeln

  • Erstellung eines Pullabonnements

  • Ändern eines Pullabonnements

  • Löschen eines Pullabonnements

  • Erstellen eines Pushabonnements

  • Ändern eines Pushabonnements

  • Löschen eines Pushabonnements

  • Synchronisieren eines Pullabonnements

Zugehöriger Inhalt