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

Artigo
07/31/2023

Aplica-se a:Instância Gerenciada 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 SQL do Azure. A Instância Gerenciada SQL pode ser um editor, distribuidor ou assinante na topologia de replicação. Consulte as configurações de replicação transacional para obter as configurações disponíveis.

Neste tutorial, irá aprender a:

Configure uma instância gerenciada como um editor e distribuidor de replicação.
Configure uma instância gerenciada como um assinante de replicação.

Replicate between two managed instances

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 e VMs do SQL Server no Azure.

Nota

Este artigo descreve o uso da replicação transacional na Instância Gerenciada SQL do Azure. Ele não está relacionado a grupos de failover, um recurso de Instância Gerenciada SQL do Azure que permite criar réplicas completas legíveis de instâncias individuais. Há considerações adicionais ao configurar a replicação transacional com grupos de failover.

Requisitos

Configurar a Instância Gerenciada SQL para funcionar como um editor e/ou um distribuidor requer:

Que a instância gerenciada pelo editor está na mesma rede virtual que o distribuidor e o assinante, ou que o emparelhamento VNet ou gateways VPN foram configurados entre as redes virtuais das três entidades.
A conectividade utiliza a Autenticação SQL entre os participantes da replicação.
Um compartilhamento de 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 que as instâncias gerenciadas acessem 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 apropriada da Instância Gerenciada SQL.

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

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

sql-mi-pub (juntamente com alguns caracteres para aleatorização)
sql-mi-sub (juntamente com alguns caracteres para aleatorização)

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

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 arquivos dentro da conta de armazenamento.

Copie o caminho de compartilhamento de arquivos no formato de: \\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 de: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

Exemplo: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net

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

4 - Criar um banco de dados de editores

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

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 uma base de dados de subscritores

Conecte-se à sua sql-mi-sub instância gerenciada usando o SQL Server Management Studio e execute o seguinte código T-SQL para criar seu banco de dados de assinantes 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 distribuição

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

USE [master]
GO

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

7 - Configurar o editor para usar o distribuidor

Na Instância sql-mi-pubGerenciada SQL do editor, altere a execução da consulta para o modo SQLCMD e execute o código a seguir para registrar o novo distribuidor no editor.

: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=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;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

Nota

Certifique-se de usar apenas barras invertidas (\) para o parâmetro file_storage. Usar uma barra (/) pode causar um erro ao se conectar ao compartilhamento de arquivos.

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

8 - Criar publicação e assinante

Usando o modo SQLCMD , execute o seguinte script T-SQL para habilitar a replicação para seu banco de dados e configure a replicação entre seu editor, distribuidor e 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-sub.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

A Instância Gerenciada SQL do Azure está enfrentando alguns problemas de back-end com a conectividade com os agentes de replicação. Enquanto esse problema está sendo resolvido, a solução alternativa é aumentar o valor de tempo limite de login para os agentes de replicação.

Execute o seguinte comando T-SQL no editor para aumentar o tempo limite de login:

-- 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 para definir o tempo limite de login de volta para o valor padrão, caso seja necessário:

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

Reinicie os três agentes para aplicar essas alterações.

10 - Replicação de teste

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

Execute o seguinte trecho do T-SQL para exibir as linhas no assinante:

select * from dbo.ReplTest

Execute o seguinte trecho do T-SQL para inserir linhas adicionais no editor e, em seguida, verifique as linhas novamente no assinante.

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

Clean up resources (Limpar recursos)

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

Você pode limpar seus recursos do Azure excluindo os recursos da Instância Gerenciada SQL do grupo de recursos e, em seguida, excluindo o grupo SQLMI-Replde recursos.

Próximos passos

Você também pode obter mais informações sobre a replicação transacional com a Instância Gerenciada SQL do Azure ou aprender a configurar a replicação entre um editor/distribuidor da Instância Gerenciada do SQL e um assinante da SQL na VM do Azure.

Partilhar via