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

Aplica-se a:Azure SQL Managed Instance

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

Requerimentos

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

• A instância gerenciada SQL do editor está na mesma rede virtual que o distribuidor e o assinante, ou emparelhamento de VNet ou gateways VPN foram configurados entre as redes virtuais de todas as três entidades.
• A conectividade usa 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 do SQL acessem o compartilhamento de arquivos do Azure. Se encontrar o erro failed to connect to azure storage <storage account name> with os error 53, você 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 SQL

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:

• sql-mi-publisher (juntamente com alguns caracteres para aleatorização)
• sql-mi-subscriber (juntamente com alguns caracteres para aleatorização)

Você também precisará Configurar uma VM do Azure para se conectar às suas instâncias gerenciadas pelo 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 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=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

Para obter mais informações, veja Gerir as chaves de acesso à conta de armazenamento.

4 - Criar um banco de dados de editores

Conecte-se à instância gerenciada SQL do editor (sql-mi-publisher) 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 à instância gerenciada 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 distribuição

Conecte-se à instância gerenciada SQL do editor (sql-mi-publisher) 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 gerenciada SQL do editor (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 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=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 do SQL, 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-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

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, se necessário, para definir o tempo limite de login de volta ao 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 - Replicação de teste

Depois que a replicação estiver 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 mais linhas no editor e, em seguida, verifique as linhas novamente no assinante.

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

Limpeza de 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.

Conteúdo relacionado

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