MELHORES PRÁTICAS CONTRA FALHAS DE PERDA DE CONEXÃO NO MICROSOFT SQL AZURE
Support Engineer Marcelo Franceschi de Bianchi – CTS LATAM, Senior Support Escalation Engineer Roberto Cavalcanti – CTS LATAM
1 Introdução ao SQL Azure
O Microsoft SQL Azure é um serviço baseado em computação em nuvem, atualmente usando a tecnologia do SQL Server 2012. Ele funciona em Data Centers no qual o hardware é mantido e de propriedade da Microsoft.
O SQL Azure foi idealizado para ser uma plataforma que suporta escalabilidade, dessa forma ela possui uma vasta quantidade de computadores e gerencia todas as conexões realizando o roteamento da sua aplicação e os servidores físicos no qual residem os bancos de dados. Pode-se afirmar que o Servidor SQL Azure é um grupo lógico de banco de dados que provê um serviço compartilhado.
Existem características de balanceamento de carga e de alta disponibilidade no SQL Azure. Os bancos de dados podem estar armazenados em diferentes computadores num mesmo Data Center e cada computador pode conter muitos bancos de dados. Quando não há capacidade suficiente para atender determinados picos de uso dos bancos de dados do SQL Azure, alguns momentos de baixo desempenho podem acontecer. Nesses momentos o SQL Azure termina a sessão que está utilizando recursos excessivos para poder manter a estabilidade do sistema como um todo e prevenir que algumas poucas sessões monopolizem todos os recursos importantes que estão disponibilizados entre todos os usuários (especialmente memória e CPU).
2 Entendendo a Arquitetura do SQL Azure
Como pode ser visto na figura 1, o SQL Azure provê a interface através do Tabular Data Stream (TDS) como acontece com o SQL Server.
Os load balancers garantem que haverá uma utilização ótima dos servidores físicos e dos serviços nos Data Centers.
O TDS Gateway funciona como uma interface entre a aplicação e a plataforma que está mais abaixo, no qual os dados estão armazenados, e tem–se a execução de serviços como isolamento e provisionamento de dados, cobrança, medições e também roteamento de conexões.
Na figura abaixo temos a plataforma que consiste de múltiplas instâncias do SQL Server que podem ser gerenciadas pelo SQL Azure Fabric e dessa forma teremos o controle de falhas automático, o balanceamento de carga e a replicação também automática entre os servidores físicos.
Figura 1: SQL Azure Network Topology (*)
3 Considerações gerais sobre as conexões do SQL Azure Database
3.1 A porta TCP 1433
O serviço de banco de dados do SQL Azure está somente disponível para trabalhar na porta TCP 1433. Para acessar o banco de dados do SQL Azure de um computador remoto, deve-se garantir que o firewall permita o tráfego de saída na porta TCP 1433.
3.2 Configuração do Firewall
Antes da conexão no servidor do SQL Azure pela primeira vez, deve-se utilizar o portal de gerenciamento da plataforma Windows Azure para configurar o firewall do SQL Azure. Será necessário criar uma configuração do firewall para permitir que as conexões originadas de um computador remoto sejam permitidas.
3.3 Connection string no formato <login>@<server>
Por causa de algumas ferramentas utilizarem de forma diferente o protocolo TDS, é necessário que se coloque o nome do servidor do SQL Azure na connection string utilizando o formato <login>@<server>.
3.4 Especificar o banco de dados na connection string
Caso não seja especificado o banco de dados na connection string, a conexão será direcionada automaticamente para o banco de dados master.
3.5 O comando Transact-SQL USE não é suportado
O comando Transact-SQL “USE” não é suportado para a realização de troca de banco de dados. Deve-se estabelecer a conexão diretamente com o banco de dados alvo.
3.6 Limitações de parâmetros nos comandos Transact-SQL
Nem todos os comandos Transact-SQL do SQL Server são suportados pelo SQL Azure e alguns comandos que são suportados podem não possuir os parâmetros que são opcionais quando são rodados no SQL Server 2008.
3.7 As Views sys.sql_logins e sys.databases
Caso deseje-se criar logins e bancos de dados deve-se conectar ao banco de dados master. O banco de dados master possui as views sys.sql_logins e a sys.databases. Essas views poderão ser utilizadas para visualizar os logins e databases disponíveis em um determinado banco de dados.
3.8 A Windows Authentication não é suportada
O SQL Azure não suporta a forma de autenticação Windows Authentication. Deve-se utilizar o SQL Server authentication na connection string.
3.9 Connection by OLE DB não é suportado
O SQL Azure não suporta a forma de autenticação OLE DB.
3.10 Transações distribuídas não são suportadas
SQL Azure não suporta transações distribuídas, pois essas transações podem afetar muitos recursos do sistema.
3.11 Nunca se esqueça de fechar a conexão
O SQL Azure provê um serviço de banco de dados de larga escala com recursos compartilhados. Para que todos os usuários tenham qualidade na utilização do banco de dados, é necessário que a conexão seja fechada toda vez que a mesma não está sendo utilizada.
4 Erros de perda de conexão
A tabela 1 irá listar os erros de SQL Azure que ocorrem quando a conexão é finalizada.
Erro |
Descrição |
40197 |
The service has encountered an error processing your request. Please try again. Error code %d. Se acaso receber esse erro, ele acontece quando o serviço está indisponibilizado porque foi realizado um upgrade de software ou hardware, possíveis falhas ou então qualquer outro problema de failover em geral. Podem existir situações no qual os códigos de erro 40143 e 40166 fiquem encapsulados na mensagem de erro do código de erro 40197. Ou seja, os códigos de erros 40143 e 40166 provêm informações adicionais do tipo da falha que ocorreu. Não devemos modificar a aplicação para capturar esses códigos de erros 40143 e 40166. Deve-se tentar reconectar no banco de dados do SQL Azure até que os recursos estejam disponíveis e sua conexão seja restabelecida novamente. |
40501 |
The service is currently busy. Retry the request after 10 seconds. Code: %d. Quando o código de erro 40501 é retornado, deve-se reconectar com o banco de dados do SQL Azure em intervalos de 10 segundos até que todos os recursos estejam disponíveis e a sua conexão seja restabelecida novamente. |
40544 |
The database has reached its size quota Quando é retornado esse código de erro, deve-se apagar os dados na partição e apagar os índices. Um dica é consultar a documentação para diferentes resoluções. |
40549 |
Session is terminated because you have a long-running transaction Caso seja visualizado esse código de erro então se deve tentar fazer transações que não demorem tanto tempo para serem executadas. |
40550 |
The session has been terminated because it has acquired too many locks Caso seja recebido esse código de erro, então se deve ler ou modificar poucas linhas em uma única transação. |
40551 |
The session has been terminated because of excessive TEMPDB usage Caso esse código de erro seja visualizado, deve-se tentar modificar a consulta para reduzir o espaço utilizado da tabela temporária do banco de dados do SQL Azure. |
40552 |
The session has been terminated because of excessive transaction log space usage Caso esse código de erro seja recebido, então se deve tentar modificar poucas linhas em uma única transação. |
40553 |
The session has been terminated because of excessive memory usage Caso seja visualizado esse código de erro, então se deve tentar modificar as suas consultas para processar poucas linhas de cada vez. |
40613 |
Database '%.*ls' on server '%.*ls' is not currently available Caso esse código de erro seja recebido, então se deve tentar reconectar se mais tarde e caso o problema persista, então contate o suporte e providencie a session tracingID para os engenheiros. |
Tabela 1: Erros de perda de conexão no SQL Azure
A tabela 2 irá listar alguns erros adicionais que podem ocorrer na utilização do SQL Azure.
Erro |
Descrição |
20 |
The instance of SQL Server you attempted to connect to does not support encryption. |
64 |
A connection was successfully established with the server, but then an error occurred during the login process. |
233 |
The client was unable to establish a connection because of an error during connection initialization process before login. As possíveis causas para esse erro: O cliente tentou conectar com uma versão não suportada do SQL Server, ou então o servidor estava muito ocupado para aceitar novas conexões, ou ainda então tinha uma limitação de recursos, por exemplo memória insuficiente. |
10053 |
A transport-level error has occurred when receiving results from the server. Uma conexão estabelecida foi terminada por um software no computador remoto. |
10054 |
A transport-level error has occurred when sending the request to the server. |
10060 |
A network-related or instance-specific error occurred while establishing a connection to SQL Server O servidor não foi encontrado ou não estava acessível. Verifique o nome da instância, se está correto e que o SQL Server foi configurado para permitir conexões remotas. |
Tabela 2: Erros adicionais de perda de conexão no SQL Azure
5 Possíveis Causas de Perda de Conexão
A situação de perda de conexão para banco de dados que são compartilhados não é algo incomum. Existe uma característica que o banco de dados SQL Azure tem no qual ele monitora e faz também o balanceamento automático dos usuários que estão on line em um determinado momento no Servidor de banco de dados em um Data Center. Para que isso seja possível o SQL Azure continuamente coleta e analisa as estatísticas do banco de dados e então finaliza as conexões que julga que estão acima dos limites pré-estabelecidos para manter o equilíbrio no sistema como um todo.
As possíveis causas de perda de conexão:
5.1 Lock consuption – As sessões que consomem mais de um milhão de locks serão finalizadas. Para monitorar e obter informações sobre quais são essas queries você pode consultar algumas views:
sys.dm_tran_active_transactions
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
5.2 Uncommitted transactions: As transações não comitadas podem bloquear a ação de truncar os arquivos de log. Para prevenir isso, então é calculada a distância entre o primeiro ou o mais velho número de sequência da transação ativa (LSN) para ser cortado do log, e isso não pode exceder 20% do tamanho do arquivo de log. Quando isso é violado, a transação responsável é então finalizada e com isso então é realizado o roll back e dai o log pode ser trancado. O código de erro correspondente é o 40552.
5.3 Transactions blocking system calls: Qualquer tipo de transação pode fazer a requisição de locks de diferentes tipos para utilização de recursos, no qual podem ser linhas, páginas ou tabelas, no qual a transação é sempre dependente. Cada transação libera esses locks quando não são mais necessários a utilização dos recursos alocados. Esses locks em algumas transações podem bloquear alguns recursos que são necessários para sessões do sistema. Caso a transação bloqueie um recurso necessário para o sistema por mais de 20 segundos então essa transação será finalizada. O código de erro é o 40549. O sistema tem algumas operações que mantem a disponibilidade e a consistência do banco de dados, por exemplo:
- Failover configuration management
- Replica reconfiguration
- Configuration, quando um banco de dados ou replica fica offline.
5.4 Log file size: Transações que consomem log de forma excessiva serão terminadas. O máximo permitido para o tamanho do log para uma transação é de 1 GB. O código de erro é o 40552.
5.5 TempDB usage and size: Transações que executam grandes batches ou ordenações de muitos dados, podem consumir muito espaço na tempdb. Quando uma sessão utiliza mais do que 5 GB do espaço da tempdb, essa sessão será finalizada. Quando receber o código de erro 40551, tente modificar a sua consulta para reduzir a utilização do espaço temporário do banco de dados do SQL Azure. Caso estiver utilizando objetos temporários, após fazer o uso dos mesmos, delete-os. Outra dica importante é que caso seja possível fazer o truncate das tabelas temporárias e remover possíveis tabelas que não estão sendo mais utilizadas, é algo que irá ajudar muito.
5.6 Excessive memory usage: Quando há a contenção de memória, sessões consumindo uma grande quantidade, ou seja, mais que 16 MB por mais de 20 segundos serão finalizadas. O código de erro é o 40553.
5.7 Maximum database size: Um banco de dados será somente de leitura quando atingir o seu tamanho máximo. Transações que irão tentar realizar updates ou inserts serão finalizadas e o usuário irá receber o código de erro 40544. Para resolver esse problema pode-se particionar ou excluir alguns dados, ou então poderá apagar alguns índices ou ainda então fazer um upgrade para um banco de dados maior utilizando o comando ALTER DATABASE.
5.8 Idle conections: Conexões que estão inativas por mais de 30 minutos ou mais serão finalizadas. E não será retornado nenhum erro.
5.9 Transactions Termination: O SQL Azure irá matar todas as transações que ainda permanecerem rodando após o período de 24 horas. Caso a conexão seja perdida nessas condições será por essa razão, se receber o código de erro 40549.
5.10 Denial of Service attacks: Quando há um alto número de falhas de logins de um IP específico, o SQL Azure irá bloquear as conexões vindas daquele determinado IP por um período de tempo. Nesse caso , o SQL Azure apenas finaliza a conexão e não retorna nenhum erro. Uma boa prática seria configurar as regras de Firewall para evitar esse tipo de ataque DoS (Denial of Service).
5.11 Network problems: A conexão pode ser finalizada por vários problemas de redes. Caso se esteja armazenando e rodando o software no Windows Azure, isso é algo muito benéfico para a desempenho da aplicação, pois minimiza a latência de rede que está associada com a requisição de dados da aplicação para o banco de dados do SQL Azure. Para clientes que precisam que os dados atravessem a Internet para alcançar o banco de dados haverá o tráfego de rede entre o código do cliente e dos dados. Se um problema de rede acontecer, então poderá causar a perda da conexão no SQL Azure e retornará uma mensagem de erro, pois a sessão foi terminada. Caso haja uma tentativa de se utilizar a mesma conexão justamente quando o connection pooling estiver agindo, então um erro será visualizado.
10053: A transport level error has occurred when receiving results from the server.
5.12 Failover problems: O SQL Azure foi construído baseado nas tecnologias Windows Server e SQL Server e é flexível o bastante para aguentar variações de utilização e de carga. O serviço realiza a replicação de cópias que são redundantes em múltiplos servidores físicos para manter os dados com alta disponibilidade. No caso de uma falha de hardware, o SQL Azure providência um automatic failover para aperfeiçoar a disponibilidade da aplicação. Em alguns casos algumas ações de failover resultam em uma finalização da sessão e a aplicação recebe um erro genérico.
10053: A transport-level error has occurred when receiving results from the server
Ou então,
40197: The service has encountered an error processing your request. Please try again.
Quando esse tipo de erro acontecer, faça a reconexão com o SQL Azure automaticamente.
5.13 Outras razões para a perda de conexão: O balanceamento de carga no SQL Azure garante que a utilização de servidores físicos e serviços nos Data Centers seja aperfeiçoada. Quando a utilização da CPU, a latência de input / output ou então o número de processos excedem o limite pré-estabelecido o SQL Azure irá finalizar a conexão. Quando isso acontece, o código de erro é 40501. O limite para o número de processos ativos é de 300.
6- Melhores Práticas para prevenir Perdas de Conexão
Para prevenir a perda de conexão, deve-se gerenciar apropriadamente os recursos nas aplicações que utilizam o SQL Azure. Pode- se afirmar que o conceito de conexões estabelecidas, são as conexões que retornam dados. Para prover um serviço contínuo ao usuário, caso seja fechada a conexão, deve-se reestabelecer a conexão e então reexecutar os comandos que falharam ou então reexecutar uma consulta.
Para um melhor aproveitamento do SQL Azure é necessário implementar a lista de boas práticas relacionadas abaixo:
6.1 Minimize network latency:
Ao criar um novo servidor de banco de dados no SQL Azure, é sempre melhor escolher o Data Center mais próximo, para evitar um custo de largura de banda extra e com isso consequentemente obter uma melhor desempenho entre a aplicação e o banco de dados SQL Azure.
6.2 Reduce network usage
Deve-se implementar uma aplicação que utilize caching para evitar a renderização de grandes quantidades de dados em cada chamada ao banco de dados e também assim também minimizando as possíveis round-trips entre a aplicação e o SQL Azure.
6.3 Gerenciamento da conexão de rede:
Deve-se manter a conexão aberta o máximo que possível. É necessário fazer o ajuste do timeout da conexão de 30 segundos na connection string.
É recomendado utilizar o connection pooling, pois aumenta a eficiência do código e então reduzindo o número de vezes que novas conexões precisem ser abertas e fecha automáticamente as conexões que estão em estado de ociosas. O SQL Azure termina conexões que tem o tempo de ociosidade de 30 minutos ou mais.
É necessário realizar todas as operações de transações com os comandos TRY ... CATCH, incluindo todas as exceções. Se um erro acontece e impede que uma transação seja completada com sucesso, o SQL Azure automaticamente executa o roll back dessa transação e libera todos os recursos que estavam alocados para a mesma. É importante sempre lembrar que se uma exceção ocorrer e houver a desconexão, então se deve realizar a reconexão.
7 Implemente retry logic em suas aplicações
O SQL Azure TDS Gateway, tenta reconectar as conexões por cerca de 30 segundos antes de reportar uma falha. Se você esperar um alto volume no tráfico de dados, projete o retry logic na aplicação. Se a conexão falhar, então se deve seguir as seguintes recomendações:
7.1 Realize desconexões transientes
É fortemente recomendado a utilização do retry logic para que a reconexão aconteça em um intervalo de 10 segundos até que os recursos estejam disponíveis e sua conexão seja estabelecida novamente. Dependendo de sua aplicação, banco de dados e do workload de rede então se deve aumentar o delay time caso seja necessário.
7.2 Mude o seu workload se a conexão é terminada novamente
Se a conexão é finalizada novamente então se deve visualizar qual foi o código do erro e então é necessário encontrar o problema real e após isso se deve realizar a mudança do seu workload novamente. Pode-se implementar uma fila ou então um mecanismo de delay em sua aplicação cliente para reduzir o workload.
Uma das possíveis soluções seria fazer o projeto novamente da aplicação e do banco de dados, pois dessa forma pode-se remover os possíveis gargalos. É necessário ter certeza que a aplicação não irá realizar um overload de dados na tabela tempdb através de excessivas operações DML e DDL. Além do mais, deve-se garantir que as transações não bloqueiem nenhum recurso disponível no sistema. Caso seja necessário, então se deve realizar o particionamento do banco de dados em múltiplos banco de dados.
7.3 Implementar as seguintes recomendações no código da sua aplicação:
- Executar transações em loop contínuos
- Tratar erros de finalização de conexões
- Pausar por alguns segundos e então fazer a tentativa de reconexão no banco de dados novamente.
8 References
SQL Azure Connection Management
https://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx
Guidelines for Connecting to SQL Azure Database
https://msdn.microsoft.com/en-us/library/windowsazure/ee336282.aspx
Retry Logic for Transient Failures in SQL Azure
SQL Azure Connectivity Troubleshooting
SQL Azure Performance and Elasticity Guide
* Figura extraída do artigo: SQL Azure Connection Management