Conceitos de Procedimentos Armazenados do Sistema de Replicação

Aplica-se a:SQL ServerAzure SQL Managed Instance

No SQL Server, o acesso programático a toda a funcionalidade configurável pelo utilizador numa 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 a utilidade de linha de comandos sqlcmd, pode ser benéfico escrever Transact-SQL ficheiros de script que possam ser executados para executar uma sequência lógica de tarefas de replicação.

As tarefas de replicação de scripts oferecem os seguintes benefícios:

• Mantém uma cópia permanente dos passos usados para implementar a tua topologia de replicação.

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

• Educa rapidamente novos administradores de bases de dados, permitindo-lhes avaliar, compreender, alterar ou resolver problemas no código.

  Importante

  Scripts podem ser a fonte de vulnerabilidades de segurança; Podem invocar funções do sistema sem conhecimento ou intervenção do utilizador e podem conter credenciais de segurança em texto simples. Revise scripts para questões de segurança antes de os usar.

Criação de Scripts de Replicação

Do ponto de vista da replicação, um script é uma série de uma ou mais instruções Transact-SQL onde cada instrução executa um procedimento armazenado de replicação. Scripts são ficheiros de texto, frequentemente com uma extensão de ficheiro .sql, que podem ser executados usando a utilidade sqlcmd. Quando um ficheiro de script é executado, a utilitária executa as instruções SQL armazenadas no ficheiro. De forma semelhante, um script pode ser armazenado como objeto de consulta num projeto SQL Server Management Studio.

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

• Cria o script manualmente.

• Use as funcionalidades de geração de scripts fornecidas nos assistentes de replicação ou

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

• Use Objetos de Gestão de Replicação (RMOs) 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, é executado como um único lote.

• Ao executar múltiplos procedimentos armazenados de replicação num único lote, após o primeiro procedimento, todos os procedimentos subsequentes do lote devem ser precedidos pela palavra-chave EXECUTAR.

• Todos os procedimentos armazenados num batch devem ser compilados antes de um batch ser executado. No entanto, uma vez compilado o lote e criado um plano de execução, pode ou não ocorrer um erro em tempo de execução.

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

Exemplo de Script de Replicação

O script seguinte pode ser executado para configurar a publicação e distribuição num 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'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  
  

Este script pode então ser guardado localmente para instdistpub.sql que possa ser executado ou reexecutado quando necessário.

O script anterior inclui variáveis de scripting sqlcmd , que são usadas em muitos exemplos de código de replicação no SQL Server Books Online. As variáveis de scripting são definidas usando $(MyVariable) sintaxe. Os valores das variáveis podem ser passados para um script na linha de comandos ou no SQL Server Management Studio. Para mais informações, consulte a secção seguinte deste tópico, "Execução de Scripts de Replicação."

Execução de Scripts de Replicação

Uma vez criado, um script de replicação pode ser executado de uma das seguintes formas:

Criação de um ficheiro de consulta SQL no SQL Server Management Studio

Um ficheiro de replicação Transact-SQL script pode ser criado como um ficheiro SQL Query num projeto SQL Server Management Studio. Depois de o script ser escrito, pode ser feita uma ligação à base de dados para este ficheiro de consulta e o script pode ser executado. Para mais informações sobre como criar scripts de Transact-SQL utilizando o SQL Server Management Studio, consulte Editores de Consulta e Texto (SQL Server Management Studio).

Para usar um script que inclua variáveis de scripting, o SQL Server Management Studio deve estar a correr em modo SQLCMD. No modo SQLCMD, o Editor de Consultas aceita sintaxe adicional específica do sqlcmd, como :setvar, que é usada para um valor de uma variável. Para mais informações sobre o modo SQLCMD, consulte Editar scripts SQLCMD com o Editor de Consultas. No script seguinte, :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 a utilidade sqlcmd da linha de comandos

O exemplo seguinte mostra como a linha de comandos é usada para executar o instdistpub.sql ficheiro script usando a utilidade sqlcmd:

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

Neste exemplo, o -E switch indica que a Autenticação do Windows é usada ao ligar ao SQL Server. Ao utilizar a Autenticação Windows, não é necessário armazenar um nome de utilizador e palavra-passe no ficheiro de script. O nome e o caminho do ficheiro de script são especificados pelo -i switch e o nome do ficheiro de saída é especificado pelo -o switch (a saída do SQL Server é escrita neste ficheiro em vez da consola quando este switch é utilizado). A sqlcmd ferramenta permite-te passar variáveis de scripting para um script Transact-SQL em tempo de execução usando o -v switch. Neste exemplo, sqlcmd substitui cada instância de $(DistPubServer) no script pelo valor N'MyDistributorAndPublisher' antes da execução.

Automatização de Tarefas num Ficheiro Batch

Ao usar um ficheiro batch, tarefas de administração de replicação, sincronização de replicação e outras tarefas podem ser automatizadas no mesmo ficheiro batch. O ficheiro batch seguinte utiliza a utilidade sqlcmd para eliminar e recriar a base de dados de subscrições e adicionar uma subscrição merge pull. Depois, o ficheiro invoca o agente de fusão para sincronizar a nova subscrição:

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  
  

Scripting de Tarefas Comuns de Replicação

Seguem-se algumas das tarefas de replicação mais comuns que podem ser scriptadas usando procedimentos armazenados do sistema:

• Configuração da publicação e distribuição

• Modificação das propriedades do Editor e Distribuidor

• Desativação da publicação e distribuição

• Criação de publicações e definição de artigos

• Eliminação de publicações e artigos

• Criação de uma subscrição pull

• Modificar uma subscrição pull

• Eliminar uma subscrição pull

• Criar uma subscrição push

• Modificar uma subscrição push

• Eliminar uma subscrição push

• Sincronização de uma subscrição pull

Conteúdo relacionado

• Conceitos de Programação de Replicação
• Procedimentos Armazenados de Replicação (Transact-SQL)
• Replicação de Scripts