Zelfstudie: Replicatie tussen twee met SQL beheerde exemplaren configureren

Van toepassing op:Azure SQL Managed Instance

Met transactionele replicatie kunt u gegevens van de ene database repliceren naar een andere die wordt gehost op SQL Server of Azure SQL Managed Instance. SQL Managed Instance kan een uitgever, distributeur of abonnee zijn in de replicatietopologie. Zie transactionele replicatieconfiguraties voor beschikbare configuraties.

In deze handleiding leer je hoe je:

Deze zelfstudie is bedoeld voor een ervaren doelgroep en gaat ervan uit dat de gebruiker bekend is met het implementeren en maken van verbinding met zowel beheerde SQL-exemplaren als SQL Server-VM's in Azure.

Requirements

Voor het configureren van SQL Managed Instance als uitgever en/of distributeur is het volgende vereist:

• Het beheerde exemplaar van DE uitgever bevindt zich in hetzelfde virtuele netwerk als de distributeur en de abonnee, of VNet-peering of VPN-gateways zijn geconfigureerd tussen de virtuele netwerken van alle drie de entiteiten.
• Connectiviteit maakt gebruik van SQL-verificatie tussen replicatiedeelnemers.
• Een Azure Storage-accountshare voor de replicatiewerkmap.
• Poort 445 (TCP uitgaand) is geopend in de beveiligingsregels van NSG voor de met SQL beheerde exemplaren voor toegang tot de Azure-bestandsshare. Als de fout failed to connect to azure storage <storage account name> with os error 53optreedt, moet u een uitgaande regel toevoegen aan de NSG van het juiste sql Managed Instance-subnet.

1 - Een resourcegroep maken

Gebruik Azure Portal om een resourcegroep met de naam SQLMI-Replte maken.

2 - Beheerde SQL-exemplaren maken

Gebruik Azure Portal om twee met SQL beheerde exemplaren te maken in hetzelfde virtuele netwerk en subnet. Geef bijvoorbeeld de twee beheerde SQL-exemplaren een naam:

• sql-mi-publisher (samen met een aantal tekens voor randomisatie)
• sql-mi-subscriber (samen met een aantal tekens voor randomisatie)

U moet ook een Azure-VM configureren om verbinding te maken met uw met SQL beheerde exemplaren.

3 - Een Azure-opslagaccount maken

Maak een Azure-opslagaccount voor de werkmap en maak vervolgens een bestandsshare in het opslagaccount.

Kopieer het bestandssharepad in de indeling van: \\storage-account-name.file.core.windows.net\file-share-name

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

Kopieer de toegangssleutels voor opslag in de indeling van: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

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

Zie Toegangssleutels voor opslagaccounts beheren voor meer informatie.

4 - Een uitgeverdatabase maken

Maak verbinding met uw beheerde EXEMPLAAR van SQLsql-mi-publisher () met behulp van SQL Server Management Studio en voer de volgende Transact-SQL (T-SQL)-code uit om uw uitgeverdatabase te maken:

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 - Een abonneedatabase maken

Maak verbinding met uw abonnee met SQL Managed Instance (sql-mi-subscriber) met behulp van SQL Server Management Studio en voer de volgende T-SQL-code uit om uw lege abonneedatabase te maken:

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 - Distributie configureren

Maak verbinding met uw beheerdesql-mi-publisher EXEMPLAAR van SQL () met behulp van SQL Server Management Studio en voer de volgende T-SQL-code uit om uw distributiedatabase te configureren.

USE [master]
GO

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

7 - Uitgever configureren voor gebruik van distributeur

Wijzig de queryuitvoering in de SQLCMD-modus in uw uitgever met SQL Managed Instance (sql-mi-publisher) en voer de volgende code uit om de nieuwe distributeur te registreren bij uw uitgever.

: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

Met dit script configureert u een lokale uitgever op het beheerde SQL-exemplaar, voegt u een gekoppelde server toe en maakt u een set taken voor de SQL Server-agent.

8 - Publicatie en abonnee maken

Voer met behulp van de SQLCMD-modus het volgende T-SQL-script uit om replicatie in te schakelen voor uw database en configureer replicatie tussen uw uitgever, distributeur en abonnee.

-- 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 - Agentparameters wijzigen

Azure SQL Managed Instance ondervindt momenteel enkele back-endproblemen met connectiviteit met de replicatieagents. Hoewel dit probleem wordt opgelost, is de tijdelijke oplossing om de time-outwaarde voor aanmelding voor de replicatieagenten te verhogen.

Voer de volgende T-SQL-opdracht uit op de uitgever om de time-out voor aanmelding te verhogen:

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

Voer indien nodig de volgende T-SQL-opdracht opnieuw uit om de time-out voor aanmelding weer in te stellen op de standaardwaarde:

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

Als u deze wijzigingen wilt toepassen, start u alle drie de agents opnieuw op.

10 - Replicatie testen

Zodra de replicatie is geconfigureerd, kunt u deze testen door nieuwe items op de uitgever in te voegen en de wijzigingen door te geven aan de abonnee.

Voer het volgende T-SQL-fragment uit om de rijen op de abonnee weer te geven:

select * from dbo.ReplTest

Voer het volgende T-SQL-fragment uit om meer rijen in te voegen in de uitgever en controleer de rijen opnieuw op de abonnee.

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

De hulpbronnen opschonen

Voer de volgende T-SQL-opdracht uit om de publicatie te verwijderen:

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

Als u de replicatieoptie uit de database wilt verwijderen, voert u de volgende T-SQL-opdracht uit:

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

Voer de volgende T-SQL-opdracht uit om publicatie en distributie uit te schakelen:

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

U kunt uw Azure-resources opschonen door de SQL Managed Instance-resources uit de resourcegroep te verwijderen en vervolgens de resourcegroep SQLMI-Replte verwijderen.

Verwante inhoud

• Transactionele replicatie met Azure SQL Managed Instance
• Zelfstudie: Transactionele replicatie configureren tussen Azure SQL Managed Instance en SQL Server