Configurare il routing di sola lettura in un gruppo di disponibilità Always On in Server on SQL AWS

Creato da Subhani Shaik () AWS

Ambiente: PoC o pilota	Tecnologie: database; infrastruttura	Carico di lavoro: Microsoft
AWSservizi: Microsoft AD AWS gestito; Amazon EC2

Riepilogo

Questo modello illustra come utilizzare la replica secondaria in standby in SQL Server Always On trasferendo i carichi di lavoro di sola lettura dalla replica principale alla replica secondaria.

Il mirroring del database prevede one-to-one la mappatura. Non è possibile leggere direttamente il database secondario, quindi è necessario creare istantanee. La funzionalità del gruppo di disponibilità Always On è stata introdotta in Microsoft SQL Server 2012. Nelle versioni successive, sono state introdotte funzionalità principali, incluso il routing in sola lettura. Nei gruppi di disponibilità Always On, è possibile leggere i dati direttamente dalla replica secondaria modificando la modalità di replica in sola lettura.

La soluzione Always On Availability Groups supporta l'alta disponibilità (HA), il disaster recovery (DR) e un'alternativa al mirroring del database. I gruppi di disponibilità Always On lavorano a livello di database e massimizzano la disponibilità di un set di database utente.

SQLIl server utilizza il meccanismo di routing di sola lettura per reindirizzare le connessioni di sola lettura in entrata alla replica di lettura secondaria. A tale scopo, è necessario aggiungere i seguenti parametri e valori nella stringa di connessione:

• ApplicationIntent=ReadOnly

• Initial Catalog=<database name>

Prerequisiti e limitazioni

Prerequisiti

• Un AWS account attivo con un cloud privato virtuale (VPC), due zone di disponibilità, sottoreti private e un gruppo di sicurezza

• Due macchine Amazon Elastic Compute Cloud (AmazonEC2) con Amazon Machine Image SQL Server 2019 Enterprise Edition con Windows Server Failover Clustering (WSFC) configurate a livello di istanza e un gruppo di disponibilità Always On configurato a livello di SQL Server tra il nodo primario (WSFCNODE1) e il nodo secondario (WSFCNODE2), che fanno parte della AWS directory Directory Service for Microsoft Active Directory denominata tagechtalk.com

• Uno o più nodi configurati per l'accettazione read-only nella replica secondaria

• Un listener denominato SQLAG1 per il gruppo di disponibilità Always On

• SQLMotore di database del server in esecuzione con lo stesso account di servizio su due nodi

• SQLServer Management Studio (SSMS)

• Un database di test denominato test

Versioni del prodotto

• SQLServer 2014 e versioni successive

Architettura

Stack tecnologico Target

• Amazon EC2

• Microsoft AD gestito da AWS

• Amazon FSx

Architettura Target

Il diagramma seguente mostra come il listener del gruppo di disponibilità Always On (AG) reindirizza le query che contengono il ApplicationIntent parametro nella connessione al nodo secondario appropriato.

1. Viene inviata una richiesta al listener del gruppo di disponibilità Always On.

2. Se la stringa di connessione non contiene il ApplicationIntent parametro, la richiesta viene inviata all'istanza principale.

3. Se la stringa di connessione lo contieneApplicationIntent=ReadOnly, la richiesta viene inviata all'istanza secondaria con una configurazione di routing in sola lettura, ovvero WSFC con un gruppo di disponibilità Always On.

Strumenti

AWSservizi

• AWSDirectory Service per Microsoft Active Directory consente ai carichi di lavoro e AWS alle risorse compatibili con le directory di utilizzare Microsoft Active Directory nel cloud. AWS

• Amazon Elastic Compute Cloud (AmazonEC2) fornisce capacità di elaborazione scalabile nel AWS cloud. Puoi avviare tutti i server virtuali di cui hai bisogno e dimensionarli rapidamente.

• Amazon FSx fornisce file system che supportano i protocolli di connettività standard del settore e offrono disponibilità e replica elevate in tutte le regioni. AWS

Altri servizi

• SQLServer Management Studio (SSMS) è uno strumento per la connessione, la gestione e l'amministrazione delle istanze del SQL server.

• sqlcmd è un'utilità da riga di comando.

Best practice

Per ulteriori informazioni sui gruppi di disponibilità Always On, consulta la documentazione del server. SQL

Epiche

Configura il routing di sola lettura

Attività	Descrizione	Competenze richieste
Aggiorna le repliche in modalità di sola lettura.	Per aggiornare sia la replica principale che quella secondaria in modalità di sola lettura, connettiti alla replica principale da ed esegui il codice Step 1 dalla SSMS sezione Informazioni aggiuntive.	DBA
Crea il routing. URL	Per creare il routing URL per entrambe le repliche, esegui il codice del passaggio 2 nella sezione Informazioni aggiuntive. In questo codice, tagechtalk.com è il nome della directory AWS Managed Microsoft AD.	DBA
Crea la lista di routing.	Per creare la lista di routing per entrambe le repliche, esegui il codice del passaggio 3 nella sezione Informazioni aggiuntive.	DBA
Convalida la lista di routing.	Connect all'istanza principale da SQL Server Management Studio ed esegui il codice Step 4 dalla sezione Informazioni aggiuntive per convalidare la lista di routing.	DBA

Verifica il routing di sola lettura

Attività	Descrizione	Competenze richieste
Connect utilizzando il ApplicationIntent parametro.	DaSSMS, connettiti al nome del listener del gruppo di disponibilità Always On conApplicationIntent=ReadOnly;Initial Catalog=test. La connessione viene stabilita con la replica secondaria. Per verificarlo, esegui il comando seguente per mostrare il nome del server connesso. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') L'output mostrerà il nome della replica secondaria corrente (WSFCNODE2).	DBA
Eseguite un failover.	DaSSMS, connettiti al nome del listener del gruppo di disponibilità Always On. Verifica che il database primario e secondario siano sincronizzati, senza perdita di dati. Esegui un failover in modo che la replica primaria corrente diventi la replica secondaria e la replica secondaria diventi la replica principale. DaSSMS, connettiti al nome del listener del gruppo di disponibilità Always On con. ApplicationIntent=ReadOnly;Initial Catalog=test La connessione viene stabilita con la replica secondaria. Per verificarlo, mostra il nome del server connesso eseguendo il comando seguente. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') Visualizzerà il nome corrente della replica secondaria (WSFCNODE1).	DBA

Connect utilizzando l'utilità da riga di comando sqlcmd

Attività	Descrizione	Competenze richieste
Connect utilizzando sqlcmd.	Per connetterti da sqlcmd, esegui il codice Step 5 dalla sezione Informazioni aggiuntive del prompt dei comandi. Dopo la connessione, esegui il comando seguente per mostrare il nome del server connesso. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') . L'output mostrerà il nome corrente della replica secondaria (WSFCNODE1).	DBA

Risoluzione dei problemi

Problema	Soluzione
La creazione del listener non riesce e viene visualizzato il messaggio «Il WSFC cluster non è riuscito a portare online la risorsa Network Name».	Per informazioni, consulta il post sul blog di Microsoft Create Listener Fails with Message «Il WSFC cluster non è riuscito a portare online la risorsa Network Name».
Potenziali problemi, inclusi altri problemi relativi agli ascoltatori o problemi di accesso alla rete.	Vedi Risoluzione dei problemi di configurazione dei gruppi di disponibilità Always On (SQLServer) nella documentazione Microsoft.

Risorse correlate

• Configura il routing di sola lettura per un gruppo di disponibilità Always On

• Risolvi i problemi di configurazione dei gruppi di disponibilità Always On (server) SQL

Informazioni aggiuntive

Fase 1: Aggiorna le repliche in modalità di sola lettura

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO

Fase 2. Crea il routing URL

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode1.tagechtalk.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode2.tagechtalk.com:1433'))
GO

Fase 3. Crea la lista di routing

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('WSFCNODE2','WSFCNODE1')));
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WSFCNODE1','WSFCNODE2')));
GO

Fase 4. Convalida la lista di routing

SELECT AGSrc.replica_server_name AS PrimaryReplica, AGRepl.replica_server_name AS ReadOnlyReplica, AGRepl.read_only_routing_url AS RoutingURL , AGRL.routing_priority AS RoutingPriority FROM sys.availability_read_only_routing_lists AGRL INNER JOIN sys.availability_replicas AGSrc ON AGRL.replica_id = AGSrc.replica_id INNER JOIN sys.availability_replicas AGRepl ON AGRL.read_only_replica_id = AGRepl.replica_id INNER JOIN sys.availability_groups AV ON AV.group_id = AGSrc.group_id ORDER BY PrimaryReplica

Fase 5: SQLUtilità di comando

sqlcmd -S SQLAG1,1433 -E -d test -K ReadOnly