• Articolo

Concetti di base relativi alle stored procedure del sistema di replica

In SQL Server, l'accesso a livello di codice a tutte le funzionalità configurabili dall'utente in una topologia di replica viene fornito da stored procedure di sistema. Mentre le stored procedure possono essere eseguite singolarmente utilizzando SQL Server Management Studio o l'utilità della riga di comando sqlcmd, può rivelarsi vantaggioso scrivere file script Transact-SQL che consentono di 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.

    Nota sulla protezioneNota sulla protezione

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

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. Allo stesso modo, 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:

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

  • Gli script Transact-SQL possono contenere uno o più batch. Il comando GO indica la fine di un batch. Uno script Transact-SQL che non include alcun comando GO viene eseguito come batch singolo.

  • 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 protezione 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'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

È 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 utilizzate in molti degli esempi di codice di replica presenti nella documentazione in linea di SQL Server. Le variabili di scripting vengono definite mediante la sintassi $(MyVariable). I valori per le variabili possono essere passati a uno script dalla 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 script di replica di Transact-SQL può essere creato come file 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 ulteriori informazioni su come creare script di Transact-SQL utilizzando SQL Server Management Studio, vedere Scrittura, analisi e modifica di script con SQL Server Management Studio.

Per utilizzare uno script che include variabili di scripting, è necessario che SQL Server Management Studio sia in esecuzione in modalità sqlcmd. In modalità sqlcmd, l'editor di query accetta la sintassi aggiuntiva specifica di sqlcmd, ad esempio il comando :setvar utilizzato per assegnare un valore a una variabile. Per ulteriori informazioni sulla modalità sqlcmd, vedere Modifica di script SQLCMD con l'editor di query. Nello script seguente, il comando :setvar viene utilizzato 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

Nell'esempio seguente viene illustrato come utilizzare la riga di comando per eseguire il file script instdistpub.sql mediante 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 viene utilizzata l'autenticazione di Windows 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 script vengono specificati dall'opzione -i e il nome del file di output viene specificato dall'opzione -o. Quando viene utilizzata questa opzione, l'output da SQL Server viene scritto in questo file anziché indirizzato alla console. L'utilità sqlcmd consente di passare variabili di scripting a uno script di Transact-SQL durante l'esecuzione utilizzando 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 utilizza l'utilità sqlcmd per rilasciare 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\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

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.