Autores Rodrigo Crespi e Tiago Crespi
Hoje aqui no blog da CrespiDB iremos falar um pouco sobre os arquivos de um banco de dados SQL Server e seu comportamento. Nos últimos meses, atendemos clientes com necessidades diferentes sobre estratégia de arquivos.
Os bancos de dados no SQL Server são compostos de dois arquivos principais o “.mdf” (master data file) e o “.ldf” (log data file) e pode ou não haver um “.ndf” (secondary data file) dependendo da estrutura escolhida para o banco de dados.
Para entender a estrutura de cada um destes arquivos e como eles funcionam acesse o post do DBA Rodrigo Crespi que está em: https://sqlcrespi.com/2011/02/06/quais-arquivos-compoem-um-banco-de-dados-no-sql-server/
Arquivo de Log (.LDF)
Vamos iniciar pelos Arquivos de Log (.ldf), estes arquivos não são formatados e por isso sua gravação é sequencial e cíclica, ou seja, quando um backup transacional é feito o espaço desse arquivo é marcado para reciclar. Sendo que, caso haja uma transação aberta, o backup transacional não vai liberar o espaço referente a esta transação no log.
Alerta! Se o arquivo transacional necessitar de mais espaço, vale a pena avaliar se não há alguma transação em sleeping ou que não foi dado o commit ou rollback. Este tipo de falha no código, acarreta em retenção do log até que a transação seja finalizada.
Arquivo de Dados (.MDF)
É onde se encontram os metadados do banco. Quando adotamos uma estratégia de criar novos arquivos, por padrão estes arquivos secundários são nomeados com a extensão .NDF. Lembrando que, as extensões citadas nesse arquivo são amplamente adotadas como padrão, mas se escolherem outra extensão, isso não causará problemas ao banco.
A criação de novos arquivos deve vir acompanhada sempre de uma estratégia, visto que mais arquivos não necessariamente significa mais performance. Abaixo seguem algumas razões para haver mais arquivos secundários.
Particionamento de Tabelas
O particionamento de tabelas é um estratégia que permite dividir um objeto em diversas partes. Há alguns motivos para adotar o particionamento, tais como: movimento de dados para arquivamento ou expurgo, separar dados geograficamente ou até mesmo facilitar o rebuild de índices. Veja mais em: https://sqlcrespi.com/2018/09/10/video-particionamento-de-tabelas-no-sql-server/
Dividir o I/O em vários Discos
Com vários arquivos, o SQL Server gravará paralelamente desde que estejam no mesmo filegroup e tenham o mesmo tamanho, o mesmo AutoGrow, etc. Estes arquivos podem ser gravados em discos diferentes dividindo o I/O, mas esta estratégia só funcionará para performance se houver no mínimo controladoras diferentes.
Separação em filegroups
É perfeitamente possível criar vários filegroups. Por exemplo: um para cada setor da empresa e dentro deles adicionar as respectivas tabelas. Isso ajuda a organizar o banco e a controlar o I/O de cada filegroup. Ou seja, se o RH esta competindo por I/O com Vendas, podemos direcionar o filegroup de cada setor para discos diferentes.
A separação das tabelas em filegroups oferece outras vantagens, tais como: backup e restore de um filegroup para isolar o tempo e IO de backup ou até mesmo, para restaurar um setor antes dos demais em casos de desastre.
Conclusão
As estratégias para a criação de vários arquivos de dados em um banco SQL Server são diversas. Tudo irá depender de sua necessidade, seja ela performance, contenção de disco, segurança, etc. Não há certo ou errado, há o que é melhor para o seu negócio.
Esperamos que este post seja útil para você. Ficamos à disposição!
