Tutorial: Configurar a replicação transacional entre Azure SQL Managed Instance e SQL Server

Aplica-se a: Azure SQL Managed Instance

A replicação transacional permite-lhe replicar dados de uma base de dados para outra alojada em SQL Server ou Azure SQL Managed Instance. SQL Managed Instance 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, você aprenderá a:

Este tutorial destina-se a um público experiente e pressupõe que o utilizador está familiarizado com a implementação e ligação tanto a instâncias geridas SQL como a VMs do SQL Server dentro do Azure.

Pré-requisitos

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

• Uma subscrição Azure.
• Experiência com a implantação de duas instâncias gerenciadas SQL na mesma rede virtual.
• Um assinante do SQL Server, seja on-premises ou numa VM Azure. Este tutorial utiliza uma máquina virtual do Azure.
• SQL Server Management Studio (SSMS) 18.0 ou posterior.
• A versão mais recente de Azure PowerShell.
• As portas 445 e 1433 permitem tráfego SQL tanto no firewall do Azure como no firewall do Windows.

Nomes e definições de rede obrigatórias

Este tutorial utiliza os seguintes nomes de recursos e definições:

Criar o grupo de recursos

Use o seguinte excerto de código PowerShell para criar um novo grupo de recursos.

Defina as suas variáveis:

$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

Crie o grupo de recursos:

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Verifique se o grupo de recursos foi criado:

Get-AzResourceGroup -Name $ResourceGroupName | Select-Object ResourceGroupName, Location

Criar duas instâncias gerenciadas SQL

Crie duas instâncias geridas SQL dentro deste novo grupo de recursos usando o portal Azure.

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

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

  

Para mais informações sobre como criar uma instância gerida em SQL, consulte Quickstart: Criar Azure SQL Managed Instance.

Criar uma VM SQL Server

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

• Nome: sql-vm-subscriber
• Imagem: versão do SQL Server que suporta replicação transacional com Azure SQL Managed Instance, segundo a matriz de suporte
• Grupo de recursos: o mesmo que a instância gerenciada SQL
• Rede virtual: sql-vm-subscriber-vnet

Para mais informações sobre como implementar uma VM SQL Server para Azure, consulte Quickstart: Criar SQL Server numa máquina virtual Windows no portal Azure.

Configurar o Peering de VNet

Configure VNet peering para permitir a comunicação entre a rede virtual das duas instâncias SQL geridas e a rede virtual do SQL Server.

Defina as suas variáveis:

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

Recuperar as redes virtuais:

$virtualNetwork1 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $pubvNet
$virtualNetwork2 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $subvNet

Configurar o peering de VNet do publicador para o assinante:

Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

Configurar o peering VNet entre o assinante e o editor:

Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

Verifique o estado de peering na VNet do publicador (deve devolver Connected):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $pubvNet | Select-Object PeeringState

Verificar o estado de peering no VNet do assinante (deve devolver Connected):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $subvNet | Select-Object PeeringState

Uma vez estabelecido o peering VNet, teste a conectividade lançando o SQL Server Management Studio (SSMS) no host do SQL Server e ligando-se a ambas as instâncias geridas por SQL. Para mais informações sobre como ligar a um SQL managed instance usando SSMS, veja Use SSMS para ligar a SQL Managed Instance.

Criar uma zona DNS privada

Uma zona DNS privada permite o encaminhamento DNS entre as instâncias geridas por SQL e o SQL Server.

Criar uma zona privada

1. Inicie sessão no portal Azure.

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

3. Pesquise por private dns zone no Azure Marketplace.

4. Escolha o recurso zona de DNS privado publicado pela Microsoft e depois selecione Criar para criar a zona de 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 Verificar + criar. Reveja os parâmetros da sua zona DNS privada e, em seguida, selecione Criar para criar o recurso.

Criar um registo A

1. Vai à tua nova zona DNS Privado e seleciona 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. Vai à tua nova zona DNS Privado e seleciona Ligações de rede virtual.

2. Selecione + Adicionar.

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

4. Selecione a sua assinatura na lista suspensa e, em seguida, selecione a rede virtual para a instância SQL gerida do publisher.

5. Marque a caixa ao lado de Ativar registo 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 no Azure

Crie uma conta de armazenamento Azure para o diretório de trabalho e depois crie uma partilha ficheiro dentro da conta de armazenamento.

Valores de configuração de armazenamento

Precisa dos seguintes valores ao configurar a distribuição:

• Formato do caminho do diretório de trabalho: \\<storage-account-name>.file.core.windows.net\<file-share-name>
• Storage cadeia de ligação format: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=<key>;EndpointSuffix=core.windows.net

Exemplos de valores usados neste tutorial:

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

Criar uma base de dados

Crie um novo banco de dados na instância SQL gerida do publicador. Para fazer isso, execute as seguintes etapas:

1. Inicie o SQL Server Management Studio no contexto do SQL Server.
2. Conecte-se à Instância Gerida SQL do publicador (sql-mi-publisher).
3. Abra uma nova janela de Consulta e execute as seguintes consultas T-SQL.

Abandone a base de dados se existir e crie uma nova:

USE [master];
GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial;
END
GO

CREATE DATABASE [ReplTutorial];
GO

Crie a tabela de teste de replicação:

USE [ReplTutorial];
GO

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

Inserir dados de exemplo:

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

Verifique se os dados foram inseridos:

SELECT * FROM ReplTest;
GO

Saída esperada: 5 linhas com IDs 2, 3, 4, 5 e 6.

Configurar distribuição

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

Parâmetros de configuração de distribuição

Recolha os seguintes valores antes de configurar a distribuição:

Configurar a instância do distribuidor

1. Inicie o SQL Server Management Studio no SQL Server.
2. Conecte-se à instância gerenciada SQL do distribuidor (sql-mi-distributor).
3. Abra uma nova janela de Consulta e execute os seguintes comandos.

Adicione o distribuidor:

EXECUTE sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Crie a base de dados de distribuição:

EXECUTE sp_adddistributiondb @database = N'distribution';

Verifique se a base de dados de distribuição foi criada:

SELECT name FROM sys.databases WHERE name = 'distribution';

Adicione o editor ao distribuidor:

EXECUTE sp_adddistpublisher
    @publisher = 'sql-mi-publisher.b6bf57.database.windows.net',
    @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'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net';

Registe o distribuidor na editora

1. Conecte-se à Instância Gerida SQL do publicador (sql-mi-publisher).
2. Abra uma nova janela de Consulta e execute o seguinte comando para registar o distribuidor:

USE master;
GO

EXECUTE sys.sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Verifique se o distribuidor está registado:

SELECT * FROM sys.servers WHERE is_distributor = 1;

Criar a publicação

Uma vez configurada a distribuição, você pode criar a publicação. Para fazer isso, execute as seguintes etapas:

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

2. Conecte-se à Instância Gerida SQL do publicador (sql-mi-publisher).

3. Em Object Explorer, expanda o nó Replication e clique com o botão direito na pasta Publicação Local. Selecione Nova Publicação....

4. Selecione Avançar para avançar além da página de boas-vindas.

5. Na página Banco de Dados de Publicações, selecione o banco de dados ReplTutorial que criou 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 Seguinte sem adicionar filtros.

9. Na página Snapshot Agent, marque a caixa ao lado de Criar captura instantânea imediatamente e manter a captura instantânea disponível para inicializar assinaturas. Selecione Seguinte.

10. Na página Agent Security, selecione Definições de Segurança... . Forneça credenciais de login do SQL Server para usar no Snapshot Agent e para se ligar ao publicador. Selecione OK para fechar a página Segurança do Agente de Instantâneos. Selecione Seguinte.

    

11. Na página Ações do Assistente, escolha Criar a publicação e (opcionalmente) escolha Gerar um ficheiro de script com etapas para criar a publicação se quiser guardar esse script para mais tarde.

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

13. Depois de a sua publicação ser criada, atualize o nó Replication no Object Explorer e expanda Publicações Locais para ver a sua nova publicação.

Criar a subscrição

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

1. Inicie o SQL Server Management Studio no contexto do SQL Server.
2. Conecte-se à Instância Gerida SQL do publicador (sql-mi-publisher).
3. Abra uma janela New Query e execute os seguintes comandos Transact-SQL. Use o nome DNS configurado na zona DNS privada como parte do nome do assinante.

Parâmetros de subscrição

Adicione a subscrição:

USE [ReplTutorial];
GO

EXEC sp_addsubscription
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @destination_db = N'ReplSub',
    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;

Adicione o agente de subscrição push:

EXEC sp_addpushsubscription_agent
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @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

Verifique se a subscrição foi criada:

SELECT * FROM distribution.dbo.MSsubscriptions;

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.

Ver dados iniciais sobre o assinante

Ligue-se ao assinante do SQL Server e execute a seguinte consulta:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest;

Resultado esperado: 5 linhas com IDs 2, 3, 4, 5 e 6 (os dados iniciais do publicador).

Inserir novos dados no publicador

Liga-te à instância gerida SQL do publisher (sql-mi-publisher) e insere uma nova linha:

USE ReplTutorial;
GO

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

Verifique a inserção no editor:

SELECT * FROM ReplTest WHERE ID = 15;

Verificar a replicação ao assinante

Após alguns momentos, ligue-se ao assinante e verifique a nova linha replicada:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest WHERE ID = 15;

Saída esperada: 1 linha com ID 15 e valor c1 'pub'.

Limpar recursos

1. Navega até ao teu grupo de recursos no portal Azure.
2. Selecione as instâncias gerenciadas pelo SQL 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 se sua instância gerenciada SQL foi excluída.
3. Depois que a instância gerenciada pelo SQL 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 myResourceGroupde recursos e selecionando Excluir.

Erros conhecidos

Os logins do Windows não são suportados

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

O agente foi configurado com login Windows e precisa de usar um login SQL Server em vez disso. Use a página Agent Security das propriedades da Publication para alterar as credenciais de login para um login do SQL Server.

Falhou a ligação 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.

Isto deve-se provavelmente ao facto de a porta 445 estar fechada no firewall Azure, no Windows Firewall, 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 em vez de barra inversa no caminho do ficheiro para o compartilhamento de ficheiros também pode causar esse erro.

• Isto é aceitável: \\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 Subscritor

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 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 estiver a adicionar uma nova subscrição utilizando o assistente para Nova Subscrição , na página Publicação , poderá verificar que não existem bases de dados e publicações listadas 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 esta mensagem de erro seja correta, e que realmente não existam publicações disponíveis na editora a que se ligou ou que não tenha permissões suficientes, uma versão mais antiga do SQL Server Management Studio também pode causar este erro. Tenta atualizar para o SQL Server Management Studio 18.0 ou posterior para descartar esta causa raiz.

Conteúdo relacionado

• O que é Azure SQL Managed Instance?
• Auditoria do SQL Server na Instância Gerida do Azure SQL
• Sempre criptografado
• Configurar Proteção Avançada contra Ameaças na Instância Gerida do Azure SQL
• Mascaramento dinâmico de dados
• Segurança ao nível da linha
• Encriptação de dados transparente (TDE)