Tutorial: Configurar a replicação entre duas instâncias gerenciadas de SQL

Aplica-se a:Instância Gerenciada de SQL do Azure

A replicação transacional permite replicar dados de um banco de dados para outro hospedado no SQL Server ou na Instância Gerenciada de SQL do Azure. A Instância Gerenciada de SQL pode ser um editor, distribuidor ou assinante na topologia de replicação. Confira configurações de replicação transacional para obter as configurações disponíveis.

Neste tutorial, você aprenderá como:

Este tutorial destina-se a um público experiente e pressupõe que o usuário esteja familiarizado com a implantação e a conexão com instâncias gerenciadas de SQL e VMs do SQL Server no Azure.

Requisitos

A configuração da Instância Gerenciada de SQL para funcionar como um publicador e/ou um distribuidor exige:

• A instância gerenciada de SQL do editor está na mesma rede virtual que o distribuidor e o assinante, ou o emparelhamento VNet ou os gateways de VPN foram configurados entre as redes virtuais das três entidades.
• A conectividade usa Autenticação SQL entre os participantes da replicação.
• Um compartilhamento da conta de armazenamento do Azure para o diretório de trabalho de replicação.
• A porta 445 (saída TCP) está aberta nas regras de segurança do NSG para as instâncias gerenciadas de SQL acessarem o compartilhamento de arquivos do Azure. Se você encontrar o erro failed to connect to azure storage <storage account name> with os error 53, precisará adicionar uma regra de saída ao NSG da sub-rede de SQL Managed Instance apropriada.

1– Criar um grupo de recursos

Use o portal do Azure para criar um grupo de recursos com o nome SQLMI-Repl.

2 – Criar instâncias gerenciadas de SQL

Use o portal do Azure para criar duas instâncias gerenciadas de SQL na mesma rede virtual e sub-rede. Por exemplo, nomeie as duas instâncias gerenciadas de SQL:

• sql-mi-publisher (junto com alguns caracteres para aleatoriedade)
• sql-mi-subscriber (junto com alguns caracteres para aleatoriedade)

Você também precisará configurar uma VM do Azure para se conectar às instâncias gerenciadas de SQL.

3 – Criar uma conta de armazenamento do Azure

Crie uma conta de armazenamento do Azure para o diretório de trabalho e, em seguida, crie um compartilhamento de arquivo na conta de armazenamento.

Copie o caminho do compartilhamento de arquivo no formato: \\storage-account-name.file.core.windows.net\file-share-name

Exemplo: \\replstorage.file.core.windows.net\replshare

Copie as chaves de acesso de armazenamento no formato: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

Exemplo: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

Para obter mais informações, confira Gerenciar chaves de acesso da conta de armazenamento.

4 – Criar um banco de dados publicador

Conecte-se à instância gerenciada de SQL do publicador (sql-mi-publisher) usando o SQL Server Management Studio e execute o seguinte código Transact-SQL (T-SQL) para criar o banco de dados do publicador:

USE [master]
GO

CREATE DATABASE [ReplTran_PUB]
GO

USE [ReplTran_PUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

USE [ReplTran_PUB]
GO

INSERT INTO ReplTest (ID, c1) VALUES (6, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (2, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (3, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (4, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (5, 'pub')
GO
SELECT * FROM ReplTest
GO

5 – Criar um banco de dados do assinante

Conecte-se à instância gerenciada de SQL do assinante (sql-mi-subscriber) usando o SQL Server Management Studio e execute o seguinte código T-SQL para criar seu banco de dados de assinante vazio:

USE [master]
GO

CREATE DATABASE [ReplTran_SUB]
GO

USE [ReplTran_SUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

6 – Configurar a distribuição

Conecte-se à instância gerenciada de SQL do publicador (sql-mi-publisher) usando o SQL Server Management Studio e execute o seguinte código T-SQL para configurar o banco de dados de distribuição.

USE [master]
GO

EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

7 – Configurar o publicador para usar o distribuidor

Na instância gerenciada de SQL do publicador (sql-mi-publisher), altere a execução da consulta para o modo SQLCMD e execute o código a seguir para registrar o novo distribuidor com o publicador.

:setvar username loginUsedToAccessSourceManagedInstance
:setvar password passwordUsedToAccessSourceManagedInstance
:setvar file_storage "\\storage-account-name.file.core.windows.net\file-share-name"
-- example: file_storage "\\replstorage.file.core.windows.net\replshare"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net"
-- example: file_storage_key "DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net"

USE [master]
EXEC sp_adddistpublisher
  @publisher = @@ServerName,
  @distribution_db = N'distribution',
  @security_mode = 0,
  @login = N'$(username)',
  @password = N'$(password)',
  @working_directory = N'$(file_storage)',
  @storage_connection_string = N'$(file_storage_key)'; -- Remove this parameter for on-premises publishers

Esse script configura um editor local na instância gerenciada de SQL, adiciona um servidor vinculado e cria um conjunto de trabalhos para o agente do SQL Server.

8 – Criar a publicação e o assinante

Usando o modo SQLCMD, execute o script T-SQL a seguir para habilitar a replicação no banco de dados e configurar a replicação entre o publicador, o distribuidor e o assinante.

-- Set variables
:setvar username sourceLogin
:setvar password sourcePassword
:setvar source_db ReplTran_PUB
:setvar publication_name PublishData
:setvar object ReplTest
:setvar schema dbo
:setvar target_server "sql-mi-subscriber.wdec33262scj9dr27.database.windows.net"
:setvar target_username targetLogin
:setvar target_password targetPassword
:setvar target_db ReplTran_SUB

-- Enable replication for your source database
USE [$(source_db)]
EXEC sp_replicationdboption
  @dbname = N'$(source_db)',
  @optname = N'publish',
  @value = N'true';

-- Create your publication
EXEC sp_addpublication
  @publication = N'$(publication_name)',
  @status = N'active';

-- Configure your log reader agent
EXEC sp_changelogreader_agent
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the publication snapshot
EXEC sp_addpublication_snapshot
  @publication = N'$(publication_name)',
  @frequency_type = 1,
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the ReplTest table to the publication
EXEC sp_addarticle
  @publication = N'$(publication_name)',
  @type = N'logbased',
  @article = N'$(object)',
  @source_object = N'$(object)',
  @source_owner = N'$(schema)';

-- Add the subscriber
EXEC sp_addsubscription
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @destination_db = N'$(target_db)',
  @subscription_type = N'Push';

-- Create the push subscription agent
EXEC sp_addpushsubscription_agent
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @subscriber_db = N'$(target_db)',
  @subscriber_security_mode = 0,
  @subscriber_login = N'$(target_username)',
  @subscriber_password = N'$(target_password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Initialize the snapshot
EXEC sp_startpublication_snapshot
@publication = N'$(publication_name)';

9 – Modificar parâmetros do agente

No momento, a Instância Gerenciada de SQL do Azure apresenta alguns problemas de back-end na conectividade com os agentes de replicação. Enquanto esse problema está sendo resolvido, a solução alternativa é aumentar o valor de tempo limite de logon dos agentes de replicação.

Execute o seguinte comando T-SQL no publicador para aumentar o tempo limite do logon:

-- Increase login timeout to 150s
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Execute o seguinte comando T-SQL novamente se necessário para definir o tempo limite de logon de volta para o valor padrão:

-- Increase login timeout to 30
update msdb..sysjobsteps set command = command + N' -LoginTimeout 30'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Para aplicar essas alterações, reinicie os três agentes.

10 – Testar a replicação

Depois que a replicação estiver configurada, você poderá testá-la inserindo novos itens no publicador e observando as alterações propagadas para o assinante.

Execute o seguinte snippet T-SQL para ver as linhas no assinante:

select * from dbo.ReplTest

Execute o seguinte trecho de T-SQL para inserir mais linhas no publicador e, em seguida, verificar as linhas novamente no assinante.

INSERT INTO ReplTest (ID, c1) VALUES (15, 'pub')

Limpar os recursos

Para remover a publicação, execute o seguinte comando T-SQL:

-- Drops the publication
USE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'
GO

Para remover a opção de replicação por meio do banco de dados, execute o seguinte comando T-SQL:

-- Disables publishing of the database
USE [ReplTran_PUB]
EXEC sp_removedbreplication
GO

Para desabilitar a publicação e a distribuição, execute o seguinte comando T-SQL:

-- Drops the distributor
USE [master]
EXEC sp_dropdistributor @no_checks = 1
GO

Limpe os recursos do Azure excluindo os recursos da Instância Gerenciada de SQL do grupo de recursos e excluindo o grupo de recursos SQLMI-Repl.

Conteúdo relacionado

• Replicação transacional com a Instância Gerenciada de SQL do Azure
• Tutorial: Configurar a replicação transacional entre a Instância Gerenciada de SQL do Azure e o SQL Server