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

Si applica a: Database SQL di Azure Istanza gestita di SQL 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à 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, invece di eseguirli nelle repliche di lettura/scrittura. In questo modo, alcuni carichi di lavoro di sola lettura possono essere isolati dai carichi di lavoro di lettura/scrittura senza influire 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à 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à in lettura e altri vantaggi. È possibile usare più repliche a disponibilità elevata secondarie per carichi di lavoro di sola lettura con bilanciamento del carico 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à in lettura non è disponibile in questi livelli di servizio. Tuttavia, con il database SQL di Azure, le repliche geografiche possono offrire funzionalità simili in questi livelli di servizio. Quando si usano gruppi di failover e Istanza gestita di SQL di Azure, il listener di sola lettura del gruppo di failover può fornire funzionalità simili.

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

La funzionalità di scalabilità 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 proprietà ApplicationIntent, vedere Specificazione della finalità dell'applicazione.

Solo per database SQL di Azure, per assicurarsi che l'applicazione si connetta alla replica primaria indipendentemente dall'impostazione ApplicationIntent nel stringa di connessione SQL, è necessario disabilitare in modo esplicito la scalabilità in lettura durante la creazione del database o quando se ne modifica la configurazione. Ad esempio, se si aggiorna il database dal livello Standard o Utilizzo generico a Premium o Business Critical e ci si vuole assicurare che tutte le connessioni continuino a passare alla replica primaria, disabilitare la scalabilità in lettura. Per informazioni dettagliate su come disabilitarla, vedere Abilitare e disabilitare la scalabilità 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, repliche diverse possono restituire dati in momenti leggermente diversi rispetto alla replica primaria e le une rispetto alle altre. 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 sulla replica primaria e li legge immediatamente usando una sessione di sola lettura su una replica di sola lettura, è possibile che la 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 una 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 (se applicabile) i record di log 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à in lettura per un database, l'opzione ApplicationIntent nella stringa di connessione fornita dal client indica se la connessione viene instradata alla replica in scrittura o a una replica di sola lettura. In particolare, se il valore di ApplicationIntent è ReadWrite (valore predefinito), la connessione viene indirizzata alla replica di lettura/scrittura. Questo comportamento è identico a quando ApplicationIntent non è incluso nel 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 aggiuntivi per la connessione 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. Viene restituito 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 di replica di sola lettura

Quando si è connessi a una replica di sola lettura, DMV (Dynamic Management View) riflette lo stato della replica e possono essere sottoposte a query per scopi di monitoraggio e risoluzione dei problemi. Il motore di database offre più viste 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 sulla sincronizzazione e sullo stato di integrità della replica. 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 operazioni di I/O al secondo, produttività e latenza di archiviazione per tutti i file di database.

Nota

Le DMV sys.resource_stats 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 di eventi estesi quando si è connessi a una replica di sola lettura. Tuttavia, nel database SQL di Azure e nell’istanza gestita di SQL di Azure, 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 gli eventi di acquisizione nelle repliche di sola lettura.

Nel database SQL di Azure, la sessione di eventi estesi in una replica di sola lettura basata su una definizione di sessione dalla replica primaria può essere avviata e arrestata indipendentemente dalla sessione nella replica primaria.

In Istanza gestita di SQL di Azure, per avviare una traccia in una replica di sola lettura, è necessario innanzitutto avviare la traccia nella replica primaria prima di poter avviare la traccia nella replica di sola lettura. Se non si avvia prima la traccia nella replica primaria, quando si tenta di avviare la traccia nella replica di sola lettura viene visualizzato l'errore seguente:

Messaggio 3906, Livello 16, Stato 2, Riga 1 Impossibile aggiornare il database "master" perché il database è di sola lettura.

Dopo aver avviato la traccia prima nella replica primaria e quindi nella replica di sola lettura, è possibile arrestare la traccia nella replica primaria.

Per eliminare una sessione eventi in una replica di sola lettura, seguire questa procedura:

1. Connettere Esplora oggetti di SSMS o un intervallo di query alla replica di sola lettura.
2. Arrestare la sessione nella replica di sola lettura selezionando Arresta sessione nel menu di scelta rapida della sessione in Esplora oggetti oppure eseguendo ALTER EVENT SESSION [session-name-here] ON DATABASE STATE = STOP; in un intervallo di query.
3. Connettere Esplora oggetti o un intervallo di query alla replica primaria.
4. Eliminare la sessione nella replica primaria selezionando Elimina nel menu di scelta rapida della sessione o eseguendo DROP EVENT SESSION [session-name-here] ON DATABASE;

Livello di isolamento della transazione 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 writer da parte 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 incoerenze se combinato con l'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 recupero del database se si verificasse un failover, causando tempi inattivi più lunghi del previsto.

Se una query con 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.

Suggerimento

Nei livelli di servizio Premium e Business Critical, quando si è connessi a una replica di sola lettura, le colonne redo_queue_size 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à in lettura per il database SQL

Per Istanza gestita di SQL, la scalabilità 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 database SQL, la scalabilità in lettura è abilitata per impostazione predefinita nei livelli di servizio Premium, Business Critical e Hyperscale. La scalabilità in lettura non può essere abilitata nei livelli di servizio Basic, Standard o 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à 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à in lettura non può essere disabilitata.

Nota

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

Azure portal

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

PowerShell

Importante

Il modulo Azure Resource Manager di PowerShell è ancora supportato, ma tutte le future attività di sviluppo sono incentrate sul modulo Az.Sql. Il modulo Azure Resource Manager continuerà a ricevere correzioni di bug almeno fino a dicembre 2020. Gli argomenti per i comandi nei moduli Az e Azure Resource Manager sono sostanzialmente identici. Per altre informazioni sulla compatibilità, vedere Introduzione del nuovo modulo Az di Azure PowerShell.

Per gestire la scalabilità in lettura in Azure PowerShell, è necessaria la versione di Azure PowerShell di dicembre 2016 o una successiva. Per la versione più recente di PowerShell, vedere Azure PowerShell.

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

Per disabilitare la scalabilità in lettura in un database esistente (sostituendo gli elementi nelle parentesi acute con i valori corretti per l'ambiente ed eliminando le parentesi acute):

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

Per disabilitare la scalabilità in lettura per un database esistente (sostituendo gli elementi nelle parentesi acute con i valori corretti per l'ambiente ed eliminando le parentesi acute):

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

Per riabilitare la scalabilità in lettura in un database esistente (sostituendo gli elementi nelle parentesi acute con i valori corretti per l'ambiente ed eliminando le parentesi acute):

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

REST API

Per creare un database con scalabilità in lettura disabilitata o per modificare l'impostazione di un database esistente, utilizzare il metodo seguente con la proprietà readScale 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 Databases - Create or Update (Database - Creare o aggiornare).

Usare il database tempdb in una replica di sola lettura

Il database tempdb nella replica primaria non viene replicato nelle repliche di sola lettura. Ogni replica ha un proprio database tempdb 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 di oggetti tempdb, è necessario creare questi oggetti come parte dello stesso carico di lavoro, mentre si è connessi a una replica di sola lettura.

Usare la scalabilità 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à in lettura abilitata, le sessioni con ApplicationIntent=ReadOnly sono indirizzate a una delle repliche a disponibilità elevata nello stesso modo in cui sono indirizzate al database scrivibile primario. Le sessioni senza ApplicationIntent=ReadOnly vengono indirizzate alla replica primaria del database secondario con replica geografica, anche questa 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, a 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, le convenzioni di denominazione e il formato dei log, vedere Formato dei log di controllo di Database SQL.
• Informazioni dettagliate sulle prestazioni delle query si basa sui dati di Query Store, che attualmente non tiene traccia dell'attività nella replica di sola lettura. Informazioni dettagliate sulle prestazioni delle 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 sull'ottimizzazione automatica. L'ottimizzazione automatica funziona solo per i carichi di lavoro in esecuzione nella replica primaria.

Passaggi successivi

• Per altre informazioni sul livello di servizio Hyperscale, vedere Livello di servizio Hyperscale.