Compartilhar via

Conceitos de procedimentos armazenados do sistema de replicação

No SQL Server, o acesso programático a toda a funcionalidade configurável pelo usuário em uma topologia de replicação é fornecido por procedimentos armazenados do sistema. Embora os procedimentos armazenados possam ser executados individualmente usando o SQL Server Management Studio ou o utilitário de linha de comando sqlcmd, pode ser benéfico gravar Transact-SQL arquivos de script que podem ser executados para executar uma sequência lógica de tarefas de replicação.

As tarefas de replicação de script fornecem os seguintes benefícios:

  • Mantém uma cópia permanente das etapas usadas para implantar sua topologia de replicação.

  • Usa um único script para configurar vários Assinantes.

  • Instrui rapidamente os novos administradores de banco de dados, permitindo que eles avaliem, entendam, alterem ou solucionem o código rapidamente.

    Importante

    Os scripts podem ser a fonte de vulnerabilidades de segurança; eles podem invocar funções do sistema sem conhecimento ou intervenção do usuário e podem conter credenciais de segurança em texto sem formatação. Examine os scripts para problemas de segurança antes de usá-los.

Criando scripts de replicação

Do ponto de vista da replicação, um script é uma série de uma ou mais instruções Transact-SQL em que cada instrução executa um procedimento armazenado de replicação. Scripts são arquivos de texto, geralmente com uma extensão de arquivo .sql, que podem ser executados usando o utilitário sqlcmd. Quando um arquivo de script é executado, o utilitário executa as instruções SQL armazenadas no arquivo. Da mesma forma, um script pode ser armazenado como um objeto de consulta em um projeto do SQL Server Management Studio.

Os scripts de replicação podem ser criados das seguintes maneiras:

  • Crie manualmente o script.

  • Usar os recursos de geração de script fornecidos nos assistentes de replicação ou

  • SQL Server Management Studio. Para obter mais informações, consulte Replicação de Scripts.

  • Use RMOs (Replication Management Objects) para gerar programaticamente o script para criar um objeto RMO.

Ao criar manualmente scripts de replicação, tenha em mente as seguintes considerações:

  • Transact-SQL scripts têm um ou mais lotes. O comando GO sinaliza o fim de um lote. Se um script Transact-SQL não tiver comandos GO, ele será executado como um único lote.

  • Ao executar vários procedimentos armazenados de replicação em um único lote, após o primeiro procedimento, todos os procedimentos subsequentes no lote devem ser precedidos pela palavra-chave EXECUTE.

  • Todos os procedimentos armazenados em um lote devem ser compilados antes que um lote seja executado. No entanto, depois que o lote tiver sido compilado e um plano de execução tiver sido criado, um erro em tempo de execução poderá ou não ocorrer.

  • Ao criar scripts para configurar a replicação, você deve usar a Autenticação do Windows para evitar armazenar credenciais de segurança no arquivo de script. Se você precisar armazenar credenciais em um arquivo de script, deverá proteger o arquivo para impedir o acesso não autorizado.

Script de replicação de exemplo

O script a seguir pode ser executado para configurar a publicação e a distribuição em um servidor.

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

Esse script pode ser salvo localmente para instdistpub.sql que possa ser executado ou executado novamente quando necessário.

O script anterior inclui variáveis de script sqlcmd , que são usadas em muitos dos exemplos de código de replicação nos Manuais Online do SQL Server. As variáveis de script são definidas usando $(MyVariable) a sintaxe. Os valores das variáveis podem ser passados para um script na linha de comando ou no SQL Server Management Studio. Para obter mais informações, consulte a próxima seção neste tópico, "Executando scripts de replicação".

Executando scripts de replicação

Depois de criado, um script de replicação pode ser executado de uma das seguintes maneiras:

Criando um arquivo de consulta SQL no SQL Server Management Studio

Uma replicação Transact-SQL arquivo de script pode ser criada como um arquivo de consulta SQL em um projeto do SQL Server Management Studio. Depois que o script é gravado, uma conexão pode ser feita com o banco de dados para esse arquivo de consulta e o script pode ser executado. Para obter mais informações sobre como criar scripts Transact-SQL usando o SQL Server Management Studio, consulte Editores de Consulta e Texto (SQL Server Management Studio)).

Para usar um script que inclua variáveis de script, o SQL Server Management Studio deve estar em execução no modo sqlcmd . No modo sqlcmd , o Editor de Consultas aceita sintaxe adicional específica do sqlcmd, como :setvar, que é usada para um valor para uma variável. Para obter mais informações sobre o modo sqlcmd , consulte Editar scripts SQLCMD com o Editor de Consultas. No script a seguir, :setvar é usado para fornecer um valor para a $(DistPubServer) variável.

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

Usando o utilitário sqlcmd da linha de comando

O exemplo a seguir mostra como a linha de comando é usada para executar o instdistpub.sql arquivo de script usando o utilitário sqlcmd:

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

Neste exemplo, a opção -E indica que a Autenticação do Windows é usada ao se conectar ao SQL Server. Ao usar a Autenticação do Windows, não é necessário armazenar um nome de usuário e uma senha no arquivo de script. O nome e o caminho do arquivo de script são especificados pela opção -i e o nome do arquivo de saída é especificado pela opção (a -o saída do SQL Server é gravada nesse arquivo em vez do console quando essa opção é usada). O sqlcmd utilitário permite passar variáveis de script para um script Transact-SQL em runtime usando a opção -v . Neste exemplo, sqlcmd substitui todas as instâncias do $(DistPubServer) script pelo valor N'MyDistributorAndPublisher' antes da execução.

Observação

A -X opção desabilita variáveis de script.

Automatizando tarefas em um arquivo em lote

Usando um arquivo em lote, tarefas de administração de replicação, tarefas de sincronização de replicação e outras tarefas podem ser automatizadas no mesmo arquivo em lote. O arquivo em lote a seguir usa o utilitário sqlcmd para remover e recriar o banco de dados de assinatura e adicionar uma assinatura de pull de mesclagem. Em seguida, o arquivo invoca o agente de mesclagem para sincronizar a nova assinatura:

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

Tarefas comuns de replicação de script

Veja a seguir algumas das tarefas de replicação mais comuns que podem ser criadas por script usando procedimentos armazenados do sistema:

  • Configurando a publicação e a distribuição

  • Modificando propriedades do Publicador e do Distribuidor

  • Desabilitando a publicação e a distribuição

  • Criando publicações e definindo artigos

  • Excluindo publicações e artigos

  • Criando uma assinatura pull

  • Modificando uma assinatura pull

  • Excluindo uma assinatura pull

  • Criando uma assinatura push

  • Modificando uma assinatura push

  • Excluindo uma assinatura push

  • Sincronizando uma assinatura pull

Consulte Também

Conceitos de programação da replicação
Procedimentos armazenados de replicação (Transact-SQL)
Replicação de script

  • Last updated on