- Instância DEFAULT e conectividade não default – Parte 3
- Instância DEFAULT e conectividade não default – Parte 2
- Instância DEFAULT e conectividade não default – Parte 1
Quanto tempo você acha que uma conexão com o SQL Server deve levar para ser considerada como rápida ou lenta? Esse é aquele tipo de pergunta que não foge do famoso “depende”. Você já precisou ajustar as configurações de timeout de conexão da sua aplicação? E você já conseguiu se conectar na instância errada, mesmo passando as credenciais e nomes corretos?
É meu caro, as configurações de um client tem um papel muito maior quando o assunto é instância DEFAULT. A brincadeira fica mais interessante quando este tipo de instância não está configurado para responder ou no pipe padrão ou na porta TCP padrão…
CLIENTs e Ordem dos Protocolos
Bom, antes vamos recapitular como um client escolhe um protocolo para se conectar no SQL Server. Dependendo da biblioteca de conexão que estiver usando, podemos ter algumas variações:
- SQL Server Native Client (SNAC)
Usa a ordem especificada no SQL Server Configuration Manager (configuração referente à client e não a serviços). Esta biblioteca é geralmente usada por aplicações escritas em C/C++. Um exemplo de aplicação que usa o SNAC é o SQLCMD.
Como eu sei disso? BOL: https://technet.microsoft.com/en-us/library/ms187884(v=sql.130).aspx - SqlClient
Esta é usada geralmente por aplicações escritas em .NET! O Management Studio é um exemplo bem clássico. A ordem padrão é: Shared Memory, TCP/IP e Named Pipes.
Lembre-se que este client não é afetado pela ordem do Configuration Manager
Como eu sei disto? http://blogs.msdn.com/b/adonet/archive/2010/04/18/sqlclient-default-protocol-order.aspx (se não for suficiente, o link acima também fala isso).
O Native Client, grosseiramente falando, é um conjunto de bibliotecas disponibilizadas pela Microsoft para que seja possível se conectar com o SQL Server . Ele contém implementações das interfaces OLE DB e ODBC e substitui o velho MDAC (Microsoft Data Access Components) que também fornecia implementações OLE DB e ODBC. Você pode obter mais informações e documentação do Native Client na página do mesmo: https://msdn.microsoft.com/en-us/data/ff658532.aspx.
Assim como o Native Client, o SqlClient é a biblioteca disponibilizada pelo .NET Framework para que as aplicações escritas na linguagem possam se comunicar com o SQL Server. Mais informações em: https://msdn.microsoft.com/en-us/library/system.data.sqlclient(v=vs.110).aspx
Quando se trata-de uma instância DEFAULT, o client irá usar as opções padrão de conexão para cada protocolo. Por exemplo, no caso do TCP/IP a porta default é a 1433. No caso do Native Client, você pode alterar tanto a porta como o named pipe padrão. Quando uma instância NOMEADA é utilizada, então entra em ação o SSRP, ou SQL Server Resolution Protocol, onde o client vai mandar uma mensagem pro SQL Browser para tentar descobrir as informações de pipe e porta da instância desejada.
Isso nos leva há alguns cenários bem interessantes quando estamos lidando com instâncias DEFAULT, em portas/pipe diferentes do padrão:
- O que acontece se a porta 1433 estiver fechada, isto é, nenhum serviço está associado a ela?
- O que acontece se houver um firewall eliminando os pacotes?
- O que acontece se houver uma outra instância rodando na porta 1433 neste mesmo servidor?
Bom, vamos fazer uma brincadeira com cada um destes casos!
Cenário Porta 1433 fechada!
O primeiro cenário é o mais interessante e provavelmente o mais comum neste tipo de situação. Você instalou a instância DEFAULT e configurou para ela rodar em outra porta. Em meus testes, eu vi que a conexão sempre demora cerca de 1 segundo para completar. Este tempo não é alto! Ou é? Imagine uma aplicação que necessite abrir várias conexões a todo momento e que é crucial que o tempo de resposta do banco seja abaixo de 1 segundo. Bom, neste caso, você gastou 1 segundo só pra se conectar!
Para ilustrar, eu fiz um teste onde eu tento me conectar de um client, cujo o IP é 50.50.50.10, no servidor SQL1 (50.50.50.11) que possui uma instância default rodando na porta 1436. Eu usei um script muito simples em powershell pra gente poder medir o tempo que o sqlcmd levou:
$start = (Get-Date); #Pega a data atual! sqlcmd -S SQL1 -U Rodrigo -P rrg -Q "DECLARE @i int = 1;"; #Conecta com o SQL1 e faz algo simples ((Get-Date) - $start).totalMilliseconds #Exibe o tempo gasto
Aqui está o meu resultado:
No exemplo acima, o sqlcmd demorou mais de 1 segundo pra executar. Se você acha que isso é normal, então olhe uma versão mais rápida:
$start = (Get-Date); #Pega a data atual! sqlcmd -S np:SQL1 -U Rodrigo -P rrg -Q "DECLARE @i int = 1;"; #Conecta com o SQL1 e faz algo simples ((Get-Date) - $start).totalMilliseconds #Exibe o tempo gasto
Incríveis 148 milisegundos, conta 1073 do anterior. Quase 90% a menos.
Note que a unica diferença entre os dois scripts é que eu especifiquei o protocolo diretamente na conexão. Para entender o porquê dessa diferença, primeiro vejamos o que está acontecendo debaixo dos panos.
Eu usei o wireshark para capturar os pacotes de rede entre minha máquina e o servidor SQL no momento em que eu fiz o primeiro teste. Eis o resultado:
Resultado do Wireshark quando a conexão ficou lenta (clique para ampliar)
O que esse resultado nos revela:
- A coluna Time nos mostra o tempo desde o início da sessão de captura no formato Segundos.Milissegundos. A linha 1 nos mostra claramente que a primeira comunicação com o servidor SQL1 é feita diretamente na porta 1433, confirmando o que eu disse no ínicio do post. A coluna Info mostra que minha requisição, que está saindo pela porta 4752, está tentando abrir uma conexão na porta 1433. Como o protocolo TCP é usado, então um SYN enviado como parte do Three Way Handshake.
- Na linha 2, cerca de 0.0001 segundos depois do início da captura dos pacotes, o servidor SQL1 (50.50.50.11) responde com um RESET (coluna Info vem com a flag RST), isto é, como a porta está fechada e nenhum processo está na escuta, então a tentativa de conexão é negada. De novo, isso faz parte do processo Three Way Handshake do protocolo TCP.
- Cerca de 0.500 segundos depois, um TCP RETRANSMISSION é feito (linha 3). Isto é, a minha máquina está tentando novamente uma conexão na porta 1433. Na linha 4 o servidor volta a responder com um RST. Justo! Note que isto não é uma nova tentativa de conexão por parte do client SQL, mas uma tentativa do protocolo TCP. O client neste ponto, ainda está aguardando a reposta da chamada de rede (Ex.: WSAConnect())
- Na linha 5, já 1 segundo depois do ínicio da captura, um novo TCP RETRANSMISSION é feito. Se você reparar, o tempo que passou entre a linha 04 e a linha 05 foram os mesmos 500 milissegundos entre a linha 02 e linha 03. Novamente, como mostrado na linha 06, o servidor volta a negar a conexão. De novo, essa retrasmissão ainda faz parte da primeira tentativa de acesso do client (que está tentando o protocolo TCP/IP).
- A partir da linha 7, podemos perceber que o Named Pipes já começar a ser usado porque o client tenta se conectar na porta do SMB (445). O Named Pipes é uma implementação do protocolo SMB, e por isso estamos vendo ele em cena. Aqui, antes que esse tentativa fosse feita, o protocolo TCP respondeu ao client (no caso o Native Client) que a tentativa de conexão na 1433 foi sem sucesso, o que ocasionou um “failover” de protocolo, e o client tentou o próximo disponível , que é o Named Pipes.
Podemos concluir que devido ao fato da porta está fechada, uma retransmissão por parte do client está sendo feita, e essas retransmissões é quem estão consumindo a maior parte do tempo para efetivar a conexão com o SQL Server. Sim, mesmo que o TCP acaba não sendo usado no final… Quando eu especifico o protocolo diretamente, estamos forçando o client a usar este protocolo ao invés de tentar seguir a ordem configurada. Então a tentativa na porta 1433 é eliminada, e com isto os retransmissions. Os dois scripts abaixo permitem contornar esse problema porque estamos justamente eliminando aquelas tentativas de conexão iniciais:
Versão TCP:
$start = (Get-Date); #Pega a data atual! sqlcmd -S tcp:SQL1,1436 -U Rodrigo -P rrg -Q "DECLARE @i int = 1;"; #Força a conexão na porta TCP 1436 ((Get-Date) - $start).totalMilliseconds #Exibe o tempo gasto
Note que não há tentativa na 1433
Aqui a versão com Named Pipes:
$start = (Get-Date); #Pega a data atual! sqlcmd -S np:SQL1 -U Rodrigo -P rrg -Q "DECLARE @i int = 1;"; #Força a conexão por named pipes ((Get-Date) - $start).totalMilliseconds #Exibe o tempo gasto
Note que aqui também não há tentativa 1433
Outro ponto bem interessante para se notar é que a maior parte do tempo gasto para abrir a conexão foi devido ao TCP Retransmission. Um dos parâmetros que controla quantas vezes o protocolo TCP/IP irá tentar um retransmission é o MaxSYNRetransmissions. Você precisa instalar este hotfix para poder alterar no Windows 7 ou no Windows Server 2008 R2. O chato é que o valor mínimo é 2 e o máximo é 8. Para alterar, depois que o hotfix estiver instalado, basta executar o comando netsh:
netsh interface tcp set global MaxSynRetransmissions=3
Para nós este comando não tem muita utilidade, uma vez que o menor é 2 (e é justamente as duas tentativas que observamos nos exemplos acima). Vou ficar devendo um teste mais minuncioso, usando um debugger e outras ferramentas para ver se conseguimos alterar este valor apenas para fazer uma brincadeira e matar umas curiosidades, já que você não vai querer alterar essas configurações em produção! Certo!?
Ao relizar os testes, eu percebi que a ordem que estava no configuration Manager era: Shared Memory, TCP/IP, Named Pipes. Eu também poderia ter contornado isto de várias formas, onde destaco:
- Ajustar a ordem dos protocolos
Isto atenderia perfeitamente este caso. Seu eu colocar o named pipes (sim, eu fiz este teste) antes do TCP/IP, então a conexão seria feita de imediato no pipe padrão (já que este eu não alterei lá na instancia). Porém, caso a instância não aceite o outro protocolo, então não vai adiantar muito. E também, pro caso do SqlClient do .NET, você não pode alterar a ordem padrão. Então, as tentativas no SSMS ainda continuariam com o delay. - Alterar a porta default ou pipe padrão
Usando o SQL Server Configuration Manager, poderíamos alterar a porta default para 1436 (ou o pipe default, se foste o caso). Porém isso faria com que as conexões em outras instâncias DEFAULT , que estivessem rodando na 1433, falhassem. - Configurar um Alias
Esta seria uma saída interessante, porém você precisaria sair alterando em todas as máquinas que possuem alguma aplicação que se conecta com a instância. Dependendo, poderia ser viavel. - [UPDATE 21/12/2012] Adicionar a porta 1433
Esta é uma saída mais interessante ainda. Se estiver em um cenário onde mudar a porta atual não é uma saída (pelo fato de algumas aplicações estarem usando a mesma, por exemplo), você pode configurar o SQL Server para ouvir em mais de uma porta TCP. Basta separar portas por vírgula, no SQL Server Configuration Manager. Isso irá requerer um restart da instância! Os links a seguir fornecem mais detalhes:
Bom, por hoje é só. Nas próximas 2 duas partes eu vou te mostrar o que acontece quando um firewall está no meio, e também como você pode acabar se conectando na instância errada, mesmo tendo fornecido todas as informações corretamente.
Até lá!
UPDATE: O Link para a parte 2 está aqui!
[]’s
Rodrigo Ribeiro Gomes
Ops… Não acabei ainda! Já se inscreveu pro SQL Saturday 469? Se não, corre lá: https://www.sqlsaturday.com/469/RegisterNow.aspx
DBA Team Leader na Power Tuning
Comments ( 4 )