Ripartire il carico di lavoro di sola lettura in una replica secondaria di un gruppo di disponibilità Always On

Si applica a:SQL Server

Le funzionalità secondarie attive di Gruppi di disponibilità Always On includono il supporto per l'accesso in sola lettura a una o più repliche secondarie (repliche secondarie leggibili). Una replica secondaria leggibile può essere in una delle seguenti modalità di disponibilità: modalità con commit sincrono o modalità con commit asincrono. Una replica secondaria leggibile consente l'accesso in sola lettura a tutti i relativi database secondari. Tuttavia, i database secondari leggibili non sono impostati per la sola lettura. Sono dinamici. Un database secondario viene modificato in base ai cambiamenti apportati al database primario corrispondente. Per una replica secondaria tipica, i dati presenti nel database secondario, comprese le tabelle durevoli con ottimizzazione per la memoria, sono quasi in tempo reale. Inoltre, gli indici full-text sono sincronizzati con i database secondari. In molte circostanze, la latenza dei dati tra un database primario e il database secondario corrispondente è in genere solo di pochi secondi.

Le impostazioni di sicurezza nei database primari vengono rese persistenti nei database secondari. Sono inclusi utenti, ruoli del database e delle applicazioni insieme alle rispettive autorizzazioni, nonché Transparent Data Encryption (TDE), se abilitato nel database primario.

I gruppi di disponibilità Always On supportano anche il reindirizzamento delle richieste di connessione per operazioni di lettura a una replica secondaria leggibile (routing di sola lettura). Per informazioni sul routing di sola lettura, vedere Uso di un listener per connettersi a una replica secondaria di sola lettura (routing di sola lettura).

Vantaggi

L'indirizzamento delle connessioni in sola lettura alle repliche secondarie leggibili offre i seguenti vantaggi:

• Consente di scaricare i carichi di lavoro di sola lettura secondari dalla replica primaria, in cui sono conservate le relative risorse per i carichi di lavoro critici. In caso di carico di lavoro di lettura critico o di carico di lavoro per il quale non è possibile tollerare la latenza, si consiglia di effettuare la relativa esecuzione nella replica primaria.

• Consente di migliorare il rendimento dell'investimento per i sistemi in cui sono ospitate repliche secondarie leggibili.

Inoltre, le repliche secondarie leggibili forniscono un supporto robusto per le operazioni di sola lettura, come segue:

• Le statistiche temporanee automatiche in un database secondario leggibile consentono di ottimizzare le query di sola lettura sulle tabelle basate su disco. Per le tabelle con ottimizzazione per la memoria, le statistiche mancanti vengono create automaticamente. Tuttavia, non è previsto alcun aggiornamento automatico delle statistiche non aggiornate. Sarà necessario aggiornare manualmente le statistiche nella replica primaria. Per altre informazioni, vedere Statisticheper i database con accesso di sola lettura più avanti in questo argomento.

• Nei carichi di lavoro di sola lettura per le tabelle basate su disco viene usato il controllo delle versioni delle righe per rimuovere la contesa di blocco nei database secondari. Tutte le query eseguite sui database secondari vengono automaticamente associate al livello di isolamento delle transazioni snapshot, anche quando altri livelli di isolamento delle transazioni sono impostati in modo esplicito. Inoltre, tutte le indicazioni di blocco vengono ignorate. Questo elimina la contenzione tra lettori e scrittori.

• I carichi di lavoro di sola lettura per le tabelle ottimizzate per la memoria durevoli accedono ai dati esattamente nello stesso modo in cui si accede nel database primario, tramite le stored procedure native o l'interoperabilità SQL con le stesse limitazioni del livello di isolamento delle transazioni (vedere Livelli di isolamento nel motore di database). I carichi di lavoro di reporting o le query di sola lettura eseguiti sulla replica primaria possono essere eseguiti sulla replica secondaria senza richiedere modifiche. Analogamente, un carico di lavoro di report o le query di sola lettura in esecuzione in una replica secondaria possono essere eseguiti nella replica primaria senza richiedere alcuna modifica. Analogamente alle tabelle basate su disco, tutte le query eseguite sui database secondari vengono automaticamente associate al livello di isolamento delle transazioni snapshot, anche quando altri livelli di isolamento delle transazioni sono impostati in modo esplicito.

• Le operazioni DML sono consentite nelle variabili di tabella sia per i tipi di tabella basati su disco che per quelli ottimizzati per la memoria nella replica secondaria.

Prerequisiti per il gruppo di disponibilità

• Repliche secondarie leggibili (obbligatorio)

  L'amministratore del database deve configurare una o più repliche in modo tale da consentire tutte le connessioni (solo per l'accesso in lettura) o solo le connessioni con finalità di lettura quando vengono eseguite nel ruolo secondario.

  Nota

  Facoltativamente, l'amministratore del database può configurare le repliche di disponibilità per escludere le connessioni in sola lettura quando l'esecuzione avviene nel ruolo primario.

  Per altre informazioni, vedere Informazioni sull'accesso delle connessioni client per le repliche di disponibilità (SQL Server).

  Avviso

  Solo le repliche che si trovano nella stessa build principale di SQL Server saranno leggibili. Vedere Nozioni di base sull'aggiornamento progressivo per ulteriori informazioni.

• Listener del gruppo di disponibilità

  Per supportare il routing di sola lettura, un gruppo di disponibilità deve disporre di un listener del gruppo di disponibilità. Il client di sola lettura deve indirizzare le richieste di connessione al listener e la stringa di connessione del client deve specificare la finalità dell'applicazione come di sola lettura, ovvero devono essere richieste di connessione con finalità di lettura.

• Routing di sola lettura

  Routing di sola lettura si riferisce alla capacità di SQL Server di instradare le richieste di connessione in ingresso con intento di lettura, indirizzate a un listener del gruppo di disponibilità, a una replica secondaria leggibile disponibile. I prerequisiti per il routing di sola lettura sono i seguenti:

  • Per supportare il routing di sola lettura una replica secondaria leggibile richiede un URL di routing di sola lettura. Questo URL ha effetto solo quando la replica locale è in esecuzione nel ruolo secondario. L'URL di routing di sola lettura deve essere specificato per ogni singola replica in base alle esigenze. Ogni URL di routing di sola lettura viene usato per il routing delle richieste di connessione con finalità di lettura a una replica secondaria leggibile specifica. In genere, a ogni replica secondaria leggibile viene assegnato un URL di routing di sola lettura.

  • Ogni replica di disponibilità che deve supportare il routing di sola lettura quando funge da replica primaria richiede un elenco di routing di sola lettura. Un determinato elenco di routing di sola lettura ha effetto solo quando la replica locale è in esecuzione nel ruolo primario. L'elenco deve essere specificato per ogni singola replica in base alle esigenze. In genere, ciascun elenco di routing di sola lettura deve contenere tutti gli URL di routing di sola lettura, con l'URL della replica locale alla fine dell'elenco.

    Nota

    Il carico delle richieste di connessione con finalità di lettura può essere bilanciato tra le repliche. Per altre informazioni, vedi Configurare il bilanciamento del carico tra le repliche di sola lettura.

  Per altre informazioni, vedere Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server).

Limitazioni e restrizioni

Alcune operazioni non sono completamente supportate, come indicato di seguito:

• Non appena una replica leggibile viene abilitata per la lettura, può iniziare ad accettare connessioni ai relativi database secondari. Tuttavia, se in un database primario è presente una transazione attiva, le versioni di riga non saranno completamente disponibili nel database secondario corrispondente. È necessario eseguire il commit o il rollback di tutte le transazioni attive presenti nella replica primaria al momento della configurazione della replica secondaria. Fino a quando questo processo non viene completato, il mapping del livello di isolamento delle transazioni nel database secondario non è completo e le query sono temporaneamente bloccate.

  Avviso

  L'esecuzione di transazioni prolungate ha un impatto sul numero di righe con versione mantenute, sia per le tabelle basate su disco che per quelle ottimizzate per la memoria.

• In un database secondario con tabelle con ottimizzazione per la memoria, anche se le versioni di riga vengono generate sempre per le tabelle con ottimizzazione per la memoria, le query vengono bloccate finché non vengono completate tutte le transazioni attive presenti nella replica primaria quando la replica secondaria è stata abilitata per la lettura. In questo modo si garantisce che sia le tabelle basate su disco che quelle ottimizzate per la memoria siano disponibili contemporaneamente per il carico di lavoro di report e per le query di sola lettura.

• Le funzionalità di rilevamento delle modifiche e Change Data Capture non sono supportate nei database secondari che appartengono a una replica secondaria leggibile:

  • Il rilevamento delle modifiche è disabilitato in modo esplicito nei database secondari.

  • La funzionalità Change Data Capture non può essere abilitata solo in un database di replica secondaria. È possibile abilitare Change Data Capture nel database di replica primaria e leggere le modifiche dalle tabelle CDC usando le funzioni nel database di replica secondaria.

• Poiché le operazioni di lettura sono mappate al livello di isolamento delle transazioni snapshot, la rimozione dei record fantasma sulla replica primaria può essere bloccata dalle transazioni in una o più repliche secondarie. L'attività di pulizia di record fantasma consentirà di pulire automaticamente i record fantasma per le tabelle basate su disco nella replica primaria se non più necessari per qualsiasi replica secondaria. Questa operazione è simile a quella che viene effettuata quando si eseguono transazioni nella replica primaria. Nel caso estremo, nel database secondario sarà necessario terminare una query di lettura di lunga durata che blocca la pulizia dei record fantasma. Si noti che la pulizia dei record fantasma può essere bloccata se la replica secondaria si disconnette o quando il movimento dei dati è sospeso sul database secondario. I record fantasma usano lo spazio fisico in un file di dati. Ciò può provocare problemi di riutilizzo dello spazio. Per altre informazioni, vedere Pulizia fantasma. Questo stato impedisce inoltre il troncamento del log, pertanto se lo stato persiste, si consiglia di rimuovere il database secondario dal gruppo di disponibilità. Non si verifica alcun problema di pulizia dei record fantasma con le tabelle ottimizzate per la memoria perché le versioni di riga vengono mantenute in memoria e sono indipendenti dalle versioni di riga nella replica primaria.

• Potrebbe verificarsi un errore durante l'operazione DBCC SHRINKFILE nei file contenenti le tabelle basate su disco nella replica primaria se nel file sono contenuti record fantasma ancora necessari in una replica secondaria.

• A partire da SQL Server 2014 (12.x), le repliche secondarie leggibili possono rimanere online anche quando la replica primaria è offline a causa di un'azione dell'utente o di un errore, ad esempio se la sincronizzazione è stata sospesa a causa di un comando dell'utente o di un errore oppure se una replica sta risolvendo lo stato perché WSFC è offline. Tuttavia, il routing di sola lettura non funziona in questa situazione perché il listener del gruppo di disponibilità è offline. I client devono connettersi direttamente alle repliche secondarie di sola lettura per i carichi di lavoro di sola lettura.

Considerazioni sulle prestazioni

In questa sezione si illustrano le diverse considerazioni sulle prestazioni relative ai database secondari leggibili.

Contenuto della sezione

• Latenza dei dati

• Impatto sui carichi di lavoro di sola lettura

• Indicizzazione

• Statistiche per i database di Access in sola lettura

Latenza dei dati

L'implementazione dell'accesso di sola lettura alle repliche secondarie è utile qualora i carichi di lavoro di sola lettura possono tollerare una certa latenza dei dati. Nelle situazioni in cui la latenza dei dati non può essere accettata, si consideri la possibilità di eseguire i carichi di lavoro di sola lettura nella replica primaria.

I record di log delle modifiche sul database primario vengono inviati dalla replica primaria alle repliche secondarie. In ogni database secondario, un thread di redo dedicato applica i record di log. In un database secondario di accesso in lettura, una modifica ai dati specificata non viene visualizzata nei risultati della query fino a quando il record di log, in cui è contenuta la modifica, non sarà stato applicato al database secondario e non è stato eseguito il commit della transazione nel database primario.

Ciò significa che si verifica della latenza, in genere solo pochi secondi, tra la replica primaria e quella secondaria. In rari casi, tuttavia, ad esempio se problemi di rete compromettono la velocità effettiva, la latenza può diventare significativa. La latenza aumenta quando si verificano colli di bottiglia di I/O e quando il trasferimento dei dati è sospeso. Per monitorare lo spostamento dei dati sospeso, è possibile usare il Dashboard Always On o la vista a gestione dinamica sys.dm_hadr_database_replica_states.

Latenza dei dati nei database con tabelle ottimizzate per la memoria

SQL Server 2014 (12.x) prevede alcune considerazioni speciali in relazione alla latenza dei dati per le repliche secondarie attive. Vedere Repliche secondarie attive di SQL Server 2014 (12.x): repliche secondarie leggibili. A partire da SQL Server 2016 (13.x), non esistono considerazioni speciali in relazione alla latenza dei dati per le tabelle ottimizzate per la memoria. La latenza dei dati prevista per le tabelle ottimizzate per la memoria è paragonabile a quella per le tabelle basate su disco.

Impatto sui carichi di lavoro di sola lettura

Quando si configura una replica secondaria per l'accesso in sola lettura, i carichi di lavoro di sola lettura nei database secondari consumano risorse di sistema, ad esempio CPU e I/O (per le tabelle basate su disco), sottraendole ai thread di ripristino, soprattutto se i carichi di lavoro di sola lettura sulle tabelle basate su disco sono a elevata intensità di I/O. Non esiste alcun impatto I/O quando si accede alle tabelle con ottimizzazione per la memoria perché tutte le righe si trovano in memoria.

Inoltre, i carichi di lavoro di sola lettura nelle repliche secondarie possono bloccare le modifiche DDL (Data Definition Language) applicate tramite record di log.

• Anche se nelle operazioni di lettura non si accettano i blocchi condivisi a causa del controllo delle versioni delle righe, in queste operazioni si accettano i blocchi di stabilità dello schema (Sch-S) che possono bloccare le operazioni di rollforward tramite cui si applicano le modifiche DDL. Le operazioni DDL includono operazioni di ALTER/DROP su tabelle e viste, ma non il DROP o l’ALTER delle procedure archiviate. Se, quindi, ad esempio si elimina una tabella basata su disco o con ottimizzazione per la memoria nel database primario, Quando il thread REDO elabora il record del log per eliminare la tabella, deve acquisire un lock SCH_M sulla tabella e può essere bloccato da una query in esecuzione che accede alla tabella. Si tratta dello stesso comportamento nella replica primaria, tranne per il fatto che l'eliminazione della tabella viene eseguita nell'ambito di una sessione utente e non da un thread REDO.

• Per le tabelle con ottimizzazione per la memoria sono previsti blocchi aggiuntivi. L'eliminazione di una stored procedure nativa può causare il blocco del thread REDO in caso di esecuzione simultanea della stored procedure nativa sulla replica secondaria. Si osserva lo stesso comportamento nella replica primaria, con la differenza che l'eliminazione della procedura archiviata viene eseguita nell'ambito di una sessione utente e non del thread REDO.

Valutare le procedure consigliate relative alla compilazione delle query e applicarle ai database secondari. Pianificare, ad esempio, le query a lunga esecuzione, come le aggregazioni di dati, nei periodi di minore attività.

Indicizzazione

Per ottimizzare i carichi di lavoro di sola lettura nelle repliche secondarie leggibili, è possibile creare indici nelle tabelle dei database secondari. Poiché non è possibile apportare modifiche allo schema o ai dati nei database secondari, creare indici nei database primari e consentire il trasferimento delle modifiche al database secondario attraverso il processo di rollforward.

Per monitorare l'attività di utilizzo degli indici in una replica secondaria, interrogare le colonne user_seeks, user_scans e user_lookups della vista a gestione dinamica sys.dm_db_index_usage_stats.

Statistiche per i database ad accesso in sola lettura

Le statistiche sulle colonne di tabelle e viste indicizzate vengono usate per ottimizzare i piani di query. Per i gruppi di disponibilità, le statistiche create e gestite nei database primari vengono rese automaticamente persistenti nei database secondari come parte dell'applicazione dei record di log delle transazioni. Tuttavia, è possibile che per il carico di lavoro di sola lettura nei database secondari siano richieste statistiche diverse rispetto a quelle create nei database primari. Ad ogni modo, poiché i database secondari sono limitati all'accesso di sola lettura, non è possibile creare statistiche nei database secondari.

Per risolvere il problema, le statistiche temporanee per i database secondari vengono create e gestite dalla replica secondaria in tempdb. Il suffisso _readonly_database_statistic viene aggiunto al nome delle statistiche temporanee per distinguerle dalle statistiche permanenti salvate in modo permanente nel database primario.

Solo in SQL Server è possibile creare e aggiornare le statistiche temporanee. È tuttavia possibile eliminare le statistiche temporanee e monitorare le relative proprietà usando gli stessi strumenti usati per le statistiche permanenti:

• Eliminare le statistiche temporanee usando l'istruzione DROP STATISTICS Transact-SQL.

• Monitorare le statistiche mediante le viste del catalogo sys.stats e sys.stats_columns. sys_stats include una colonna, is_temporary, che indica quali statistiche sono permanenti e quali invece temporanee.

L'aggiornamento automatico delle statistiche per le tabelle con ottimizzazione per la memoria nella replica primaria o secondaria non è supportato. È necessario monitorare i piani e le prestazioni delle query nella replica secondaria e aggiornare manualmente le statistiche nella replica primaria quando necessario. Tuttavia, le statistiche mancanti vengono create automaticamente sia nella replica primaria che in quella secondaria.

Per altre informazioni sulle statistiche di SQL Server, vedere Statistiche.

Contenuto della sezione

• Statistiche permanenti non aggiornate nei database secondari

• Limitazioni e restrizioni

Statistiche permanenti non aggiornate nei database secondari

SQL Server rileva situazioni in cui le statistiche permanenti in un database secondario non sono aggiornate. Tuttavia non è possibile apportare le modifiche alle statistiche permanenti se non modificando il database primario. Per l'ottimizzazione query, in SQL Server è possibile creare statistiche temporanee per le tabelle basate su disco nel database secondario e usarle al posto di quelle permanenti non aggiornate.

Quando le statistiche permanenti vengono aggiornate nel database primario, vengono rese automaticamente persistenti nel database secondario. In SQL Server è quindi possibile usare le statistiche permanenti aggiornate che sono più recenti delle statistiche temporanee.

Se si verifica il failover del gruppo di disponibilità, le statistiche temporanee vengono eliminate su tutte le repliche secondarie.

Limitazioni e restrizioni

• Poiché le statistiche temporanee sono archiviate in tempdb, un riavvio del servizio SQL Server comporta l'indisponibilità di tutte le statistiche temporanee.

• Il suffisso _readonly_database_statistic è riservato alle statistiche generate da SQL Server. Non è possibile usare questo suffisso quando si creano statistiche in un database primario. Per ulteriori informazioni, vedi Statistiche.

Accesso alle tabelle ottimizzate per la memoria in una replica secondaria

Con le tabelle ottimizzate per la memoria in una replica secondaria possono essere usati gli stessi livelli di isolamento delle transazioni usati nella replica primaria. È consigliabile impostare il livello di isolamento a livello di sessione su READ COMMITTED e l'opzione a livello di database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT su ON. Ad esempio:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Considerazioni sulla pianificazione della capacità

• In caso di tabelle basate su disco, le repliche secondarie leggibili possono richiedere spazio in tempdb per due motivi:

  • Le versioni di riga vengono copiate dal livello di isolamento dello snapshot in tempdb.

  • Le statistiche temporanee per i database secondari vengono create e mantenute in tempdb. Le statistiche temporanee possono provocare un lieve aumento delle dimensioni di tempdb. Per altre informazioni, vedere Statistiche per i database con accesso di sola letturapiù avanti in questa sezione.

• Quando si configura l'accesso in lettura per una o più repliche secondarie, nei database primari vengono aggiunti 14 byte di overhead sulle righe di dati eliminate, modificate o inserite per archiviare i puntatori alle versioni di riga nei database secondari per le tabelle basate su disco. Questo overhead di 14 byte si applica anche ai database secondari. Poiché l'overhead di 14 byte viene aggiunto alle righe di dati, è possibile che si verifichino divisioni di pagina.

  I dati della versione di riga non sono generati dai database primari. Al contrario, i database secondari generano le versioni di riga. Tuttavia, il controllo delle versioni delle righe aumenta l'archiviazione dei dati nei database primari e secondari.

  L'aggiunta dei dati di versione delle righe dipende dall'impostazione del livello di isolamento Snapshot oppure Read Committed Snapshot Isolation (RCSI) nel database primario. Nella tabella seguente viene descritto il comportamento del controllo delle versioni in un database secondario leggibile con impostazioni diverse per le tabelle basate su disco.

  Replica secondaria leggibile?	Livello di isolamento Snapshot o RCSI abilitato?	Database principale	Database secondario
  NO	NO	Nessuna versione di riga né overhead di 14 byte	Nessuna versione di riga né overhead di 14 byte
  NO	Sì	Versioni di riga e overhead di 14 byte	Nessuna versione di riga, ma un sovraccarico di 14 byte
  Sì	NO	Nessuna versione delle righe, ma un overhead di 14 byte	Versioni di riga e overhead di 14 byte
  Sì	Sì	Versioni di riga e overhead di 14 byte	Versioni di riga e overhead di 14 byte

Attività correlate

• Configurare l'accesso in sola lettura su una replica di disponibilità (SQL Server)

• Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)

• Creare o configurare un listener del gruppo di disponibilità (SQL Server)

• Monitorare Gruppi di disponibilità (Transact-SQL)

• Visualizza le proprietà della replica di disponibilità (SQL Server)

• Usare la finestra di dialogo Nuovo gruppo di disponibilità (SQL Server Management Studio)

Contenuto correlato

• SQL Server AlwaysOn Team Blog: blog ufficiale del team di SQL Server AlwaysOn

Vedi anche

Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server)
Informazioni sull'accesso delle connessioni client per le repliche di disponibilità (SQL Server)
Listener di gruppi di disponibilità, connettività client e failover dell'applicazione (SQL Server)
Statistica