Connessione di diagnostica per gli amministratori di database

  • Articolo

Applies to:SQL ServerAzure SQL Database

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. La connessione DAC consente solo il cambiamento del contesto utente in un altro utente con privilegi amministrativi.

SQL Server esegue ogni possibile tentativo per garantire una corretta connessione amministrativa dedicata, tuttavia in alcune situazioni particolari potrebbe verificarsi un errore.

Connettersi con la connessione amministrativa dedicata

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 siano configurate tramite la stored procedure sp_configure con l'opzione Connessioni di amministrazione remote.

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

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 Usare sqlcmd con variabili di scripting. È anche possibile stabilire la connessione aggiungendo il prefisso admin: al nome dell'istanza nel formato sqlcmd -S admin:<instance_name>. Una connessione amministrativa dedicata può essere stabilita anche da un editor di query di SQL Server Management Studio connettendosi a admin:<instance_name>.

Per stabilire una connessione amministrativa dedicata da 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.

Restrizioni

Dato che l'applicazione livello dati ha il solo scopo di consentire la diagnosi di problemi del server in rare circostanze, vi sono alcune restrizioni nella connessione:

  • Per garantire che vi siano risorse disponibili per la connessione, per ogni istanza di SQL Server è consentita un'unica connessione amministrativa dedicata. 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 rimane in ascolto sulla porta della connessione amministrativa dedicata, a meno che non sia avviato con un flag di traccia 7806.

  • La connessione DAC tenta inizialmente di connettersi al database predefinito associato all'account di accesso. Dopo aver stabilito correttamente la connessione, è possibile connettersi al database master. Se il database predefinito è offline o altrimenti non disponibile, la connessione restituisce l'errore 4060. La connessione riesce, tuttavia, se si sostituisce la connessione al database predefinito con quella al database master usando il comando seguente:

    sqlcmd -A -d master

    È consigliabile connettersi al database master tramite la connessione amministrativa dedicata perché in questo modo master sarà certamente disponibile quando 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 la disponibilità di risorse limitate. Non usare la connessione amministrativa dedicata per eseguire query che impegnano molte risorse, ad esempio un join complesso su una grande tabella, o query che potrebbero bloccare il sistema. Questa misura consente di evitare che la connessione DAC aggravi gli eventuali problemi già esistenti sul server. Per evitare l'insorgere di potenziali scenari di blocco, quando è necessario eseguire query che potrebbero causare blocchi, eseguire la query in livelli di isolamento dello snapshot, se possibile, in caso contrario impostare il livello di isolamento della transazione su READ UNCOMMITTED, impostare il valore LOCK_TIMEOUT su un intervallo di tempo breve, ad esempio 2000 millisecondi, oppure adottare entrambe le misure. In questo modo si eviterà il blocco della sessione della connessione DAC. A seconda dello stato in cui si trova SQL Server, tuttavia, la sessione della connessione amministrativa dedicata potrebbe bloccarsi su un latch. Potrebbe essere possibile terminare la sessione della connessione amministrativa dedicata usando la combinazione di tasti CTRL+C, ma l'esito dell'operazione non è garantito. In tal caso l'unica possibilità potrebbe consistere nel 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 qualsiasi istruzione Transact-SQL che non richiede l'esecuzione in parallelo sulla connessione amministrativa dedicata, è consigliabile limitare l'utilizzo ai comandi di diagnostica e risoluzione dei problemi seguenti:

  • Query di viste a gestione dinamica per diagnostica di base, quali sys.dm_tran_locks per lo stato di blocco, sys.dm_os_memory_cache_counters per verificare l'integrità delle cache e sys.dm_exec_requests e sys.dm_exec_sessions per richieste e sessioni attive. Evitare viste a gestione dinamica che usano una grande quantità di risorse, ad esempio sys.dm_tran_version_store che analizza l'intero archivio delle versioni e può causare un numero elevato di operazioni I/O, oppure join complessi. Per informazioni sulle implicazioni a livello di prestazioni, vedere la documentazione relativa alla vista a gestione dinamica specifica.

  • Query di 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>. In base allo stato di SQL Server, il comando KILL potrebbe non avere sempre esito positivo. In questo caso, l'unica possibilità potrebbe consistere nel riavviare SQL Server. 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, 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 viene visualizzato il processo, è molto probabile che sia in corso la terminazione della sessione. L'operazione potrebbe richiedere una notevole quantità di tempo e potrebbe non riuscire.

    • Se in sys.dm_os_tasks non vi sono attività associate alla sessione, ma questa è ancora presente in sys.dm_exec_sessions dopo l'esecuzione di un comando KILL, allora il ruolo di lavoro non è disponibile per l'utente. Selezionare uno dei processi attualmente in esecuzione (un processo elencato nella vista sys.dm_os_tasks con un sessions_id <> NULL) e terminare la sessione ad esso associata per liberare il thread di lavoro. La terminazione di una singola sessione potrebbe non essere sufficiente e potrebbe essere necessario terminare più sessioni.

Porta della connessione amministrativa dedicata

SQL Server è in ascolto della connessione amministrativa dedicata sulla porta TCP 1434 se disponibile o su una porta TCP assegnata dinamicamente all'avvio del motore di database. Il log degli errori contiene il numero di porta su cui è in ascolto la connessione DAC. Per impostazione predefinita, il listener della connessione DAC accetta connessioni solo sulla porta locale. Per un esempio di codice che attiva le connessioni amministrative remote, vedere Opzione di configurazione del server remote admin connections.

Dopo aver configurato la connessione amministrativa remota, il relativo listener viene abilitato senza il riavvio di SQL Server, pertanto da tale istante un client può stabilire una connessione amministrativa dedicata in remoto. È possibile abilitare il listener della connessione amministrativa dedicata per accettare connessioni in remoto anche se SQL Server non risponde effettuando prima una connessione a SQL Server con la connessione amministrativa dedicata locale e poi eseguendo la stored procedure sp_configure per accettare la connessione da connessioni remote.

Nelle configurazioni cluster la connessione DAC è disattivata per impostazione predefinita. Gli utenti possono usare l'opzione Connessioni di amministrazione remote di sp_configure per abilitare il listener della connessione amministrativa dedicata per l'accesso a una connessione remota. Se SQL Server non risponde e il listener della connessione amministrativa dedicata non è abilitato, potrebbe essere necessario riavviare SQL Server per poter stabilire la connessione amministrativa dedicata. È pertanto consigliabile abilitare l'opzione di configurazione remote admin connections in sistemi cluster.

La porta della connessione amministrativa dedicata viene assegnata dinamicamente da SQL Server durante l'avvio. Durante la connessione all'istanza predefinita, la connessione amministrativa dedicata evita di usare una richiesta SSRP (SQL Server Resolution Protocol) al servizio SQL Server Browser. Essa si connette prima sulla porta TCP 1434. Se questo tentativo di connessione termina con esito negativo, la connessione DAC esegue una chiamata SSRP per ottenere la porta. Se SQL Server Browser non è in ascolto di richieste SSRP, la richiesta di connessione restituisce un errore. Vedere il log degli errori per ottenere il numero di porta su cui è in attesa la connessione DAC. Se SQL Server è configurato per accettare connessioni amministrative remote, è necessario inizializzare la connessione amministrativa dedicata con un numero di porta esplicito:

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

Il log degli errori di SQL Server elenca il numero di porta relativo alla connessione amministrativa dedicata, che per impostazione predefinita è 1434. Se SQL Server è configurato per accettare solo connessioni amministrative dedicate locali, stabilire la connessione usando la scheda Loopback con il comando seguente:

sqlcmd -S 127.0.0.1,1434

Suggerimento

Quando ci si connette al database SQL di Azure con la connessione amministrativa dedicata, è necessario specificare anche il nome del database nella stringa di connessione usando l'opzione -d.

Esempio

In questo esempio un amministratore si accorge che il server URAN123 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 URAN123 utilizzando -A per indicare la connessione DAC.

sqlcmd -S URAN123 -U sa -P <StrongPassword> -A

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

In un esempio simile per la connessione al database SQL viene usato il comando seguente che include il parametro -d per specificare il database:

sqlcmd -S serverName.database.windows.net,1434 -U sa -P <StrongPassword> -d AdventureWorks

Vedi anche