Acho que a resposta para muitas de suas perguntas é que depende. Quais problemas de desempenho você está tendo? Parece incomum que um banco de dados tenha problemas de desempenho apenas crescendo para 250 GB de tamanho.

Talvez suas consultas estejam realizando varreduras de tabela em toda a tabela de fatos, mesmo quando apenas uma pequena parte (por exemplo, o último ano) do intervalo de datas é necessária? Se houver uma consulta específica que seja mais importante para otimizar, considere postar seu esquema, consulta e um plano de execução real em outra pergunta para ver se ela pode ser otimizada.

Você prefere uma das soluções sobre a outra?

Eu geralmente prefiro o banco de dados de histórico e acho que Guy descreve boas razões para isso em sua resposta .

A principal desvantagem que vejo para um banco de dados de histórico (em oposição a um esquema) é que você não pode mais usar chaves estrangeiras para sua tabela de arquivo. Isso pode ser bom para você, mas é algo para estar ciente.

A desvantagem que você listou para essa abordagem não é precisa; você poderá consultar facilmente entre bancos de dados no mesmo servidor e o otimizador de consulta geralmente lida muito bem com consultas entre bancos de dados.

Existem possibilidades melhores?

Se você precisar consultar os dados de arquivo regularmente, considere particionar a tabela por data . No entanto, esta é uma grande mudança que pode vir com muitas implicações de desempenho, tanto positivas (por exemplo, eliminação de partição, carregamento de dados mais eficiente) quanto negativas (por exemplo, buscas singleton mais lentas, maior potencial para desvio de encadeamento em consultas paralelas). Portanto, eu não tomaria essa decisão de ânimo leve se for um banco de dados muito usado.

A tabela de arquivos recém-criada também precisaria de chaves primárias/estrangeiras? Ou eles deveriam ter apenas as colunas, mas sem chaves/restrições?

Eu recomendaria ter pelo menos a chave primária e os índices exclusivos para que você possa obter os benefícios de integridade de dados que eles oferecem. Por exemplo, isso impedirá que você insira acidentalmente um ano de dados na tabela de histórico duas vezes. E, como benefício colateral, pode melhorar o desempenho se você precisar consultar a tabela de histórico.

Algum outro pensamento?

Como você está usando a edição Enterprise e planeja atualizar para o SQL 2008+, considere a compactação de dados para esta tabela. A compactação certamente reduzirá o espaço em disco, mas dependendo do disco do servidor e dos recursos da CPU, ela também pode melhorar o desempenho da consulta para leituras, reduzindo a E/S do disco e melhorando a utilização da memória (mais dados cabem no cache de uma só vez).

Eu preferiria ter um esquema de histórico ou um segundo banco de dados histórico em um servidor vinculado a qualquer dia. Ele economiza custos de licença é mais fácil de gerenciar e consultar. Você também pode usar um esquema mais simples e descartar alguns dos índices, tornando o banco de dados menor

Mas como você tem a edição corporativa você tem a terceira opção que é particionar suas tabelas o que, quando colocado em prática facilita o arquivamento dos dados e a consulta dos dados antigos é transparente para seus usuários e você não precisará fazer alterações no aplicativo .

Na minha experiência, um segundo banco de dados seria a escolha preferida por dois motivos.

1. Você pode restaurar os dados de um backup histórico e, em seguida, descartar as tabelas e índices desnecessários.
2. Você pode mover isso para um servidor diferente para fins de relatório, isso tem as vantagens de não usar os recursos do servidor primário

Você ainda precisaria excluir todos os dados históricos do banco de dados primário, mas isso poderia ser agendado.

Ignorando a licença por enquanto, pois não é onde passo meu tempo.

IMHO, banco de dados de arquivo é mais simples de implementar e manter. São entidades distintas e fracamente acopladas. A movimentação de dados e os controles de carga/recurso têm limites claros. Pode mover-se facilmente para uma instância ou servidor diferente para melhor gerenciamento de desempenho e custo não é um problema importante. Note que mais simples != mais barato ou menos esforço. Na verdade, ele tem um pouco mais de tarefas, mas todas são tarefas simples, com duas exceções importantes:

1. imposição de restrições - não há restrições entre bancos de dados no SQL Server, portanto, você precisa decidir se isso é um fator decisivo.
2. consultas cruzadas de banco de dados usam consultas distribuídas que ainda dependem do OLEDB, que está obsoleto. Isso significa que você pode encontrar problemas com novos tipos de dados e, se encontrar problemas de desempenho, é improvável que eles sejam corrigidos

O esquema de arquivamento ou apenas a tabela de arquivamento é um pouco mais complexo de implementar, mas muito mais fácil de usar. Todos os objetos no mesmo banco de dados significam que você não precisa replicar e manter controles de acesso. Sem consultas cruzadas de banco de dados, facilitando o ajuste de desempenho, monitoramento, solução de problemas etc.

O particionamento de tabela é uma ótima solução e oferece muitos dos benefícios de uma tabela/esquema de arquivamento, mas fornece transparência para usuários/consultas. Dito isto, é o mais complexo de implementar e requer cuidados contínuos que não são fáceis para um iniciante.

Algumas considerações importantes:

• As consultas retornam dados históricos/frios regularmente ou os dados frios são acessados ​​com pouca frequência?
• Os dados históricos são imutáveis ​​ou são atualizados/excluídos regularmente?
• 310m de linhas é "moderado" (assumindo tudo em 1 tabela) dependendo do tamanho da linha. Você tem dados de tamanho de linha? Quantos GB é essa linha de 310m?
• Qual é a taxa de crescimento dessa tabela?
• Você pode modificar o código do aplicativo e suas consultas SQL?

Essas são considerações importantes, pois podem ter um impacto significativo na solução escolhida ou podem até não permitir determinadas soluções. Por exemplo, se seus dados históricos são modificados/atualizados regularmente (mais de uma vez por semana), usar um banco de dados separado significa que você precisa usar o DTC para essas consultas ou gerenciar manualmente a segurança da transação (não trivial para garantir sempre a correta). O custo é significativamente maior do que os dados históricos imutáveis.

Além disso, se você está pensando em atualizar, considere 2016 e o ​​novo recurso Stretch Database: https://msdn.microsoft.com/en-us/library/dn935011.aspx

Eu preferiria dividir o banco de dados em um banco de dados lógico separado pelos seguintes motivos:

1. Requisitos de recursos

Ao dividir isso em um banco de dados separado, ele pode ser armazenado em uma unidade diferente e monitorado em uma taxa diferente dos dados de produção principais.

2. Desempenho

Ao dividir os dados em um banco de dados separado, o banco de dados principal de Produção é reduzido em tamanho, ajudando no desempenho geral.

3. Backups mais simples

O backup de dados arquivados pode não ser considerado tão essencial quanto os registros 'vivos/atuais' no banco de dados SQL principal. Isso pode significar que os dados arquivados podem ser copiados com menos frequência. Também devido à natureza sequencial de como os dados arquivados são registrados, pode ser possível fazer backup de seções do banco de dados arquivado uma vez e nunca mais. Por exemplo, uma vez que os dados do arquivo sejam gravados no banco de dados do arquivo Change para 2014, nunca haverá nenhuma alteração nesses dados novamente.

Observação: acho que a resposta para muitas de suas perguntas depende de suas circunstâncias, natureza dos dados e problemas de desempenho que você estava tendo.