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

Si applica a: Database SQL di Azure Istanza gestita di SQL di Azure

Come parte dell'architettura a disponibilità elevata, ogni database singolo, database del pool elastico e istanza gestita nel livello di servizio Premium e business critical viene eseguito automaticamente il provisioning con una replica di lettura/scrittura primaria e una o più repliche di sola lettura secondarie. Istanza gestita di SQL di Azure supporta una replica secondaria 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 potrebbero 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 un'ampia gamma di scenari di scalabilità orizzontale in lettura e altri vantaggi. È possibile usare più repliche a disponibilità elevata secondaria per i 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 Azure SQL Database, le repliche geografiche possono fornire funzionalità simili in questi livelli di servizio. Quando si usano Istanza gestita di SQL di Azure e gruppi di failover, il listener di sola lettura del gruppo di failover può offrire funzionalità simili.

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

Repliche readonly

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 verrà 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 Azure SQL Database, 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 durante la creazione del database o quando ne modifica 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 ai dati apportate nella replica primaria vengono rese persistenti in 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 al database primario. 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 momenti leggermente diversi rispetto al database primario e l'altro. Se una replica di sola lettura diventa non disponibile e una sessione si riconnette, può connettersi a una replica che si trova 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 tra decine di millisecondi e secondi a cifra singola. Tuttavia, non esiste 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 garantita dei dati tra le sessioni o richiedono che i dati di cui è stato eseguito il commit siano leggibili immediatamente devono usare la replica primaria.

Nota

Per monitorare la latenza di propagazione dei dati, vedere Monitoraggio e risoluzione dei problemi di 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 verrà 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 tramite SQL Server Management Studio (SSMS), selezionare Opzioni

Opzioni SSMS

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

Parametri aggiuntivi di SSMS

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. Verrà 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.

Monitoraggio e risoluzione dei problemi relativi alle repliche di sola lettura

Quando si è connessi a una replica di sola lettura, le dmv (Dynamic Management Views) riflettono lo stato della replica e possono essere sottoposte a query a scopo 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 di 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 master logico restituiscono i dati di utilizzo delle risorse della replica primaria.

Monitoraggio delle repliche di sola lettura con eventi estesi

Non è possibile creare una sessione eventi estesa quando si è connessi a una replica di sola lettura. In Azure SQL Database, 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 nelle 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 dal livello di isolamento delle transazioni della sessione e indipendentemente 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 modificati in un'altra transazione simultanea, potrebbe essere visualizzato l'errore 3961" "La transazione di isolamento dello snapshot non è riuscita nel database '%.*ls' perché l'oggetto a cui l'istruzione ha eseguito l'accesso dall'istruzione è stata modificata da un'istruzione DDL in un'altra transazione simultanea dall'inizio di questa transazione. È stata respinta perché i metadati non sono sottoposti al controllo delle versioni. Un aggiornamento simultaneo ai metadati può causare incoerenza se misto con l'isolamento dello snapshot."

Query con esecuzione prolungata su 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 non verrà 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 su una replica di sola lettura causa questo tipo di blocco, può essere terminata automaticamente per evitare un impatto eccessivo sulla disponibilità dei dati e sulla disponibilità del database. La sessione riceverà 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. Riprovare 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 mentre le query a esecuzione prolungata vengono eseguite su repliche secondarie.

Suggerimento

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 di latenza di propagazione dei dati nella replica di sola lettura.

Abilitare e disabilitare la scalabilità orizzontale in lettura per 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 per 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à orizzontale in lettura non può essere abilitata nei livelli di servizio Basic, Standard o per utilizzo generico. La scalabilità in lettura viene disabilitata automaticamente nei database Hyperscale configurati con zero repliche secondarie.

Per i database singoli e in pool nel database Azure SQL, è 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é non è possibile disabilitare la scalabilità in lettura.

Nota

Per i database singoli e i database del pool elastico, la possibilità di disabilitare la scalabilità in lettura viene fornita per garantire 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 Azure SQL Database, è possibile gestire l'impostazione di scalabilità orizzontale in lettura nel pannello Configura database. 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 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 Resource Manager di Azure sono sostanzialmente identici. Per altre informazioni sulla compatibilità, vedere Introduzione al nuovo modulo az Azure PowerShell.

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

In Azure SQL Database è 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à 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à 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 la scalabilità 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 per un database esistente, usare 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.

Uso del 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.

Uso della 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 verranno instradate a una delle repliche a disponibilità elevata nello stesso modo in cui vengono instradate nel database scrivibile primario. Le sessioni senza ApplicationIntent=ReadOnly verranno 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 qualsiasi 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 nelle repliche di sola lettura viene abilitato automaticamente. Per altri dettagli sulla gerarchia delle cartelle di archiviazione, sulle convenzioni di denominazione e sul formato di log, vedere database SQL Formato del log di controllo.
  • Informazioni dettagliate prestazioni query si basa sui dati del Query Store, che attualmente non tiene traccia dell'attività nella replica di sola lettura. Informazioni dettagliate prestazioni query non mostrerà le query eseguite nella replica di sola lettura.
  • L'ottimizzazione automatica si basa sulla Query Store, come descritto nel documento di ottimizzazione automatica. L'ottimizzazione automatica funziona solo per i carichi di lavoro in esecuzione nella replica primaria.

Passaggi successivi