Usare repliche di sola lettura per eseguire l'offload dei carichi di lavoro di query di sola lettura

Si applica a:Istanza gestita di SQL di Azure del databaseSQL di Azure

Nell'ambito dell'architettura a disponibilità elevata, viene eseguito automaticamente il provisioning di ogni database singolo o database del pool elastico nel livello di servizio Premium e Business Critical con una replica primaria di lettura/scrittura e una o più repliche secondarie di sola lettura. Viene effettuato il provisioning delle repliche secondarie con le stesse dimensioni di calcolo della replica primaria. La funzionalità di scalabilità orizzontale in lettura consente di eseguire l'offload dei carichi di lavoro di sola lettura usando la capacità di calcolo di una delle repliche di sola lettura, anziché eseguirle nella replica di lettura/scrittura. In questo modo, alcuni carichi di lavoro di sola lettura possono essere isolati dai carichi di lavoro di lettura/scrittura e non influiscono sulle prestazioni. La funzionalità è destinata alle applicazioni che includono carichi di lavoro di sola lettura separati logicamente, ad esempio l'analisi. Nei livelli di servizio Premium e Business Critical le applicazioni possono ottenere vantaggi in termini di prestazioni usando questa capacità aggiuntiva senza costi aggiuntivi.

La funzionalità di scalabilità orizzontale in lettura è disponibile anche nel livello di servizio Hyperscale quando viene aggiunta almeno una replica secondaria. Le repliche denominate secondarie hyperscale offrono scalabilità indipendente, isolamento dell'accesso, isolamento del carico di lavoro, supporto per diversi scenari di scalabilità orizzontale in lettura e altri vantaggi. È possibile usare più repliche a disponibilità elevata secondarie per carichi di lavoro di sola lettura che richiedono più risorse rispetto a quelle disponibili in una replica a disponibilità elevata secondaria.

L'architettura a disponibilità elevata dei livelli di servizio Basic, Standard e Utilizzo generico non include repliche. La funzionalità di scalabilità orizzontale in lettura non è disponibile in questi livelli di servizio. Tuttavia, quando si usa il database SQL di Azure, le repliche geografiche possono fornire funzionalità simili in questi livelli di servizio. Quando si usano Istanza gestita di SQL di Azure e i gruppi di failover, il listener di sola lettura del gruppo di failover può fornire funzionalità simili rispettivamente.

Il diagramma seguente illustra la funzionalità per i database Premium e Business Critical e le istanze gestite.

La funzionalità di scalabilità orizzontale in lettura è abilitata per impostazione predefinita nei nuovi database Premium, Business Critical e Hyperscale.

Nota

La scalabilità in lettura è sempre abilitata nel livello di servizio Business Critical di Istanza gestita di SQL e per i database Hyperscale con almeno una replica secondaria.

Se la stringa di connessione SQL è configurata con ApplicationIntent=ReadOnly, l'applicazione viene reindirizzata a una replica di sola lettura di tale database o istanza gestita. Per informazioni su come usare la ApplicationIntent proprietà , vedere Specifica della finalità dell'applicazione.

Solo per il database SQL di Azure, se si vuole assicurarsi che l'applicazione si connetta alla replica primaria indipendentemente dall'impostazione ApplicationIntent nella stringa di connessione SQL, è necessario disabilitare in modo esplicito la scalabilità orizzontale in lettura quando si crea il database o quando ne viene modificata la configurazione. Ad esempio, se si aggiorna il database dal livello Standard o Per utilizzo generico a Premium o Business Critical e si vuole assicurarsi che tutte le connessioni continuino a passare alla replica primaria, disabilitare la scalabilità orizzontale in lettura. Per informazioni dettagliate su come disabilitarla, vedere Abilitare e disabilitare la scalabilità orizzontale in lettura.

Nota

Le funzionalità di Query Store e SQL Profiler non sono supportate nelle repliche di sola lettura.

Coerenza dei dati

Le modifiche apportate ai dati nella replica primaria vengono rese persistenti nelle repliche di sola lettura in modo sincrono o asincrono a seconda del tipo di replica. Tuttavia, per tutti i tipi di replica, le letture da una replica di sola lettura sono sempre asincrone rispetto alla replica primaria. All'interno di una sessione connessa a una replica di sola lettura le letture sono sempre coerenti a livello di transazione. Poiché la latenza di propagazione dei dati è variabile, le repliche diverse possono restituire dati in punti leggermente diversi nel tempo rispetto al database primario e l'uno all'altro. Se una replica di sola lettura diventa non disponibile e una sessione si riconnette, può connettersi a una replica in un momento diverso rispetto alla replica originale. Analogamente, se un'applicazione modifica i dati usando una sessione di lettura/scrittura nel database primario e lo legge immediatamente usando una sessione di sola lettura in una replica di sola lettura, è possibile che le modifiche più recenti non siano immediatamente visibili.

La latenza tipica di propagazione dei dati tra la replica primaria e le repliche di sola lettura varia nell'intervallo da decine di millisecondi a secondi a una cifra singola. Non esiste tuttavia alcun limite superiore fisso sulla latenza di propagazione dei dati. Le condizioni come l'utilizzo elevato delle risorse nella replica possono aumentare notevolmente la latenza. Le applicazioni che richiedono la coerenza dei dati garantita tra le sessioni o richiedono che i dati di cui è stato eseguito il commit siano leggibili immediatamente devono usare la replica primaria.

Nota

La latenza di propagazione dei dati include il tempo necessario per inviare e rendere persistenti i record di log (se applicabile) a una replica secondaria. Include anche il tempo necessario per ripetere (applicare) questi record di log alle pagine di dati. Per garantire la coerenza dei dati, le modifiche non sono visibili fino a quando non viene applicato il record del log di commit della transazione. Quando il carico di lavoro usa transazioni di dimensioni maggiori, viene aumentata la latenza di propagazione dei dati effettiva.

Per monitorare la latenza di propagazione dei dati, vedere Monitorare e risolvere i problemi relativi alla replica di sola lettura.

Connettersi a una replica di sola lettura

Quando si abilita la scalabilità orizzontale in lettura per un database, l'opzione ApplicationIntent nella stringa di connessione fornita dal client determina se la connessione viene instradata alla replica di scrittura o a una replica di sola lettura. In particolare, se il ApplicationIntent valore è ReadWrite (il valore predefinito), la connessione viene indirizzata alla replica di lettura/scrittura. Questo comportamento è identico a quando ApplicationIntent non è incluso nella stringa di connessione. Se il valore di ApplicationIntent è ReadOnly, la connessione viene instradata a una replica di sola lettura.

La stringa di connessione seguente, ad esempio, connette il client a una replica di sola lettura (sostituendo gli elementi nelle parentesi acute con i valori corretti per l'ambiente ed eliminando le parentesi acute):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Per connettersi a una replica di sola lettura usando SQL Server Management Studio (SSMS), selezionare Opzioni

Selezionare Parametri di connessione aggiuntivi e immettere ApplicationIntent=ReadOnly e quindi selezionare Connetti

Entrambe la stringhe di connessione seguenti connettono il client a una replica di lettura/scrittura (sostituendo gli elementi nelle parentesi acute con i valori corretti per l'ambiente ed eliminando le parentesi acute):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadWrite;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Verificare di essere connessi a una replica di sola lettura

È possibile verificare se si è connessi a una replica di sola lettura eseguendo la query seguente nel contesto del database. Restituisce READ_ONLY quando si è connessi a una replica di sola lettura.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Nota

Nei livelli di servizio Premium e Business Critical, solo una delle repliche di sola lettura è accessibile in qualsiasi momento. Hyperscale supporta più repliche di sola lettura.

Monitorare e risolvere i problemi relativi alle repliche di sola lettura

Quando si è connessi a una replica di sola lettura, le viste a gestione dinamica riflettono lo stato della replica e possono essere sottoposte a query per scopi di monitoraggio e risoluzione dei problemi. Il motore di database offre più visualizzazioni per esporre un'ampia gamma di dati di monitoraggio.

Le viste seguenti vengono comunemente usate per il monitoraggio e la risoluzione dei problemi delle repliche:

Nome	Scopo
sys.dm_db_resource_stats	Fornisce le metriche di utilizzo delle risorse per l'ultima ora, tra cui CPU, I/O dei dati e utilizzo della scrittura dei log rispetto ai limiti degli obiettivi di servizio.
sys.dm_os_wait_stats	Fornisce statistiche di attesa aggregate per l'istanza del motore di database.
sys.dm_database_replica_states	Fornisce statistiche sullo stato di integrità della replica e sulla sincronizzazione. Le dimensioni della coda di rollforward e la frequenza di rollforward fungono da indicatori della latenza di propagazione dei dati nella replica di sola lettura.
sys.dm_os_performance_counters	Fornisce contatori delle prestazioni del motore di database.
sys.dm_exec_query_stats	Fornisce statistiche di esecuzione per query, ad esempio il numero di esecuzioni, il tempo di CPU usato e così via.
sys.dm_exec_query_plan()	Fornisce piani di query memorizzati nella cache.
sys.dm_exec_sql_text()	Fornisce il testo della query per un piano di query memorizzato nella cache.
sys.dm_exec_query_profiles	Fornisce lo stato di avanzamento delle query in tempo reale durante l'esecuzione delle query.
sys.dm_exec_query_plan_stats()	Fornisce l'ultimo piano di esecuzione effettivo noto, incluse le statistiche di runtime per una query.
sys.dm_io_virtual_file_stats()	Fornisce statistiche di I/O al secondo, velocità effettiva e latenza di archiviazione per tutti i file di database.

Nota

Le sys.resource_stats DMV e sys.elastic_pool_resource_stats nel database logico master restituiscono i dati di utilizzo delle risorse della replica primaria.

Monitorare le repliche di sola lettura con eventi estesi

Non è possibile creare una sessione eventi estesa quando si è connessi a una replica di sola lettura. Nel database SQL di Azure, tuttavia, le definizioni delle sessioni di eventi estesi con ambito database create e modificate nella replica primaria vengono replicate in repliche di sola lettura, incluse le repliche geografiche e acquisisci eventi in repliche di sola lettura.

Una sessione eventi estesa in una replica di sola lettura basata su una definizione di sessione dalla replica primaria può essere avviata e arrestata indipendentemente dalla replica primaria. Quando una sessione eventi estesa viene eliminata nella replica primaria, viene eliminata anche in tutte le repliche di sola lettura.

Livello di isolamento delle transazioni nelle repliche di sola lettura

Le transazioni nelle repliche di sola lettura usano sempre il livello di isolamento delle transazioni snapshot, indipendentemente da quello della sessione e dagli hint di query. L'isolamento dello snapshot usa il controllo delle versioni delle righe per evitare scenari di blocco dei lettori.

In rari casi, se una transazione di isolamento dello snapshot accede ai metadati dell'oggetto che sono stati modificati in un'altra transazione simultanea, potrebbe ricevere l'errore 3961" "Transazione di isolamento dello snapshot non riuscita nel database '%.*ls' perché l'oggetto a cui si accede dall'istruzione è stata modificata da un'istruzione DDL in un'altra transazione simultanea dall'inizio della transazione. È stata respinta perché i metadati non sono sottoposti al controllo delle versioni. Un aggiornamento simultaneo ai metadati può causare incoerenza se misto con isolamento dello snapshot".

Query con esecuzione prolungata nelle repliche di sola lettura

Le query in esecuzione nelle repliche di sola lettura devono accedere ai metadati per gli oggetti a cui si fa riferimento nella query (tabelle, indici, statistiche e così via) In rari casi, se i metadati dell'oggetto vengono modificati nella replica primaria mentre una query contiene un blocco sullo stesso oggetto nella replica di sola lettura, la query può bloccare il processo che applica le modifiche dalla replica primaria alla replica di sola lettura. Se una query di questo tipo dovesse essere eseguita per molto tempo, la replica di sola lettura risulterebbe significativamente non sincronizzata con la replica primaria. Per le repliche che sono potenziali destinazioni di failover (repliche secondarie nei livelli di servizio Premium e Business Critical, repliche a disponibilità elevata Hyperscale e tutte le repliche geografiche), questo ritarderebbe anche il ripristino del database se si verificasse un failover, causando tempi di inattività più lunghi del previsto.

Se una query a esecuzione prolungata in una replica di sola lettura causa direttamente o indirettamente questo tipo di blocco, potrebbe essere terminata automaticamente per evitare un impatto eccessivo sulla latenza dei dati e il potenziale impatto sulla disponibilità del database. La sessione riceve l'errore 1219, "La sessione è stata disconnessa a causa di un'operazione DDL con priorità elevata" o l'errore 3947, "La transazione è stata interrotta perché il calcolo secondario non è riuscito a recuperare il rollforward. Ripetere la transazione."

Nota

Se viene visualizzato l'errore 3961, 1219 o 3947 durante l'esecuzione di query su una replica di sola lettura, ripetere la query. In alternativa, evitare operazioni che modificano i metadati degli oggetti (modifiche dello schema, manutenzione dell'indice, aggiornamenti delle statistiche e così via) nella replica primaria durante l'esecuzione di query con esecuzione prolungata nelle repliche secondarie.

Consiglio

Nei livelli di servizio Premium e Business Critical, quando si è connessi a una replica di sola lettura, le redo_queue_size colonne e redo_rate nella DMV sys.dm_database_replica_states possono essere usate per monitorare il processo di sincronizzazione dei dati, fungendo da indicatori della latenza di propagazione dei dati nella replica di sola lettura.

Abilitare e disabilitare la scalabilità orizzontale in lettura per il database SQL

Per Istanza gestita di SQL, la scalabilità orizzontale in lettura viene abilitata automaticamente nel livello di servizio Business Critical e non è disponibile nel livello di servizio Utilizzo generico. Non è possibile disabilitare e riabilitare la scalabilità in lettura.

Per il database SQL, la scalabilità in lettura è abilitata per impostazione predefinita nei livelli di servizio Premium, Business Critical e Hyperscale. La scalabilità orizzontale in lettura non può essere abilitata nei livelli di servizio Basic, Standard o Per utilizzo generico. La funzionalità di scale-out in lettura viene disabilitata automaticamente nei database Hyperscale configurati con zero repliche secondarie.

Per i database singoli e in pool nel database SQL di Azure, è possibile disabilitare e riabilitare la scalabilità orizzontale in lettura nei livelli di servizio Premium o Business Critical usando il portale di Azure e Azure PowerShell. Queste opzioni non sono disponibili per Istanza gestita di SQL perché la scalabilità orizzontale in lettura non può essere disabilitata.

Nota

Per i database singoli e i database del pool elastico, la possibilità di disabilitare la scalabilità orizzontale in lettura è disponibile per la compatibilità con le versioni precedenti. La scalabilità orizzontale in lettura non può essere disabilitata nelle istanze gestite di Business Critical.

Portale di Azure

Per il database SQL di Azure, è possibile gestire l'impostazione di scalabilità orizzontale in lettura nel riquadro Calcolo e database di archiviazione , disponibile in Impostazioni. L'uso del portale di Azure per abilitare o disabilitare la scalabilità orizzontale in lettura non è disponibile per Istanza gestita di SQL di Azure.

PowerShell

Importante

Il modulo Di Azure Resource Manager di PowerShell è ancora supportato, ma tutto lo sviluppo futuro è destinato al modulo Az.Sql. Il modulo azure Resource Manager continuerà a ricevere correzioni di bug fino ad almeno dicembre 2020. Gli argomenti per i comandi nel modulo Az e nei moduli di Azure Resource Manager sono sostanzialmente identici. Per altre informazioni sulla compatibilità, vedere Introduzione al nuovo modulo Az di Azure PowerShell.

La gestione della scalabilità orizzontale in lettura in Azure PowerShell richiede la versione di dicembre 2016 di Azure PowerShell o versione successiva. Per la versione più recente di PowerShell, vedere Azure PowerShell.

Nel database SQL di Azure è possibile disabilitare o riabilitare la scalabilità in lettura in Azure PowerShell richiamando il cmdlet Set-AzSqlDatabase e passando il valore desiderato (Enabled o Disabled) per il -ReadScale parametro . La disabilitazione della scalabilità orizzontale in lettura per Istanza gestita di SQL non è disponibile.

Per disabilitare la scalabilità orizzontale in lettura in un database esistente (sostituendo gli elementi tra parentesi angolari con i valori corretti per l'ambiente e eliminando le parentesi angolari):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled

Per disabilitare la scalabilità orizzontale in lettura in un nuovo database (sostituendo gli elementi tra parentesi angolari con i valori corretti per l'ambiente e eliminando le parentesi angolari):

New-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled -Edition Premium

Per riabilitare lo scale-out in lettura in un database esistente (sostituendo gli elementi tra parentesi angolari con i valori corretti per l'ambiente e eliminando le parentesi angolari):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Enabled

API REST

Per creare un database con scalabilità orizzontale in lettura disabilitato o per modificare l'impostazione di un database esistente, utilizzare il metodo seguente con la readScale proprietà impostata su Enabled o Disabled, come nella richiesta di esempio seguente.

Method: PUT
URL: https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014-04-01-preview
Body: {
   "properties": {
      "readScale":"Disabled"
   }
}

Per altre informazioni, vedere Database - Creare o aggiornare.

Usare il tempdb database in una replica di sola lettura

Il tempdb database nella replica primaria non viene replicato nelle repliche di sola lettura. Ogni replica ha un proprio tempdb database creato al momento della creazione della replica. Ciò garantisce che tempdb sia aggiornabile e possa essere modificato durante l'esecuzione della query. Se il carico di lavoro di sola lettura dipende dall'uso tempdb di oggetti, è necessario creare questi oggetti come parte dello stesso carico di lavoro, mentre si è connessi a una replica di sola lettura.

Usare la scalabilità orizzontale in lettura con database con replica geografica

I database secondari con replica geografica hanno la stessa architettura a disponibilità elevata dei database primari. Se ci si connette al database secondario con replica geografica con scalabilità orizzontale in lettura abilitata, le sessioni con ApplicationIntent=ReadOnly vengono instradate a una delle repliche a disponibilità elevata nello stesso modo in cui vengono instradate nel database scrivibile primario. Le sessioni senza ApplicationIntent=ReadOnly vengono instradate alla replica primaria del database secondario con replica geografica, anch'esso di sola lettura.

In questo modo, la creazione di una replica geografica può fornire più repliche di sola lettura aggiuntive per un database primario di lettura/scrittura. Ogni replica geografica aggiuntiva fornisce un altro set di repliche di sola lettura. Le repliche geografiche possono essere create in qualsiasi area di Azure, inclusa l'area del database primario.

Nota

Non esiste alcun round robin automatico o un altro routing con bilanciamento del carico tra le repliche di un database secondario con replica geografica, ad eccezione di una replica geografica Hyperscale con più repliche a disponibilità elevata. In tal caso, le sessioni con finalità di sola lettura vengono distribuite su tutte le repliche a disponibilità elevata di una replica geografica.

Supporto delle funzionalità nelle repliche di sola lettura

Di seguito è riportato un elenco del comportamento di alcune funzionalità nelle repliche di sola lettura:

• Il controllo sulle repliche di sola lettura viene abilitato automaticamente. Per altre informazioni sulla gerarchia delle cartelle di archiviazione, sulle convenzioni di denominazione e sul formato di log, vedere Formato log di controllo del database SQL.
• Informazioni dettagliate prestazioni query si basa sui dati di Query Store, che attualmente non tiene traccia dell'attività nella replica di sola lettura. Informazioni dettagliate prestazioni query non mostra le query eseguite nella replica di sola lettura.
• L'ottimizzazione automatica si basa su Query Store, come descritto in dettaglio nel documento Ottimizzazione automatica. L'ottimizzazione automatica funziona solo per i carichi di lavoro in esecuzione nella replica primaria.

Passaggi successivi

• Per informazioni sull'offerta Hyperscale del database SQL, vedere Livello di servizio Hyperscale.