Conexão de diagnóstico para administradores de banco de dados

Artigo
06/25/2013

O SQL Server fornece uma conexão diagnóstica especial para administradores quando conexões padrão com o servidor não são possíveis. Esta conexão diagnóstica permite que um administrador acesse o SQL Server para executar consultas diagnósticas e resolver problemas mesmo quando o SQL Server não está respondendo às solicitações de conexão padrão.

Esta conexão de administrador dedicada (DAC) oferece suporte à criptografia e outros recursos de segurança do SQL Server. O DAC só permite alterar o contexto de usuário para outro usuário admin.

O SQL Server esforça-se o máximo para o êxito de uma conexão DAC, mas pode falhar em situações extremas.

Conectando com DAC

Por padrão, a conexão só é permitida de um cliente executando no servidor. As conexões de rede apenas são permitidas se forem configuradas com o procedimento armazenado sp_configure com a opção remote admin connections remotas.

Apenas membros da função SQL Server sysadmin podem se conectar usando o DAC.

O DAC está disponível e tem suporte através do utilitário de prompt de comando sqlcmd que usa uma opção de administrador especial (-A). Para obter mais informações sobre como usar o sqlcmd, consulte Usar sqlcmd com variáveis de script. Você também pode se conectar inserindo o prefixo admin: no nome da instância no formato sqlcmd -Sadmin:<instance_name>. Você também pode iniciar um DAC de um SQL Server Management Studio Editor de Consulta se conectando ao admin:<instance_name>.

Restrições

Como o DAC existe somente para diagnosticar problemas do servidor em circunstâncias raras, há algumas restrições na conexão:

Para garantir que existam recursos disponíveis para a conexão, apenas um DAC é permitido por instância do SQL Server. Se uma conexão DAC já estiver ativa, qualquer nova solicitação de conexão via DAC será negada com o erro 17810.
Para conservar recursos, o SQL Server Express não escuta na porta do DAC, a menos que tenha sido iniciado com um sinalizador de rastreamento 7806.
Inicialmente, o DAC tenta se conectar ao banco de dados padrão associado ao logon. Após uma conexão bem-sucedida, você pode se conectar ao banco de dados master. Se o banco de dados padrão estiver offline ou não estiver disponível, a conexão retornará o erro 4060. Entretanto, ela terá êxito se você substituir o banco de dados padrão para se conectar ao banco de dados master usando o seguinte comando:

sqlcmd –A –d mestre

Recomendamos que você se conecte ao banco de dados master com o DAC porque o master estará com certeza disponível se a instância do Mecanismo de Banco de Dados for iniciada.
O SQL Server impede a execução de consultas paralelas ou comandos com o DAC. Por exemplo, erro 3637 será gerado se você executar uma das seguintes instruções com o DAC:
- RESTORE
- BACKUP
Apenas recursos limitados têm garantia de disponibilidade com o DAC. Não use o DAC para executar consultas com muitos recursos (por exemplo, uma junção complexa em tabela grande) ou consultas que podem ser bloqueadas. Isso ajuda a evitar que o DAC combine qualquer problema de servidor existente. Para evitar possíveis cenários de bloqueio, se você tiver que executar consultas que possam ser bloqueadas, execute-as nos níveis de isolamento baseados em instantâneos, se possível. Caso contrário, defina o nível de isolamento da transação como READ UNCOMMITTED e o valor LOCK_TIMEOUT como um valor curto, como 2000 milissegundos, ou ambos. Isso impedirá que a sessão DAC seja bloqueada. Porém, dependendo do estado do SQL Server, a sessão DAC pode ser travada imediatamente. É possível que você consiga encerrar a sessão DAC usando CNTRL-C, mas não é garantido. Nesse caso, sua única opção pode ser reiniciar o SQL Server.
Para garantir a conectividade e resolver problemas com o DAC, o SQL Server reserva recursos limitados para processar comandos executados no DAC. Esses recursos geralmente só são suficientes para diagnósticos simples e resolução de problemas de funções, como as listadas abaixo.

Embora seja teoricamente possível executar qualquer instrução Transact-SQL que não tenha que ser executada paralelamente no DAC, recomendamos que você restrinja o uso dos seguintes comandos de diagnóstico e resolução de problemas:

Consultando exibições de gerenciamento dinâmico para diagnóstico básico como sys.dm_tran_locks para o status de bloqueio, para verificar sys.dm_os_memory_cache_counters a integridade de caches e sys.dm_exec_requests e sys.dm_exec_sessions para sessões ativas e solicitações. Evite exibições de gerenciamento dinâmico que usem muitos recursos (por exemplo, sys.dm_tran_version_store examina o repositório de versão completo e pode causar E/S extensiva) ou que usem junções complexas. Para obter informações sobre implicações de desempenho, consulte a documentação sobre exibições de gerenciamento dinâmico específicas.
Consulta de exibições do catálogo.
Comandos DBCC básicos como DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, e DBCC SQLPERF. Não execute comandos intensivos do recurso como DBCC CHECKDB, DBCC DBREINDEX ou DBCC SHRINKDATABASE.
Comando Transact-SQL KILL <spid> . Dependendo do estado do SQL Server, o comando KILL nem sempre pode ter êxito; assim, a única opção pode ser reiniciar o SQL Server. A seguir, algumas diretrizes gerais:
- Verifique se o SPID foi realmente interrompido consultando SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>. Se nenhuma linha for retornada, significa que a sessão foi interrompida.
- Se a sessão ainda estiver ativa, verifique se há tarefas atribuídas para ela executando a consulta SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>. Se a tarefa estiver ativa, é provável que sua sessão esteja sendo interrompida no momento. Observe que isso pode levar um tempo considerável e não ter êxito nenhum.
- Se não houver nenhuma tarefa no sys.dm_os_tasks associada a esta sessão, mas a sessão continuar em sys.dm_exec_sessions após a execução do comando KILL, significa que você não tem um trabalhador disponível. Selecione uma das tarefas atualmente em execução (uma tarefa listada na exibição de sys.dm_os_tasks com sessions_id <> NULL) e interrompa a sessão associada a ela para liberar o trabalhador. Observe que talvez não seja suficiente interromper apenas uma sessão: talvez seja necessário interromper várias.

Porta DAC

O SQL Server verifica o DAC em uma porta TCP 1434, se estiver disponível, ou em uma porta TCP dinamicamente atribuída na inicialização do Mecanismo de Banco de Dados. O log de erros contém o número da porta que o DAC está escutando. Por padrão, a escuta do DAC aceita conexão apenas na porta local. Para uma amostra de código que ativa conexões de administração remotas, consulte Opção de configuração de servidor remote admin connections.

Depois que uma conexão de administração remota for configurada, a escuta do DAC será habilitada sem a necessidade de reiniciar o SQL Server e um cliente poderá se conectar ao DAC remotamente. Você pode habilitar a escuta do DAC para aceitar conexões remotamente, mesmo se o SQL Server não responda durante a primeira conexão com o SQL Server usando o DAC localmente e, em seguida, executar o procedimento armazenado sp_configure para aceitar conexão de conexões remotas.

Em configurações de cluster, o DAC estará offline, por padrão. Os usuários podem executar a opção remote admin connection do sp_configure para permitir que a escuta do DAC acesse uma conexão remota. Se o SQL Server não estiver respondendo e a escuta do DAC não estiver habilitada, talvez seja necessário reiniciar o SQL Server para se conectar ao DAC. Dessa forma, recomendamos que você habilite a opção de configuração remote admin connections em sistemas clusterizados.

A porta DAC é atribuída dinamicamente pelo SQL Server durante a inicialização. Ao conectar-se à instância padrão, o DAC evita usar uma solicitação do Protocolo de Resolução (SSRP) do SQL Server ao serviço SQL Server Browser. Primeiro ele se conecta usando a porta TCP 1434. Se isso falhar, ele faz uma chamada ao SSRP para obter a porta. Se o navegador do SQL Server não estiver escutando as solicitações de SSRP, a solicitação de conexão retornará um erro. Consulte o log de erros para localizar o número da porta que o DAC está escutando. Se o SQL Server for configurado para aceitar conexões de administração remotas, o DAC deverá ser iniciado com um número de porta explícito:

sqlcmd –Stcp:<server>,<port>

O log de erros do SQL Server lista o número da porta para o DAC, que, por padrão, é 1434. Se o SQL Server estiver configurado para aceitar apenas conexões DAC locais, conecte usando o adaptador de loopback com o seguinte comando:

sqlcmd –S127.0.0.1,1434

Exemplo

Neste exemplo, um administrador nota que o servidor URAN123 não está respondendo e deseja diagnosticar o problema. Para isso, o usuário ativa o utilitário de prompt de comando sqlcmd e se conecta ao servidor URAN123 usando -A para indicar o DAC.

sqlcmd -S URAN123 -U sa -P <xxx> –A

Agora o administrador pode executar consultas para diagnosticar o problema e possivelmente encerrar as sessões sem-resposta.