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

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 em uma Instância Gerida do Azure SQL. 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, você 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 do 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 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 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 SQL

Crie duas instâncias gerenciadas SQL dentro desse novo grupo de recursos usando o portal do 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 obter mais informações sobre como criar uma instância gerenciada SQL, consulte Criar uma instância gerenciada SQL 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-subscriber
• Imagem: SQL Server 2016 ou posterior
• Grupo de recursos: o mesmo que a instância gerenciada SQL
• Rede virtual: sql-vm-subscriber-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 Peering de VNet

Configure o emparelhamento VNet para habilitar a comunicação entre a rede virtual das duas instâncias gerenciadas pelo SQL 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-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 de rede virtual for estabelecido, teste a conectividade iniciando o SQL Server Management Studio (SSMS) no host do SQL Server e conectando-se a ambas as instâncias gerenciadas pelo SQL. Para obter mais informações sobre como se conectar a uma instância gerenciada SQL usando o SSMS, consulte Usar o SSMS para se conectar à instância gerenciada do SQL.

Criando uma zona DNS privada

Uma zona DNS privada permite o roteamento DNS entre as instâncias gerenciadas pelo SQL 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. Pesquise private dns zone 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 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. Vá para o seu novo de zona DNS privada do 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. Aceda à sua nova zona Private DNS e selecione Ligações 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 SQL do editor.

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 do Azure

Crie uma conta de armazenamento do Azure para o diretório de trabalho e, em seguida, crie uma partilha de ficheiros 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=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

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 gerenciada SQL do editor. Para fazer isso, execute as seguintes etapas:

1. Inicie o SQL Server Management Studio no SQL Server.
2. Conecte-se à instância gerenciada SQL do editor (sql-mi-publisher).
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 na instância gerenciada SQL do distribuidor (sql-mi-distributor). Para fazer isso, execute as seguintes etapas:

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 janela Nova Consulta e execute o seguinte código Transact-SQL para configurar a distribuição na instância gerenciada 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

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

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

5. Abra uma janela Nova Consulta e execute o seguinte código de 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

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 SQL Server.

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

3. No Explorador de Objetos, expanda o nó Replicação e clique 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 do Snapshot Agent, marque a caixa ao lado de Criar instantâneo imediatamente e manter o instantâneo 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 Snapshot Agent Security. 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 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 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 SQL Server.
2. Conecte-se à instância gerenciada SQL do editor (sql-mi-publisher).
3. Abra uma janela New Query e execute o seguinte código de 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-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

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 comando T-SQL para exibir as linhas no subscritor:

Use ReplSub
select * from dbo.ReplTest

Execute o seguinte trecho de código T-SQL 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 recursos

1. Navegue até o seu 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

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 no Windows e precisa passar a usar um logon no SQL Server. Use a página Segurança do Agent do de propriedades de 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 invertida em vez de barra invertida no caminho do arquivo para o compartilhamento de arquivos 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 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.

T 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 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, 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 é a Instância Gerenciada SQL do Azure?
• Comece com a auditoria da Instância Gerenciada SQL do Azure
• Sempre criptografado
• Configurar a Proteção Avançada contra Ameaças na Instância Gerenciada SQL do Azure
• Mascaramento dinâmico de dados
• Segurança ao nível da linha
• Encriptação de dados transparente (TDE)