Júlio Battisti Livros e Cursosa sua sala de aula na internet

No SQL Server 2005 (na verdade a partir do SQL Server 7.0), temos o conceito de Filegroup. Este conceito muitas vezes não é utilizado na prática por falta de entendimento do que o ele significa, como pode ser utilizado e quais as vantagens em utilizar Filegroups.

A utilização de um Filegroup permite que os arquivos de um Banco de Dados sejam agrupados para facilitar o gerenciamento, bem como a distribuição ao longo de volumes redundantes e de melhor desempenho (RAID-0, RAID-1, etc.). A utilização de Filegroup pode ser um auxiliar valioso na melhoria do desempenho de um Banco de Dados, ao permitir que o banco de dados (através dos seus diversos arquivos) seja criado em múltiplos discos, múltiplas controladoras ou em sistemas do tipo RAID, quer seja RAID por software ou por hardware, conforme descrito no item anterior. Também podemos fazer com que uma tabela ou índice seja criada em um disco específico, simplesmente associando a tabela ou índice com um filegroup. Isto nos abre maiores possibilidades. Por exemplo, podemos deslocar uma tabela que é muito consultada para um sistema de discos de melhor desempenho. Um arquivo pode estar associado com um único filegroup. O uso de Filegroups também pode ser utilizado para facilitar o gerenciamento das rotinas de Backup/Restore, pois ao fazer o backup de um Filegroup, todos os arquivos contidos no Filegroup – mesmo que estejam em discos e volumes separados, serão incluídos no Backup.

Os arquivos de log não fazem parte de nenhum filegroup, sendo que estes são gerenciados separadamente pelo Banco de Dados.

Podemos ter três tipos de filegroups. A seguir descrevemos os três tipos existentes:

• Primary filegroup: É o filegroup padrão. Contém todos os arquivos de dados primários (.mdf) e qualquer arquivo que não tenha sido, explicitamente, atribuído a outro filegroup. Todo Banco de Dados possui as chamadas tabelas de sistema, as quais possuem informações sobre os usuários autorizados a acessar os objetos do Banco de Dados e o nível de permissão de acesso de cada um. As tabelas de sistema são criadas automaticamente pelo SQL Server, quando criamos o banco de dados. Estas tabelas ficam gravadas no arquivo de dados primário (.mdf) o qual, por padrão, é associado com o Primary filegroup (o qual é, por padrão, o Default filegroup).
• User-defined filegroups: É qualquer filegroup criado pelo usuário durante o processo de criação ou alteração de uma tabela. Conforme descrito anteriormente, podemos criar tabelas ou índices associados com um filegroup criado pelo usuário.
• Default filegroup: Toda tabela ou índice ao ser criado será associado com o filegroup padrão, a menos que seja explicitamente associado com um filegroup definido pelo usuário. O filegroup padrão, por definição, é o Primary filegroup. Cada Banco de Dados possui o seu Default filegroup, com o qual é associado. Conforme veremos ainda neste capítulo, existem comandos T-SQL que permitem alterar o filegroup associado com um determinado Banco de Dados.

A criação de filegroups adicionais não é obrigatória e, para muitas aplicações, todos os dados podem ser colocados no Primary filegroup. Porém em situações mais complexas, onde o desempenho precisa ser otimizado o máximo possível, a utilização de filegroups pode ser um auxiliar valioso para distribuir os dados entre diferentes discos, controladores ou sistemas de RAID. Por exemplo, a colocação de uma tabela que está sendo intensamente acessada, em um RAID de Hardware, de vários discos, pode melhorar bastante o desempenho do sistema.

Todos nós sabemos que uma imagem vale por mil palavras. Então vamos apresentar alguns exemplos de utilização de filegroups.

Considere o exemplo apresentado na Figura 3.24. Neste exemplo, temos apenas o filegroup padrão (Primary filegroup) e todos os arquivos do Banco de Dados em um único disco. Esta situação apresenta um desempenho bastante insatisfatório para grandes bancos de dados, os quais serão utilizados por um grande número de usuários. Um único disco, uma única controladora = desempenho nada satisfatório se o volume de dados e o número de usuários, for grande.

Figura 3.24 Uma situação com possíveis problemas no desempenho.

Agora vamos fazer algumas modificações. Iremos adicionar mais três discos. Vamos criar mais dois filegroups, além do Primary filegroup: Filegroup1 e Filegroup2. Vamos deslocar os arquivos secundários para estes filegroups em outros discos. Também iremos deslocar o arquivo de log para um quarto disco, conforme indicado na Figura 3.25. Neste caso, estamos melhorando consideravelmente o desempenho do nosso sistema.

Figura 3.25 Melhorando o desempenho do nosso Banco de Dados.

Poderíamos otimizar ainda mais o desempenho do nosso sistema de discos. Por exemplo, ao invés de termos simplesmente Disco 2 e Disco 3, estes poderiam ser sistemas de RAID-5 com controladoras diferentes, o que otimizaria ainda mais o desempenho.

A seguir, apresento alguns detalhes importantes, os quais você não pode esquecer quando for definir a sua estratégia de filegroups:

• Um arquivo somente pode fazer parte de um único filegroup.
• Informações de dados e do log de transações não podem fazer parte do mesmo arquivo. Por isso, os arquivos de log sempre são gerenciados separadamente dos arquivos de dados.
• Os arquivos de log não podem fazer parte de um filegroup e são gerenciados separadamente pelo SQL Server 2005.

Até neste momento tratamos apenas da teoria sobre a estrutura de um Banco de Dados no SQL Server 2005 e de filegroups. Aprenderemos, no próximo tópico, a criar Bancos de Dados, a criar arquivos secundários (Secondary data files) e a criar filegroups.

Conforme descrito anteriormente, aprenderemos a realizar estas operações utilizando o SQL Server Management Studio. Também aprenderemos a executar estas tarefas, usando comandos T-SQL, na janela para execução de comandos, dentro do próprio SQL Server Management Studio (a qual substitui o Query Analyzer), o qual era um aplicativo separado, no SQL Server 2000.

Fundamentos em Criar Bancos de Dados no SQL Server 2005 com o SQL Server Management Studio:

Pré-Requisitos:

• Noções do SQL Server 2005.
• Utilização dos consoles baseados no MMC.
• Conhecer os opções que fazem parte de uma instância do SQL Server 2005.

Metodologia:

• Apresentação dos passos utilizados para a criação de Bancos de Dados.

Técnica:

• Criação de Bancos de Dados com o SQL Server Management Studio.

Antes de criarmos um novo Banco de Dados quero fazer um pequeno parênteses sobre segurança. Você deve ter notado que sempre que abrimos o SQL Server Management Studio, tivemos acesso a todos os elementos do SQL Server 2005. Nunca foi solicitado que digitássemos um nome de usuário e senha. Isto acontece porque ao instalarmos o SQL Server 2005 no Capítulo 2, optamos pela segurança integrada com o Windows. Como estou fazendo o logon com a conta Administrator, esta possui “poderes totais” sobre todos os elementos do servidor SQL Server, quando estamos utilizando a segurança integrada com o Windows. Falaremos detalhadamente sobre segurança no Capítulo 6.

Agora chega de conversa e vamos à criação de um Banco de Dados de exemplo.

A seguir, temos os detalhes sobre o Banco de Dados que iremos criar:

Nome no SQL Server: Exemplo-1
Arquivo primário: C:\livrosql\exemplo1\exemplo1-prim.mdf
Arquivo secundário: C:\livrosql\exemplo1\exemplo1-sec1.ndf
Arquivo de log: C:\livrosql\exemplo1\exemplo1-log.ldf

Exemplo prático:
Criando um novo Banco de Dados na instância SERVIDOR\SRVINST01, usando o SQL Server Management Studio:

1.  Abra o SQL Server Management Studio e conecte-se com o servidor no qual você deseja criar um novo banco de dados. Neste exemplo, estou fazendo a conexão com o servidor SERVIDOR\SQL2005
2.  No painel da esquerda, localize a janela Object Explorer. Se esta janela não estiver aberta, pressione a tecla de função F8 para exibi-la. Caso a janela não esteja na posição desejada, você pode arrastá-la para qualquer posição, dentro da janela do SQL Server Management Studio. Clique no sinal de + ao lado da instância na qual será criado o banco de dados.
3.  Nas opções que surgem abaixo da instância, dê um clique em Databases para selecioná-la.
4.  Clique com o botão direito do mouse em Databases. No menu que surge dê um clique na opção New Database... Será exibida a janela New Database, conforme indicado na Figura 3.26, com a guia General, selecionada por padrão.

Figura 3.26 A janela New Database.

5.  Na guia General, iremos definir o nome do Banco de Dados, no nosso exemplo digitaremos: Exemplo1. Nesta guia também podemos definir qual a Collation que será definida para este banco de dados. Podemos optar por utilizar a Collation definida para o servidor quando da instalação do mesmo ou um outro tipo. Lembre-se que Collation define propriedades importantes da maneira como os dados serão armazenados e recuperados. A possibilidade de ter diferentes opções para cada Banco de Dados é uma novidade do SQL Server 2000 a qual, evidentemente, faz parte do SQL Server 2005. Nesta guia você também define quem será o usuário dono do banco de dados. O usuário dono, por padrão, tem permissões completas sobre todos os objetos do banco de dados. Por padrão, é sugerido o usuário as, que é o usuário com permissões de Administrador no SQL Server 2005. Vamos aceitar a sugestão e deixar o usuário as como dono do banco de dados. Nesta guia aceite a opção padrão do servidor e digite Exemplo1 no campo Name.
6.  Na parte de baixo desta guia, poderemos definir quais os arquivos de dados que serão utilizados para o Banco de Dados Exemplo1. Lembre que neste caso teremos o arquivo primário C:\livrosql\exemplo1\exemplo1-prim.mdf e o secundário C:\livrosql\exemplo1\ exemplo1-sec1.ndf. Poderemos criar estes dois arquivos na guia Data Files.
7.  No campo File Name, digite o nome do arquivo primário exemplo1-prim sem a extensão. Você também pode definir o tamanho inicial para o arquivo. No nosso exemplo, digite 20 na coluna Initial Size do arquivo primeiro, para criarmos um arquivo primário com um tamanho inicial de 10 MB. Na coluna Autogrowth (Auto crescimento), você define de que maneira o arquivo primário irá crescer, a medida que os dados forem sendo adicionados. O padrão é crescer de 10 em 10 por cento, com tamanho ilimitado (evidentemente que o tamanho máximo será limitado pelo espaço disponível em disco). Se você quiser alterar a forma de crescimento, clique no botão com as reticências ... ao lado da opção selecionada. Será exibida a janela Change Autogrowht, indicada na Figura 3.27, na qual você pode definir diferentes formas de crescimento para o arquivo primário exemplo1-prim, do banco de dados Exemplo1. Você pode habilitar ou desabilitar o crescimento automático, pode fazer com que o crescimento seja em percentual ou em MB e pode permitir um tamanho máximo ou crescimento irrestrito. Selecione as opções desejadas, que para o nosso exemplo serão: Tamanho inicial: 10 MB,  Crescimento automático em incrementos de 1 MB e Poderá crescer até o máximo de 50 MB . Selecione estas opções e clique em OK, para fechar a janela Change Autogrowht.

Figura 3.27 Definindo as opções de crescimento do arquivo primário.

8.  Agora vamos definir a pasta onde será gravado o arquivo primário. Por padrão, o SQL Server 2005 sugeri o seguinte caminho: C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. Vamos alterar o caminho para: C:\livrosql\exemplo1. Você pode digitar este caminho diretamente, ou clicar no botão com as reticências, para selecionar o caminho com o mouse e deixar que o SQL Server 2005 preencha esta coluna, com o caminho selecionado.
9.  Ainda na guia General, e seguindo as orientações passadas anteriormente, vamos configurar as opções do arquivo secundário C:\livrosql\exemplo1\exemplo1-sec1.ndf, com as seguintes características:

• Tamanho inicial: 10 MB.
• Crescimento automático em incrementos de 2 MB.
• Poderá crescer até o máximo de 100 MB.

10. Para adicionar um arquivo secundário, clique no botão Add, na parte de baixo da guia General. Uma nova linha será criada. Na coluna File name, digite exemplo1-sec1.ndf. Na coluna File Type selecione Data. Na coluna Filegroup selecione Primary. Na colunia Initial Size (MB), digite 10. Clique no botão de reticências, ao lado da opção de Auto crescimento, para abrir a janela Change Autogrowth e selecione um Crescimento automático em incrementos de 2 MB e defina que o arquivo Poderá crescer até o máximo de 100 MB. Clique em OK para fechar a janela Change Autogrowth.
11. Agora vamos definir a pasta onde será gravado o arquivo secundário. Por padrão, o SQL Server 2005 sugeri o seguinte caminho: C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. Vamos alterar o caminho para: C:\livrosql\exemplo1. Você pode digitar este caminho diretamente, ou clicar no botão com as reticências, para selecionar o caminho com o mouse e deixar que o SQL Server 2005 preencha esta coluna, com o caminho selecionado. Suas configurações devem estar conforme indicado na Figura 3.28

Figura 3.28 Arquivos primário e secundário, já configurados.

O passo final é a definição do arquivo de log C:\livrosql\exemplo1\exemplo1-log.ldf. Este arquivo já vem com definições padrão, na linha logo abaixo de onde foi definido o arquivo primário. Vamos criar um arquivo de log com as seguintes características:

• Tamanho inicial: 5 MB.
• Crescimento automático em incrementos de 10 %.
• Poderá crescer até o máximo de 40 MB.

12. Observe que na coluna File Type já vem selecionado Log e na coluna Filegroup vem selecionada a opção Not Applicabe. Na colunia Initial Size (MB), digite 5. Clique no botão de reticências, ao lado da opção de Auto crescimento, para abrir a janela Change Autogrowth e selecione um Crescimento automático em incrementos de 10% e defina que o arquivo Poderá crescer até o máximo de 40 MB. Clique em OK para fechar a janela Change Autogrowth.
13. Agora vamos definir a pasta onde será gravado o arquivo de log. Por padrão, o SQL Server 2005 sugeri o seguinte caminho: C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. Vamos alterar o caminho para: C:\livrosql\exemplo1. Você pode digitar este caminho diretamente, ou clicar no botão com as reticências, para selecionar o caminho com o mouse e deixar que o SQL Server 2005 preencha esta coluna, com o caminho selecionado. Suas configurações devem estar conforme indicado na Figura 3.29

Figura 3.29 Configurações para o banco de dados Exemplo1.

Poderíamos usar as demais guias – Options, Filegroups e Extended Properties para definir opções avançadas do banco de dados. Para este primeiro exemplo, estas guias não serão utilizadas.

A pasta C:\livrosql\exemplo1 já deve ter sido criada, se não será gerado um erro na hora de criar o banco de dados Exemplo1.

13. Dê um clique no botão OK, e após alguns segundos o Banco de Dados Exemplo1 terá sido criado.
14. Agora vamos nos certificar de que o banco de dados Exemplo1 foi, efetivamente, criado. Na janela Object Explorer clique no sinal de + ao lada da opção Databases, abaixo da instância na qual o banco de dados foi criado. Na lista de banco de dados já deverá ser exibido o banco de dados Exemplo1. Dê um clique neste banco de dados, para exibir as suas propriedades, no painel da direita, conforme indicado na Figura 3.30:

Figura 3.30 O banco de dados Exemplo1, recém criado.

Caso o Banco de Dados Exemplo1 ainda não esteja aparecendo na listagem, clique com o botão direito do mouse na opção Databases e no menu de opções que surge clique em Atualizar (Refresh).

14.   Após termos criado um Banco de Dados, podemos, facilmente, adicionar novos arquivos de dados ou de log. Para isso basta clicar com o botão direito do mouse no nome do Banco de Dados e no menu que surge selecionar Propriedades. Será exibida a janela de propriedades do Banco de Dados. Basta clicar na guia Files, para ter acesso as opções de alterações dos arquivos existentes e de adição de novos arquivos secundários.

Agora que já sabemos criar um Banco de Dados utilizando o Enterprise Manager, vamos propor um pequeno exercício para o amigo leitor fixar os conceitos apresentados.

Exercício proposto:
Acesse as propriedades do Banco de Dados Exemplo1 e crie mais um arquivo secundário e um arquivo de log, conforme definido a seguir. Para isso basta clicar com o botão direito do mouse no banco de dados Exemplo1 e, no menu de opções que é exibido, clicar em Propriedades. Na janela de propriedades que é exibida, clique na guia Files. Agora é só usar o botão Add, para adicionar novos arquivos e configurar as propriedades de cada novo arquivo, conforme definições da Tabela 3.1:

Tabela 3.1 Criando arquivos adicionais, no banco de dados Exemplo1.

Exercício:
Criar o Banco de Dados indicado na Tabela 3.2, utilizando os passos que você aprendeu no exemplo prático anterior. O banco de dados será chamado de Exemplo2 e será criado na instância SERVIDOR\SQL2005 (use a instância que você tem disponível, no seu computador). Os arquivos do Banco de Dados Exemplo2 serão gravados na pasta: C:\livrosql\Exemplo2. Lembre-se de que esta pasta já deve ter sido criada, antes que o banco de dados possa ser criado, se não será gerado um erro na hora da criação do banco de dados.

Tabela 3.2 O banco de dados Exemplo2.

Então mãos à obra. Em caso de dúvidas sobre a criação do banco de dados Exemplo2, é só entrar em contato, pelo e-mail: webmaster@juliobattisti.com.br

Observação importante sobre o crescimento automático de arquivos.

Vamos falar um pouco mais sobre o crescimento automático dos arquivos – primário, secundário(s) e de log – de um Banco de Dados. Por exemplo, vamos supor que estejamos criando um Banco de Dados com um as características indicadas na Tabela 3.3.

Tabela 3.3 Algumas questões sobre espaço em disco.

Vamos responder às seguintes questões:

1) Ao criarmos este Banco de Dados, quanto ele estará, inicialmente, ocupando de espaço em disco?
R: 19 MB. Este valor é a soma dos tamanhos iniciais do arquivo primário (5 MB), mais todos os arquivos secundários, que no caso temos apenas um (7 MB), mais todos os arquivos de log, que no nosso exemplo temos apenas um (7 MB).

Com isso podemos concluir que um arquivo (primário, secundário ou de log) de um Banco de Dados do SQL Server, é criado com o seu tamanho inicial. Este tamanho vai aumentando, conforme definido pelas configurações de auto crescimento, a medida que mais espaço vai sendo necessário no Banco de Dados.

2) Qual o tamanho máximo que pode atingir este Banco de Dados?
Esta parece e é fácil. Basta somar os tamanhos máximos que cada arquivo pode atingir. No nosso exemplo, teríamos: 10+15+25 = 50 MB. Porém, se a pergunta fosse um pouco diferente, como: Qual o tamanho máximo para dados deste Banco de Dados? Neste caso devemos desconsiderar os arquivos de log, nos quais ficam registradas apenas as operações realizadas sobre os dados.

A utilização do crescimento automático juntamente com a definição de tamanho máximo são mecanismos importantes para o administrador do Banco de Dados. O crescimento automático facilita a vida do DBA – Database Administrator, pois dispensa o administrador de ter que aumentar, manualmente, o tamanho dos arquivos do Banco de Dados, sempre que o tamanho máximo fosse atingido. A definição de um tamanho máximo também é recomendada, pois evita que devido a alguma operação com problemas, devido a algum erro de programação, todo o espaço do disco seja preenchido.