Corrigindo erros de conexão após diminuir os valores da propriedade ‘Maximum Number of Concurrent Connections’ no SQL Server

Olá Pessoal!

Publiquei hoje um novo artigo na Wiki do TechNet da Microsoft e vou compartilhar com vocês.

A situação abordada no artigo corresponde a um problema que passei enquanto testava o comportamento do SQL Server após alterar as propriedades do Maximum Number of Concurrent Connections para valores baixos.

Segue o link para a Wiki do TechNet: http://social.technet.microsoft.com/wiki/pt-br/contents/articles/29489.corrigindo-erros-de-conexao-apos-diminuir-os-valores-da-propriedade-maximum-number-of-concurrent-connections-no-sql-server.aspx

Artigo publicado:

Introdução

No SQL Server existe uma propriedade ao nível de servidor chamada Maximum Number of Concurrent Connections, essa configuração especifica o número máximo de conexões de usuários concorrentes a uma instância do SQL Server. O número atual de conexões de usuários permitida depende da versão do SQL Server, assim como, os limites da aplicação ou hardware.

O valor padrão para essa propriedade é zero e normalmente esse valor não será alterado, pois significa que não existem “limites” (na verdade o valor máximo é de 32.767) para a quantidade de conexões de usuários concorrentes.

Problema

Tive o interesse de observar o comportamento da propriedade Maximum Number of Concurrent Connections com valores pequenos e acabei me deparando com uma situação inesperada ao alterar o valor configurado para 1.

print 1

Após aplicar as alterações e reiniciar o serviço do SQL Server, começaram os problemas para se conectar a instância. Consegui observar em diversas tentativas de conexão três cenários com mensagens de erro diferentes retornadas ao tentar se conectar.

O primeiro cenário o erro apresentado é o de número 233 descrevendo que não é possível estabelecer a conexão devido à falha no processo de logon. O interessante é que essa mensagem cita em um dos trechos o limite máximo de conexões permitidas como uma das possíveis causas dos problemas.

print 2

O segundo é o erro de número 64 que informa que a conexão foi estabelecida, mas também com erro no logon.

print 3

O terceiro e último também é o erro de número 64, mas dessa vez foi possível estabelecer a conexão no Object Explorer.

print 4

Apesar dessa conexão no Object Explorer, qualquer operação via interface não é possível e novos erros são retornados, como a mensagem abaixo.

print 5

Resolução

Primeiro passo a ser feito para a solução é tentar a conexão DAC (Conexão de Administrador Dedicada) pelo Management Studio, colocando a instrução ‘ADMIN:’ antes do nome da conexão. Caso seja possível abrir uma sessão, na sequência execute as instruções abaixo para resolver o problema.

sp_configure ‘show advanced options’, 1
Go
Reconfigure
Go

sp_configure ‘user connections’, 0 –ou outro valor desejado
Go
Reconfigure
Go

Nem sempre é tão simples, a solução via Management Studio pode não funcionar, como podemos ver abaixo.

print 6

Sendo assim, vamos optar por utilizar o utilitário SqlCmd para fazer a conexão.

Abra o Command Prompt e faça os seguintes passos.

1º – Execute o comando:

sqlcmd.exe -E -S nome_servidor (Ex: .\SERVER2012)

Legenda:
-E (Uso da autenticação do Windows)
-S (Nome da conexão)

2º – Após conectar com sucesso na instância, execute as instruções TSQL linha a linha:

sp_configure ‘show advanced options’, 1
Go
Reconfigure
Go

sp_configure ‘user connections’, 0 — ou outro valor desejado
Go
Reconfigure
Go

3º – Reinicie o serviço do SQL Server, tente a conexão novamente e provavelmente o problema terá sido resolvido.

print 7

Conclusão

Após passar por esse contratempo durante os testes na propriedade Maximum Number of Concurrent Connections, foi possível contornar através de umas das técnicas acima. Essa é uma propriedade avançada e normalmente apenas cenários de grande concorrência de conexões que necessitará de alteração no valor padrão. Para mais informações veja nas recomendações no site da Microsoft.

Obs: Os passos efetuados acima foram simulados em ambientes SQL Server 2014 e 2012 com sucesso.

Até a próxima!

SQL Server

Anúncios

Removendo o SQL Server Management Data Warehouse (Data Collector)

Olá Pessoal!

Essa semana publiquei um artigo na Wiki do TechNet da Microsoft e gostaria de compartilhar com vocês.

O artigo em questão é sobre um problema que passei recentemente e só consegui resolver após muita pesquisa e testes.

Segue:

 

Introdução

O Data Collector é um componente presente desde o SQL Server 2008 e coleta diferentes informações dos bancos de dados. Todas as informações coletadas são armazenadas em uma base de dados chamada Management Data Warehouse e posteriormente alguns relatórios podem ser extraídos com esses dados.

Esse componente é muito utilizado pelas informações apresentadas através de seus relatórios e também por ser de fácil configuração, mas o problema é quando o usuário deseja remover por completo a solução.

A remoção do MDW (Management Data Warehouse) não é suportada, mas no SQL Server 2012 uma nova Stored Procedure foi adicionada para auxiliar nesse ponto e pode ser executada em versões anteriores.

Vamos avançar para o entendimento do que é criado no processo de configuração e como remover tudo que é criado em seu ambiente.

Composição

Ao fim da criação do Management Data Warehouse um job chamado de “mdw_purge_data_[MDW]” é criado, esse tem o objeto de limpar os dados de tempos em tempos.

Após a etapa de configurar a instância para ter seus dados coletados, outros 5 jobs (coletores) são criados:

“collection_set_1_noncached_collect_and_upload”
“collection_set_2_collection”,
“collection_set_2_upload”,
“collection_set_3_collection”
“collection_set_3_upload”.
Para o SQL Server 2012 3 jobs adicionais são incluídos:

“sysutility_get_cache_tables_data_into_aggregate_tables_daily”
“sysutility_get_cache_tables_data_into_aggregate_tables_hourly”
“sysutility_get_views_data_into_cache_tables”

Remoção

Vamos tentar remover da maneira mais intuitiva.

Vá até cada coletor e pare o serviço de coleta, após esse processo os Jobs estarão desabilitados, com exceção dos “mdw_purge_data” e “sysutility”, você pode ainda desabilitar o próprio Data Collector indo nas propriedades do componente. Na sequência ao tentar excluir os Jobs que não serão mais utilizados, reparem que isso não será possível devido a uma série de restrições associadas a tabela de sistema MSDB.

Essa seria a única maneira viável de exclusão, visto que, via interface de configuração não é apresentado em nenhum momento esse tipo de opção.

Solução

No SQL Server 2012 a Microsoft adicionou a Stored Procedure “msdb.dbo.sp_syscollector_cleanup_collector” para realizar essa remoção.

Script:

USE msdb;
GO
— Disable constraints
— this is done to make sure that constraint logic does not interfere with cleanup process
ALTER TABLE dbo.syscollector_collection_sets_internal
NOCHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal
NOCHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
— Delete data collector jobs
DECLARE @job_id uniqueidentifier
DECLARE datacollector_jobs_cursor CURSOR LOCAL
FOR
SELECT collection_job_id AS job_id FROM syscollector_collection_sets
WHERE collection_job_id IS NOT NULL
UNION
SELECT upload_job_id AS job_id FROM syscollector_collection_sets
WHERE upload_job_id IS NOT NULL
OPEN datacollector_jobs_cursor
FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id
WHILE (@@fetch_status = 0)
BEGIN
IF EXISTS ( SELECT COUNT(job_id) FROM sysjobs WHERE job_id = @job_id )
BEGIN
DECLARE @job_name sysname
SELECT @job_name = name from sysjobs WHERE job_id = @job_id
PRINT ‘Removing job ‘+ @job_name
EXEC dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=0
END
FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id
END
CLOSE datacollector_jobs_cursor
DEALLOCATE datacollector_jobs_cursor
— Enable Constraints back
ALTER TABLE dbo.syscollector_collection_sets_internal
CHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal
CHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
— Disable trigger on syscollector_collection_sets_internal
— this is done to make sure that trigger logic does not interfere with cleanup process
EXEC(‘DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger
ON syscollector_collection_sets_internal’)
— Set collection sets as not running state
UPDATE syscollector_collection_sets_internal
SET is_running = 0
— Update collect and upload jobs as null
UPDATE syscollector_collection_sets_internal
SET collection_job_id = NULL, upload_job_id = NULL
— Enable back trigger on syscollector_collection_sets_internal
EXEC(‘ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger
ON syscollector_collection_sets_internal’)
— re-set collector config store
UPDATE syscollector_config_store_internal
SET parameter_value = 0
WHERE parameter_name IN (‘CollectorEnabled’)
UPDATE syscollector_config_store_internal
SET parameter_value = NULL
WHERE parameter_name IN ( ‘MDWDatabase’, ‘MDWInstance’ )
— Delete collection set logs
DELETE FROM syscollector_execution_log_internal

 
Essa SP pode também ser utilizada no SQL Server 2008 e 2008 R2, mas não tão eficiente quanto no 2012.

A base de dados do MDW deve ser excluída manualmente, assim como, o job do “mdw_purge_data_[MDW]” . Os Schedules existentes também podem ser excluídos, mas não recomendo, pois apesar de parecerem que foram criados junto do MDW, eles são adicionados por padrão na instalação do SQL Server e são necessários para o futuro funcionamento do MDW, caso queiram habilitar novamente.

No SQL Server 2012 também devem ser excluído manualmente os jobs do “sysutility”.

Conclusão

Podemos ver que o Management Data Warehouse não é fácil de remover em nenhuma das versões abordadas. Apesar de usarmos um script como parte da solução, existem questões que podem ficar para traz, como logins e usuários.

Link TechNet Wiki: http://social.technet.microsoft.com/wiki/pt-br/contents/articles/26044.removendo-o-sql-server-management-data-warehouse-data-collector.aspx

ATUALIZAÇÃO

Este artigo foi escolhido pela equipe do TechNet Wiki Brasil como um dos melhores do mês de agosto/2014 e se tornou destaque.

Segue link para publicação:

http://blogs.technet.com/b/wikininjasbr/archive/2014/09/30/community-win-artigos-em-destaque-e-pr-234-mio-technet-wiki-day-de-agosto-2014.aspx

Até a próxima!

SQL Server