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

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:

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.

Pré-requisitos

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

• Uma subscrição do Azure.
• Experiência com a implantação de duas instâncias gerenciadas dentro da 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 superior.
• 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 trecho de código do PowerShell para criar um novo 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

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

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

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

  

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

Criar uma VM do SQL Server

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

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

Para obter mais informações sobre como implantar uma VM do SQL Server no Azure, consulte Guia de início rápido: criar uma VM do SQL Server.

Configurar o VNet Peering

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

# Set variables
$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-sub-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 de rede virtual for estabelecido, teste a conectividade iniciando o SQL Server Management Studio (SSMS) no SQL Server e conectando-se a ambas as instâncias gerenciadas. Para obter mais informações sobre como se conectar a uma instância gerenciada usando o SSMS, consulte Usar o SSMS para se conectar à instância gerenciada do SQL.

Criar uma zona DNS privada

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

Criar uma zona DNS privada

1. Inicie sessão no Portal do Azure.

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

3. private dns zone Pesquise no Azure Marketplace.

4. Escolha o recurso de zona DNS privada 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 sua zona DNS, como repldns.com.

   

7. Selecione Rever + criar. Reveja os parâmetros para a sua zona DNS privada e, em seguida, selecione Criar para criar o seu recurso.

Criar um registo A

1. Vá para a sua nova zona DNS privada e selecione Visão geral.

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

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

   

4. Selecione OK para criar o registro A.

Ligar a rede virtual

1. Vá para sua nova zona DNS privada 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, em seguida, selecione a rede virtual para sua instância gerenciada pelo editor.

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

   

6. Selecione OK para vincular sua rede virtual.

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

Criar uma base de dados

Crie um novo banco de dados na instância gerenciada pelo editor. Para o fazer, siga estes passos:

1. Inicie o SQL Server Management Studio no SQL Server.
2. Conecte-se à sql-mi-publisher instância gerenciada.
3. Abra uma janela Nova consulta e execute a seguinte consulta T-SQL 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, poderá configurar a distribuição em sua sql-mi-distributor instância gerenciada. Para o fazer, siga estes passos:

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

2. Conecte-se à sql-mi-distributor instância gerenciada.

3. Abra uma janela Nova consulta e execute o seguinte código Transact-SQL para configurar a distribuição na instância gerenciada pelo 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=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net'
   
   

   Nota

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

4. Conecte-se à sql-mi-publisher instância gerenciada.

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

   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 o fazer, siga estes passos:

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

2. Conecte-se à sql-mi-publisher instância gerenciada.

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 passar pela página de boas-vindas.

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

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

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

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

9. Na página Snapshot Agent, marque a caixa ao lado de Criar snapshot imediatamente e mantenha o snapshot disponível para inicializar assinaturas. Selecione Seguinte.

10. Na página Segurança do Agente, selecione Configurações de Segurança.... Forneça credenciais de logon do SQL Server para usar no Snapshot Agent e conectar-se ao editor. Selecione OK para fechar a página Segurança do Snapshot Agent. Selecione Seguinte.

    

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 se quiser salvar esse script para mais tarde.

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

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 subscrição

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

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

use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'sql-vm-sub.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-sub.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

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:

Use ReplSub
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.

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

Clean up resources (Limpar recursos)

1. Navegue até o seu grupo de recursos no portal do Azure.
2. Selecione a(s) instância(s) gerenciada(s) e, em seguida, selecione Excluir. Digite yes a caixa de texto para confirmar que 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 que sua instância gerenciada foi excluída.
3. Depois que a instância gerenciada for excluída, exclua o cluster virtual selecionando-o no grupo de recursos e escolhendo Excluir. Digite yes a caixa de texto para confirmar que deseja excluir o recurso e selecione Excluir.
4. Exclua todos os recursos restantes. Digite yes a caixa de texto para confirmar que deseja excluir o recurso e selecione Excluir.
5. Exclua o grupo de recursos selecionando Excluir grupo de recursos, digitando o nome do grupo de recursos e selecionando Excluir. myResourceGroup

Erros conhecidos

Não há suporte para logins 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 de Publicação para alterar as credenciais de logon para um logon do SQL Server.

Falha ao ligar ao Armazenamento do Microsoft 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 Obtida Cadeia de Conexão de Armazenamento do Azure para replstorage 2019-11-19 02:21:05.07 Conectando-se ao Armazenamento de Arquivos do Azure '\replstorage.file.core.windows.net\replshare' 2019-11-19 02:21:31.21 Falha ao se conectar ao Armazenamento do Azure '' com erro do sistema operacional: 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 invertida em vez de barra invertida no caminho do arquivo para o compartilhamento de arquivos pode causar esse erro.

• Tudo bem: \\replstorage.file.core.windows.net\replshare
• Isso pode causar um erro do OS 55: '\\replstorage.file.core.windows.net/replshare'

Não foi possível conectar-se ao Assinante

The process could not connect to Subscriber 'SQL-VM-SUB 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 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 suas redes virtuais estão vinculadas corretamente na zona DNS privada.
• Verifique se o registro A está configurado corretamente.
• Verifique se o emparelhamento de rede virtual está configurado corretamente.

Não há publicações que possa subscrever

Quando você estiver adicionando uma nova assinatura usando o assistente para Nova assinatura , na página Publicação , você pode achar que não há bancos de dados e publicações listadas como opções disponíveis e você pode 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 que você não tenha permissões suficientes, esse erro também pode ser causado por uma versão mais antiga do SQL Server Management Studio. Tente atualizar para o SQL Server Management Studio 18.0 ou superior para descartar isso como uma causa raiz.

Próximos passos

Ativar recursos de segurança

Consulte o artigo O que é a Instância Gerenciada SQL do Azure? para obter uma lista abrangente de maneiras de proteger seu banco de dados. São discutidos os seguintes elementos de segurança:

• Auditoria de instância gerenciada SQL
• Always Encrypted
• Deteção de ameaças
• Máscara de dados dinâmica
• Segurança ao nível da linha
• Encriptação de dados transparente (TDE)

Recursos da instância gerenciada SQL

Para obter uma visão geral completa dos recursos da instância gerenciada, consulte: