Meu aplicativo está centrado em "espaços de trabalho" independentes. Por muitas razões realmente boas (tudo, desde gerenciamento até segurança), sempre tivemos uma arquitetura de um banco de dados por espaço de trabalho. Cada banco de dados possui esquema idêntico, procedimentos armazenados, gatilhos, etc. Existe um “banco de dados de bancos de dados” que coordena tudo isso. Funciona bem.
O problema: escalabilidade. Recentemente, foi proposto que um cliente desejasse ter 100.000 espaços de trabalho. Obviamente, isso não é inicial para uma instância SQL. Além disso, cada área de trabalho pode ser bastante pequena, mas também pode haver uma distribuição de tamanho muito ampla - a maior área de trabalho pode ter 100 vezes o tamanho da mediana . O 1% superior dos espaços de trabalho poderia facilmente constituir mais de 90% das linhas em todos os espaços de trabalho.
Estou procurando opções para rearquitetar coisas para dar suporte a esse cenário, e aqui estão algumas coisas que considerei e os problemas que vejo em cada uma.
Mantenha a arquitetura de vários bancos de dados, mas espalhe-a por várias instâncias SQL. O problema é o custo (administrativo e de infraestrutura). Se mantivermos um limite de 1.000 bancos de dados em cada instância, ainda serão 100 instâncias, espalhadas por quem sabe quantas VMs reais. Mas, como muitos dos espaços de trabalho serão pequenos (muito menores do que nossa média atual), a receita não aumentará de acordo. Portanto, acho que isso provavelmente está fora de questão e estou me concentrando agora em arquiteturas de banco de dados único.
Cada espaço de trabalho compartilha as mesmas tabelas, indexadas pelo ID do espaço de trabalho. Portanto, toda tabela precisaria de uma nova coluna de ID de espaço de trabalho e toda consulta precisa adicionar a condição do espaço de trabalho na cláusula WHERE (ou mais provavelmente toda tabela real é agrupada em uma função de valor de tabela embutida que recebe o WorkspaceID; de qualquer maneira ...) O a chave primária de cada tabela também teria que ser redefinida para incluir o ID do espaço de trabalho, uma vez que nem todo PK agora é globalmente exclusivo. Em termos de programação, tudo bem, mas mesmo com indexação adequada e design de consulta perfeito (e não, nem todas as nossas consultas são perfeitas - a temida varredura de linha ainda acontece ocasionalmente) existe alguma maneira concebível de funcionar bem - para todos - como bancos de dados separados? Mais especificamente, podemos garantir que pequenos projetos não sofrerão com a presença de grandes projetos que podem ocupar 100x mais linhas do que os pequenos? E quais etapas específicas precisariam ser executadas, seja o tipo de índice a ser usado ou como escrever consultas para garantir que o otimizador sempre restrinja as coisas por ID do espaço de trabalho antes de fazer literalmente qualquer outra coisa?
Particionamento - pelo que li, isso não ajuda no desempenho da consulta e parece que a MS recomenda limitar tabelas ou índices a 1.000 partições, portanto, isso também não ajudará.
Crie o mesmo conjunto de tabelas, mas com um novo esquema para cada espaço de trabalho. Pensei nisso porque não há limites para o número de tabelas que um banco de dados pode ter além do limite geral de objetos 2G. Mas não explorei muito essa ideia. Estou me perguntando se haveria preocupações de desempenho com 100.000 esquemas e milhões de tabelas, visualizações, processos armazenados, etc.
Com tudo isso, aqui está a pergunta específica - quais recursos específicos do SQL Server e/ou estratégias gerais, incluindo, entre outros, coisas que considerei, seriam mais úteis para manter um grande número de conjuntos de dados independentes com esquemas idênticos em um único banco de dados gigante? Reiterando, manter o desempenho o mais próximo possível de uma arquitetura de vários bancos de dados é de alta prioridade.
E nem é preciso dizer que, se alguma parte da minha avaliação acima parecer incorreta ou equivocada, ficarei feliz em ser corrigido. Muito obrigado.
Não é tudo ou nada. Você pode manter sua arquitetura de vários bancos de dados enquanto permite que vários projetos compartilhem um banco de dados. Em seguida, você armazena apenas vários espaços de trabalho em um banco de dados para espaços de trabalho menores.
A abordagem normal de indexação é adicionar WorkspaceID como a coluna inicial de todas as chaves primárias, o que colocará fisicamente as linhas de um espaço de trabalho específico.
Você precisará de um procedimento para excluir um espaço de trabalho de um banco de dados. Em seguida, para dividir um banco de dados, basta restaurar uma nova cópia dele e excluir espaços de trabalho de cada um.
Dada a arquitetura atual, esta é a maneira óbvia de dimensionar isso. Você precisaria encontrar alguma maneira de equilibrar a carga. Talvez você possa ter até 25.000 bancos de dados de espaços de trabalho pequenos em alguns servidores, mas não mais do que 20 bancos de dados de espaços de trabalho grandes em outros servidores. Há um custo administrativo para isso, mas os custos de infraestrutura devem ser aproximadamente os mesmos que o SQL Server é licenciado por núcleo e o número de núcleos, bem como memória e armazenamento, deve ser aproximadamente o mesmo, mesmo se distribuído por vários servidores .
Qualquer uma das outras soluções reduzirá o isolamento dos dados, o que pode ser um problema.