Esercitazione: Configurare la replica tra due istanze gestite di SQL

Si applica a:Istanza gestita di SQL di Azure SQL

La replica transazionale consente di replicare i dati da un database a un altro ospitato in SQL Server o in un'istanza gestita di SQL di Azure. Istanza gestita di SQL può essere un server di pubblicazione, un server di distribuzione o un sottoscrittore nella topologia di replica. Per le configurazioni disponibili, vedere Configurazioni di replica transazionale.

In questa esercitazione apprenderai a:

Questa esercitazione è destinata a un pubblico esperto e presuppone che l'utente abbia familiarità con la distribuzione e la connessione a istanze gestite di SQL e alle macchine virtuali di SQL Server all'interno di Azure.

Requisiti

Per configurare Istanza gestita di SQL in modo che svolga la funzione di server di pubblicazione e/o database di distribuzione, è necessario che siano soddisfatti i requisiti seguenti:

• L'istanza gestita di SQL del server di pubblicazione si trova nella stessa rete virtuale del server di distribuzione e del sottoscrittore oppure il peering reti virtuali o i gateway VPN sono stati configurati tra le reti virtuali di tutte e tre le entità.
• Per la connettività viene usata l'autenticazione SQL tra i partecipanti alla replica.
• Una condivisione di account di archiviazione di Azure per la directory di lavoro della replica.
• La porta 445 (TCP in uscita) è aperta nelle regole di sicurezza del gruppo di sicurezza di rete per le istanze gestite di SQL per accedere alla condivisione file di Azure. Se si verifica l'errore failed to connect to azure storage <storage account name> with os error 53, sarà necessario aggiungere una regola in uscita al gruppo di sicurezza di rete della subnet di Istanza gestita di SQL appropriata.

1 - Creare un gruppo di risorse

Usare il portale di Azure per creare un gruppo di risorse con il nome SQLMI-Repl.

2 - Creare istanze gestite di SQL

Usare il portale di Azure per creare due istanze gestite di SQL nella stessa rete virtuale e nella stessa subnet. Ad esempio, denominare le due istanze gestite di SQL:

• sql-mi-publisher (con alcuni caratteri per la sequenza casuale)
• sql-mi-subscriber (con alcuni caratteri per la sequenza casuale)

È anche necessario configurare una macchina virtuale di Azure per connettersi alle istanze gestite di SQL.

3 - Creare un account di archiviazione di Azure

Creare un account di archiviazione di Azure per la directory di lavoro e quindi creare una condivisione file al suo interno.

Copiare il percorso della condivisione file nel formato \\storage-account-name.file.core.windows.net\file-share-name

Esempio: \\replstorage.file.core.windows.net\replshare

Copiare la chiave di accesso alle risorse di archiviazione nel formato DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

Esempio: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

Per altre informazioni, vedere Gestire le chiavi di accesso dell'account di archiviazione.

4 - Creare un server di pubblicazione

Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher) usando SQL Server Management Studio ed eseguire il codice di Transact-SQL (T-SQL) seguente per creare il database di pubblicazione:

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 - Creare un database sottoscrittore

Connettersi all'istanza gestita di SQL del sottoscrittore (sql-mi-subscriber) usando SQL Server Management Studio ed eseguire il codice T-SQL seguente per creare il database sottoscrittore vuoto:

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 - Configurare la distribuzione

Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher) usando SQL Server Management Studio ed eseguire il codice T-SQL seguente per configurare il database di distribuzione.

USE [master]
GO

EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

7 - Configurare il server di pubblicazione per usare il server di distribuzione

Nell'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher) modificare l'esecuzione della query in modalità SQLCMD ed eseguire il codice seguente per registrare il nuovo server di distribuzione con il server di pubblicazione.

: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

Questo script configura un server di pubblicazione locale nell'istanza gestita di SQL, aggiunge un server collegato e crea un set di processi per SQL Server Agent.

8 - Creare la pubblicazione e il database sottoscrittore

Usando la modalità SQLCMD, eseguire lo script T-SQL seguente per abilitare la replica per il database e configurare la replica tra il server di pubblicazione, il database di distribuzione e il database sottoscrittore.

-- 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 - Modificare i parametri dell'agente

In Istanza gestita SQL di Azure si stanno verificando problemi di back-end con la connettività con gli agenti di replica. Anche se questo problema è stato risolto, la soluzione alternativa consiste nell'aumentare il valore di timeout di accesso per gli agenti di replica.

Eseguire il comando T-SQL seguente nel server di pubblicazione per aumentare il timeout di accesso:

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

Eseguire di nuovo il comando T-SQL seguente se necessario per impostare nuovamente il timeout di accesso sul valore predefinito:

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

Per applicare queste modifiche, riavviare tutti e tre gli agenti.

10 - Testare la replica

Dopo aver configurato la replica, è possibile testarla inserendo nuovi elementi nel server di pubblicazione e osservando le modifiche propagate al sottoscrittore.

Eseguire il frammento di codice T-SQL seguente per visualizzare le righe nel sottoscrittore:

select * from dbo.ReplTest

Eseguire il frammento di codice T-SQL seguente per inserire altre righe nel server di pubblicazione e quindi controllare di nuovo le righe nel sottoscrittore.

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

Pulire le risorse

Per eliminare la pubblicazione, eseguire il comando T-SQL seguente:

-- Drops the publication
USE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'
GO

Per rimuovere l'opzione di replica dal database, eseguire il comando T-SQL seguente:

-- Disables publishing of the database
USE [ReplTran_PUB]
EXEC sp_removedbreplication
GO

Per disabilitare la pubblicazione e la distribuzione, eseguire il comando T-SQL seguente:

-- Drops the distributor
USE [master]
EXEC sp_dropdistributor @no_checks = 1
GO

È possibile pulire le risorse di Azure eliminando le risorse di Istanza gestita di SQL dal gruppo di risorse e quindi eliminando il gruppo di risorse SQLMI-Repl.

Contenuti correlati

• Replica transazionale con Istanza gestita di SQL di Azure
• Esercitazione: Configurare la replica transazionale tra Istanza gestita di SQL di Azure e SQL Server