Início / dba / Perguntas / 339388
Accepted
Trubs
Asked: 2024-05-10 15:35:31 +0800 CST

Replicação de scripts

  • 772

Gostaria de saber como automatizar a criação de replicação transacional.

em um ambiente de teste, muitas vezes preciso restaurar os dados do Prod. Os dados de teste são replicados para outro servidor para geração de relatórios (e teste dos referidos relatórios). Tudo isso é muito simples de criar scripts - mas preciso reativar a replicação.

Eu sei que posso criar um script da replicação existente e reaplicá-la após a restauração, mas isso será problemático se houver objetos adicionais (ou ausentes) no banco de dados restaurado.

Basicamente - quero criar um processo que crie o encanamento básico para replicação, depois passe por todas as tabelas do banco de dados, adicione-o como um artigo e faça o mesmo para procs, visualizações etc.

com um pouco de esforço (e tempo), presumo que poderia escrever algo que daria conta do recado, só não quero reinventar a roda...

THX.

sql-server

1 respostas

  1. Best Answer

    Automatizar a criação de replicação transacional no SQL Server envolve várias etapas. Abaixo está um processo que você pode seguir, que inclui o script da configuração da replicação e a garantia de que todos os objetos do banco de dados sejam adicionados corretamente como artigos na publicação.

    Processo passo a passo

    1. Restaure os dados de produção : certifique-se de que seu ambiente de teste restaurou os dados de produção mais recentes.

    2. Recriar replicação : Recrie a configuração de replicação definindo o distribuidor, criando a publicação e adicionando os artigos. Esse processo pode ser automatizado usando SQL Server Management Studio (SSMS) ou scripts SQL.

    Aqui estão exemplos de scripts que podem ajudá-lo a automatizar esse processo.

    1. Configurando o distribuidor - o script abaixo configura o servidor como distribuidor. (em muitos casos, o Editor (produção) e o Distribuidor são o mesmo servidor):
    
-- Set the distributor
EXEC sp_adddistributor @distributor = 'DistributorServerName', @password = 'DistributorPassword';

-- Configure the distribution database
EXEC sp_adddistributiondb @database = 'distribution', @security_mode = 1;

-- Configure the publisher
EXEC sp_adddistpublisher @publisher = 'PublisherServerName',
                         @distribution_db = 'distribution',
                         @working_directory = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData',
                         @security_mode = 1;
    1. Crie a publicação – o script abaixo cria uma publicação:
    -- Create the publication
EXEC sp_addpublication @publication = 'TestPublication',
                       @status = N'active',
                       @allow_push = N'true',
                       @allow_pull = N'true',
                       @allow_anonymous = N'true',
                       @enabled_for_internet = N'false',
                       @snapshot_in_defaultfolder = N'true',
                       @compress_snapshot = N'false',
                       @ftp_port = 21,
                       @ftp_login = N'anonymous',
                       @allow_subscription_copy = N'false',
                       @add_to_active_directory = N'false',
                       @repl_freq = N'continuous',
                       @status = N'active';

-- Add articles to the publication (example for tables)
DECLARE @article NVARCHAR(255);
DECLARE articles_cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

OPEN articles_cursor;

FETCH NEXT FROM articles_cursor INTO @article;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_addarticle @publication = 'TestPublication',
                       @article = @article,
                       @source_owner = 'dbo',
                       @source_object = @article,
                       @type = N'logbased',
                       @schema_option = 0x000000000803509F,
                       @identityrangemanagementoption = N'manual';
    FETCH NEXT FROM articles_cursor INTO @article;
END;

CLOSE articles_cursor;
DEALLOCATE articles_cursor;
    1. Adicione procedimentos armazenados, visualizações e outros objetos - o script abaixo adiciona esses objetos como artigos:
    -- Add stored procedures as articles
DECLARE @proc NVARCHAR(255);
DECLARE procs_cursor CURSOR FOR
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';

OPEN procs_cursor;

FETCH NEXT FROM procs_cursor INTO @proc;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_addarticle @publication = 'TestPublication',
                       @article = @proc,
                       @source_owner = 'dbo',
                       @source_object = @proc,
                       @type = N'logbased',
                       @schema_option = 0x000000000803509F,
                       @identityrangemanagementoption = N'manual';
    FETCH NEXT FROM procs_cursor INTO @proc;
END;

CLOSE procs_cursor;
DEALLOCATE procs_cursor;

-- Add views as articles
DECLARE @view NVARCHAR(255);
DECLARE views_cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS;

OPEN views_cursor;

FETCH NEXT FROM views_cursor INTO @view;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_addarticle @publication = 'TestPublication',
                       @article = @view,
                       @source_owner = 'dbo',
                       @source_object = @view,
                       @type = N'logbased',
                       @schema_option = 0x000000000803509F,
                       @identityrangemanagementoption = N'manual';
    FETCH NEXT FROM views_cursor INTO @view;
END;

CLOSE views_cursor;
DEALLOCATE views_cursor;
    1. Crie a assinatura – Finalmente, o script abaixo cria a assinatura. O assinante é o ambiente de teste.
    -- Add the subscription
EXEC sp_addsubscription @publication = 'TestPublication',
                        @subscriber = 'SubscriberServerName',
                        @destination_db = 'SubscriberDBName',
                        @subscription_type = N'Push',
                        @sync_type = N'automatic',
                        @article = N'all',
                        @update_mode = N'read only',
                        @subscriber_type = 0;

-- Add the push subscription job
EXEC sp_addpushsubscription_agent @publication = 'TestPublication',
                                  @subscriber = 'SubscriberServerName',
                                  @subscriber_db = 'SubscriberDBName',
                                  @job_login = null,
                                  @job_password = null,
                                  @subscriber_security_mode = 1,
                                  @frequency_type = 64,
                                  @frequency_interval = 1,
                                  @frequency_relative_interval = 0,
                                  @frequency_recurrence_factor = 0,
                                  @frequency_subday = 8,
                                  @frequency_subday_interval = 1,
                                  @active_start_time_of_day = 0,
                                  @active_end_time_of_day = 235959,
                                  @active_start_date = 20220101,
                                  @active_end_date = 99991231,
                                  @enabled_for_syncmgr = N'False';

    Você pode automatizar esses scripts usando trabalhos do SQL Server Agent. Crie trabalhos que executem esses scripts em sequência. Certifique-se de que o tratamento de erros e o registro estejam em vigor para monitorar o processo.

    No entanto, seria melhor se você tivesse estas considerações:

    1. Segurança : Garanta que seus scripts lidem com a segurança de maneira adequada, especialmente no ambiente de produção.

    2. Alterações de esquema : se as alterações de esquema forem frequentes, certifique-se de que seu script lide com essas alterações dinamicamente.

    3. Teste : teste minuciosamente sua automação em um ambiente que não seja de produção antes de implantá-la.

    • 1

relate perguntas