Atualmente, nosso banco de dados possui apenas um FileGroup, PRIMARY, que contém aproximadamente 8 GB de dados (linhas da tabela, índices, catálogo de texto completo).
Quando é um bom momento para dividir isso em arquivos de dados secundários? Quais são alguns critérios dos quais devo estar ciente?
Há duas partes nesta questão: quando adicionar um novo FILEGROUP e quando adicionar um novo FILE em um grupo de arquivos. Primeiro vamos falar de teoria:
Mark está certo sobre o principal motivo ser o desempenho.
O motivo secundário é a recuperação de desastres. Com o SQL Server 2005 e mais recente, você pode fazer restaurações de grupos de arquivos. Quando ocorre um desastre, você pode restaurar apenas seu grupo de arquivos principal primeiro e colocar o banco de dados parcialmente online para consultas. Os usuários podem executar consultas enquanto você restaura outros grupos de arquivos. Isso é útil para bancos de dados com uma grande quantidade de dados históricos que podem não ser necessários imediatamente ou data warehouses que precisam carregar dados em tabelas atuais sem precisar de dados históricos para acesso.
Outro motivo é o perfil de leitura/gravação de grupos de dados. Se você tiver alguns dados que são gravados constantemente e outros dados que recebem atividade de leitura pesada, você pode criar diferentes tipos de armazenamento para acomodar essas necessidades. Você pode colocar o material de gravação pesada no raid 10 e deixar o material com viés de leitura no raid 5, mais barato.
Agora, vamos falar sobre arquivos versus grupos de arquivos. Ao colocar objetos no SQL Server, você deve colocá-los no nível do grupo de arquivos. Você pode colocar uma tabela ou um índice em um grupo de arquivos, mas não pode escolher um arquivo específico. Portanto, tudo o que discutimos até agora foi sobre quando adicionar um grupo de arquivos - mas quando você adiciona um arquivo?
Se você estiver projetando armazenamento e tiver 80 discos rígidos, há algumas maneiras de dividi-los:
Diferentes subsistemas de armazenamento possuem diferentes perfis de desempenho. Trabalhei com algumas SANs que tiveram melhor desempenho com matrizes de 12 a 16 unidades e qualquer coisa maior do que isso não teve uma melhoria de desempenho. Outro exemplo são SANs com caminhos múltiplos: se você tiver vários HBAs conectando seu servidor ao seu armazenamento e se seu software de caminhos múltiplos não for realmente ativo/ativo, talvez seja necessário um array por caminho para distribuir a carga. Quatro caminhos, quatro pools de unidades obterão melhor desempenho nesses tipos de unidades.
Nesses casos, você acaba com quatro matrizes diferentes, quatro unidades diferentes no Windows (a menos que use pontos de montagem e, mesmo assim, são pastas diferentes) e precisará de quatro arquivos separados no SQL Server. Esses arquivos separados podem estar no mesmo grupo de arquivos.
O principal motivo é o desempenho. Quando você ficar sem capacidade de IOPS na unidade de disco do grupo de arquivos primário, precisará expandir para um segundo grupo de arquivos para dividir o IOPS em vários discos/LUNs, dependendo da configuração de armazenamento.
EDIT: Brad Wilson fez um bom comentário sobre SSDs. Se você estiver usando um sistema de armazenamento composto SSD/SATA/FC, talvez queira ter diferentes grupos de arquivos em diferentes tipos de armazenamento. Você pode então colocar suas tabelas de requisitos extremos de IOPS no grupo de arquivos SSD, enquanto as tabelas de histórico/estatísticas podem ser armazenadas em grupos de arquivos SATA baratos.
Eu também gostaria de apontar que há um aspecto de capacidade de recuperação / disponibilidade de dados para esta questão também. Usando vários grupos de arquivos e não colocando nenhum objeto definido pelo usuário no grupo de arquivos principal, você tem mais flexibilidade para ativar restaurações online. isso permite a restauração fragmentada no nível do grupo de arquivos.
A restauração online está disponível nas edições Enterprise e Developer do sql server posteriores a 2005
Outro pensamento que vem à mente é separar os dados de referência estáticos somente leitura dos dados transacionais. Para bancos de dados maiores, isso pode reduzir a quantidade de tempo e/ou espaço necessário para executar um backup.