Condividi tramite

Replication System Stored Procedures Concepts

  • Articolo

Si applica a: SQL Server Istanza gestita di SQL di Azure

In SQL Server l'accesso a livello di codice a tutte le funzionalità configurabili dall'utente in una topologia di replica viene fornito dalle stored procedure di sistema. Anche se le stored procedure possono essere eseguite singolarmente usando SQL Server Management Studio o l'utilità della riga di comando sqlcmd, può essere utile scrivere file script Transact-SQL che possono essere eseguiti per eseguire una sequenza logica di attività di replica.

L'esecuzione di attività di replica tramite script offre i vantaggi seguenti:

  • Consente di conservare una copia permanente dei passaggi utilizzati per distribuire la topologia di replica.

  • Consente di utilizzare un unico script per configurare più sottoscrittori.

  • Consente di formare in breve tempo nuovi amministratori di database permettendo loro di valutare, comprendere e modificare il codice, nonché di risolvere i problemi a livello del codice.

    Importante

    È possibile che gli script siano all'origine di vulnerabilità nella sicurezza, in quanto possono richiamare funzioni di sistema all'insaputa dell'utente e senza richiederne l'intervento, nonché contenere credenziali di sicurezza in testo normale. Prima di utilizzarli, verificare gli script in modo da individuare possibili problemi di sicurezza.

Creazione di script di replica

Dal punto di vista della replica, uno script è una serie di una o più istruzioni Transact-SQL in cui ogni istruzione esegue una stored procedure di replica. Gli script sono file di testo, spesso con l'estensione di file sql, che possono essere eseguiti utilizzando l'utilità sqlcmd. Quando un file script viene eseguito, l'utilità esegue le istruzioni SQL archiviate nel file. Analogamente, uno script può essere archiviato come oggetto query in un progetto di SQL Server Management Studio.

Per creare script di replica è possibile procedere nei modi seguenti:

  • Creare lo script in modo manuale.

  • Utilizzare le caratteristiche di generazione degli script fornite nelle procedure guidate per la replica oppure

  • SQL Server Management Studio. Per altre informazioni, vedere Scripting Replication.

  • Utilizzare RMO (Replication Management Objects) per generare lo script a livello di codice per la creazione di un oggetto RMO.

Quando si creano manualmente script di replica, tenere presente le considerazioni seguenti:

  • Gli script Transact-SQL hanno uno o più batch. Il comando GO indica la fine di un batch. Se uno script Transact-SQL non dispone di comandi GO, viene eseguito come singolo batch.

  • Quando si eseguono più stored procedure di replica in un solo batch, tutte le procedure successive alla prima procedura nel batch devono essere precedute dalla parola chiave EXECUTE.

  • Tutte le stored procedure in un batch devono essere compilate prima che un batch venga eseguito. Tuttavia, dopo aver compilato il batch e creato un piano di esecuzione, potrebbe o meno verificarsi un errore di run-time.

  • Quando si creano script per la configurazione della replica, è necessario utilizzare l'autenticazione di Windows per evitare di archiviare le credenziali di sicurezza nel file script. Se è necessario archiviare le credenziali in un file script, è fondamentale proteggere il file per evitare accessi non autorizzati.

Script di replica di esempio

Lo script seguente può essere eseguito per configurare la pubblicazione e la distribuzione in un 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'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

È quindi possibile salvare questo script localmente come instdistpub.sql in modo da poterlo eseguire più volte in base alle esigenze.

Lo script precedente include variabili di scripting sqlcmd , usate in molti degli esempi di codice di replica nella documentazione online di SQL Server. Le variabili di scripting vengono definite mediante la sintassi $(MyVariable). I valori per le variabili possono essere passati a uno script nella riga di comando o in SQL Server Management Studio. Per ulteriori informazioni, vedere la sezione successiva in questo argomento, "Esecuzione di script di replica".

Esecuzione di script di replica

Dopo averlo creato, uno script di replica può essere eseguito in uno dei modi seguenti:

Creazione di un file query SQL in SQL Server Management Studio

Un file di script Transact-SQL di replica può essere creato come file di query SQL in un progetto di SQL Server Management Studio. Dopo aver scritto lo script, è possibile stabilire una connessione al database per questo file query ed eseguire lo script. Per altre informazioni su come creare script Transact-SQL tramite SQL Server Management Studio, vedere Editor di query e testo (SQL Server Management Studio).

Per usare uno script che include variabili di scripting, SQL Server Management Studio deve essere in esecuzione in modalità sqlcmd . In modalità sqlcmd, l'editor di query accetta la sintassi aggiuntiva specifica di sqlcmd, ad esempio il comando :setvar usato per assegnare un valore a una variabile. Per altre informazioni sulla modalità sqlcmd, vedere Modificare script SQLCMD con l'editor di query. Nello script seguente il comando :setvar viene usato per fornire un valore per la variabile $(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  
--

Utilizzo dell'utilità sqlcmd dalla riga di comando

L'esempio seguente mostra come usare la riga di comando per eseguire il file di script instdistpub.sql con l'utilità sqlcmd:

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

In questo esempio, l'opzione -E indica che l'autenticazione di Windows viene usata per la connessione a SQL Server. Quando si utilizza l'autenticazione di Windows, non è necessario archiviare un nome utente e una password nel file script. Il nome e il percorso del file di script vengono specificati dall'opzione -i e il nome del file di output viene specificato dall'opzione (l'output -o di SQL Server viene scritto in questo file anziché nella console quando viene usata questa opzione). L'utilità sqlcmd consente di passare variabili di scripting a uno script Transact-SQL in fase di esecuzione usando l'opzione -v . In questo esempio, sqlcmd sostituisce ogni istanza di $(DistPubServer) nello script con il valore N'MyDistributorAndPublisher' prima dell'esecuzione.

Nota

L'opzione -X disabilita le variabili di scripting.

Automatizzazione di attività in un file batch

L'utilizzo di un file batch consente di automatizzare le attività di amministrazione della replica, le attività di sincronizzazione della replica e altre attività nello stesso file batch. Il file batch seguente usa l'utilità sqlcmd per eliminare e ricreare il database di sottoscrizione e aggiungere una sottoscrizione pull di tipo merge. Il file richiama quindi l'agente di merge per sincronizzare la nuova sottoscrizione:

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

Generazione di script per attività di replica comuni

Di seguito vengono illustrate alcune delle attività di replica più comuni per le quali è possibile generare script utilizzando stored procedure di sistema:

  • Configurazione della pubblicazione e della distribuzione.

  • Modifica delle proprietà del server di pubblicazione e del server di distribuzione.

  • Disabilitazione della pubblicazione e della distribuzione.

  • Creazione di pubblicazioni e definizione di articoli.

  • Eliminazione di pubblicazioni e articoli.

  • Creazione di una sottoscrizione pull.

  • Modifica di una sottoscrizione pull.

  • Eliminazione di una sottoscrizione pull.

  • Creazione di una sottoscrizione push.

  • Modifica di una sottoscrizione push.

  • Eliminazione di una sottoscrizione push.

  • Sincronizzazione di una sottoscrizione pull.

Vedi anche

Concetti di base relativi alla programmazione della replica
Stored procedure di replica (Transact-SQL)
Creazione di script di replica