Sou um pouco novo no SSIS. Eu tenho muito 'dev', construção, consulta, ajuste de desempenho no SQL Server, mas não sou o mestre da modelagem de desempenho geral/administrativa.
Então aqui está minha pergunta --- Os pacotes SSIS devem ser executados no mesmo servidor que o data warehouse (bancos de dados do servidor SQL?)
Por um lado, já recebo uma mensagem de erro enigmática de que certas tarefas de 'inserção em massa' no SSIS só podem ser executadas em bancos de dados locais. Talvez isso possa ser contornado. Talvez não possa e seja muito mais rápido, portanto, coloque o ETL na mesma caixa que o db.
Segundo --- mesmo que isso possa ser contornado, não seria mais rápido ter o ETL no mesmo servidor que o banco de dados em qualquer caso? Ou, tirando isso, o mais próximo geograficamente possível? (ou seja, não continentes diferentes).
Em particular, os ETL são executados durante a noite, e o banco de dados não é trabalhado durante a noite (possivelmente processos de backup, no entanto) - mas não prevejo muito tropeçar um no outro.
Se as tarefas de inserção em massa só puderem ser feitas em bancos de dados locais, o ambiente de teste/desenvolvimento também não precisaria ser configurado no servidor de banco de dados? Basta saber o que é sensato. É uma loja menor aqui, mas nós esticamos as coisas.
Oh Deus não, não use o SQL Server Destination.
Neste ponto, os ganhos de desempenho insignificantes não valem o erro aleatório que pode ocorrer ao carregar os dados. OLE DB Destination é sólido, oferece excelente desempenho e não sofre com os erros infernais que você pode encontrar com o SQL Server Destination. Ambos os componentes podem armazenar dados no SQL Server e, como bônus, o OLE DB Destination permite que você execute seu pacote SSIS de qualquer servidor.
SQL Server Destination funciona por algum vodu muito inteligente, como eu o entendo. Basicamente, o mecanismo SSIS coloca os dados como carregaria no SQL Server e é sugado com uma quantidade mínima de verificação no lado do banco de dados, já que o SSIS super promete, jura que os dados são bons.
O problema que atormentava muitos usuários nos dias 2005/2008 era que algo aconteceria entre pensar em inserir os dados e realmente inseri-los, onde o único erro relatado seria algo excepcionalmente perspicaz como "O componente de destino relatou um erro". No entanto, se você reiniciar imediatamente o pacote - sem alterações no banco de dados de origem ou destino, os dados serão carregados perfeitamente.
Quanto às questões de nível de arquiteto, se você projetar seus pacotes usando o Destino OLE DB, poderá ser infinitamente mais ágil do que usar o Destino do SQL Server. Qual é a única coisa que o SQL Server quer mais do que qualquer outra coisa para um bom desempenho? Memória. Quanto mais informações ele puder manter na RAM, melhor desempenho você terá. Por quanto tempo o SQL Server mantém essa memória? Até que ele reinicie ou até que as coisas fiquem realmente ruins e seja forçado a devolver para que o servidor não caia.
O SQL Server Integration Services é um mecanismo ETL de alto desempenho. Ele é capaz de transformar os dados muito rapidamente, mantendo tudo na memória em vez de gravá-lo em arquivos ou tabelas temporárias. Quanto mais linhas de dados ele puder carregar na memória, mais alterações simultâneas ele poderá processar e sua taxa de transferência aumentará. No entanto, o padrão geral para o consumo de recursos do SSIS é que ele é intermitente. Claro, pode parecer hipopótamos famintos, pois começa a consumir tudo o que vê, mas corre - talvez por minutos, talvez horas, mas assim que terminar, todos os recursos serão liberados e estarão disponíveis para processamento geral.
Como o SSIS é uma ferramenta ETL na memória e o SQL Server realmente gosta de memória, eles vão lutar. Talvez não hoje. O servidor tem 256 GB de memória, o SQL Server está usando apenas 180, então há uns bons 30% restantes para processos no nível do sistema operacional e o SSIS funciona bem nesse espaço.
O tempo passa e ah, o negócio está crescendo e temos novos web thingers batendo no banco de dados o dia todo e agora todos esses analistas estão indo contra o servidor com Access e puxando todos os dados apenas para filtrá-los para "hoje" uma vez chega à sua área de trabalho. Agora o SQL Server está pedindo cada vez mais memória. Você pode alterar a configuração de memória máxima do servidor no SQL Server para fornecer um pouco mais ao mecanismo de banco de dados, mas com um custo para outras tarefas do sistema operacional, como SSIS (ele não é executado no espaço de memória do SQL Server).
Em algum momento, você pode se deparar com uma situação em que maximizou a memória física do servidor e reduziu tudo em execução nessa caixa que não é o SQL Server e a única coisa que resta é o SSIS. E agora?
Em um mundo OLE DB Destination, você cria outra instância (ou usa uma caixa existente e menos tributada), implanta/mover seus pacotes do servidor 1 para 2. Desde que existam permissões e quaisquer dependências do sistema de arquivos na nova caixa, você está feito.
Em um mundo de destino do SQL Server, você precisa editar todos os pacotes e alterar o componente de destino, testar, passar por qualquer processo de revisão de gerenciamento de alterações e, em seguida, implantar.
Algo vai ser o seu fator limitante para o desempenho com SSIS: Memória, Rede, Armazenamento, CPU. Se você tiver pacotes puxando terabytes de dados pela rede, apenas para gravar kilobytes de dados, sim, esse subconjunto de pacotes pode precisar ser executado o mais próximo possível da origem.
Desenvolvedores novatos do SSIS podem escrever alguns pacotes realmente intensivos em memória. Por exemplo, eles precisam reunir clientes de server1.database1 e seus pedidos, também em server1.database1. Se eles apenas usarem as ferramentas, eles podem ter dois componentes OLE DB Source que trazem todos os clientes e todos os pedidos para a memória. Para unir os dados, eles precisariam usar um componente de mesclagem. Ah, mas isso tem um requisito para dados classificados. Não se preocupe, há um prático componente Sort bem ali na caixa de ferramentas, então adicionamos um deles a cada uma de nossas fontes e, em seguida, conectamos a junção. Neste ponto, apenas puxamos todos esses dados para a memória, classificamos tudo na memória e dividimos pela metade nossa memória disponível no "antes do componente assíncrono/bloqueio" e "após o componente de bloqueio"
Não tem problema, eles reescrevem e percebem que podem adicionar um ORDER BY explícito na fonte, e marcá-lo como tal, e eliminar dois SORTs, mas eles ainda têm que pagar a penalidade pela junção.
Ou, alguém que realmente escreve consultas vê o que está fazendo e diz "vamos usar uma única consulta para obter esses dados. Deixe o mecanismo de banco de dados usar seus índices para tornar isso eficiente"