Connessione diagnostica per gli amministratori di database

Si applica a:SQL ServerIstanza gestita di Azure SQL

SQL Server offre una speciale connessione di diagnostica a cui possono ricorrere gli amministratori quando non è possibile usare connessioni standard al server. Questa connessione di diagnostica consente a un amministratore di accedere a SQL Server per eseguire query di diagnostica e risolvere i problemi anche quando SQL Server non risponde alle richieste di connessione standard.

Questa connessione amministrativa dedicata supporta la crittografia e altre caratteristiche di sicurezza di SQL Server. Il DAC consente solo di cambiare il contesto dell'utente con quello di un altro utente amministratore.

SQL Server fa tutto il possibile per consentire la corretta connessione DAC, ma in circostanze estreme potrebbe non riuscirci.

Connetti con DAC

Per impostazione predefinita, la connessione è consentita solo da un client in esecuzione sul server. Le connessioni di rete non sono consentite a meno che non vengano configurate mediante la stored procedure sp_configure con l'opzione di configurazione del server connessioni di amministrazione remota.

Solo i membri del ruolo sysadmin di SQL Server possono connettersi tramite la connessione amministrativa dedicata (DAC).

La connessione DAC è disponibile e supportata tramite l'utilità della riga di comando sqlcmd usando un'opzione di amministrazione speciale (-A). Per altre informazioni sull'uso di sqlcmd, vedere sqlcmd - Uso con variabili di script. È anche possibile stabilire la connessione aggiungendo il prefisso admin: al nome dell'istanza nel formato sqlcmd -S admin:<instance_name>. È anche possibile avviare una DAC dall'Editor di query di SQL Server Management Studio connettendosi a admin:<instance_name>.

Per stabilire una connessione amministrativa dedicata (DAC) tramite SQL Server Management Studio:

  • Disconnettere tutte le connessioni all'istanza di SQL Server correlata, incluse le finestre di Esplora oggetti e tutte le finestre di query aperte.

  • Nel menu selezionare File> Nuovo > Query del motore di database

  • Dalla finestra di dialogo di connessione immettere admin:<server_name> nel campo Nome server se si usa l'istanza predefinita o admin:<server_name>\<instance_name> se si usa un'istanza denominata.

porta DAC

SQL Server resta in ascolto per la DAC sulla porta TCP 1434, se è disponibile, oppure su una porta TCP assegnata dinamicamente all'avvio del Motore di database. Il log degli errori contiene il numero di porta sulla quale il DAC è in ascolto. Per impostazione predefinita, il listener DAC accetta connessioni solo tramite la porta locale. Per un esempio di codice che attiva le connessioni di amministrazione remota, vedere Configurazione del server: connessioni di amministrazione remota.

Dopo aver configurato la connessione di amministrazione remota, il listener DAC viene abilitato senza riavviare SQL Server e un client può ora connettersi al DAC in remoto. È possibile abilitare il listener DAC affinché accetti connessioni remote anche se SQL Server non risponde, connettendosi prima localmente a SQL Server tramite il DAC e quindi eseguendo la procedura archiviata sp_configure per consentire le connessioni remote.

Nelle configurazioni cluster la connessione DAC è disattivata per impostazione predefinita. Gli utenti possono eseguire l'opzione di connessione amministrativa remota di sp_configure per consentire al listener DAC di accettare una connessione remota. Se SQL Server non risponde e il listener del DAC non è abilitato, potrebbe essere necessario riavviare SQL Server per connettersi tramite il DAC. È pertanto consigliabile abilitare l'opzione di configurazione remote admin connections in sistemi cluster.

La porta DAC viene assegnata dinamicamente da SQL Server durante l'avvio. Durante la connessione all'istanza predefinita, la connessione amministrativa dedicata (DAC) evita di utilizzare una richiesta SSRP (SQL Server Resolution Protocol) durante la connessione al servizio SQL Server Browser. Essa si connette prima sulla porta TCP 1434. Se ciò non riesce, effettua una chiamata SSRP per ottenere la porta. Se SQL Server Browser non è in ascolto di richieste SSRP, la richiesta di connessione restituisce un errore. Consultare il log degli errori per individuare il numero di porta su cui DAC è in ascolto. Se SQL Server è configurato per accettare connessioni di amministrazione remote, la DAC deve essere avviata con un numero di porta esplicito:

sqlcmd -S tcp:<server>,<port>

Il registro degli errori di SQL Server elenca il numero di porta della DAC (connessione amministrativa dedicata), che per impostazione predefinita è 1434. Se SQL Server è configurato per accettare solo connessioni DAC locali, connettersi tramite l'adattatore di loopback usando il comando seguente:

sqlcmd -S 127.0.0.1,1434

Limitazioni

Poiché il DAC esiste esclusivamente per diagnosticare i problemi del server in casi rari, vi sono alcune restrizioni relative alla connessione:

  • Per garantire che siano disponibili risorse per la connessione, è consentita una sola DAC per ogni istanza di SQL Server. Se è già attiva una connessione DAC, qualsiasi nuova richiesta di connessione attraverso la connessione DAC viene negata e restituisce l'errore 17810.

  • Per risparmiare risorse, SQL Server Express non è in ascolto sulla porta DAC, a meno che non sia stato avviato con il flag di traccia 7806.

  • Il DAC tenta inizialmente di connettersi al database predefinito associato al login. Dopo la connessione, è possibile connettersi al master database. Se il database predefinito è offline o altrimenti non disponibile, la connessione restituisce l'errore 4060. Tuttavia, l'operazione avrà esito positivo se si sovrascrive il database predefinito per connettersi invece al database master usando il comando seguente:

    sqlcmd -A -d master

    Si consiglia di connettersi al database master con la DAC perché è garantita la disponibilità di master se l'istanza del Motore di database viene avviata.

  • SQL Server non consente l'esecuzione di query parallele o di comandi con la connessione amministrativa dedicata. Se, ad esempio, una delle istruzioni seguenti viene eseguita con la connessione DAC, viene generato l'errore 3637:

    • RESTORE...
    • BACKUP...

  • Con la connessione DAC è garantita solo la disponibilità di risorse limitate. Non usare il DAC per eseguire query che richiedono molte risorse o query che possono bloccare altre query. Questa misura consente di evitare che la connessione DAC aggravi gli eventuali problemi già esistenti sul server. Per evitare potenziali scenari di blocco, se è necessario eseguire query che potrebbero bloccare, eseguire la query usando livelli di isolamento basati su snapshot, se possibile; in caso contrario, impostare il livello di isolamento della transazione su READ UNCOMMITTED e impostare LOCK_TIMEOUT su un valore basso, ad esempio 2000 millisecondi, oppure entrambe le operazioni. In questo modo si eviterà il blocco della sessione della connessione DAC. Tuttavia, a seconda dello stato in cui si trova SQL Server, la sessione DAC potrebbe rimanere bloccata su un latch. Potrebbe essere possibile terminare la sessione DAC con CTRL+C, ma non è garantito. In tal caso, l'unica opzione potrebbe essere riavviare SQL Server.

  • Per garantire la connettività e la risoluzione dei problemi con la connessione amministrativa dedicata, SQL Server riserva risorse limitate all'elaborazione dei comandi eseguiti sulla connessione amministrativa dedicata. Generalmente queste risorse sono sufficienti solo per semplici funzioni di diagnostica e risoluzione dei problemi, quali ad esempio quelle elencate di seguito.

Sebbene sia teoricamente possibile eseguire sul DAC qualsiasi istruzione Transact-SQL che non debba essere eseguita in parallelo, si consiglia vivamente di limitarne l'uso ai soli comandi di diagnostica e risoluzione dei problemi seguenti:

  • Interrogazione delle viste a gestione dinamica per la diagnostica di base, come sys.dm_tran_locks per lo stato dei blocchi, sys.dm_os_memory_cache_counters per controllare lo stato di integrità delle cache e sys.dm_exec_requests e sys.dm_exec_sessions per le sessioni e le richieste attive. Evitare le viste di gestione dinamica che richiedono un uso intensivo delle risorse, ad esempio sys.dm_tran_version_store, che esegue la scansione dell'intero archivio versioni e può causare operazioni di I/O estese, oppure che usano join complessi. Per informazioni sulle implicazioni a livello di prestazioni, vedere la documentazione relativa alla vista a gestione dinamica specifica.

  • Esecuzione di query sulle viste del catalogo.

  • Comandi DBCC di base quali DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS e DBCC SQLPERF. Evitare di eseguire comandi che usano una grande quantità di risorse, ad esempio DBCC CHECKDB, DBCC DBREINDEX o DBCC SHRINKDATABASE.

  • Comando Transact-SQL KILL <spid>. A seconda dello stato di SQL Server, il KILL comando potrebbe non riuscire. L'unica opzione potrebbe essere riavviare l'istanza, nel caso di SQL Server o Istanza gestita di SQL di Azure. Di seguito vengono riportate alcune linee guida generali:

    • Verificare che lo SPID sia stato effettivamente terminato eseguendo la query SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>;. Se non viene restituita alcuna riga, significa che la sessione è stata terminata.

    • Se la sessione è ancora attiva, verificare l'eventuale presenza di processi assegnati alla sessione eseguendo la query SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>;. Se vedi l’attività, molto probabilmente la sessione è attualmente in fase di terminazione. Questo può richiedere una notevole quantità di tempo e potrebbe non riuscire affatto.

    • Se non sono presenti attività nel sys.dm_os_tasks associato a questa sessione, ma la sessione rimane in sys.dm_exec_sessions dopo aver eseguito il comando KILL, significa che non è disponibile alcun worker. Seleziona una delle attività attualmente in esecuzione (un'attività elencata nella vista sys.dm_os_tasks con un sessions_id <> NULL) e termina la sessione associata per liberare il worker. Potrebbe non essere sufficiente terminare una singola sessione: potrebbe essere necessario terminarne più di una.

Limitazione nel database SQL di Azure

Il cliente di amministrazione dedicato nel database SQL di Azure è solitamente occupato da un processo back-end e non è accessibile agli utenti.

Limitazione in Istanza gestita di SQL di Azure

La DAC non funziona tramite un endpoint privato per Istanza gestita di Azure SQL. Nelle istanze gestite di SQL, la DAC è in ascolto sulla porta 1434. Poiché gli endpoint privati per le istanze gestite di SQL consentono solo connessioni sulla porta 1433, non è possibile usare un endpoint privato per stabilire una connessione DAC. Per connettersi tramite DAC, è necessario trovarsi nella stessa rete virtuale dell'istanza gestita di SQL.

Esempi

In questo esempio un amministratore si accorge che il server contoso-server non risponde e desidera diagnosticare il problema. A tale scopo, l'utente attiva l'utilità della riga di comando sqlcmd e si connette al server contoso-server utilizzando -A per indicare la connessione DAC.

sqlcmd -S contoso-server -U sa -P <StrongPassword> -A

L'amministratore può quindi eseguire query per diagnosticare il problema e, se necessario, terminare le sessioni che non rispondono.

Contenuti correlati

  • Last updated on