Piores Práticas – Utilizar a opção Auto Close em um banco de dados SQL Server

Boa Noite Pessoal,

No meu último post eu fiz uma lista de 5 funcionalidades que poderiam ser retiradas do SQL Server. Acabei detalhando um pouco a opção AUTO CLOSE, mas achei interessante escrever um pouco sobre o uso dessa opção em banco de dados. Acho importante esclarescer como ela funciona, cenários de utilização e o porquê da sua não recomendação. Vejamos a explicação sobre essa opção no Books OnLine do SQL Server 2000 e do SQL Server 2008

SQL Server 2000 – Auto Close

When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to ON for all databases when using Microsoft® SQL Server™ 2000 Desktop Engine, and OFF for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When set to OFF, the database remains open even if no users are currently using the database.

Quando marcado como "ON", o banco de dados é fechado de forma segura quando o último usuário conectado na base e todos os processos que à acessam são finalizados, liberando portanto qualquer recurso relacionado. Por padrão, essa opção é marcada como "ON" para todos os bancos de dados que utilizam a edição MSDE e "OFF" para todas as outras edições independente do sistema operacional. O banco de dados é reaberto automaticamente quando um usuário tenta utilizar o banco de dados novamente. Se o banco de dados for fechado de forma segura, ele não será reaberto até que um usuário tente utilizar novamente o banco de dados ou o SQL Server seja reiniciado. Quando marcado como "OFF", o banco de dados permanecerá aberto mesmo que nenhum usuário esteja utilizando o banco de dados.

The AUTO_CLOSE option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

A opção AUTO CLOSE é útil para bases Desktop, pois, permite que os arquivos de banco de dados sejam gerenciados como arquivos normais. Eles podem ser movidos, copiados para fazer backups, ou inclusive enviados por e-mail para outros usuários. A opção AUTO CLOSE não deve ser utilizada por bancos de dados acessados por uma aplicação que repetidamente conecta-se e desconecta-se ao SQL Server. O Overhead de fechar e abrir o banco de dados entre cada conexão irá impactar o desempenho.

SQL Server 2008 – Auto Close

Há poucas modificações na escrita do texto, mas o entendimento é o mesmo. O destaque vem para a complementação que fala sobre o comportamento assíncrono da opção AUTO CLOSE a partir do 2005 e presente no 2008.

In earlier versions of SQL Server, AUTO_CLOSE is a synchronous process that can degrade performance when the database is accessed by an application that repeatedly makes and breaks connections to the Database Engine. Starting in SQL Server 2005, the AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database no longer reduces performance.

Em versões anteriores do SQL Server, a opção AUTO CLOSE era um processo síncrono que degradava o desempenho quando um banco de dados é acessado por uma aplicação que repetidamente conecta-se e desconecta-se ao banco de dados. A partir do SQL Server 2005, o processo de abrir e fechar o banco de dados é assíncrono. Abrir e fechar conexões repetidas vezes não irá mais degradar o desempenho.

Uma outra explicação

Todas as versões e edições do SQL Server possuem um serviço (sqlservr.exe) que representa o sistema gerenciador de banco de dados (Database Engine). Quando esse serviço é iniciado, ele lê na registry a localização do banco de dados MASTER e a partir daí encontra seus arquivos (master.mdf e mastlog.ldf) e os bloqueia de forma exclusiva, ou seja, nenhum outro processo poderá acessar esses arquivos que não o sqlservr.exe. Após bloquear esses arquivos, o SQL Server lê os dados na sys.master_files (ou sysaltfiles para o SQL Server 7 e 2000) e localiza todos os arquivos MDF, NDF e LDF de todas as bases de dados. Ele acessa esses arquivos de forma exclusiva para subir cada banco de dados. Se a opção AUTO CLOSE estiver marcada como OFF, esse bloqueio imposto pelo SQL Server ficará vigente até que o serviço seja parado. Se a opção AUTO CLOSE estiver marcada como ON, o SQL Server não irá acessar os arquivos da base na inicialização. Apenas quando um usuário precisar acessar o banco, o SQL Server tentará bloquear os arquivos de forma exclusiva durante o tempo que algo estiver sendo realizado no banco de dados. No momento em que não houver mais nenhuma atividade no banco, o SQL Server irá retirar os bloqueios exclusivos liberando os arquivos para qualquer outro processo.

Para não ficar na teoria e mostrar todo esse processo na prática vou utilizar o Process Monitor (disponível gratuitamente no site da Microsoft) para acompanhar o processo sqlservr.exe e suas atividades. A demonstração será feita utilizando-se uma base chamada Pedidos (inicialmente com a propriedade Auto Close desmarcada). O primeiro passo é parar o serviço do SQL Server, ligar o Process Monitor e em seguida reiniciar o serviço. A figura abaixo utilizou diversos filtros no Process Monitor para filtrar apenas os eventos relevantes.

As chaves de registry possuem os parâmetros –d e –l que representam a localização dos arquivos master.mdf e mastlog.ldf que inicializam o banco MASTER. Posteriormente esses arquivos são lidos e consultados e em seguida alguns dos demais bancos de dados como o TempDB, ReportServer, MSDB e a base "Pedidos" existente na minha instância. Algumas outras bases com o ResourceDB e Model também são lidas, mas para contemplar essas bases na visualização seria necessário retirar alguns outros filtros (aqueles que usarem o Process Monitor diretamente podem visualizar essas entradas).

O SQL Server bloqueou todos os arquivos listados de forma exclusiva e a tentativa de qualquer outro processo (e até mesmo o próprio Windows) em acessar esses arquivos não é bem sucedida. A figura abaixo mostra uma tentativa de copiar o arquivo Pedidos.mdf.

Agora que o SQL Server foi reiniciado, o script a seguir muda a propriedade AUTO CLOSE do banco de dados Pedidos, aumenta-o para 1GB e em seguida o serviço é parado.

ALTER DATABASE Pedidos SET AUTO_CLOSE ON
ALTER DATABASE Pedidos MODIFY FILE (NAME=‘Pedidos’, SIZE=1024MB)
SHUTDOWN WITH NOWAIT

Os dados coletados pelo Process Monitor antes do serviço do SQL Server reinicializar mostram o efeito da opção AUTO CLOSE no banco de dados Pedidos conforme a figura abaixo:

Dessa vez, o arquivo .MDF e .LDF sequer foram acessados pelo SQL Server durante sua inicialização. Na lista anterior, o arquivo ReportServer.MDF era acessado antes do arquivo Pedidos.MDF. Após o acesso do arquivo Pedidos.MDF, o arquivo ReportServerTempDB.mdf era o próximo da lista. Com a opção AUTO CLOSE não existe mais o acesso aos arquivos da base Pedidos. Isso significa que o SQL Server não irá impor bloqueios exclusivos a esses arquivos no ato de sua inicialização, mas somente quando o arquivo for solicitado. Como a base ainda não foi acessada, os arquivos estão livres para outros processos manipulá-los e uma simples cópia pelo Windows Explorer é permitida.

A propriedade AUTO CLOSE viabiliza algumas estratégias de backup alternativas. Quando essa opção está ativa, é possível por exemplo copiar os arquivos .MDF e .LDF para outros locais como se fosse arquivos comuns o que não seria possível caso essa opção estivesse marcada como OFF. Isso pode dar uma certa sensação de utilidade, mas da mesma forma que representa uma possibilidade bastante arriscada. O que aconteceria se alguém estivesse acessando o banco de dados e outra pessoa tentasse copiar o arquivo no mesmo momento ?

O script a seguir cria e popula uma tabela com 100 mil registros (pouco mais de 1 minuto até o seu término). Enquanto esse script é executado, uma cópia do arquivo é realizada simultaneamente pelo File System.

— Muda o contexto de banco de dados
USE Pedidos

— Cria uma tabela
CREATE TABLE T (Seq INT IDENTITY(1,1),
    ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
    Data DATETIME DEFAULT GETDATE(),
    TextoLivre CHAR(8000) DEFAULT REPLICATE(‘A’,8000))

— Insere 100.000 registros
INSERT INTO T DEFAULT VALUES
GO 100000

Não foi possível realizar a cópia, pois, no momento do acesso, o SQL Server bloqueou o arquivo exclusivamente. Isso pode ser confirmado pelo Process Monitor. É visível o acesso ao arquivo Pedidos.mdf.

O bloqueio exclusivo protegeu o arquivo contra uma cópia não autorizada (afinal ao final da cópia o arquivo já estaria diferente), mas o que aconteceria se fosse ao contrário, ou seja, se alguém estivesse copiando o arquivo e o acesso ao banco fosse necessário ? Para verificar essa hipótese é necessário desconectar-se do banco para que o SQL Server desbloquei os arquivos. O script abaixo, desconecta-se do banco, aguarda 10 segundos e tenta um novo acesso. Nesse tempo, uma cópia do arquivo é realizada pelo Windows Explorer. Uma mensagem de erro é retornada pelo SQL Server.

Msg 5120, Level 16, State 101, Line 8
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Pedidos.mdf".
Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
Msg 945, Level 14, State 2, Line 8
Database ‘Pedidos’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

A mensagem de erro é bem clara. O SQL Server não conseguiu obter um bloqueio exclusivo sobre os arquivos da base pedidos, pois, os mesmos estavam sendo utilizados por outro processo (no caso o Windows Explorer). Mesmo após a cópia dos arquivos ter terminado e os arquivos estarem disponíveis, o SQL Server não consegue mais acessar a base de dados. A consulta abaixo mostra que a mesma foi marcada como SUSPECT o que indica dados corrompidos.

SELECT ‘Pedidos’ As Base, DATABASEPROPERTYEX(‘Pedidos’,‘Status’) As Status

Base Status
Pedidos SUSPECT

A solução é colocar o banco em modo de emergência e em seguida devolvê-lo para o status de ONLINE além de preventivamente checar sua integridade.

— Muda o contexto de banco de dados
USE MASTER

— Coloca a base em modo de emergência
ALTER DATABASE Pedidos SET EMERGENCY

— Tenta colocar a base em modo ONLINE
ALTER DATABASE Pedidos SET ONLINE

— Verifica se houve corrupção de dados
DBCC CHECKDB(‘Pedidos’) WITH NO_INFOMSGS

Por sorte, o CHECKDB não retornou nenhum erro e a base voltou a ficar operacional.

Resumidamente, a opção AUTO CLOSE abre a possibilidade de se trabalhar arquivos fora do SQL Server. O principal argumento para utilizar essa opção é flexibilidade para "fazer os backups" sem a indisponibilidade do serviço. Ao meu ver esse não é um argumento válido uma vez que o comando BACKUP DATABASE é o utilizado para fazer o backup e isso é feito de forma OnLine. Pode contra-argumentar que a cópia do arquivo de forma direta torna o processo mais rápido, pois, é necessária uma única operação enquanto que o BACKUP e o RESTORE incorrem em duas operações (mesmo a operação de attach é mais rápida que um RESTORE). Eu concordo, mas se essa é a justificativa, que se desatache então a base e se faça a cópia dos arquivos. Copiar os arquivos com a base atachada é arriscado, pois, o acesso a essa base durante a cópia pode eventualmente corrompê-la (conforme demonstrado). Ao meu ver, utilizar essa opção é incorrer em um risco desnecessário.

[ ]s,

Gustavo

2 Respostas para “Piores Práticas – Utilizar a opção Auto Close em um banco de dados SQL Server

  1. Concordo. Acho que ficou bem evidente os problemas causados por essa característica.O que é mais impressionante é que ela vem ligada por padrão!Eu conheço uma pessoa que sem querer, acabou substituindo o arquivo MDF de uma base de dados. Foi uma complicação terrível, que poderia ter sido envitada neh.Pelo que me lembro, o SQL 2000 nem tinha essa opção, ele só liberava o acesso se vc parasse o processo, o que é correto na minha opnião. Na época eu achava isso muito ruim, pois, "dificultava" o backup, mas depois de descobrir o comando BACKUP DATABASE foi só alegria.

  2. Oi Cléber,Pois é. Temos que tomar cuidado com essa característica. Já vi gente perder banco por conta disso.O 2000 já contava com essa característica só que era ainda pior, pois, o arquivo era aberto e fechado constantemente. No 2005 ele ficou assíncrono.[ ]s,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s