Condividi tramite

Configurare il database di distribuzione repliche nel gruppo di disponibilità Always On

  • Articolo

Si applica a: SQL Server

Questo articolo illustra come configurare un database di distribuzione della replica di SQL Server in un gruppo di disponibilità AlwaysOn.

SQL Server 2017 CU6 e SQL Server 2016 SP2-CU3 introducono il supporto per i database di distribuzione repliche in un gruppo di disponibilità tramite i meccanismi seguenti:

  • Il gruppo di disponibilità del database di distribuzione deve avere un listener. Quando il server di pubblicazione aggiunge il server di distribuzione, usa il nome del listener come nome del server di distribuzione.
  • I processi di replica vengono creati con il nome del listener come nome del server di distribuzione. I processi di snapshot di replica, di lettura log e dell'agente di distribuzione (sottoscrizione push) creati nel server di distribuzione vengono creati in tutte le repliche secondarie del gruppo di disponibilità per il database di distribuzione.

Nota

I progetti dell'agente di distribuzione per le sottoscrizioni pull vengono creati nel server di sottoscrizione e non nel server di distribuzione.

  • Un nuovo processo consente di monitorare lo stato dei database di distribuzione (primari o secondari nel gruppo di disponibilità) e di disabilitare o abilitare i processi di replica in base allo stato dei database di distribuzione.

Dopo aver configurato un database di distribuzione nel gruppo di disponibilità in base ai passaggi descritti di seguito, i processi di runtime e la configurazione delle repliche possono essere eseguiti correttamente prima e dopo il failover del gruppo di disponibilità del database di distribuzione.

Scenari supportati

  • Configurazione del database di distribuzione da includere in un gruppo di disponibilità.
  • Configurazione delle repliche, come ad esempio pubblicazioni e sottoscrizioni prima e dopo il failover del gruppo di disponibilità.
  • Processi di replica funzionali prima e dopo il failover.
  • Rimozione della replica nel server di distribuzione e nel server di pubblicazione quando il database di distribuzione si trova nel gruppo di disponibilità.
  • Aggiunta o rimozione di nodi nel gruppo di disponibilità del database di distribuzione esistente.
  • Un'istanza del server di distribuzione può avere più database di distribuzione. Ogni database di distribuzione può essere nel proprio gruppo di disponibilità e non può essere in tutti i gruppi di disponibilità. Più database di distribuzione possono condividere un gruppo di disponibilità.
  • Il server di pubblicazione e il server di distribuzione devono essere in istanze separate di SQL Server.
  • Se il listener per il gruppo di disponibilità che ospita il database di distribuzione è configurato per l'uso di una porta non predefinita, è necessario configurare un alias per il listener e la porta non predefinita.

Limitazioni o esclusioni

  • Il server di distribuzione locale (in cui il server di pubblicazione è anche il server di distribuzione) non è supportato. Il server di pubblicazione e il server di distribuzione devono essere in istanze di SQL Server separate. Queste istanze possono essere ospitate nello stesso set di nodi. Un server di distribuzione locale non è supportato per i motivi seguenti:

    • Se il server di distribuzione è configurato a livello locale, non è possibile usare il listener del gruppo di disponibilità per instradare il traffico al server di distribuzione, causando l'esito negativo degli agenti di replica dopo il failover.
    • Se un server di distribuzione locale è configurato e il gruppo di disponibilità del server di distribuzione esegue il failover nel database secondario originale, la connessione del server di pubblicazione al server di distribuzione passa da locale a remota, causando l'esito negativo delle stored procedure e degli agenti di replica.

  • Il server di pubblicazione Oracle non è supportato.

  • La replica di tipo merge non è supportata.

  • La replica transazionale con sottoscrittore ad aggiornamento immediato o in coda non è supportata.

  • La replica peer-to-peer non è supportata prima di SQL Server 2019 (15.x) CU 17

  • Tutte le istanze di SQL Server 2017 che ospitano le repliche del database di distribuzione devono essere SQL Server 2017 CU 6 o versione successiva.

  • Tutte le istanze di SQL Server 2016 che ospitano le repliche del database di distribuzione devono essere SQL Server 2016 SP2-CU3 o versione successiva.

  • Tutte le istanze di SQL Server che ospitano le repliche del database di distribuzione devono essere della stessa versione, tranne nel breve intervallo di tempo durante il quale viene eseguito l'aggiornamento.

  • Il database di distribuzione deve essere nel modello di recupero con registrazione completa.

  • Per il ripristino e per consentire il troncamento del log delle transazioni, configurare il backup del log delle transazioni e il backup completo.

  • Il gruppo di disponibilità del database di distribuzione deve avere un listener configurato.

  • Le repliche secondarie in un gruppo di disponibilità del database di distribuzione possono essere sincrone o asincrone. La modalità sincrona è consigliata e preferita.

  • La replica transazionale bidirezionale non è supportata.

  • SSMS non visualizza il database di distribuzione come in fase di sincronizzazione/sincronizzato quando il database di distribuzione viene aggiunto a un gruppo di disponibilità.

    Nota

    Prima di eseguire qualsiasi stored procedure di replica (ad esempio, sp_dropdistpublisher, sp_dropdistributiondb, sp_dropdistributor, sp_adddistributiondb, sp_adddistpublisher) nella replica secondaria, verificare che la replica sia completamente sincronizzata.

  • Tutte le repliche secondarie in un gruppo di disponibilità del database di distribuzione devono essere leggibili. Se una replica secondaria non è leggibile, non è possibile accedere alle proprietà del server di distribuzione in SQL Server Management Studio nella replica secondaria specifica, ma la replica continuerà a funzionare correttamente.

  • Tutti i nodi del gruppo di disponibilità del database di distribuzione devono usare lo stesso account di dominio per eseguire SQL Server Agent, e tale account di dominio deve avere lo stesso privilegio in ogni nodo.

  • Se vi sono agenti di replica che vengono eseguiti in un account proxy, l'account proxy deve esistere in ogni nodo del gruppo di disponibilità del database di distribuzione e avere lo stesso privilegio in ogni nodo.

  • Apportare modifiche alle proprietà del server di distribuzione o del database di distribuzione in tutte le repliche del gruppo di disponibilità del database di distribuzione.

  • Modificare i processi di replica tramite stored procedure msdb o SQL Server Management Studio in tutte le repliche del gruppo di disponibilità del database di distribuzione.

  • Se si usa un profilo personalizzato per qualsiasi agente, è necessario crearlo manualmente in tutte le repliche secondarie usando la procedura sp_add_agent_profile. Il profilo deve avere lo stesso ID in tutte le repliche. Se il profilo non esiste in una replica secondaria, potrebbero verificarsi errori di violazione della chiave primaria dopo il failover. È probabile che sia necessario reinizializzare la sottoscrizione per la pubblicazione per risolvere gli errori.

  • La configurazione del server di distribuzione nel server di pubblicazione deve essere eseguita con gli script. Non è possibile usare la procedura guidata di replica. Le procedure guidate di replica e le finestre delle proprietà sono supportate per altri scopi.

  • La configurazione del gruppo di disponibilità per i database di distribuzione può essere eseguita solo tramite script.

  • Per configurare i database di distribuzione in un gruppo di disponibilità è necessario configurare una nuova replica. Il passaggio di un database di distribuzione esistente a un gruppo di disponibilità non è supportato. Una volta che un database di distribuzione viene tolto da un gruppo di disponibilità, non può più essere usato come database di distribuzione valido e deve essere eliminato.

Architettura della configurazione

Negli esempi in questo articolo vengono usati i nomi dei server e le impostazioni seguenti.

  • DIST1, DIST2, DIST3 sono server di distribuzione;
  • PUB è il server di pubblicazione;
  • Dopo che è stato creato il gruppo di disponibilità, il nome del listener è DISTLISTENER;
  • DIST1 è la replica primaria iniziale del gruppo di disponibilità del database di distribuzione.

Configurare server di distribuzione, database di distribuzione e server di pubblicazione

In questo esempio vengono configurati un nuovo server di distribuzione e un nuovo server di pubblicazione e il database di distribuzione viene aggiunto a un gruppo di disponibilità.

Flusso di lavoro dei server di distribuzione

  1. Configurare DIST1, DIST2, DIST3 come server di distribuzione con sp_adddistributor @@servername. Specificare la password per distributor_admin mediante l'elemento @password. L'elemento @password deve essere identico in DIST1, DIST2, DIST3.

  2. Creare il database di distribuzione in DIST1 con sp_adddistributiondb. Il nome del database di distribuzione è distribution. Modificare il modello di recupero del database distribution da recupero con registrazione minima a recupero con registrazione completa.

  3. Creare un gruppo di disponibilità per il database distribution con le repliche in DIST1, DIST2 e DIST3. È preferibile che tutte le repliche siano sincrone. Configurare le repliche secondarie in modo che siano leggibili o che consentano la lettura. A questo punto, i database di distribuzione sono nel gruppo di disponibilità, DIST1 è la replica primaria e DIST2 e DIST3 sono repliche secondarie.

  4. Configurare un listener denominato DISTLISTENER per il gruppo di disponibilità.

  5. Per il ripristino e per consentire il troncamento del log delle transazioni, configurare il backup del log delle transazioni e il backup completo.

  6. In DIST2 e DIST3 eseguire:

    EXEC sys.sp_adddistributiondb @database = 'distribution';

  7. Per aggiungere PUB come server di pubblicazione in DIST1, eseguire:

    EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

    Il valore di @working_directory deve essere un percorso di rete indipendente da DIST1, DIST2 e DIST3.

  8. In DIST2 e DIST3, se la replica è leggibile come secondaria, eseguire il codice seguente:

    EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

    Se una replica non è leggibile come secondaria, eseguire il failover in modo che la replica diventi primaria ed eseguita

    EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

    Il valore di @working_directory deve coincidere con quello del passaggio precedente.

Flusso di lavoro del server di pubblicazione

Per aggiungere il listener del gruppo di disponibilità del database distribution come server di distribuzione, eseguire in PUB:

EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;

Il valore di @password deve essere quello specificato quando i server di distribuzione sono stati configurati nel flusso di lavoro del server di distribuzione.

Rimuovere il server di distribuzione e il server di pubblicazione

In questo esempio vengono rimossi il server di pubblicazione e il server di distribuzione quando il database di distribuzione si trova nel gruppo di disponibilità.

Flusso di lavoro del server di pubblicazione

In PUB eliminare tutte le sottoscrizioni e le pubblicazioni per il server di pubblicazione e chiamare sp_dropdistributor.

Flusso di lavoro dei server di distribuzione

In questo esempio DIST1 è la replica primaria corrente del gruppo di disponibilità del database distribution. DIST2 e DIST3 sono repliche secondarie.

  1. In DIST2 e DIST3 eseguire:

    EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

  2. In DIST1 eseguire:

    EXEC sys.sp_dropdistpublisher @publisher = 'PUB';

  3. Eliminare il gruppo di disponibilità.

  4. In DIST2 e DIST3 modificare il database distribution in modalità read_write ripristinando il database con il recupero.

    RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;

  5. Per eliminare il database distribution e per mantenere la directory di snapshot, eseguire:

    EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;

Questa procedura rimuove tutti i processi tralasciati nella replica.

  1. Per eliminare il database distribution in DIST1, eseguire

    EXEC sys.sp_dropdistributiondb @database = 'distribution';

  2. Se non sono disponibili altri database di distribuzione nel gruppo di disponibilità, eseguire sp_dropdistributor in DIST1, DIST2 e DIST3.

Aggiungere una replica al gruppo di disponibilità del database di distribuzione

In questo esempio viene aggiunto un nuovo server di distribuzione a una configurazione di replica esistente con un database di distribuzione nel gruppo di disponibilità. In questo esempio un database di distribuzione esistente è in un gruppo di disponibilità. DIST1 e DIST2 sono i server di distribuzione, distribution è il database di distribuzione nel gruppo di disponibilità e PUB è il server di pubblicazione. Aggiungere DIST3 come replica del gruppo di disponibilità.

Flusso di lavoro dei server di distribuzione

  1. DIST3 deve essere configurato come server di distribuzione tramite sp_adddistributor @@servername. La password per distributor_admin deve essere specificata tramite il parametro @password. La password deve essere quella specificata per DIST1 e DIST2.

  2. Aggiungere DIST3 al gruppo di disponibilità per il database di distribuzione corrente.

  3. In DIST3 eseguire:

    EXEC sys.sp_adddistributiondb @database = 'distribution';

  4. In DIST3, se la replica è leggibile come secondaria, eseguire il codice seguente:

    EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

    Se la replica non è leggibile come secondaria, eseguire il failover in modo che la replica diventi primaria ed eseguire:

    EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';

    Il valore di @working_directory deve essere quello specificato per DIST1 e DIST2.

  5. In DIST3 è necessario ricreare i server collegati presso i sottoscrittori.

Rimuovere una replica dal gruppo di disponibilità del database di distribuzione

In questo esempio viene rimosso un server di distribuzione da un gruppo di disponibilità del database di distribuzione corrente senza alcun effetto sulle repliche nel gruppo di disponibilità del database di distribuzione. In questo esempio il database di distribuzione è in un gruppo di disponibilità. DIST1, DIST2 e DIST3 sono i server di distribuzione, distribution è il database di distribuzione nel gruppo di disponibilità, e PUB è il server di pubblicazione. Rimuovere DIST3 dal gruppo di disponibilità.

Flusso di lavoro dei server di distribuzione

  1. Assicurarsi che DIST3 sia un server secondario per il gruppo di disponibilità del database distribution.

  2. Rimuovere DIST3 dal gruppo di disponibilità del database distribution.

  3. In DIST3 modificare il database distribution in modalità read_write ripristinando il database con il recupero. Ad esempio, eseguire il comando seguente:

    RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;

  4. Per rimuovere tutti i processi orfani in DIST3, eseguire:

    EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;

  5. In DIST3 eseguire:

    EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;

  6. In DIST3 eseguire:

    EXEC sys.sp_dropdistributor;

Rimuovere un server di pubblicazione dal gruppo di disponibilità del database di distribuzione

In questo esempio viene rimosso un server di pubblicazione da un gruppo di disponibilità del database di distribuzione corrente del server di distribuzione senza alcun effetto sui restanti server di pubblicazione gestiti dal gruppo di disponibilità del database di distribuzione. In questo esempio la configurazione esistente include un database di distribuzione in un gruppo di disponibilità. DIST1, DIST2 e DIST3 sono i server di distribuzione, distribution è il database di distribuzione nel gruppo di disponibilità e PUB1 e PUB2 sono i server di pubblicazione serviti dal database distribution. Nell'esempio PUB1 viene rimosso dai server di distribuzione.

Flusso di lavoro del server di pubblicazione

In PUB1 eliminare tutte le sottoscrizioni e le pubblicazioni per il server di pubblicazione e chiamare sp_dropdistributor.

Flusso di lavoro del server di distribuzione

DIST1 è la replica primaria corrente del gruppo di disponibilità del database distribution.

  1. In DIST2 e DIST3 eseguire:

    EXEC sys.sp_dropdistpublisher @publisher = 'PUB1',  @no_checks = 1;

  2. In DIST1 eseguire:

    EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';

  3. A questo punto, potrebbero esservi processi orfani correlati a PUB1 in DIST2 o DIST3. Ogni volta che si verifica un failover in DIST2 e DIST3, i processi orfani correlati a tutte le pubblicazioni di PUB1 verranno rimossi durante il processo Monitor and sync replication agent jobs.

Aggiungere una sottoscrizione

Questo esempio illustra la configurazione corretta delle informazioni relative al sottoscrittore tra i server di distribuzione. Nell'esempio viene aggiunto un sottoscrittore. DIST1 è la replica primaria corrente del database di distribuzione nel gruppo di disponibilità, DIST2 e DIST3 sono repliche secondarie del database di distribuzione nel gruppo di disponibilità. Il nome del sottoscrittore è SUB.

Flusso di lavoro del server di pubblicazione

In PUB aggiungere una sottoscrizione come si farebbe normalmente al sottoscrittore SUB.

Flusso di lavoro del server di distribuzione

In DIST2 e DIST3 aggiungere un server collegato per "SUB" se non è registrato in precedenza con DIST2 o DIST3. Di seguito è riportato un TSQL di esempio per la creazione del server collegato.

EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;

Aggiungere una sottoscrizione pull

Flusso di lavoro sottoscrittore

Per aggiungere una sottoscrizione pull per una pubblicazione con il database di distribuzione in un gruppo di disponibilità, usare il nome del listener del gruppo di disponibilità nel parametro @distributor di sp_addpullsubscription_agent.

Esempio T-SQL per creare un database di distribuzione nel gruppo di disponibilità

Lo script seguente crea un database di distribuzione in un gruppo di disponibilità.

--- WorkFlow to Enable Distribution Database In AG.

-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS

-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
    @database = 'DistributionDB',
    @security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC);
GO


:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO  
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO

--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1

USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO

-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.	
	@password = 'Pass@word1';
GO

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
	@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;

Contenuto correlato