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

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.

Pré-requisitos

Para concluir o tutorial, verifique se você tem os seguintes pré-requisitos:

• Uma assinatura do Azure.
• Experiência com a implantação de duas instâncias gerenciadas de SQL na mesma rede virtual.
• Um assinante do SQL Server, local ou em uma VM do Azure. Este tutorial usa uma VM do Azure.
• SQL Server Management Studio (SSMS) 18.0 ou posterior.
• A versão mais recente do Azure PowerShell.
• As portas 445 e 1433 permitem o tráfego SQL no firewall do Azure e no Firewall do Windows.

Criar o grupo de recursos

Use o seguinte snippet de código do PowerShell para criar um grupo de recursos:

# set variables
$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

# Create a new resource group
New-AzResourceGroup -Name  $ResourceGroupName -Location $Location

Criar duas instâncias gerenciadas de SQL

Crie duas instâncias gerenciadas de SQL nesse novo grupo de recursos usando o portal do Azure.

• O nome da instância gerenciada de SQL do editor deve ser sql-mi-publisher (juntamente com alguns caracteres para randomização) e o nome da rede virtual deve ser vnet-sql-mi-publisher.

• O nome da instância gerenciada de SQL do distribuidor deve ser sql-mi-distributor (juntamente com alguns caracteres para randomização) e deve estar na mesma rede virtual que a instância gerenciada de SQL do editor.

  

Para obter mais informações sobre como criar uma instância gerenciada de SQL, consulte Criar uma instância gerenciada de SQL no portal.

Criar uma VM do SQL Server

Crie uma máquina virtual do SQL Server no portal do Azure. A máquina virtual do SQL Server deve ter as seguintes características:

• Nome: sql-vm-subscriber
• Imagem: SQL Server 2016 ou posterior
• Grupo de recursos: o mesmo que a instância gerenciada de SQL
• Rede virtual: sql-vm-subscriber-vnet

Para obter mais informações sobre como implantar uma VM do SQL Server no Azure, confira Início Rápido: Criar uma VM do SQL Server.

Configurar o Emparelhamento VNET

Configure o emparelhamento VNet para habilitar a comunicação entre a rede virtual das duas instâncias gerenciadas de SQL e a rede virtual do SQL Server. Para fazer isso, use este snippet de código do PowerShell:

# Set variables
$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-subscriber-vnet'
$pubsubName = 'Pub-to-Sub-Peer'
$subpubName = 'Sub-to-Pub-Peer'

$virtualNetwork1 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $pubvNet

 $virtualNetwork2 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $subvNet

# Configure VNet peering from publisher to subscriber
Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

# Configure VNet peering from subscriber to publisher
Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

# Check status of peering on the publisher VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $pubvNet `
 | Select PeeringState

# Check status of peering on the subscriber VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $subvNet `
 | Select PeeringState

Depois que o emparelhamento VNet for estabelecido, teste a conectividade iniciando o SSMS (SQL Server Management Studio) no host do SQL Server e conectando-se a ambas as instâncias gerenciadas do SQL. Para obter mais informações sobre como se conectar a uma instância gerenciada de SQL usando o SSMS, consulte Usar o SSMS para se conectar à Instância Gerenciada de SQL.

Criando uma zona DNS privada

Uma zona DNS privada permite o roteamento DNS entre as instâncias gerenciadas do SQL e o SQL Server.

Criar uma zona DNS privada

1. Faça logon no Portal do Azure.

2. Selecione Criar um recurso para criar um recurso do Azure.

3. Pesquise private dns zone no Azure Marketplace.

4. Escolha o recurso de zona DNS privado publicado pela Microsoft e, em seguida, selecione Criar para criar a zona DNS.

5. Escolha a assinatura e o grupo de recursos na lista suspensa.

6. Forneça um nome arbitrário para a zona DNS, como repldns.com.

   

7. Selecione Examinar + criar. Examine os parâmetros da zona DNS privada e selecione Criar para criar seu recurso.

Criar um registro A

1. Acesse a nova Zona DNS privado e selecione Visão geral.

2. Selecione + Conjunto de registros para criar um registro A.

3. Forneça o nome da VM do SQL Server, bem como o endereço IP interno privado.

   

4. Selecione OK para criar o registro A.

Vincular a rede virtual

1. Acesse a nova Zona DNS privado e selecione Links de rede virtual.

2. Selecione + Adicionar.

3. Forneça um nome para o link, como Pub-link.

4. Selecione sua assinatura na lista suspensa e selecione a rede virtual para a instância gerenciada de SQL do editor.

5. Marque a caixa ao lado de Habilitar o registro automático.

   

6. Selecione OK para vincular a rede virtual.

7. Repita essas etapas para adicionar um link para a rede virtual do assinante, com um nome como Sub-link.

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 a cadeia de conexão da chave 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.

Criar um banco de dados

Crie um novo banco de dados na instância gerenciada de SQL do editor. Para fazer isso, siga estas etapas:

1. Inicialize o SQL Server Management Studio no SQL Server.
2. Conecte-se à instância gerenciada de SQL do editor (sql-mi-publisher).
3. Abra uma janela Nova Consulta e execute a consulta T-SQL a seguir para criar o banco de dados.

-- Create the databases
USE [master]
GO

-- Drop database if it exists
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial
END
GO

-- Create new database
CREATE DATABASE [ReplTutorial]
GO

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

-- Populate table with data
USE [ReplTutorial]
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

Configurar distribuição

Depois que a conectividade for estabelecida e você tiver um banco de dados de exemplo, você poderá configurar a distribuição na instância gerenciada de SQL do distribuidor (sql-mi-distributor). Para fazer isso, siga estas etapas:

1. Inicialize o SQL Server Management Studio no SQL Server.

2. Conecte-se à instância gerenciada de SQL do distribuidor (sql-mi-distributor).

3. Abra uma janela Nova Consulta e execute o seguinte código Transact-SQL para configurar a distribuição na instância gerenciada de SQL do distribuidor:

   EXEC sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'
   
   EXEC sp_adddistributiondb @database = N'distribution'
   
   EXEC sp_adddistpublisher @publisher = 'sql-mi-publisher.b6bf57.database.windows.net', -- primary publisher
        @distribution_db = N'distribution',
        @security_mode = 0,
        @login = N'azureuser',
        @password = N'<publisher_password>',
        @working_directory = N'\\replstorage.file.core.windows.net\replshare',
        @storage_connection_string = N'<storage_connection_string>'
        -- example: @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net'
   

   Observação

   Lembre-se de usar apenas barras invertidas (\) para o parâmetro @working_directory. O uso de uma barra (/) pode causar um erro ao se conectar ao compartilhamento de arquivo.

4. Conecte-se à instância gerenciada de SQL do editor (sql-mi-publisher).

5. Abra uma janela Nova Consulta e execute o seguinte código Transact-SQL para registrar o distribuidor no publicador:

   Use MASTER
   EXEC sys.sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'
   

Criar a publicação

Depois que a distribuição tiver sido configurada, você poderá criar a publicação. Para fazer isso, siga estas etapas:

1. Inicialize o SQL Server Management Studio no SQL Server.

2. Conecte-se à instância gerenciada de SQL do editor (sql-mi-publisher).

3. No Pesquisador de Objetos, expanda o nó Replicação e clique com o botão direito do mouse na pasta Publicação Local. Selecione Nova Publicação... .

4. Selecione Avançar para ir para a página inicial.

5. Na página Banco de Dados de Publicação, selecione o banco de dados ReplTutorial criado anteriormente. Selecione Avançar.

6. Na página Tipo de publicação, selecione Publicação transacional. Selecione Avançar.

7. Na página Artigos, marque a caixa ao lado de Tabelas. Selecione Avançar.

8. Na página Filtrar Linhas da Tabela, selecione Avançar sem adicionar nenhum filtro.

9. Na página Agente de Instantâneo, marque a caixa ao lado de Criar instantâneo imediatamente e manter o instantâneo disponível para inicializar assinaturas. Selecione Avançar.

10. Na página Segurança do Agente , selecione Configurações de Segurança.... Forneça as credenciais de logon do SQL Server a serem usadas para o Snapshot Agent e conecte-se ao publicador. Selecione OK para fechar a página Segurança do Agente de Instantâneo. Selecione Avançar.

    

11. Na página Ações do Assistente, escolha Criar a publicação e (opcionalmente) escolha Gerar um arquivo de script com etapas para criar a publicação, caso deseje salvar esse script para uso posterior.

12. Na página Concluir o Assistente , nomeie sua publicação ReplTeste selecione Avançar para criar sua publicação.

13. Depois que a publicação for criada, atualize o nó Replicação no Pesquisador de Objetos e expanda Publicações Locais para ver a nova publicação.

Criar a assinatura

Depois que a publicação for criada, você poderá criar a assinatura. Para fazer isso, siga estas etapas:

1. Inicialize o SQL Server Management Studio no SQL Server.
2. Conecte-se à instância gerenciada de SQL do editor (sql-mi-publisher).
3. Abra uma janela Nova Consulta e execute o código Transact-SQL a seguir para adicionar a assinatura e o agente de distribuição. Use o DNS como parte do nome do assinante.

use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'sql-vm-subscriber.repldns.com', -- include the DNS configured in the private DNS zone
@destination_db = N'ReplSub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

exec sp_addpushsubscription_agent
@publication = N'ReplTest',
@subscriber = N'sql-vm-subscriber.repldns.com', -- include the DNS configured in the private DNS zone
@subscriber_db = N'ReplSub',
@job_login = N'azureuser',
@job_password = '<Complex Password>',
@subscriber_security_mode = 0,
@subscriber_login = N'azureuser',
@subscriber_password = '<Complex Password>',
@dts_package_location = N'Distributor'
GO

Testar a replicação

Depois que a replicação for configurada, teste-a inserindo novos itens no publicador e observando as alterações se propagarem para o assinante.

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

Use ReplSub
select * from dbo.ReplTest

Execute o snippet T-SQL a seguir para inserir linhas adicionais no publicador e, em seguida, verifique as linhas novamente no assinante.

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

Limpar os recursos

1. Procure o grupo de recursos no portal do Azure.
2. Selecione as instâncias gerenciadas de SQL e, em seguida, selecione Excluir. Digite yes a caixa de texto para confirmar se deseja excluir o recurso e selecione Excluir. Esse processo pode levar algum tempo para ser concluído em segundo plano e, até que seja concluído, você não poderá excluir o cluster virtual ou quaisquer outros recursos dependentes. Monitore a exclusão na guia Atividade para confirmar se a instância gerenciada do SQL foi excluída.
3. Depois que a instância gerenciada de SQL for excluída, exclua o cluster virtual selecionando-o em seu grupo de recursos e, em seguida, escolhendo Excluir. Digite yes a caixa de texto para confirmar se deseja excluir o recurso e selecione Excluir.
4. Exclua todos os recursos restantes. Digite yes a caixa de texto para confirmar se deseja excluir o recurso e selecione Excluir.
5. Exclua o grupo de recursos selecionando Excluir grupo de recursos, digitando o nome do grupo myResourceGroupde recursos e selecionando Excluir.

Erros conhecidos

Não há suporte para logons do Windows

Exception Message: Windows logins are not supported in this version of SQL Server.

O agente foi configurado com um logon do Windows e precisa usar um logon do SQL Server. Use a página Segurança do Agente das Propriedades da publicação para alterar as credenciais de logon para um logon do SQL Server.

Falha ao se conectar ao Armazenamento do Azure

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 53.
2019-11-19 02:21:05.07 Obtained Azure Storage Connection String for replstorage
2019-11-19 02:21:05.07 Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare'
2019-11-19 02:21:31.21 Failed to connect to Azure Storage '' with OS error: 53.

Isso provavelmente ocorre porque a porta 445 está fechada no firewall do Azure, no Firewall do Windows ou em ambos.

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 55.

Usar uma barra avançada em vez de barra invertida no caminho do arquivo para o compartilhamento de arquivos também pode causar esse erro.

• Isso é aceitável: \\replstorage.file.core.windows.net\replshare
• Isso pode causar um erro do sistema operacional 55: \\replstorage.file.core.windows.net/replshare

Não foi possível se conectar ao Assinante

The process could not connect to Subscriber 'SQL-VM-SUBSCRIBER
Could not open a connection to SQL Server [53].
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Soluções T Possíveis:

• Verifique se a porta 1433 está aberta.
• Verifique se o TCP/IP está habilitado no assinante.
• Confirme se o nome DNS foi usado ao criar o assinante.
• Verifique se as redes virtuais estão corretamente vinculadas na zona DNS privado.
• Verifique se o registro A está configurado corretamente.
• Verifique se o Emparelhamento VNET está configurado corretamente.

Não há publicações que você pode assinar

Ao adicionar uma nova assinatura usando o assistente Nova Assinatura , na página Publicação , você pode descobrir que não há bancos de dados e publicações listados como opções disponíveis e poderá ver a seguinte mensagem de erro:

There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access the publications.

Embora seja possível que essa mensagem de erro seja precisa e realmente não haja publicações disponíveis no editor ao qual você se conectou ou não tenha permissões suficientes, uma versão mais antiga do SQL Server Management Studio também pode causar esse erro. Tente atualizar para o SQL Server Management Studio 18.0 ou posterior para descartar isso como uma causa raiz.

Conteúdo relacionado

• O que é Instância Gerenciada de SQL do Azure?
• Introdução à auditoria da Instância Gerenciada de SQL do Azure
• Always Encrypted
• Configurar a Proteção Avançada contra Ameaças na Instância Gerenciada de SQL do Azure
• Mascaramento de dados dinâmicos
• Segurança em nível de linha
• TDE (transparent data encryption)