Guia Operacional e de Ajuste do SSIS
Propaganda
Guia Operacional e de Ajuste do SSIS Artigo técnico do SQL Server Autores: Alexei Khalyako, Carla Sabotta, Silvano Coriani, Sreedhar Pelluru, Steve Howard Revisores técnicos: Cindy Gross, David Pless, Mark Simms, Daniel Sol Publicado em: dezembro de 2012 Aplica-se a: SQL Server 2012; Banco de dados SQL do Windows Azure Resumo: o SSIS (SQL Server Integration Services) pode ser usado de forma eficiente como uma ferramenta para mover dados para dentro e fora do Banco de dados SQL do Windows Azure (WA), como parte da solução de ETL (extração, transformação e carregamento) total e como parte da solução de movimentação de dados. Pode ser usado também para mover dados entre origens e destinos na nuvem, bem como em um cenário híbrido entre os ambientes em nuvem e local. Este documento descreve as práticas recomendadas do SSIS para origens e destinos na nuvem, discute o planejamento para projetos SSIS, quer o projeto esteja inteiramente na nuvem ou envolva movimentações de dados híbridas, e usa um exemplo para explicar como maximizar o desempenho em uma movimentação híbrida com a expansão da movimentação de dados. Direitos autorais Este documento é fornecido "no estado em que se encontra". As informações e opiniões expressas nele, inclusive URLs e outras referências a sites da Internet, poderão ser alteradas sem aviso prévio. Você assume integralmente o risco de usá-lo. Alguns exemplos descritos aqui são fictícios e fornecidos apenas como ilustração. Nenhuma associação ou ligação real é intencional ou deve ser inferida. Este documento não dá nenhum direito legal a nenhuma propriedade intelectual em qualquer produto Microsoft. Você pode copiar e usar este documento para sua referência interna. © 2011 Microsoft. Todos os direitos reservados. 2 Sumário Introdução..................................................................................................................................................... 5 Design do projeto.......................................................................................................................................... 5 Escopo e descrição do problema .............................................................................................................. 5 Por que a movimentação de dados é tão importante no Azure............................................................... 6 Principais cenários de movimentação de dados ....................................................................................... 7 Carregamento inicial de dados e migração de locais para a nuvem..................................................... 7 Movimentação de dados gerados na nuvem para sistemas locais....................................................... 8 Movimentação de dados entre serviços em nuvem ............................................................................. 9 Ferramentas, serviços e soluções existentes ............................................................................................ 9 SSIS (SQL Server Integration Services) ................................................................................................ 10 Classe SqlBulkCopy no ADO.NET ......................................................................................................... 11 Programa de cópia em massa (BCP.EXE) ............................................................................................ 12 Filas e Blobs de Armazenamento do Azure ........................................................................................ 12 Opções de design e implementação ....................................................................................................... 13 Criar e implementar uma arquitetura equilibrada ............................................................................. 13 Considerações sobre tipos de dados .................................................................................................. 14 Empacotamento e implantação da solução............................................................................................ 15 Criar soluções portáteis ...................................................................................................................... 15 Distribuição de pacotes e componentes de código ............................................................................ 16 Banco de dados SQL do Azure como um destino da movimentação de dados .................................. 16 Considerações sobre arquitetura................................................................................................................ 17 Projetando para reiniciar sem perder o progresso do pipeline .............................................................. 18 O princípio básico.................................................................................................................................... 18 Exemplo com um único destino .......................................................................................................... 19 Exemplo com vários destinos.................................................................................................................. 25 Outras dicas de reinicialização ................................................................................................................ 27 Criando a repetição sem intervenção manual ........................................................................................ 28 Incorporando a repetição ....................................................................................................................... 29 3 Opções de ajuste de desempenho do SSIS ................................................................................................. 33 Ajustando as configurações de rede ....................................................................................................... 33 Configurações de rede ........................................................................................................................ 34 Observação: ao alterar as configurações da placa de interface de rede para usar quadros jumbo, verifique se a infraestrutura de rede pode oferecer suporte a esse tipo de quadro. ........................ 34 Configurações do pacote SSIS ................................................................................................................. 35 Considerações especiais sobre dados BLOB ....................................................................................... 37 Usando novos recursos no SSIS 2012 para monitorar o desempenho por um sistema distribuído ...... 39 Registrar estatísticas de desempenho ................................................................................................ 40 Exibir estatísticas da execução............................................................................................................ 41 Monitorar fluxo de dados ................................................................................................................... 47 Conclusão .................................................................................................................................................... 52 4 Introdução O SSIS (SQL Server Integration Services) é uma ferramenta eficiente para mover dados para dentro e fora do Banco de dados SQL do Windows Azure, como parte da solução de ETL (extração, transformação e carregamento) total ou como parte da solução de movimentação de dados, onde nenhuma transformação é necessária. O SSIS é eficiente para uma variedade de origens e destinos, estejam eles na nuvem, em ambientes locais ou combinados em uma solução híbrida. Este documento descreve as práticas recomendadas do SSIS para origens e destinos na nuvem, discute o planejamento para projetos SSIS, quer o projeto esteja inteiramente na nuvem ou envolva movimentações de dados híbridas, e usa um exemplo para explicar como maximizar o desempenho em uma movimentação híbrida com a expansão da movimentação de dados. Design do projeto Os projetos que movem dados entre repositórios de dados na nuvem e locais podem envolver diversos processos em várias soluções. Normalmente, há muitas partes que começam na população inicial do destino, que pode obter dados de outro sistema ou plataforma por meio de manutenção, como o rebalanceamento de conjuntos de dados entre várias partições ou fragmentos, e possivelmente continuam com atualizações ou operações periódicas de dados em massa. O design do projeto e as suposições subjacentes são geralmente diferentes para uma solução que envolve dados na nuvem, em comparação com um ambiente tradicional de movimentação de dados totalmente local. Porém, muitas aprendizagens, experiências e práticas ainda serão válidas, mas é necessário fazer alterações para acomodar as diferenças, como o fato de que seu ambiente não esteja mais autossuficiente e sob seu total controle enquanto você muda para um pool compartilhado de recursos de consumo. Essas diferenças exigem uma abordagem mais equilibrada e escalonável para que obtenham êxito. Escopo e descrição do problema Em soluções nativas compiladas desde o início para a nuvem e em soluções migradas, os dados precisam ser movidos para a frente e para trás. Isso provavelmente acontece em várias fases do ciclo de vida do aplicativo. As fases incluem testes de pré-produção, carregamento inicial de dados, sincronização subsequente entre dados gerados na nuvem e bancos de dados locais originais, e instantâneos de dados recorrentes tirados desde a nuvem até outros sistemas locais (por exemplo, data warehouses). 5 Figura 1 Cenários de movimentação de dados O foco principal desta seção é a fase de carregamento inicial de dados: considerando a experiência completa de extrair dados do banco de dados de origem, passar do ambiente local para a nuvem e carregar os dados em um destino final. É importante enfatizar que a maioria das práticas recomendadas e otimizações (se não todas) descrita neste documento será igualmente aplicada a muitos dos cenários descritos com alterações mínimas. Falaremos sobre esses cenários e seus principais problemas nas próximas seções. Por que a movimentação de dados é tão importante no Azure Em comparação com uma experiência de data center tradicional, em que os desafios de mover dados entre aplicativos e sistemas geralmente estão relacionados a problemas como compatibilidade de aplicativos, orquestração e sincronização de processos, além de recursos de hardware físico e de rede, em ambientes de nuvem como o WA, há várias camadas extras de complexidade. A complexidade pode estar em áreas como a conectividade entre o local e a nuvem (ou entre diferentes serviços em nuvem) e estar relacionada a confiabilidade, largura de banda e latência da conexão. Considerar essa complexidade é essencial no desenvolvimento de uma boa solução de movimentação de dados. Quando você tem um número alto de partes móveis envolvidas na solução, é ainda mais importante concentrar seus esforços em encontrar um design equilibrado entre todos os componentes e tecnologias envolvidos. Você tenta evitar a "saturação" do vínculo mais fraco da cadeia, uma vez que isso afetará negativamente todos os outros. 6 Com base em nossos testes, uma das áreas críticas é a capacidade do destino de dados de ingerir, na velocidade apropriada, a quantidade de dados despejados nele. A abordagem mais comum é expandir o banco de dados de destino para vários nós de back-end usando a fragmentação personalizada (http://social.technet.microsoft.com/wiki/contents/articles/1926.how-to-shard-withwindows-azure-sql-database.aspx). Essa técnica será obrigatória se a quantidade de dados a ser carregada for significativa (mais de 20 GB/hora era considerada significativa na data em que este documento foi escrito) e pode ser aplicada às instâncias do Banco de dados SQL do Azure e ao SQL Server em execução nas VMs (máquinas virtuais) do WA. Uma vez que isso não introduzirá automaticamente escalabilidade linear na solução de carregamento de dados, há um aumento na necessidade de balancear as outras partes móveis da solução. As seções a seguir descreverão bem as áreas mais críticas e as opções de design que podem ser adotadas para maximizar os resultados finais. Principais cenários de movimentação de dados Veja a seguir os três cenários principais que consideraremos como parte da experiência completa de movimentação de dados. Eles conterão a maioria dos temas e desafios recorrentes que encontramos ao longo do caminho. Carregamento inicial de dados e migração de locais para a nuvem Movimentação de dados gerados na nuvem para sistemas locais Movimentação de dados entre serviços em nuvem Carregamento inicial de dados e migração de locais para a nuvem Banco de dados SQL ou SQL Server em uma VM Figura 2 Cenário de carregamento inicial de dados 7 Cada aplicativo que precise ser movido de uma implantação local para um ambiente de nuvem também exigirá que um determinado volume de dados seja movido. Quando o volume de dados se torna significativo, essa operação pode apresentar alguns desafios críticos que exigirão uma abordagem um pouco diferente em comparação com a que costumamos usar localmente. Isso se deve principalmente a duas áreas: a latência e a largura de banda da rede pública, e a quantidade de recursos (compartilhados) para executar a fase de carregamento de dados, que estão disponíveis nos nós de hardware físico que hospedam um banco de dados (Banco de dados SQL do Azure ou VMs do WA) no ambiente de nuvem. Há abordagens específicas (veja a Figura 2), como o particionamento dos dados originais em vários arquivos de compartimento de memória e a compactação desses arquivos antes da transferência pela rede, o que pode ajudar a minimizar o impacto do componente com desempenho mais baixo na solução geral. Os dados de particionamento também ajudarão na nuvem para facilitar a inserção desses dados em um destino que muito provavelmente estará fragmentado em várias instâncias do Banco de dados SQL do Azure ou hospedado por várias VMs do WA. O SSIS terá um papel importante no local e na nuvem para executar fisicamente as operações de importação e exportação. A solução total exigirá tecnologias adicionais, como Filas e Armazenamento de Blob do Azure, para armazenar formatos de arquivos intermediários e para orquestrar a cópia e recuperar a operação em várias instâncias de processos de importação do SSIS. Para obter mais informações relacionadas especificamente à migração de objetos e de um esquema de banco de dados para o Banco de dados SQL Azure, consulte as orientações em Migrando aplicativos centrados em dados para o Windows Azure (http://msdn.microsoft.com/pt-br/library/ windowsazure/jj156154.aspx). Movimentação de dados gerados na nuvem para sistemas locais Esse cenário é uma variação próxima do anterior, uma vez que de uma perspectiva técnica trata-se de reverter o processo de carregamento e fluxo de dados, embora possa haver algumas diferenças em termos de objetivos gerais. Esse cenário é normalmente associado a dados gerados a frio que precisam ser recuperados e carregados periodicamente em sistemas locais, como data warehouses, ou a dados que alimentarão soluções transacionais locais. Isso significa que a maioria das técnicas e tecnologias mencionadas na seção anterior será relevante para esse cenário também. O SSIS vai extrair dados na nuvem e, em seguida, compactar e enviar os dados de volta a um sistema local onde, novamente, todas as orientações tradicionais serão aplicadas. Consulte Guia de Desempenho do Carregamento de Dados (http://msdn.microsoft.com/pt-br/library/dd425070(v=SQL.100).aspx) para obter mais informações. 8 Movimentação de dados entre serviços em nuvem Há vários cenários em que é obrigatória a movimentação de dados entre diferentes serviços em nuvem e bancos de dados. Isso inclui a troca de dados entre diferentes soluções que precisam interagir entre si e redistribuir dados particionados entre tabelas hospedadas em diferentes fragmentos, conforme ilustrado na Figura 3 abaixo. Figura 3 Movendo dados entre fragmentos de banco de dados Banco de dados SQL ou SQL Server em uma VM Esses fragmentos podem ser igualmente hospedados pelas instâncias do Banco de dados SQL do Azure ou pelo SQL Server em uma VM do WA sem alterar a arquitetura e a abordagem subjacente. Como uma diferença em relação aos cenários anteriores, o processo inteiro de movimentação de dados geralmente ocorre dentro dos limites de uma única região do WA, reduzindo significativamente o impacto da latência de rede e eliminando a necessidade de exportar e importar dados por meio de um local de armazenamento intermediário (discos locais ou Armazenamento do Azure). Embora alguns cenários possam exigir a movimentação de dados entre regiões, essa discussão está fora do escopo deste documento. Ao mesmo tempo, como a origem e o destino dos dados serão ambos hospedados em um ambiente em nuvem compartilhado, isso aumentará consideravelmente a necessidade de um ajuste cuidadoso da fase de carregamento, em particular. Ferramentas, serviços e soluções existentes Para implementar soluções adequadas aos cenários descritos anteriormente, você pode usar uma combinação de ferramentas novas e existentes, além de componentes e abordagens que podem ajudar tanto no ambiente local quanto na nuvem. Em um ambiente híbrido, alguns desses componentes precisarão estar localizados com sistemas e fontes de dados existentes no local, enquanto outros componentes se beneficiarão da localização com destinos de dados na nuvem. 9 SSIS (SQL Server Integration Services) Como uma solução básica de movimentação e integração de dados, o SSIS oferece uma ampla variedade de recursos que atendem à maioria das áreas exigidas nessa gama de cenários. Embora não tenham sido especificamente desenvolvidos para o ambiente híbrido, com o aparecimento de VMs do WA, os pacotes SSIS podem ser executados no local e na nuvem e podem, potencialmente, conectar os dois mundos diretamente. Isso está abrindo portas para uma grande reutilização de conhecimentos e habilidades na comunidade de desenvolvedores de DBA/ETL, pois muitos profissionais antigos foram treinados e expostos a essa tecnologia. No entanto, é importante entender que nem todos os processos ETL existentes implementados com o SSIS podem ser diretamente reutilizados ao mover dados do ambiente local para a nuvem. Dependendo da complexidade do processo, do volume de dados e da velocidade, bem como das diferenças intrínsecas entre destinos de dados baseados em nuvem, como o SQL Server em execução em uma VM do WA e o Banco de dados SQL do Azure, certo grau de rearquitetura será necessário. Alguns desses desafios podem estar relacionados à falta atual de recursos para lidar com realidades de conexão na nuvem, ao conectar ao Banco de dados SQL do Windows Azure, ou com o volume de trabalho necessário para criar os pacotes SSIS que precisam lidar com as falhas e as repetições durante processos de carregamento de dados. Outro desafio pode ser desenvolver pacotes que precisam se conectar a destinos de dados particionados (fragmentados), onde as entidades de banco de dados possam ser distribuídas a um número de nós físicos que, às vezes, mudam. A lógica de particionamento e os metadados precisam ser gerenciados e recuperados de arquivos de configuração de aplicativos ou de estruturas de dados. A plataforma SSIS já possui a maioria dos recursos para lidar com esses desafios. Por exemplo, você pode usar componentes de Fluxo de Dados, como transformações Multicast e Divisão Condicional, para implementar a lógica de particionamento. Para enfrentar alguns dos desafios de arquitetura, será necessário algum esforço para praticamente implementar o novo design, seja usando a abordagem de ferramenta visual tradicional ou usando um meio automatizado e programático de arquitetar uma solução mais complexa. Para a abordagem programática, o SSIS oferece um ambiente com suporte total a script que abrange desde a criação de tarefas personalizadas no pipeline de transformação até a instrumentação do mecanismo que ajuda a solucionar problemas e depurar a execução de pacotes. Como parte da versão do SQL Server 2012 do Integration Services, uma solução completa de monitoramento e gerenciamento, baseada em um catálogo comum, pode ajudar a criar uma solução de movimentação de dados distribuída e a coletar informações relacionadas às estatísticas e aos resultados da execução de pacotes. 10 Classe SqlBulkCopy no ADO.NET Se o desenvolvimento de uma solução personalizada de movimentação de dados for o método preferencial para resolver um problema específico de movimentação de dados, a classe SqlBulkCopy (http://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlbulkcopy.aspx) dentro da biblioteca de acesso a dados ADO.NET provavelmente será uma das ferramentas mais comuns para a realização do trabalho. Criada como um wrapper fino em torno de APIs de cópia em massa ODBC, essa classe aceitará uma tabela de dados como entrada e uma conexão de banco de dados existente; além disso, oferecerá um modo rápido e totalmente configurável de carregar dados em um Banco de dados SQL do Azure ou no SQL Server. Um aspecto importante de usar a classe SqlBulkCopy para interagir com um destino de dados baseado em nuvem é a capacidade de substituir com facilidade a classe tradicional SqlConnection (http://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlconnection.aspx), usada para interagir com o servidor, por uma classe ReliableSqlConnection (http://msdn.microsoft.com/pt-br/ library/microsoft.practices.enterpriselibrary.windowsazure.transientfaulthandling.sqlazure.reliablesqlco nnection(v=pandp.50).aspx) mais apropriada que faça parte da biblioteca Bloco de aplicativo para tratamento de falhas transitórias (http://msdn.microsoft.com/pt-br/library/hh680934 (v=PandP.50).aspx). Isso simplifica muito a tarefa de implementar um mecanismo de lógica de repetição em um processo de carregamento de dados novo ou existente. Outra faceta interessante da biblioteca é a capacidade de fornecer as políticas de repetição criadas, padrão e personalizadas, para facilitar a adaptação a diferentes condições de conectividade. A classe SqlBulkCopy expõe todos os atributos e propriedades necessários para que seja possível adaptar o processo de carregamento a quase todas as condições. Este artigo explicará como ajustar e otimizar os tamanhos de lote de acordo com o local de execução do processo de carregamento de dados, a quantidade de dados que o processo precisará importar e o tipo de conectividade que estará disponível entre o processo e o destino de dados. Uma situação em que a classe SqlBulkCopy não seria a opção mais eficiente para carregar dados em um destino é quando a quantidade de dados dentro de um único lote é muito baixa, por exemplo, entre 10 e 1.000 linhas por lote. Nesse caso, a sobrecarga exigida pela classe SqlBulkCopy para estabelecer a verificação inicial de metadados antes do início do carregamento de dados pode afetar o desempenho geral. Um boa abordagem alternativa para lotes pequenos seria definir um TVP (parâmetro com valor de tabela) que implemente o esquema desejado e usar "INSERT INTO Destination SELECT * FROM @TVP" para carregar os dados. Para obter um exemplo completo do uso da API de cópia em massa, consulte Classe SqlBulkCopy (http://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlbulkcopy.aspx). 11 Programa de cópia em massa (BCP.EXE) O programa de cópia em massa (um utilitário de linha de comando criado nas mesmas APIs de cópia em massa descritas para a classe SqlBulkCopy) foi usado por algum tempo para carregar dados em massa para dentro e fora de uma instância do SQL Server. O programa é uma ferramenta simples, porém potente, para automatizar as soluções simples de movimentação de dados com eficiência. Uma das principais vantagens da ferramenta é a simplicidade de automatizar a instalação da ferramenta em nós de computação ou em VMs do Azure e usar com facilidade os scripts existentes que podem ser adaptados para execução em um ambiente de nuvem. Por outro lado, o BCP.EXE não fornece nenhum recurso avançado de gerenciamento de conexão. O BCP.EXE também exige o mesmo esforço que o SSIS faz na implementação de tarefas confiáveis de movimentação de dados baseadas em operações de repetição que podem causar instabilidade e perda de conexão. Além disso, diferentemente das outras ferramentas que mencionamos, o BCP.EXE precisa importar ou exportar dados de arquivos físicos hospedados em uma unidade local, mapeada ou conectada. Isso torna impossível a transmissão direta de dados da origem ao destino, ou a leitura programática de dados de diferentes origens, como o SSIS ou um aplicativo baseado em SqlBulkCopy podem fazer. Filas e Blobs de Armazenamento do Azure Embora não sejam ferramentas estritamente relacionadas à movimentação de dados, os recursos de Armazenamento do Azure são definitivamente perfeitos para a implementação de soluções complexas que exigem armazenamento intermediário entre processos locais e em nuvem, além de serem perfeitos para orquestrar fases e operações entre os dois ambientes. Os Blobs de Armazenamento do Azure representam um mecanismo avançado de armazenamento para carregar arquivos intermediários, e para trocar esses arquivos entre VMs ou nós de computação do Azure e aplicativos em execução local. As filas de Armazenamento do Azure representam uma ferramenta simples de mensagem que pode ser usada para sinalizar e coordenar o acesso a arquivos e conteúdo armazenados como Blobs do Azure, desde os processos de carregamento de dados. Os Blobs de Armazenamento e as Filas de Armazenamento do Azure são fáceis de integrar em aplicativos existentes graças à biblioteca .NET do Cliente de Armazenamento do Azure, que oferece um conjunto simples de classes para a interação com contas, contêineres, blobs e operações relacionadas ao Armazenamento. Essa biblioteca oculta os detalhes da interface subjacente baseada em REST e oferece uma ponte entre dados no local e na nuvem. Para obter mais informações sobre como usar as Filas de Armazenamento do Azure, e os Blobs, consulte Como usar o Serviço de Armazenamento de Fila (http://www.windowsazure.com/en-us/develop/net/how-to-guides/queue-service/) e Como usar o Serviço de Armazenamento de Blob do Windows Azure no .NET (http://www.windowsazure.com/ pt-br/develop/net/how-to-guides/blob-storage/). 12 Opções de design e implementação Vários fatores podem afetar as opções de design e implementação em relação às soluções híbridas de movimentação de dados. A necessidade de reutilizar artefatos e processos existentes em oposição a iniciar usando uma folha em branco é provavelmente a mais impactante para as decisões de arquitetura, seguida pelo conjunto de habilidades e pelos perfis de membros da equipe (você tem mais desenvolvedores vs. mais DBAs disponíveis, por exemplo). A equipe tem o conjunto de habilidades para criar uma solução totalmente personalizada de forma programática ou o conjunto de habilidades para adaptar processos ETL existentes? Em qualquer um dos casos, há considerações a serem levadas em conta ao introduzir a nuvem no design, pois algumas das suposições óbvias que você possa ter feito sobre os ambientes locais tradicionais talvez não sejam válidas em um ambiente de nuvem. Outro aspecto de design importante é onde colocar e executar tarefas e serviços específicos de movimentação de dados, como a lógica de divisão condicional que executa atividades de fragmentação ou compactação de dados. Dependendo de como essas tarefas são implementadas no nível de componente (tarefas personalizadas ou pipeline do SSIS), esses componentes podem consumir muitos recursos de CPU. Para balancear o consumo de recursos, pode fazer sentido mover as tarefas para as VMs do Azure e aproveitar a elasticidade natural desse ambiente de nuvem. Ao mesmo tempo, a proximidade às fontes de dados nas quais elas funcionarão pode oferecer ainda mais benefícios devido à redução da latência de rede, que pode ser realmente essencial nesse tipo de solução. O planejamento e os testes ajudarão a determinar os afunilamentos de recursos específicos, e ajudarão a guiar suas decisões sobre como implementar várias tarefas. Os esforços necessários para implementar ou adaptar uma solução existente a um cenário híbrido precisam ser justificados pelos benefícios que o cenário híbrido pode fornecer. É preciso esclarecer as vantagens técnicas que serão obtidas com a migração de algumas partes de sua solução para a nuvem, em oposição ao conjunto de itens que podem ser parcialmente perdidos, para adotar uma perspectiva correta e obter êxito em uma implementação híbrida. Essas trocas estão relacionadas a aspectos muito tangíveis de um design de solução. Como posso aproveitar os muitos recursos de expansão fornecidos pelas plataformas de nuvem sem perder muito controle sobre os componentes de minha solução? Como posso executar meus processos de carregamento de dados em uma plataforma que foi criada para expansão horizontal, e não vertical, e ainda oferecer um desempenho aceitavelmente previsível? Resolver essas questões exige o abandono de algumas suposições sobre o desempenho e a confiabilidade da conectividade de rede, os componentes do aplicativo e os serviços que estão sempre ativos e, em vez disso, planejar recursos que possam ser adicionados para resolver problemas de desempenho. Isso exige entrar em um mundo onde o design para falhas é obrigatório, a latência geralmente é mais alta do que na experiência passada e o particionamento de uma carga de trabalho entre vários serviços ou máquinas virtuais pequenas é altamente desejado. Criar e implementar uma arquitetura equilibrada Todas essas considerações devem nos guiar no sentido correto ao criar uma solução complexa de movimentação de dados com várias partes móveis, onde nenhuma delas está necessariamente representando um equivalente "melhor da categoria" de componentes locais tradicionais. 13 Este deve ser o princípio da direção: dividir o processo de movimentação de dados em várias partes menores, desde a extração de fonte de dados até o carregamento do destino de dados, que precisam ser assíncronas e orquestradas para que se ajustem ao ambiente de latência mais alta introduzido pela solução híbrida. Encontrar o equilíbrio certo entre todos os componentes em um ambiente será muito mais importante do que obter o máximo (os limites) de um único componente. Mesmo as etapas individuais desse processo, como o carregamento de dados, por exemplo, podem precisar ser particionadas em fluxos de carregamento menores atingindo diferentes fragmentos ou bancos de dados físicos para superar as limitações de um único nó de back-end em nossa arquitetura de Banco de dados SQL do Azure. Devido à natureza altamente disponível, compartilhada e multilocatária de alguns componentes em nosso sistema (nós do Banco de dados SQL do Azure e o repositório Blob de Armazenamento do Azure para uma VM do WA que hospeda o SQL Server), o envio de muitos dados por push para um único nó pode criar problemas adicionais de desempenho. Um exemplo de problema de desempenho é inundar o mecanismo de replicação, o que resulta na redução de todo o processo de carregamento de dados. Figura 4 Representação esquemática de uma arquitetura de carregamento de dados equilibrada Considerações sobre tipos de dados Os tipos de dados, o design da entidade e o esquema de banco de dados usados podem afetar o processo de movimentação de dados de várias maneiras. Como regra geral, os tipos de dados que podem atingir um grau maior de compactação podem fornecer vários benefícios quando os dados são carregados em massa de suas fontes para o armazenamento local ou Blobs do Azure para operações temporárias. A compactação dos dados antes de sua transferência por meio eletrônico, obviamente, melhora o desempenho. 14 Empacotamento e implantação da solução Em geral, implementar e implantar uma solução que abrange data centers locais e ambientes baseados em nuvem significa que você precisa lidar com vários componentes e serviços. Proporcionar um alto grau de automação na implantação e na configuração de todas essas partes é ainda mais importante se você planeja implantar várias instâncias da solução de movimentação de dados. As tecnologias de virtualização podem ajudar a criar uma imagem mestra, a qual pode ser usada potencialmente em ambientes locais e na infraestrutura de VM do Azure para simplificar a implantação dos serviços comuns que precisam estar presentes em ambos os ambientes. Ao mesmo tempo, trabalhar com as VMs do WA apresenta algumas limitações em comparação com o que podem fornecer outros nós da computação do Azure, como funções Web e funções de trabalho, em termos de pacotes e serviços de aplicativo e correlacionados (pense nas tarefas de inicialização, por exemplo). Se você já estiver usando recursos de distribuição de software, como os disponíveis por meio das famílias de produtos do Windows Server e System Center, outra possibilidade seria distribuir pacotes e componentes da solução com alguns componentes que são executados na nuvem e outros que são executados em ambientes locais. Outra possibilidade é instalar e configurar manualmente os vários componentes da solução, como o SSIS e o SDK do Azure (para acessar recursos de Armazenamento do Azure), além de todos os pacotes de instalação do aplicativo (.msi) necessários em cada VM que será executada como parte do ambiente distribuído. Criar soluções portáteis Um aspecto que se tornará ainda mais importante quando você estiver executando uma solução em uma arquitetura com capacidade de expansão, é a possibilidade de reconfigurar opções rapidamente, como cadeias de conexão, credenciais e todas as outras opções de configuração que a solução incluirá. Geralmente, isso requer alguma forma de mecanismo de configuração centralizada, onde as informações serão acessadas e propagadas em todos os diversos componentes e serviços envolvidos no processo de movimentação de dados para garantir que cada alteração exija o mínimo de esforço. Ambas as ferramentas padrão, como o SSIS e componentes e aplicativos desenvolvidos de modo personalizado, podem ser implementadas facilmente com essa abordagem em mente. O armazenamento do Azure pode ser uma boa opção para salvar e manter as informações de configuração, supondo-se que elas possam ser acessadas facilmente e estejam disponíveis para os componentes locais e em nuvem. Vale observar que a plataforma SSIS já inclui vários recursos que simplificam a portabilidade e o dimensionamento de uma solução, como arquivos e parâmetros de configuração. Os processos e serviços adicionais que compõem a solução completa de movimentação de dados podem implementar os mesmos tipos de abordagens configuráveis, tornando fácil mover a solução entre diferentes ambientes. 15 Distribuição de pacotes e componentes de código Depois que todos os elementos da solução tiverem sido implementados, o processo escolhido para distribuir fisicamente os vários Pacotes SSIS e componentes de código por várias máquinas se torna essencial. Outro aspecto, e ainda mais importante, será como esses pacotes e elementos de código serão hospedados e executados nos vários servidores e VMs. Embora o ambiente nativo do SSIS no SQL Server 2012 ofereça diferentes tipos de armazenamento e modelos de implantação de pacotes, desenvolver uma solução completa de movimentação de dados pode exigir opções diferentes. Se você precisar executar alguns serviços/aplicativos de orquestração para supervisionar e controlar o processo de movimentação de dados, como esses serviços/aplicativos poderão ser implementados? E quanto da infraestrutura SSIS subjacente você pode usar? Um exemplo específico de distribuição e coordenação de componentes é fornecido no white paper "SQL Server 2012 SSIS para Azure e movimentação de dados híbridos". O white paper está localizado na biblioteca MSDN no nó White papers da Microsoft para SQL Server 2012. As VMs do WA e os servidores físicos baseados no Windows Server não implementam alguns dos recursos que a plataforma Azure fornece para funções Web e funções de trabalho. Consequentemente, a melhor opção é implementar esses componentes como serviços do Windows, para garantir que os processos serão iniciados quando os vários hosts forem inicializados e que continuarão sendo executados independentemente de uma sessão de usuário interativa nesse computador específico. A plataforma .NET facilita bastante a criação e o empacotamento desse tipo de pacote de software que, em seguida, pode ser distribuído e implantado nos vários hosts que usam as opções que foram descritas anteriormente. Os serviços/aplicativos de orquestração interagem com os vários componentes externos (Filas, Armazenamento de BLOB do Windows Azure etc.), invocam o mecanismo de execução do SSIS (DtExec.exe) e orquestram as tarefas de transformação e carregamento de dados em vários hosts ou VMs. Os componentes personalizados que a execução do pacote exige também precisarão ser distribuídos pelos vários nós. Com essa abordagem distribuída, um ambiente robusto, portátil e flexível de implantação e execução pode ser criado para hospedar nossa solução completa de movimentação de dados em uma infraestrutura completamente híbrida. Banco de dados SQL do Azure como um destino da movimentação de dados Embora os Bancos de dados SQL do Windows Azure e do SQL Server tenham semelhanças, é um erro considerá-los idênticos. Há várias diferenças que podem afetar a forma como os aplicativos são executados no Banco de dados SQL do Windows Azure em comparação com o banco de dados do SQL Server. 16 O Banco de dados SQL do Windows Azure é um serviço hospedado que implementa uma arquitetura de multilocação completa. Diferentemente das implementações tradicionais do SQL Server, o Banco de dados SQL do Windows Azure contém recursos como alta disponibilidade interna e backups automatizados, além de ser executado em hardware de consumo, e não em grandes servidores. O banco de dados executa um subconjunto dos recursos frequentemente usados em ambientes locais, incluindo compactação do banco de dados, consultas paralelas, índices ColumnStore, particionamento de tabela etc. Para obter mais informações sobre limitações de recursos para o Banco de dados SQL do Windows Azure, consulte Limitações de recursos do SQL Server (Banco de dados SQL do Windows Azure) (http://msdn.microsoft.com/pt-br/library/windowsazure/ff394115.aspx). Uma das maiores diferenças entre o Banco de dados SQL do Windows Azure e o SQL Server é que o primeiro expõe um serviço de multilocação e expansão, em que diferentes assinaturas compartilham os recursos de uma ou mais máquinas em um data center da Microsoft. A meta é balancear a carga geral no data center movendo ocasionalmente clientes para máquinas diferentes. Essas máquinas são servidores padrão baseados em rack, que maximizam o preço/desempenho em vez do desempenho geral. Nem todos os nós do Banco de dados SQL do Windows Azure usarão hardware extremamente avançado em sua oferta hospedada. Quando um aplicativo cliente precisa exceder os limites de uma única máquina, o aplicativo deverá ser modificado para distribuir a carga de trabalho do cliente pelos vários bancos de dados (provavelmente significando várias máquinas) no lugar de um único servidor. Uma das compensações da elasticidade e da capacidade de gerenciamento desse ambiente é que, às vezes, seu aplicativo pode ser movido para outra máquina inesperadamente. Uma vez que as sessões não têm monitoração de estado, o design do aplicativo deve usar técnicas que evitam pontos únicos de falha. Isso inclui o cache em outras camadas quando apropriado e uso da lógica de repetição na conexão e nos comandos a serem resilientes a falhas. Além disso, as várias camadas em uma infraestrutura do WA não estarão na mesma sub-rede da rede, de modo que haverá algumas diferenças de latência entre os aplicativos cliente e o Banco de dados SQL do Windows Azure. Isso acontece quando os aplicativos e o banco de dados são hospedados no mesmo data center físico. As soluções tradicionais de carregamento de dados do SQL Server que são muito "tagarelas" podem ser executadas mais lentamente no Windows Azure devido a essas diferenças de rede física. Para aqueles já familiarizados com a computação cliente/servidor, as mesmas soluções se aplicam aqui: reflita sobre viagens de ida e volta entre camadas em uma solução para tratar quaisquer diferenças visíveis de latência. Considerações sobre arquitetura Alguns dos desafios mais comuns com pacotes SSIS se referem a como manipular falhas inesperadas durante a execução e como minimizar a quantidade de tempo necessária para concluir a execução de um processo ETL quando você precisar retomar o processamento depois de uma falha. Para tarefas de fluxo de controle, como tarefas do sistema de arquivos, é possível usar pontos de verificação para retomar a execução sem processar novamente o trabalho que já tiver sido concluído. Para obter todas as instruções sobre como usar pontos de verificação, consulte Reiniciar pacotes usando pontos de verificação (http://msdn.microsoft.com/pt-br/library/ms140226.aspx). 17 Muitas vezes, o fluxo de dados é a maior parte do pacote SSIS. Nesta seção, você verá estratégias de design de pacotes para permitir uma repetição automática de reparo em caso falha, além do design do pipeline de fluxo de dados para permitir a recuperação a partir do ponto de falha, em vez de precisar repetir o fluxo de dados inteiro. Considerações adicionais sobre manipulação de falhas transitórias na plataforma WA são abordadas no white paper “SQL Server 2012 SSIS para Azure e movimentação de dados híbridos". O white paper está localizado na biblioteca MSDN no nó White papers da Microsoft para SQL Server 2012. Projetando para reiniciar sem perder o progresso do pipeline Durante o design de pacotes, uma das maiores preocupações é projetar de modo que, em caso de falha, você não perca todo o progresso que o pacote fez até o momento. Para itens no fluxo de controle de um pacote, isso é realizado pelo uso de pontos de verificação. No entanto, poder reiniciar um fluxo de dados sem perder o progresso é algo que só pode ser obtido pelo design do pacote. Nesta seção, você verá uma estratégia de design de pacotes que permite ao fluxo de dados reiniciar a partir do ponto de falha, e também que o pacote seja repetido automaticamente para que não haja falha no caso de queda de conexão. O planejamento para essas quedas de conexão ou breves interrupções torna-se especialmente importante na movimentação de dados para dentro e fora do Banco de dados SQL do WA. O princípio básico Embora o desempenho seja importante em qualquer movimentação de dados, você deverá equilibrar os requisitos de desempenho com o progresso que você poderá perder se algo acontecer durante a movimentação dos dados. Em cada movimentação de dados, você deve ter alguma maneira de saber quais dados já foram descarregados no destino e quais não foram. No caso de dados que são apenas inseridos, isso pode, muitas vezes, ser determinado apenas pela chave primária. Para outros dados, pode ser pela data da última modificação. Seja qual for a natureza dos dados, a primeira parte do projeto de reinício é entender como identificar dados que já existem no destino, quais dados devem ser atualizados no destino e quais dados ainda precisam ser entregues ao destino. Após estabelecer essa parte, você poderá ordenar e segmentar os dados de modo que possa processar somente os dados que não chegaram ao destino ou minimizar a quantidade de retrabalho que deverá efetuar em qualquer fase. A fragmentação e a ordenação permitem rastrear com facilidade quais partes foram processadas e, com a ordenação, é possível rastrear quais registros dentro de qualquer parte já foram processados. Seguindo essa abordagem, você não precisa comparar cada linha da origem com o destino para saber se o registro foi processado. Processos ETL mais complexos podem ter vários ambientes de preparo. Cada ambiente de preparo é um destino para uma fase de ETL. Nesses tipos de ambiente, considere cada um de seus ambientes de preparo como um destino distinto e crie cada segmento de seu ETL para reinicialização. 18 Exemplo com um único destino O fluxo de dados reiniciável mais simples é um pequeno, com um único valor inteiro como uma chave primária. Quando o SQL Server for a origem, você poderá fazer consultas nessa origem usando a prática recomendada de limitação de dados que você extrai da sua origem. Considere a tabela de exemplo clássica da AdventureWorks: "Production.TransactionHistory". A tabela apresenta a seguinte estrutura: CREATE TABLE [Production].[TransactionHistory] ( [TransactionID] INT [ProductID] INT [ReferenceOrderID] INT [ReferenceOrderLineID] INT [TransactionDate] INT [TransactionType] NCHAR(1) [Quantity] INT [ActualCost] MONEY [ModifiedDate] DATETIME ) NOT NOT NOT NOT NOT NOT NOT NOT NOT NULL NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY KEY, Nesse exemplo, a tabela tem um único inteiro como sua chave primária. Se os dados forem estáticos no momento da movimentação, ou se os dados forem apenas inseridos nessa tabela (sem atualizações), tudo o que você precisa para saber se uma linha foi recebida ou não no destino é da chave primária. Comparar cada valor de chave primária com o destino será relativamente caro. Em vez disso, use uma estratégia de classificação dos dados no arquivo de origem pela coluna TransactionID. Com essa estratégia, você só precisa saber que os dados são processados na ordem e qual é a TransactionID mais alta confirmada no destino. Em um pacote SSIS, é possível fazer isso executando-se o procedimento a seguir. 1. Verifique a maior chave em seu destino. 2. Crie a consulta na origem para efetuar pull somente de registros com uma TransactionID maior que a TransactionID mais alta no destino. 3. Use uma TransactionID ORDER BY em sua consulta de origem para assegurar que a comparação da TransactionID mais alta permaneça válida na próxima vez que você iniciar o pacote. No SSIS, usando uma fonte de dados relacional como sua origem, você pode usar uma Tarefa Executar SQL para efetuar pull do valor mais alto em uma variável no seu pacote. Entretanto, considere a possibilidade de não existir nenhuma linha no destino. SELECT MAX(TransactionID) FROM Production.TransactionHistory Para recuperar a TransactionID máxima, considere que o resultado de uma tabela vazia será nulo. Isso pode causar alguns problemas na lógica que você usa em seu pacote. Uma abordagem melhor é primeiramente usar uma Tarefa Executar SQL na origem e encontrar a TransactionID mínima não processada em sua origem. Em seguida, consulte a TransactionID máxima do seu destino ou, se não existir nenhuma, use um valor mais baixo do que a TransactionID mínima da origem. Crie a consulta de origem para efetuar pull somente de registros maiores que esse valor, e não se esqueça de usar uma TransactionID ORDER BY na consulta. 19 OBSERVAÇÃO: ainda que de forma lógica, você obtém os mesmos resultados apenas recuperando um valor do seu destino e usando uma função ISNULL, ou instrução CASE, na cláusula WHERE de sua consulta de origem. Fazer isso pode gerar problemas de desempenho, especialmente quando as consultas de origem ganham complexidade. Resista ao impulso de usar esse atalho. Em vez disso, localize um valor que você possa usar seguramente como limite inferior e crie a consulta de origem usando o valor. OBSERVAÇÃO: quando a origem for o SQL Server, usar uma cláusula ORDER BY em um índice clusterizado não faz com que o SQL Server realize um trabalho extra para classificar. Os dados já foram ordenados de forma que possam ser recuperados sem executar uma classificação (SORT). Se os dados no destino também tiverem um índice clusterizado na mesma coluna, a ordenação na origem que otimiza sua consulta de origem também otimizará as inserções em seu destino. O outro efeito é a garantia da ordem no pipeline do SSIS, permitindo, assim, que você possa reiniciar o fluxo de dados no ponto de falha. Para criar esse pacote de exemplo, execute o procedimento que se segue. 1. Crie um novo pacote em um projeto novo ou existente. Renomeie o pacote "SimpleRestart". 2. Crie gerenciadores de conexão para se conectar à origem e ao destino. Para esse exemplo, um gerenciador de conexões OLE DB é criado para os servidores de origem e de destino. 3. Arraste uma nova Tarefa Executar SQL para a superfície de fluxo de controle e renomeie-a para "Efetuar pull de TransactionID mínima da origem". 4. Crie uma variável do SSIS no nível de pacote e nomeie-a como minTransactionIDAtSource. Usaremos essa variável para armazenar o valor que você extrai da Tarefa Executar SQL que acabou de adicionar. Verifique se o tipo de dados é Int32, para que corresponda ao valor de TransactionID na tabela, e defina um valor inicial apropriado. 5. Configure a tarefa Efetuar pull de TransactionID mínima da origem como se segue. a. Edite-a e defina a Conexão para ser o gerenciador de conexões do servidor de origem. b. Embora você possa armazenar SQLStatement em uma variável, para este exemplo deixe SQLSourceType como Entrada direta. Abra a janela de entrada para SQLStatement e digite a seguinte consulta: SELECT ISNULL(MIN(TransactionID), 0) FROM Production.TransactionHistory OBSERVAÇÃO: teste suas consultas SQL antes de inseri-las nos editores do SSIS. Isso simplifica a depuração, uma vez que não há ajuda real de depuração nas janelas do editor de consulta do SSIS. 20 Figura 5: Configurando a Tarefa Executar SQL para localizar a transactionID mínima na origem. c. Feche a janela Digitar Consulta SQL clicando em OK. d. Defina a propriedade ResultSet para Linha Simples. e. No painel esquerdo do Editor da Tarefa Executar SQL, clique em Conjunto de Resultados para configurar como você capturará o valor dessa consulta. f. Clique no botão Adicionar para adicionar um conjunto de resultados. g. No novo conjunto de resultados, altere o Nome do Resultado para 0. Verifique se User::minTransactionIDAtSource (variável que você criou na etapa 4) aparece em Nome da Variável. Essa variável armazenará o resultado da consulta SQL. h. Feche o Editor da Tarefa Executar SQL. Não deverá aparecer nenhum erro na tarefa após o fechamento. 6. Arraste outra Tarefa Executar SQL para a superfície de controle. Nomeie-a como Efetuar pull de TransactionID máxima do destino. Conecte a restrição de precedência de êxito de Efetuar pull de TransactionID mínima da origem a essa nova tarefa. 7. Crie uma nova variável com escopo no pacote. Nomeie essa nova variável como maxTransactionIDAtDestination. Atribua a ela um tipo de dados de Int32, para que corresponda ao tipo de dados de TransactionID, e forneça um valor inicial apropriado. 8. Abra o Editor da Tarefa Executar SQL para a nova tarefa e faça o seguinte: a. Defina ResultSet como Linha Simples. b. Defina seu gerenciador de conexões do Servidor de Destino c. SQLSourceType: Entrada direta 21 d. Para SQLStatement, use SELECT ISNULL(MAX(TransactionID), ?) FROM Production.TransactionHistory OBSERVAÇÃO: o ? é um parâmetro de consulta. Definiremos esse valor momentaneamente. e. Feche o Editor de Consultas clicando em OK e clique em Mapeamento de Parâmetros no painel esquerdo do Editor da Tarefa Executar SQL. f. Clique em Adicionar para adicionar um único parâmetro. i. Para Nome da Variável, escolha User::minTransactionIDAtSource. ii. Para Direção, você deve escolher Entrada. iii. O Tipo de Dados deve ser LONG, que é um número inteiro de 32 bits nesse contexto. iv. Altere o Nome do Parâmetro para 0. Observe que isso deve ser alterado para 0. O nome do caractere resultará em um erro. g. Clique no Conjunto de Resultados no painel esquerdo. Clique no botão Adicionar para adicionar um novo conjunto de resultados. i. Altere o Nome do Resultado para 0. ii. Em Nome da Variável, escolha User::maxTransactionIDAtDestination, que é a variável que você criou na etapa 7. Essa variável conterá o resultado da consulta que você insere depois que essa tarefa é executada. OBSERVAÇÃO: a próxima etapa varia de acordo com o tipo de origem que você usará no fluxo de dados. Uma origem OLE DB pode usar uma variável SSIS contendo uma Instrução SQL como sua consulta. Uma conexão ADO.NET não pode fazer isso, mas pode ser parametrizada para usar um parâmetro de projeto ou pacote como sua consulta de origem. Neste primeiro exemplo, você usará uma origem OLE DB com uma variável que contém a consulta de origem. 9. Arraste uma tarefa de Fluxo de Dados para a superfície de controle. Renomeie-a para Movimentação de dados principal e conecte a restrição de precedência de êxito de Efetuar pull de TransactionID máxima do destino para essa tarefa de Fluxo de Dados. Quando o pacote é executado neste ponto, você terá armazenado os valores que precisa conhecer para estabelecer o ponto de partida para sua execução atual. Em seguida, é preciso configurar uma variável para manter a consulta Origem SQL. 10. Crie uma variável com escopo no nível de pacote. Nomeie essa variável como sourceQuery e defina o tipo de dados como cadeia de caracteres (string). Você usará uma expressão para derivar dinamicamente esse valor em tempo de execução com base no valor determinado como o ponto de partida para a consulta seguindo o processo a seguir. a. Clique no botão de reticências à direita da coluna Expressão para exibir o Construtor de Expressões. b. Expanda o nó Variáveis e Parâmetros na janela superior esquerda do Construtor de Expressões. Você usará a variável User::MaxTransactionIDAtDestination que criou na etapa 7. Você deve ver essa variável nas variáveis listadas. Essa variável é um Int32. No entanto, você a usará como parte de uma variável String. Para fazer isso, será preciso convertê-la em um tipo de dados DT_WSTR. No painel superior direito, expanda o nó Conversões de Tipos para localizar a conversão de tipos (DT_WSTR, <<comprimento>>). 22 c. Na Expressão, digite sua consulta. Em locais onde você precisa do nome da variável, ou em sua conversão de tipos, é possível arrastá-lo da janela apropriada para a caixa Expressão para adicioná-lo. Isso ajuda a reduzir o número de erros ortográficos nesse editor. Crie uma expressão como esta: "SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate FROM Production.TransactionHistory WHERE TransactionID > " + (DT_WSTR, 12 ) @[User::maxTransactionIDAtDestination] + " ORDER BY TransactionID" Observe o uso de conversão de tipos para alterar o valor inteiro para uma cadeia de caracteres com um máximo de 12 caracteres de largura. Foi usada uma largura de 12 caracteres porque ela é suficiente para conter todo o intervalo dos valores SQL INT, incluindo o negativo quando aplicável. Para BIGINT, você precisará de uma largura de 22 caracteres. Dimensione sua variável de caractere de acordo com o tipo de dados recuperado. Depois de digitar a expressão, clique no botão Avaliar Expressão para se certificar de que o SSIS possa analisar sua expressão corretamente. Você verá que o valor inicial de maxTransactionIDAtDestination foi adequadamente colocado na expressão avaliada. Esse valor será definido da maneira apropriada em tempo de execução. Não se esqueça de incluir a cláusula ORDER BY na Instrução SQL. Somente com uma ORDER BY você obtém uma ordem garantida de um banco de dados relacional. O método de reinício que você está criando depende da ordem dos valores de chave. d. Feche o Construtor de Expressões clicando no botão OK. Sua Instrução SQL dinamicamente construída agora está armazenada na variável SourceQuery. 11. Clique duas vezes na tarefa de Fluxo de Dados Movimentação de dados principal para abrir a superfície de design de Fluxo de Dados. 12. Arraste uma Origem OLE DB para a superfície de controle do Fluxo de Dados. Renomeie-a como Recuperar de TransactionHistory. OBSERVAÇÃO: você não pode incluir um ponto no nome de um componente de fluxo de dados, portanto o nome completo Efetuar pull de Production.TransactionHistory não é permitido. Se você não usar sublinhados para nomes de tabela, é possível substituí-los pelo ponto na convenção de nomenclatura do SSIS. 23 13. Clique duas vezes na origem Recuperar de TransactionHistory para abrir o Editor de Origem OLE DB. a. Para o Gerenciador de Conexões OLE DB, escolha o gerenciador de conexões Servidor de Origem. b. Na lista suspensa Modo de Acesso a Dados, escolha Comando do SQL da Variável. c. Na lista suspensa Nome da variável, escolha a variável User::sourceQuery que você criou na etapa 10. d. Clique em Visualizar para garantir que sua consulta possa ser executada no servidor de origem. e. Na página Colunas do editor, garanta que todas as colunas sejam selecionadas. f. Clique em OK para sair do Editor de Origem OLE DB. 14. Arraste um Destino OLE DB para a superfície de controle. Renomeie-o como Destino de TransactionHistory. Conecte a origem Recuperar de TransactionHistory ao novo destino. Abra o destino clicando duas vezes e configure seguindo este processo: a. Escolha o gerenciador de conexões Servidor de Destino na lista suspensa Gerenciador de conexões OLE DB. b. Para Modo de acesso a dados, escolha Tabela ou exibição – carregamento rápido se a opção já não estiver selecionada. c. Em Nome da tabela ou exibição, escolha na lista suspensa ou digite o nome do servidor de destino. Nesse caso, é Production.TransactionHistory. d. Se estiver usando a definição de TransactionHistory determinada acima, para fins de demonstração, você poderá manter as configurações padrão na página Gerenciador de Conexões. Se estiver usando o banco de dados AdventureWorks, você deverá selecionar Manter Valores de Identidade. e. Na página Mapeamentos do editor, mapeie suas colunas. OBSERVAÇÃO: em quase todos os casos, é uma prática recomendada enviar linhas de erro para um arquivo de destino e redirecionar a saída. Isso não é necessário para demonstrar a reinicialização no pipeline. Para ver as etapas de criação de um fluxo de erro e redirecionamento de linhas, consulte Configurar uma saída de erro em um componente do fluxo de dados (http://msdn.microsoft.com/pt-br/library/ms140083.aspx). f. Clique em OK para sair do Editor de Destino OLE DB. 15. Teste essa abordagem iniciando e interrompendo uma importação e, em seguida, reiniciando. A cada vez, o fluxo de dados deve escolher a próxima linha que precisa ser movida. As linhas anteriores são ignoradas. 24 Você precisa apenas criar um pacote simples que possa reiniciar seu fluxo de dados após uma falha. Esse pacote será usado como um ponto de partida para o exemplo de design de um pacote que pode fazer novas tentativas. Observe que você não quer que nenhuma das tarefa no Fluxo de Controle salve pontos de verificação. Se o pacote falhar e precisar ser reiniciado, será necessário que os componentes Efetuar pull de TransactionID mínima da origem e Efetuar pull de TransactionID máxima do destino sejam executados para localizar onde exatamente o fluxo de dados foi interrompido na execução anterior. Uma prática recomendável é criar um pacote capaz de localizar seu progresso e reiniciar o fluxo sempre que as características existirem nos dados, para permitir a localização do ponto em que o fluxo de dados foi interrompido. Entretanto, essa prática torna-se especialmente importante em um ambiente como a nuvem, que é mais suscetível às incertezas da rede. Exemplo com vários destinos Esse princípio pode ser estendido a fluxos de dados com vários destinos. Um cenário em que isso é útil é ao mover dados para um modelo de dados fragmentado, como geralmente ocorre com um Banco de dados SQL do WA. Nesses casos, é usada uma divisão condicional para enviar cada linha ao destino apropriado ou, para dados de referência, é possível usar um multicast para enviar todos os dados a todos os destinos. Veja a seguir os princípios fundamentais a serem lembrados no design de reinicialização. Destinos diferentes podem ter progredido para pontos diferentes no momento da falha. Desse modo, você deve localizar o valor de chave mais alto inserido em cada destino. Crie uma variável para manter o valor mais alto em cada destino. O ponto de partida em sua origem será o próximo registro após o valor de chave mais baixo que foi inserido com êxito em seus destinos. Por exemplo, se o valor de chave mais alto inserido em um conjunto de fragmentos fosse como o que se segue, em sua origem você deveria retomar o fluxo de dados com o próximo registro depois de 1.000. o Shard00 key value 1000 o Shard01 key value 1100 o Shard02 key value 1050 Como isso deixa aberta a possibilidade de que alguns dados possam ser extraídos mais de uma vez, você deve filtrar cada destino para evitar uma violação de Chave Primária. Use uma Transformação Divisão Condicional para filtrar os valores de chave que já foram processados. Para o exemplo de fragmentação acima, você criaria variáveis denominadas maxTransactionIDAtShard00, maxTransactionIDAtShard01 e maxTransactionIDAtShard02. Nas Tarefas Executar SQL, você encontraria os valores a serem armazenados em cada uma delas. Na Divisão Condicional, você poderia definir as saídas denominadas Shard00, Shard01 e Shard02. As expressões nas saídas teriam esta aparência: ShardID == 0 && TransactionID > @[User::maxTransactionIDAtShard00] ShardID == 1 && TransactionID > @[User::maxTransactionIDAtShard01] ShardID == 2 && TransactionID > @[User::maxTransactionIDAtShard02] 25 Se entrar alguma linha no pipeline que seja menor que a TransactionID em um fragmento específico, mas o registro for para esse fragmento, o registro não será enviado e nenhuma violação de chave primária será encontrada. Deixe esses registros irem para a saída Padrão ou qualquer saída de desconexão, para que não sejam mais processados. Figura 2: Uma divisão condicional configurada para 3 fragmentos e para permitir a reinicialização sem violações de chave primária no destino. No início de qualquer execução, o valor de chave máximo em cada destino é armazenado nas variáveis maxTransactionIDAtShardXX. No pipeline, as linhas destinadas a esse fragmento com um valor de chave muito baixo não serão enviadas ao destino e, desse modo, não serão reprocessadas. Essas linhas irão para a Saída Padrão. Como a saída padrão não está conectada, elas não avançarão mais no pipeline. 26 Outras dicas de reinicialização Quando seu destino for um arquivo, use a variável para representar o nome do seu arquivo e torne cada nome significativo (como em uma ordem ou um carimbo de data e hora acrescentados ao fim). Processe partes de dados diferentes para arquivos diferentes. Se você processar na mesma ordem, e suas partes forem definidas de forma determinista, será possível observar quais arquivos já foram criados e determinar o ponto de partida a partir daí. Dependendo de suas necessidades e da lógica, talvez seja preciso mover, renomear ou excluir arquivos que já foram processados. Se a origem forem arquivos múltiplos, processe cada arquivo separadamente e rastreie qual é seu arquivo atual. Na reinicialização, você poderá retomar depois que o último arquivo for processado com êxito. Use uma ordenação por nome de arquivo ou data para garantir o processamento dos arquivos sempre na mesma ordem e para poder determinar com facilidade o último arquivo que foi processado com êxito. Quando o SQL Server for sua origem, você poderá usar um tipo de dados de cadeia de caracteres SSIS para consultar valores inteiros. Desde que a cadeia de caracteres possa ser convertida em um inteiro do SQL, o SQL converterá o tipo de dados durante a otimização. Usando esse princípio, você pode alterar as variáveis MaxTransactionIDAtDestination e MinTransactionIDAtSource no pacote de exemplo para uma cadeia de caracteres, alterar os tipos de parâmetro de entrada em Efetuar pull de TransactionID máxima do destino e usar esse pacote como um modelo que também funcionará com chaves primárias de caracteres. Não use um tipo de dados SQL_VARIANT com nenhum outro tipo de dados no SQL, pois isso resultará em exames completos na recuperação dos valores de chave mínimo e máximo. Se a origem for um arquivo, ou qualquer origem onde você não possa fazer consultas com WHERE ou qualquer outro tipo de cláusula de filtro, no seu fluxo de dados execute o procedimento a seguir. 1. Coloque uma transformação divisão condicional entre os componentes de origem e destino. 2. Na divisão condicional, configure uma saída. Nomeie a saída como Lixo ou algum outro nome que permita saber que você não deseja essas linhas. 3. Para a condição que obtém as linhas enviadas ao destino, use uma expressão para filtrar as linhas que já estão em seu destino. Por exemplo, a expressão para o exemplo anterior seria a seguinte: [TransactionID] <= @[User::maxTransactionIDAtDestination] 4. Não conecte a saída Lixo a nenhum componente, a menos que você precise realmente avaliar essas linhas. A única finalidade é filtrar as linhas que você processou em sua última execução. Isso não impede que você leia as linhas que já processou nesse arquivo, mas o impede de enviá-las ao destino novamente, o que economiza a largura de banda da rede. Como essas linhas não são enviadas ao seu destino, você não precisa excluir dados do destino. 27 Ao lidar com chaves compostas, use subconsultas correlacionadas para localizar o ponto de parada exato. Certifique-se de classificar seus dados pelas mesmas chaves. Um exemplo é mostrado a seguir. SELECT MAX(EmployeeID) AS EmployeeID, (SELECT MAX(EffectiveDate) FROM HumanResources.EmployeeHistory i_forDate WHERE h.EmployeeID = i_forDate.EmployeeID) as EffectiveDate, (SELECT MAX(EffectiveSequence) from HumanResources.EmployeeHistory i_forSeq where h.EmployeeID = i_forSeq.EmployeeID and h.EffectiveDate = i.ForSeq.EffectiveDate) as EffectiveDate FROM HumanResources.EmployeeHistory h ORDER BY EmployeeID, EffectiveDate, EffectiveSequence Observe o que a ordem faz nesse exemplo. Se você estivesse movendo todos os registros de todos os funcionários, e essa fosse a ordem de suas chaves, essa seria uma boa ordenação. Porém, se todos (ou alguns) funcionários já existissem no destino e você quisesse importar somente as alterações que entraram em vigor desde a última importação, a ordenação por EffectiveDate, EffectiveSequence e EmployeeID seria o tipo que você precisaria usar para retomar de onde parou. Analise o que você está importando para definir a ordem que permite determinar o ponto de onde se deve retomar. Criando a repetição sem intervenção manual Os componentes de origem e de destino do SSIS não incorporam diretamente a lógica de repetição. No entanto, o desenvolvedor do pacote SSIS não está desamparado ao lidar com essa situação. Criar seus pacotes SSIS de forma que você possa reiniciá-los sem perder o progresso no fluxo de dados também permite a repetição automática após uma falha com alguma consideração adicional. Nos casos em que há movimentação de dados para dentro e fora de Bancos de dados SQL do WA, talvez você precise repetir automaticamente para manipular condições de falha transitórias, como a limitação de recursos ou conexões. Esta seção foi criada com base nos conceitos demonstrados na última seção e mostra um exemplo de lógica de repetição simples no seu pacote. Em seguida, é demonstrado como usar uma abordagem em partes para permitir a repetição de cada parte a fim de tornar seu pacote mais robusto nos momentos em que as falhas transitórias podem ser mais prováveis. Para obter mais informações sobre a Limitação do SQL, consulte o Guia de Desempenho e Elasticidade do Banco de dados SQL do Windows Azure (http://social.technet.microsoft.com/wiki/contents/articles/ 3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx). 28 Incorporando a repetição Para casos de um único pacote, incorporar a repetição envolve as etapas que se seguem. 1. Determine o número máximo de vezes que o pacote deverá ser repetido antes da falha. Para este exemplo, determinaremos que um componente poderá ter no máximo 5 repetições antes da falha no pacote. Crie uma variável no pacote chamada maxNumOfRetries. Torne essa variável um tipo int e forneça o valor 5. Esse valor será usado em expressões no pacote. 2. Configure uma variável para armazenar seu status de êxito. Crie uma nova variável no pacote SSIS. Nomeie essa nova variável como attemptNumber. 3. Use um loop FOR para repetir até o número máximo permitido se o fluxo de dados não for bem-sucedido. 4. Coloque a tarefa de fluxo de dados dentro do loop FOR. 29 5. Defina a propriedade MaximumErrorCount no loop FOR para o número máximo de vezes que o fluxo de dados deve ser repetido, de modo não haja mais falha do pacote após uma repetição. Você deve fazer isso com uma expressão que use a variável maxNumOfRetries que foi configurada na etapa 1. 6. Use tarefas Executar SQL, como mostrado na última seção, para localizar os valores de chave mínimos na origem e os valores de chave máximos no destino. Para uma repetição simples, isso pode ser feito em um loop FOR. Nos exemplos mais avançados, isso pode aparecer em qualquer lugar no fluxo de controle antes do loop FOR. 7. Coloque suas tarefas Executar SQL no loop FOR. 8. Conecte a restrição de êxito da Tarefa Executar SQL à tarefa de Fluxo de Dados. 9. Conecte a restrição de precedência de êxito da tarefa de Fluxo de Dados a uma tarefa de Script que define a variável de status de êxito como true para sair do loop FOR. Este é um exemplo da configuração da tarefa de Script. 30 10. Conecte uma restrição de precedência de falha da tarefa de Fluxo de Dados a outra tarefa de Script durante o tempo que você espera ser mais longo do que o problema esperado mais frequente ou mais preocupante. Para esse exemplo, usaremos 10 segundos para cobrir a possibilidade de limitação. Defina o valor da variável de êxito como false. 31 11. Para cada tarefa no loop FOR, defina a propriedade FailPackageOnFailure como false. Nessa configuração, somente quando o loop FOR falhar depois de usar todas as tentativas de repetição configuradas, ele relatará a falha e, portanto, será gerado um erro no pacote. 12. Configure tarefas Executar SQL após a falha para verificar novamente se há valores de chave mínimos na origem, e valores de chave máximos no destino, de forma que o progresso possa ser retomado sem ser necessário refazer nenhum trabalho. Defina isso conforme é descrito na subseção Projetando para reiniciar da seção Projetando para reiniciar sem perder o progresso do pipeline, anteriormente neste documento. 32 Toda vez que ocorrer um erro no fluxo de dados, se o número máximo de repetições não for atingido, o progresso voltará ao ponto de localização do valor de chave máximo no destino. O processo continua a partir do ponto que foi atualmente confirmado no destino. Se houver vários destinos no fluxo de dados, inicie depois que o menor valor de chave tiver chegado com segurança ao destino, e use uma divisão condicional ou um fluxo de erro para manipular todas as linhas que causariam uma violação de chave primária. Opções de ajuste de desempenho do SSIS Antes de iniciar uma discussão sobre dicas e truques em relação ao ajuste de desempenho do ETL, é preciso observar quais componentes e partes móveis você tem. Geralmente, o processo ETL consiste em um ou mais dos seguintes componentes: Fonte de dados – entrega os dados Pacote em execução no servidor SSIS – extrai dados da fonte de dados, de transformações de processos, se for necessário, e carrega dados no destino Destino – recebe os dados. Um destino geralmente é um banco de dados com as tabelas que recebem os dados. Todas essas partes fundamentais trabalham juntas pela interface de rede e passam dados entre si. Uma das primeiras etapas no ajuste de desempenho de ETL é assegurar que a rede possa proporcionar o melhor desempenho possível. Para obter mais informações sobre o servidor SSIS, consulte Servidor SSIS (Integration Services) (http://msdn.microsoft.com/pt-br/library/gg471508.aspx). Ajustando as configurações de rede Há duas camadas que podem afetar a maneira como os dados são transmitidos entre as partes que definimos acima: a configuração de rede física e as configurações de conexão. 33 Configurações de rede O quadro Ethernet define a quantidade de dados que pode ser transmitida pela rede de uma só vez. Cada quadro deve ser processado, o que exige certa utilização de recursos de hardware e software. Ao aumentar o tamanho do quadro onde o adaptador de rede oferece suporte, podemos enviar mais bytes com menos sobrecarga de CPU e aumentar a taxa de transferência reduzindo o número de quadros que precisam ser processados. Um quadro Ethernet pode carregar até 9.000 bytes. Isso é conhecido como um Quadro Jumbo. Para alternar o uso de Quadros Jumbo, você precisa alterar as configurações das NICs (placas de interface de rede). Conforme mostrado no exemplo a seguir, a propriedade MaxJumboBuffers está sendo definida como 8192 para permitir o uso de quadros jumbo. Observação: ao alterar as configurações da placa de interface de rede para usar quadros jumbo, verifique se a infraestrutura de rede pode oferecer suporte a esse tipo de quadro. O SQL Server pode aceitar até 32.676 bytes em um pacote de rede do SSIS. Em geral, se um aplicativo tiver um tamanho de pacote padrão diferente, esse valor padrão substituirá a configuração do SQL Server. Consequentemente, é recomendável definir a propriedade Tamanho do Pacote do gerenciador de conexões de destino do pacote SSIS para o tamanho de pacote padrão de aplicativo. Para editar essa propriedade, clique com o botão direito do mouse no gerenciador de conexões do Designer do SSIS e clique em Editar. Na caixa de diálogo Gerenciador de Conexões, clique em Tudo. 34 Configurações do pacote SSIS Além das configurações da cadeia de conexão, há outras configurações que talvez você queira ajustar para aumentar os recursos de processamento do SQL Server. O fluxo de dados do SSIS reserva buffers de memória para processar dados. Quando você tem servidores dedicados com vários núcleos e aumento de memória, as configurações de memória padrão para o SSIS podem ser frequentemente ajustadas para aproveitar melhor os recursos do servidor SSIS. Veja a seguir as configurações de memória do SSIS que devem ser consideradas no ajuste. 35 DefaultBufferSize DefaultBufferMaxRows EngineThreads DefaultBufferSize e DefaultBufferMaxRows estão relacionadas entre si. O mecanismo de fluxo de dados tenta estimar o tamanho da única linha de dados. Esse tamanho é multiplicado pelo valor armazenado em DefaultBufferMaxRows, e o mecanismo de fluxo de dados tenta reservar a parte apropriada da memória para o buffer. [Valor do tamanho do buffer] = [Tamanho da única linha de dados] x [DefaultBufferMaxRows] Se o valor do tamanho do buffer for maior que a configuração DefaultBufferSize, o mecanismo de fluxo de dados reduzirá o número de linhas de dados. Se o valor do tamanho do buffer mínimo calculado internamente for maior que o valor do tamanho do buffer, o mecanismo de fluxo de dados aumentará o número de linhas de dados. No entanto, o mecanismo não excede o valor DefaultBufferMaxRows. Quando você estiver ajustando as configurações DefaultBufferSize e DefaultBufferMaxRows, é recomendável prestar atenção em quais valores farão com que o mecanismo de fluxo de dados grave dados nos discos. A memória paginada para o disco do servidor SSIS afetará negativamente o desempenho da execução do pacote SSIS. Você pode observar o contador Buffers em spool para determinar se os buffers de dados estão sendo gravados temporariamente no disco quando um pacote está em execução. Para obter mais informações sobre contadores de desempenho para pacotes SSIS e como obter estatísticas do contador, consulte Contadores de Desempenho (http://msdn.microsoft.com/ pt-br/library/ms137622.aspx). 36 A configuração EngineThreads sugere ao mecanismo de fluxo de dados quantos threads pode ser usados para executar uma tarefa. Quando é usado um servidor de vários núcleos, é recomendável aumentar o valor padrão de 10. No entanto, o mecanismo não usará mais threads do que o necessário, independentemente do valor dessa propriedade. Se for necessário para evitar problemas de simultaneidade, o mecanismo também poderá usar mais threads do que o especificado na propriedade. Um bom ponto de partida para pacotes complexos é usar um mínimo de 1 Thread de Mecanismo por árvore de execução, embora não fique abaixo do valor padrão de 10. Para obter mais informações sobre as configurações, consulte Recursos de desempenho do fluxo de dados (http://msdn.microsoft.com/pt-br/library/ms141031.aspx). Considerações especiais sobre dados BLOB Quando houver mais dados em um conjunto de pipelines do SSIS do que cabem no buffer de pipeline pré-dimensionado, os dados serão armazenados no spool. Esse é um problema de desempenho principalmente quando você está lidando com dados BLOB, como XML, texto ou imagem. Quando os dados BLOB estiverem no pipeline, o SSIS definirá metade de um buffer para dados em linha e metade para dados BLOB. Os dados BLOB que não se ajustarem na metade do buffer serão armazenados no spool. Desse modo, você deve executar as ações a seguir para ajustar pacotes SSIS quando dados BLOB devem estar no pipeline: 1. Altere o valor de BLOBTempStoragePath e BufferTempStoragePath para apontar para uma unidade de alto desempenho. Por padrão, os objetos armazenados no spool serão gravados em arquivos temporários no diretório definido pelas variáveis de ambiente TEMP e TMP. Por padrão, esse diretório está localizado na unidade do sistema operacional. Normalmente, a unidade do sistema operacional não é uma unidade de alto desempenho. Para garantir que os arquivos de spool de dados temporários sejam gravados em uma unidade de alto desempenho, altere o valor de BLOBTempStoragePath nas propriedades da tarefa de Fluxo de Dados do SSIS para apontar para um diretório no armazenamento de alto desempenho. Assim como acontece com todas as propriedades no SSIS, você pode usar expressões para definir esse valor. 2. Dimensione DefaultBufferSize e DefaultBufferMaxRows para minimizar a ocorrência de spool. Como os discos, em geral, são o componente mais lento no servidor, e sua velocidade costuma ser muito mais lenta do que as velocidades do processador ou da memória, talvez você ache que o uso inadequado do espaço do buffer seja mais eficiente do que permitir o armazenamento no spool. Para minimizar o armazenamento no spool devido aos dados BLOB, use um método como o que está a seguir para determinar DefaultBufferSize e DefaultBufferMaxRows quando você tiver dados BLOB em seu fluxo de dados. a. Determine o MaxBufferSize. Uma vez que você terá dados BLOB em seu fluxo de dados, um bom ponto de partida pode ser o valor máximo permitido, que é de 100 MB ou 104857600 bytes. b. Divida esse número por 2. No exemplo, isso resulta em 52428800 bytes. Essa é a metade do buffer que pode conter dados BLOB. 37 c. Selecione um tamanho que você usará para o tamanho estimado de dados BLOB que serão processados nesse fluxo de dados. Um bom ponto de partida para esse tamanho é o comprimento médio + 2 desvios padrão do comprimento médio de todos os seus dados blob que estarão em um buffer. Esse valor conterá aproximadamente 98% de todos os dados BLOB. Como é provável que haja mais de uma linha em um único buffer SSIS, isso garantirá que quase não ocorra o armazenamento no spool. Se sua origem for o SQL Server, você poderá usar uma consulta como a que se segue para adquirir o comprimento SELECT CAST ( AVG(DATALENGTH(ColName)) + (2 * STDEV(DATALENGTH(Demographics))) AS INT ) AS Length FROM SchemaName.TableName Se a tabela for muito grande para permitir a consulta do conjunto de dados completo para a média e o desvio padrão, use um método como o descrito em Amostragem aleatória em T-SQL (http://msdn.microsoft.com/ptbr/library/aa175776(v=SQL.80).aspx) para localizar um exemplo a partir do qual localizar o comprimento a ser usado. d. Divida o número que você obteve na etapa b pelo número que obteve na etapa c. Use esse número ou um número um pouco menor que o valor de DefaultBufferMaxRows para sua tarefa de fluxo de dados. DICA: os valores de DefaultBufferMaxRows e MaxBufferSize podem ser configurados por meio de expressões. Você pode aproveitar isso para conjuntos de dados em que a natureza estatística do comprimento dos dados blob pode ser alterada com frequência ou para a criação de pacotes de modelo para definir esses valores em tempo de execução. Para tornar essa tarefa dinâmica, execute o procedimento a seguir. 1. Crie uma variável no nível de pacote. Nomeie a nova variável como DefaultMaxRowsInBuffer. Mantenha o tipo de dados como Int32. Você poderá criar uma variável semelhante se desejar definir a propriedade MaxBufferSize de modo dinâmico. 2. Use uma tarefa Executar SQL ou uma tarefa Script para localizar o valor que usará para DefaultBufferMaxRows. Armazene o valor que você calcular na variável DefaultMaxRowsInBuffer criada na etapa 1. OBSERVAÇÃO: para obter mais informações sobre como usar uma Tarefa Executar SQL para recuperar um único valor em uma variável SSIS, consulte Conjuntos de resultados na Tarefa Executar SQL (http://technet.microsoft.com/pt-br/library/cc280492.aspx). 3. Na caixa de propriedades da tarefa de Fluxo de Dados em que você deseja definir DefaultBufferMaxRows, selecione Expressões para abrir a caixa de diálogo Editor de Expressões de Propriedades. 38 4. No Editor de Expressões de Propriedades, escolha DefaultBufferMaxRows no menu suspenso Propriedade e clique no botão de reticências para abrir o Construtor de Expressões. 5. Arraste a variável criada na etapa 1 da lista Variáveis e Parâmetros no canto superior esquerdo para a caixa Expressão e clique em Avaliar Expressão para ver o valor padrão da variável na caixa Valor avaliado. 6. Clique em OK no Construtor de Expressões e, nas caixas de diálogo Editor de Expressões de Propriedades, salve a configuração. Nessa configuração, o valor das propriedades será definido em tempo de execução para minimizar as chances de spool de dados BLOB nos discos. Usando novos recursos no SSIS 2012 para monitorar o desempenho por um sistema distribuído Há novos recursos disponíveis no SQL Server 2012 para monitorar o desempenho dos projetos do SSIS (Integration Services) que você implanta no servidor SSIS. É possível registrar as informações de desempenho em tempo de execução para uma execução de pacote, exibir estatísticas da execução do pacote e monitorar o fluxo de dados do pacote. 39 Registrar estatísticas de desempenho Especifique o escopo das informações registradas em log durante uma execução de pacote selecionando um dos nível de log a seguir. Para registrar estatísticas de desempenho, selecione o desempenho ou os níveis detalhados de log. Nível de Log Nenhum Valor 0 Descrição O log está desativado. Apenas o status da execução do pacote é registrado em log. Todos os eventos são registrados em log, menos os personalizados e de diagnóstico. Esse é o valor padrão. Básico 1 Desempenho 2 Apenas estatísticas de desempenho, e os eventos OnError e OnWarning, são registrados em log. Detalhado 3 Todos os eventos são registrados em log, inclusive eventos personalizados e de diagnóstico. O Integration Services fornece um amplo conjunto de eventos personalizados para gravação de entradas de log para pacotes e diversas tarefas. Você pode usar essas entradas para salvar informações detalhadas sobre progresso de execução, resultados e problemas registrando eventos predefinidos ou mensagens definidas pelo usuário para análise posterior. Para obter mais informações, consulte Mensagens personalizadas para log (http://msdn.microsoft.com/pt-br/library/ms345174.aspx) Você pode especificar o nível de log seguindo um ou mais destes procedimentos para uma instância de execução de pacote. Defina parâmetros para uma instância de execução de pacote usando o procedimento armazenado catalog.set_execution_parameter_value (http://msdn.microsoft.com/ptbr/library/ff877990.aspx) Configure uma instância de execução de pacote usando a caixa de diálogo Executar Pacote Configure um trabalho do SQL Server Agent para uma execução de pacote usando a caixa de diálogo Nova Etapa do Trabalho. Para definir o nível de log usando a caixa de diálogo Executar Pacote 1. 2. 3. 4. 40 No SQL Server Management Studio, navegue até o pacote no Pesquisador de Objetos. Clique com o botão direito do mouse no pacote e selecione Executar. Selecione a guia Avançado. Em Nível de log, selecione o nível de log. Para definir o nível de log usando a caixa de diálogo Nova Etapa do Trabalho 1. Crie um novo trabalho expandindo o nó SQL Server Agent no Pesquisador de Objetos, clicando com o botão direito do mouse em Trabalhos e clicando em Novo Trabalho. - ou Modifique um trabalho existente expandindo o nó SQL Server Agent, clicando com o botão direito do mouse em um trabalho existente e clicando em Propriedades. 2. Clique em Etapas no painel esquerdo e clique em Novo para abrir a caixa de diálogo Nova Etapa do Trabalho. 3. Selecione Pacote do SQL Server Integration Services na caixa de listagem Tipo. 4. Na guia Pacote, selecione Catálogo do SSIS na caixa de listagem Origem do pacote, especifique o servidor e digite o caminho do pacote na caixa Pacote. 5. Na guia Configuração, clique em Avançado e selecione um nível de log na caixa de listagem Nível de Log. 6. Termine de configurar a etapa do trabalho e salve as alterações. Para definir o nível de log usando o procedimento armazenado catalog.set_execution_parameter_value, defina parameter_name como LOGGING_LEVEL e parameter_value para o valor de desempenho ou detalhado. O exemplo a seguir cria uma instância de uma execução do pacote Package.dtsx e define o nível de log como 2. O pacote está contido no projeto SSISPackages e o projeto está na pasta Pacotes. Declare @execution_id bigint exec catalog.create_execution 'Packages', 'SSISPackages', 'Package.dtsx', NULL, 1, @execution_id output exec catalog.set_execution_parameter_value @execution_id, 50, 'LOGGING_LEVEL', 2 Exibir estatísticas da execução As exibições de banco de dados e os procedimentos armazenados do SSISDB, bem como os relatórios padrão que estão disponíveis no SQL Server Management Studio, fornecem um leque de informações sobre execuções de pacote e informações que são relevantes para as execuções. Uma execução é uma instância de execução de pacote. Entre os relatórios padrão, os relatórios Painel do Integration Services, Todas as Execuções e Todas as Conexões são particularmente úteis para exibir informações de execução do pacote. O relatório Painel do Integration Services fornece as informações a seguir para os pacotes que estão em execução ou concluíram a execução nas últimas 24 horas. 41 Relatório Painel do Integration Services Seção do relatório Informações de Execução Informações do Pacote Informações de Conexão Informações Detalhadas do Pacote Descrição Mostra o número de execuções que estão em estados diferentes (com falha, em execução, bem-sucedida, outros) Mostra o número total de pacotes que foram executados Mostra as conexões que foram usadas em execuções com falha Para cada pacote, mostra os detalhes das execuções concluídas. Por exemplo, você verá o número de execuções com falha em comparação com o número total de execuções, a duração das execuções (em segundos) e a duração média das execuções nos últimos três meses. Você pode exibir informações de execução adicionais de um pacote clicando em Desempenho de Execução, Visão geral e Todas as Mensagens. O relatório Desempenho de Execução representa em gráfico a duração das 10 últimas execuções bem-sucedidas, bem como o Tempo Ativo e o Tempo Total dos componentes de fluxo de dados do pacote. O tempo ativo se refere ao tempo total que o componente gastou na execução em todas as fases, e o tempo total se refere ao tempo total decorrido para um componente. O relatório exibe essas informações para componentes de fluxo de dados apenas quando o nível de log da última execução do pacote foi definido como Desempenho ou Detalhado. O relatório de Visão Geral mostra o estado de tarefas do pacote. O relatório Mensagens mostra as mensagens de evento e as mensagens de erro para o pacote e as tarefas, como relatar as horas de início e de término, e o número de linhas gravadas. 42 O relatório Todas as Execuções fornece as informações a seguir para execuções que foram realizadas na instância do SQL Server conectada. Pode haver várias execuções do mesmo pacote. Ao contrário do relatório Painel do Integration Services, você pode configurar o relatório Todas as Execuções para mostrar as execuções iniciadas durante um intervalo de datas. As datas podem abranger vários dias, meses ou anos. Relatório Todas as Execuções Seção do relatório Filtro Informações de Execução Descrição Mostra o filtro atual aplicado ao relatório, como o intervalo de horas de início. Mostra a hora de início, a hora de término e a duração de cada execução de pacote. Você pode exibir uma lista de valores de parâmetros que foram usados com uma execução de pacote, como valores que foram transmitidos a um pacote filho usando a tarefa Executar Pacote. Para exibir a lista de parâmetros, clique em Visão Geral. 43 O relatório Todas as Conexões fornece as informações a seguir para as conexões que falharam nas execuções que ocorreram na instância do SQL Server. Seção do relatório Filtro Detalhes 44 Descrição Mostra o filtro atual aplicado ao relatório, como conexões com uma cadeia de caracteres especificada e o intervalo de Hora da Última Falha. Você define o intervalo de Hora da Última Falha para exibir apenas as falhas de conexão que ocorreram durante um intervalo de datas. O intervalo pode abranger vários dias, meses ou anos. Mostra a cadeia de conexão, o número de execuções em que uma conexão falhou e a data da última falha na conexão. Além de exibir os relatórios padrão disponíveis no SQL Server Management Studio, você também pode consultar as exibições de banco de dados SSISDB para obter informações semelhantes sobre as execuções de pacote. A tabela a seguir descreve as principais exibições. Exibição do Banco de dados SSISDB catalog.executable_statistics (http://msdn.microsoft.com/ptbr/library/hh479592.aspx) Descrição Exibe uma linha para cada executável que é executado, inclusive cada iteração de um executável. Um executável é uma tarefa ou um contêiner que você adiciona ao fluxo de controle de um pacote. Por exemplo, a exibição mostra o período de tempo que o executável gastou na execução, as horas em que o executável entra nas fases de pré-execução e pós-execução, bem como o resultado da execução do executável, como o êxito ou falha. catalog.executions (http://msdn.microsoft.com/ptbr/library/ff878089.aspx) Um exemplo de resultado de execução é o código de retorno do procedimento armazenado que é executado por uma Tarefa Executar SQL. Para obter mais informações, consulte Parâmetros e códigos de retorno na Tarefa Executar SQL. Exibe informações sobre execuções de pacotes. Os pacotes que são executados com a tarefa Executar Pacote, são executados na mesma execução que o pacote pai. Por exemplo, a exibição mostra o status de uma execução (em execução, com falha, bem-sucedida, entre outros), a memória física total e a memória física disponível no servidor quando a execução é iniciada, bem como o total de memória de página e a memória de página disponível quando a execução é iniciada. catalog.execution_component_phases Exibe as horas de início e de término para os componentes (http://msdn.microsoft.com/ptde fluxo de dados, para cada fase de uma execução. Essas br/library/hh230981.aspx) informações são exibidas para várias execuções do pacote. catalog.event_messages (http://msdn.microsoft.com/ptbr/library/hh479594.aspx) A exibição mostra essas informações para componentes de fluxo de dados apenas quando o nível de log da execução é definido como Desempenho ou Detalhado. Exibe informações sobre mensagens que foram registradas em log durante operações como a criação e o início de uma execução de pacote. O valor para esse tipo de operação é 200. Por exemplo, a exibição mostra o texto da mensagem, o componente do pacote e o componente de fluxo de dados que são a origem da mensagem, bem como o evento associado à mensagem. As mensagens que a exibição mostra para uma execução de pacote dependem da configuração do nível de log da execução. 45 Exibição do Banco de dados SSISDB catalog.event_message_context (http://msdn.microsoft.com/ptbr/library/hh479590.aspx) Descrição Exibe informações sobre as condições que são associadas às mensagens de eventos de execução. Por exemplo, a exibição mostra o objeto associado à mensagem de evento, como um valor de variável ou uma tarefa, e o nome e o valor da propriedade que estão associados à mensagem de evento. A exibição também mostra a ID de cada mensagem de evento. Você pode localizar informações adicionais sobre uma mensagem de evento específica consultando a exibição catalog.event_messages. É possível usar a exibição catalog.execution_component_phases para calcular o tempo gasto na execução em todas as fases (tempo ativo) e o tempo total decorrido (tempo total) para componentes do pacote. Isso pode ajudar a identificar os componentes que podem ser executados mais lentamente do que o esperado. Essa exibição é populada quando o nível de log da execução de pacote é definido como Desempenho ou Detalhado. Para obter mais informações, consulte Estatísticas de desempenho do log neste artigo. No exemplo a seguir, o tempo ativo e o tempo total são calculados para os componentes em execução com a ID 33. As funções sum e DATEDIFF são usadas no cálculo. Declare @execution_id bigint Set @execution_id = 33 select package_name, task_name, subcomponent_name, execution_path, sum (DATEDIFF(ms, start_time, end_time)) as active_time, DATEDIFF(ms,min(start_time), max(end_time)) as total_time from catalog.execution_component_phases where execution_id = @execution_id group by package_name, task_name, subcomponent_name, execution_path order by active_time desc 46 Por fim, é possível usar a função dm_execution_performance_counters para obter estatísticas de contadores de desempenho, como o número de buffers usados e o número de linhas lidas e gravadas para uma execução em andamento. No exemplo a seguir, a função retorna as estatísticas para uma execução com a ID 34. select * from [catalog].[dm_execution_performance_counters] (34) No exemplo a seguir, a função retorna as estatísticas de todas as execuções em andamento. select * from [catalog].[dm_execution_performance_counters] (NULL) Monitorar fluxo de dados O Integration Services inclui recursos e ferramentas que você pode usar para solucionar problemas do fluxo de dados em um pacote durante a execução. Tocando o fluxo de dados durante a execução O SSIS (SQL Server Integration Services) no SQL Server 2012 apresenta um novo recurso que permite adicionar um toque de dados em um caminho de fluxo de dados de um pacote em tempo de execução e direcionar a saída do toque de dados para um arquivo externo. Para usar esse recurso, você deve implantar seu projeto SSIS usando o modelo de implantação de projeto em um servidor SSIS. Depois de implantar o pacote no servidor, você precisará executar scripts T-SQL no banco de dados SSISDB para adicionar toques de dados antes da execução do pacote. Veja um cenário de exemplo: 1. Crie uma instância de execução de um pacote usando o procedimento armazenado catalog.create_execution (http://msdn.microsoft.com/pt-br/library/ff878034). 47 2. Adicione um toque de dados usando o procedimento armazenado catalog.add_data_tap (http://msdn.microsoft.com/pt-br/library/hh230989) ou catalog.add_data_tap_by_guid (http://msdn.microsoft.com/pt-br/library/hh230991). 3. Inicie a instância de execução do pacote usando catalog.start_execution (http://msdn.microsoft.com/pt-br/library/ff878160). Veja um exemplo de script SQL que executa as etapas descritas no cenário anterior: Declare @execid bigint EXEC [SSISDB].[catalog].[create_execution] @folder_name=N'ETL Folder', @project_name=N'ETL Project', @package_name=N'Package.dtsx', @execution_id=@execid OUTPUT EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt' EXEC [SSISDB].[catalog].[start_execution] @execid Os parâmetros folder_name, project_name e package_name do procedimento armazenado create_execution correspondem aos nomes de pasta, projeto e pacote no catálogo do Integration Services. Você pode obter os nomes de pasta, projeto e pacote a serem usados na chamada a create_execution no SQL Server Management Studio, como mostra a imagem a seguir. Se você não visualizar seu projeto SSIS aqui, talvez não tenha implantado o projeto no servidor SSIS ainda. Clique com o botão direito do mouse no projeto SSIS no Visual Studio e clique em Implantar para implantar o projeto no servidor SSIS esperado. 48 Em vez de digitar as instruções SQL, é possível gerar o script de pacote de execução seguindo estas etapas: 1. Clique com o botão direito do mouse em Package.dtsx e clique em Executar. 2. Clique no botão da barra de ferramentas Script para gerar o script. 3. Agora, adicione a instrução add_data_tap antes da chamada a start_execution. O parâmetro task_package_path do procedimento armazenado add_data_tap corresponde à propriedade PackagePath da tarefa de fluxo de dados no Visual Studio. No Visual Studio, clique com o botão direito do mouse na Tarefa de Fluxo de Dados e clique em Propriedades para iniciar a janela Propriedades. Observe o valor da propriedade PackagePath para usá-la como um valor para o parâmetro task_package_path da chamada de procedimento armazenado add_data_tap. O parâmetro dataflow_path_id_string do procedimento armazenado add_data_tap corresponde à propriedade IdentificationString do caminho de fluxo de dados ao qual você deseja adicionar um toque de dados. Para obter dataflow_path_id_string, clique no caminho de fluxo de dados e observe o valor da propriedade IdentificationString na janela Propriedades. 49 Quando você executa o script, o arquivo de saída é armazenado em <Arquivos de Programas>\Microsoft SQL Server\110\DTS\DataDumps. Se um arquivo com o mesmo nome já existir, um novo arquivo com um sufixo (por exemplo: output[1].txt) será criado. Conforme mencionado anteriormente, você também pode usar o procedimento armazenado catalog.add_data_tap_by_guid (http://msdn.microsoft.com/pt-br/library/hh230991), em vez de usar o procedimento armazenado add_data_tap. Esse procedimento armazenado assume a ID da tarefa de fluxo de dados como um parâmetro no lugar de task_package_path. Você pode obter a ID da tarefa de fluxo de dados na janela de propriedades no Visual Studio. Removendo um toque de dados Você pode remover um toque de dados antes de iniciar a execução usando o procedimento armazenado catalog.remove_add_data_tap. Esse procedimento armazenado usa a ID do toque de dados como um parâmetro, que você obtém como uma saída do procedimento armazenado add_data_tap. DECLARE @tap_id bigint EXEC [SSISDB].[catalog].add_data_tap @execution_id = @execid, @task_package_path = '\Package\Data Flow Task', @dataflow_path_id_string = 'Paths[Flat File Source.Flat File Source Output]', @data_filename = 'output.txt' @data_tap_id=@tap_id OUTPUT EXEC [SSISDB].[catalog].remove_data_tap @tap_id 50 Listando todos os toque de dados Você também pode listar todos os toques de dados usando a exibição catalog.execution_data_taps. O exemplo a seguir extrai toques de dados para uma instância de execução de especificação (ID: 54). select * from [SSISDB].[catalog].execution_data_taps where execution_id=@execid Considerações sobre desempenho Habilitar o nível de log detalhado e adicionar toques de dados aumentam as operações de E/S executadas por sua solução de integração de dados. Portanto, é recomendável adicionar toques de dados somente para fins de solução de problemas. Monitorando o fluxo de dados durante a execução Você pode usar a exibição de banco de dados SSISDB catalog.execution_data_statistics (http://msdn.microsoft.com/pt-br/library/hh230986.aspx) para analisar o fluxo de dados dos pacotes. Essa exibição mostra uma linha cada vez que um componente de fluxo de dados envia dados a um componente downstream. As informações podem ser usadas para proporcionar uma compreensão melhor das linhas que são enviadas a cada componente. Observação: o nível de log deve ser definido como Detalhado para capturar informações com a exibição catalog.execution_data_statistics. O exemplo a seguir exibe o número de linhas enviadas entre componentes de um pacote. Execution_id é a ID de uma instância de execução que você pode obter como um valor de retorno do procedimento armazenado create_execution ou da exibição catalog.executions. use SSISDB select package_name, task_name, source_component_name, destination_component_name, rows_sent from catalog.execution_data_statistics where execution_id = 132 order by source_component_name, destination_component_name 51 O exemplo a seguir calcula o número de linhas por milissegundo enviadas por cada componente para uma execução específica. Os valores calculados são: total_rows – a soma de todas as linhas enviadas pelo componente wall_clock_time_ms – o tempo total de execução decorrido, em milissegundos, para cada componente num_rows_per_millisecond – o número de linhas, por milissegundos, enviadas por cada componente A cláusula HAVING é usada para evitar um erro de divisão por zero nos cálculos. use SSISDB select source_component_name, destination_component_name, sum(rows_sent) as total_rows, DATEDIFF(ms,min(created_time),max(created_time)) as wall_clock_time_ms, ((0.0+sum(rows_sent)) / (datediff(ms,min(created_time),max(created_time)))) as [num_rows_per_millisecond] from [catalog].[execution_data_statistics] where execution_id = 132 group by source_component_name, destination_component_name having (datediff(ms,min(created_time),max(created_time))) > 0 order by source_component_name desc Conclusão O SSIS (SQL Server Integration Services) pode ser usado de forma eficiente como uma ferramenta para mover dados para dentro e fora do Banco de dados SQL do Windows Azure (WA), como parte da solução de ETL (extração, transformação e carregamento) total e como parte da solução de movimentação de dados. Pode ser usado também para mover dados entre origens e destinos na nuvem, bem como em um cenário híbrido entre os ambientes em nuvem e local. Este documento descreveu as práticas recomendadas do SSIS para origens e destinos na nuvem, discutiu o planejamento para projetos SSIS se o projeto estiver todo na nuvem ou envolver movimentações de dados híbridas e, usando um exemplo, explicou como maximizar o desempenho em uma movimentação híbrida expandindo a movimentação de dados. 52 Para obter mais informações: http://www.microsoft.com/sqlserver/: Site do SQL Server http://technet.microsoft.com/pt-br/sqlserver/: TechCenter do SQL Server http://msdn.microsoft.com/pt-br/sqlserver/: SQL Server DevCenter Este documento ajudou você? Envie seus comentários. Em uma escala de 1 (ruim) a 5 (excelente), como você classificaria este white paper e por quê? Por exemplo: Você está classificando-o como excelente devido aos bons exemplos, às excelentes capturas de tela, à clareza do texto ou por outro motivo? Você está classificando-o como ruim devido aos exemplos insatisfatórios, às capturas de tela confusas ou ao texto mal-escrito? Estes comentários nos ajudam a melhorar a qualidade dos white papers que lançamos. Enviar comentários. 53
