SQLServerAlemDoConceitoVol2-dmzasn

Prévia do material em texto

Editor: Demétrio Silva 
Introdução: Diego Nogare, Edvaldo Castro 
Prefácio: Roberto Fonseca 
Capa: Felipe Borges 
Autores: Diego Nogare Edvaldo Castro Leonardo Pedroso Sulamita Dantas Demétrio Silva Thiago 
Alencar Nilton Pinheiro Luciano Moreira Vitor Fava Murilo Miranda Marcelo Fernandes Cibelle 
Cadastro 
 
Introdução 
Estar envolvido em uma comunidade é sempre mais importante e proveitoso de que viver de um modo 
isolado, seja nos âmbitos pessoais, profissionais e quaisquer outros. A comunidade técnica de 
profissionais que trabalham com produtos e ferramentas ligados à Plataforma de Dados da Microsoft é 
bem atuante e unida, isso faz com que muito conteúdo seja gerado, seja com vídeos, eventos, palestras, 
entrevistas e postagens em blogs. 
O segundo volume da série "SQL Server Além do Conceito" traz agora um conjunto multidisciplinar de 
capítulos originais, escritos exclusivamente para esta obra. Mais uma vez um grupo de profissionais de 
diversas áreas do SQL Server se juntaram e escreveram capítulos em suas áreas de domínio. Baixe agora 
sua cópia e mergulhe no mundo a partir da visão destes autores que dedicaram seu tempo para escrever 
sobre Administração, Desenvovimento e Business Intelligence. 
Um ponto importante a ser mencionado, este trabalho por ser voluntário e não ter um cunho com fins 
financeiros, teve uma tratativa diferente mas não menos cuidadosa com relação à um livro ordinário. Cada 
autor é o responsável direto pelo que escreveu e publicou na coletânea, mas todos estão juntos para 
proporcionar e compartilhar o conhecimento que adquiriram ao longo de vários anos de experiência. 
Aproveite bem, e quaisquer necessidades de contato, não hesite em nos enviar uma mensagem 
sqlalemdoconceito@outlook.com. 
Boa Leitura, 
Diego Nogare 
http://www.diegonogare.net/ 
Edvaldo Castro 
http://edvaldocastro.com/ 
Demétrio Silva 
https://demetriosilva.wordpress.com/ 
 
mailto:sqlalemdoconceito@outlook.com
http://www.diegonogare.net/
http://edvaldocastro.com/
https://demetriosilva.wordpress.com/
Prefácio 
Desde as suas primeiras versões, o SQL Server continuamente tem se estabelecido como uma 
plataforma confiável e robusta com performance, escalabilidade e confiabilidade que atende as todas as 
necessidades de negócio desde pequenas empresas até grandes corporações. AS novidades do SQL Server 
consolidam esta posição no mercado como um banco de dados de alta performance, confiável e robusto. 
Um dos maiores desafios em escrever o livro SQL Server – Além do Conceito – Vol 2 é como trazer os 
assuntos de maior interesse do público, de uma maneira compreensiva e com foco em compartilhar o 
conhecimento. Por este motivo, alguns dos maiores profissionais de SQL Server no Brasil se reuniram 
novamente para escrever este novo volume tratando dos assuntos mais interessantes sobre SQL Server! 
A principal decisão foi escrever este livro com foco nas funcionalidades essenciais sob os aspectos 
administrativos e de gerenciamento no dia-a-dia de todo o DBA. 
Neste livro, você terá oportunidade de conhecer mais sobre Segurança, com um capítulo 
especialmente dedicado ao Gerenciamento de Logon. Como monitorar eficientemente seu ambiente e 
como configurar o seu ambiente são dois capítulos que valem a pena ler com atenção! Não poderíamos 
deixar de lembrar os capítulos especialmente dedicados às ferramentas de Business Intelligence e Azure, 
como os capítulos dedicados ao Power BI, HDInsight e In-Memory OLTP! 
Aproveite esta leitura! Aproveite ao máximo o conhecimento transmitido pelos autores. Tenho a 
certeza de que contribuirá consideravelmente em seu conhecimento! 
Fizemos os melhores esforços para garantir que não haja nenhum erro neste livro, mas se você 
encontrar algum, por favor, nos informe pelo email sqlalemdoconceito@outlook.com. 
Roberto Fonseca 
https://rffonseca.wordpress.com/ 
mailto:sqlalemdoconceito@outlook.com
https://rffonseca.wordpress.com/
MVTech 
O Minha Vida (http://www.minhavida.com.br) é uma empresa com um grande propósito: melhorar a 
qualidade de vida da população. Queremos ser capazes de despertar nas pessoas o cuidado com a sua 
própria saúde. E quando falamos em saúde, nos referimos não só à prevenção ou ao tratamento de 
doenças, mas também a pequenas mudanças de hábitos capazes de transformar positivamente o dia a 
dia das pessoas. 
Queremos fazer diferença na vida das pessoas! 
Pensando nisso, nosso time de tecnologia criou o MVTech, uma iniciativa para disseminar 
conhecimento através de iniciativas de seus colaboradores, buscando o aprimoramento do mercado 
nacional. 
Com diversas ações como: artigos técnicos em blogs; matérias para portais; respostas em foruns de 
discussão, palestras em eventos, eventos presenciais e online, etc… afinal, somos apaixonados pelo que 
fazemos! por que não compartilhar nossa paixão? 
A criação do MVTech dá um passo adiante nesta linha de compartilhamento de conhecimento, 
passando a existir um canal oficial onde poderão ser encontrados grandes conteúdos. 
Seja bem vindo ao MVTech 
Alexandre Tarifa 
Diretor de Tecnologia – Minha Vida 
 
http://www.minhavida.com.br/
Índice 
Integrando SSIS com HDInsight e Azure Blob Storage ......................................................................... 11 
Introdução ....................................................................................................................................... 11 
Instalando e entendendo os componentes no SSIS ......................................................................... 12 
Instalação ..................................................................................................................................... 12 
Componentes............................................................................................................................... 12 
Instalando o MakeCert .................................................................................................................... 15 
Criando um certificado digital .......................................................................................................... 17 
Criando o pacote e se comunicando com o Azure ........................................................................... 19 
Azure Blob Upload Task ............................................................................................................... 19 
Azure Blob Download Task .......................................................................................................... 22 
Azure HDInsight Create Cluster Task ............................................................................................ 24 
Azure HDInsight Delete Cluster Task ............................................................................................ 29 
Azure HDInsight Hive Task ........................................................................................................... 30 
Criando a sequência de execução ................................................................................................ 33 
Recursos .......................................................................................................................................... 36 
Criação de Logon no SQL Server .......................................................................................................... 37 
Modos de Autenticação no SQL Server: ........................................................................................... 37 
Windows Authentication Mode ................................................................................................... 37 
Mixed Mode ................................................................................................................................. 38 
Papel do Servidor ............................................................................................................................. 39 
Funções de Banco de Dados ............................................................................................................40 
Permissões ....................................................................................................................................... 42 
GRANT.......................................................................................................................................... 42 
DENY ............................................................................................................................................ 42 
REVOKE ........................................................................................................................................ 42 
Logins ............................................................................................................................................... 43 
Criando um login com um domínio no Windows via Transact – SQL ........................................... 44 
Criando um login com um domínio no Windows via ferramenta SSMS: ...................................... 46 
Criando um login do próprio SQL Server via Transact – SQL ........................................................ 49 
Para excluir o login pelo SSMS ..................................................................................................... 51 
Manipulação dos Usuários via Transact SQL .................................................................................... 52 
Algumas opções para a criação do usuário .................................................................................. 52 
Conclusão ........................................................................................................................................ 60 
Column Store Index ............................................................................................................................. 61 
Necessidade de Negócios ................................................................................................................ 61 
Aonde utilizar? ................................................................................................................................. 62 
Aonde não utilizar/evitar!! .............................................................................................................. 63 
Armazenamento Atual ..................................................................................................................... 64 
CSI VS B-Tree .................................................................................................................................... 66 
Archival Compression ...................................................................................................................... 67 
Column Store - Clustered e Non Clustered .................................................................................. 67 
Row Groups e Segmentos ............................................................................................................ 68 
Conclusão ........................................................................................................................................ 73 
Referências ...................................................................................................................................... 74 
Lock Escalation no SQL Server ............................................................................................................. 75 
Introdução ....................................................................................................................................... 75 
Script 01 – 10M atualizações sem o comportamento de lock escalation ..................................... 75 
Script 02 – 20M atualizações sem o comportamento de lock escalation ..................................... 77 
Script 03 – 20M atualizações com o comportamento de lock escalation habilitado ................... 78 
Lock Escalation – limites e funcionamento ...................................................................................... 79 
Script 04 – xEvents para monitorar lock escalation ..................................................................... 80 
Script 05 – Análise do threshold para lock escalation .................................................................. 81 
Cenários que envolvem o lock escalation ........................................................................................ 82 
Cenário 01 .................................................................................................................................... 82 
Cenário 02 .................................................................................................................................... 82 
Cenário 03 .................................................................................................................................... 83 
Cenário 04 .................................................................................................................................... 83 
Conclusão ........................................................................................................................................ 85 
Como monitorar seu ambiente SQL Server de forma eficiente e proativa .......................................... 86 
O que é o Data Collector? ................................................................................................................ 86 
Arquitetura do Data Collector .......................................................................................................... 87 
Management Data Warehouse (MDW) ........................................................................................... 88 
Configurando o Data Collector......................................................................................................... 91 
Visualizando as métricas coletadas .................................................................................................. 94 
Server Activity History.................................................................................................................. 94 
Disk Usage Summary.................................................................................................................... 96 
Query Statistics History ................................................................................................................ 97 
Recomendações para configuração do DC ....................................................................................... 99 
Conclusão ...................................................................................................................................... 100 
Pensando fora da caixa – SQL Server FCI usando o File Share (SMB3.0) como opção de Storage ..... 101 
Tipos de Storage para SQL Server .................................................................................................. 101 
Storage HDD vs SSD ....................................................................................................................... 102 
Comparativo de Desempenho de I/O ............................................................................................ 103 
Escolhendo a Storage baseado no Workload ................................................................................. 105 
SMB 3SMB 3File Shares ................................................................................................................. 106 
SQL Server FCI usando o File Share (SMB3.0) como opção de Storage .......................................... 107 
SMB Transparent Failover .............................................................................................................. 109 
SMB Direct (SMB sobre RDMA) ..................................................................................................... 110 
SMB Scale-out ................................................................................................................................ 111 
SMB Multichannel .........................................................................................................................112 
SQL Server FCI usando o File Share (SMB3.0) como opção de Storage .......................................... 116 
Configurando o Scale-Out File Server (SOFs) ............................................................................. 117 
Instalando o SQL Server em cluster sobre SMB ......................................................................... 129 
Verificando o SMB ..................................................................................................................... 146 
AlwayOn Availability Groups - Conceitos e Cenários ........................................................................ 151 
Introdução ..................................................................................................................................... 151 
SQL Server Failover Cluster Instances (FCI) ................................................................................ 152 
Database Mirroring .................................................................................................................... 153 
Log Shipping ............................................................................................................................... 154 
Replicação¹ ................................................................................................................................. 155 
AlwaysOn ................................................................................................................................... 155 
Cenários ..................................................................................................................................... 158 
O SQL Server AlwaysOn AG é sempre a melhor escolha? .......................................................... 158 
Benefícios de utilização do AG ................................................................................................... 159 
Pontos “negativos” do AG .......................................................................................................... 159 
Conclusão ...................................................................................................................................... 160 
Referências deste capítulo: ............................................................................................................ 161 
Boas práticas em configuração de servidores de banco de dados ..................................................... 162 
Introdução ..................................................................................................................................... 162 
Ajuste do Allocation Unit Size nos discos do Windows .................................................................. 162 
Desabilitar o usuário SA ................................................................................................................. 164 
Configuração do banco de dados TEMPDB .................................................................................... 165 
Paralelismo .................................................................................................................................... 167 
Configuração de memória ............................................................................................................. 168 
Compressão de backup .................................................................................................................. 168 
Configurar o Blocked Process Threshold ........................................................................................ 170 
Ajustar os arquivos dos bancos Master, Model e Msdb Master .................................................... 172 
Aumentar a quantidade de arquivos do ERRORLOG ...................................................................... 173 
Configurar a opção Instant File Initialization.................................................................................. 173 
Configurar a conexão DAC ............................................................................................................. 175 
Desabilitar o XP_CMDSHELL .......................................................................................................... 176 
Conclusão ...................................................................................................................................... 178 
Criando dashboards com o Power BI ................................................................................................. 179 
Introdução ..................................................................................................................................... 179 
Configuração do Ambiente ............................................................................................................ 180 
Criação do database................................................................................................................... 180 
Criação da conta no Power BI .................................................................................................... 184 
Configuração do Gateway .......................................................................................................... 190 
Power BI Desktop ....................................................................................................................... 200 
OneDrive .................................................................................................................................... 210 
Criação dos relatórios e dashboards .............................................................................................. 214 
Importação dos dados ............................................................................................................... 214 
Atualização dos dados ............................................................................................................... 216 
Criação dos relatórios ................................................................................................................ 224 
Usando o Q&A ............................................................................................................................... 231 
Insights........................................................................................................................................... 234 
Conclusão ...................................................................................................................................... 237 
Aumentando a Disponibilidade do SQL Server Failover Cluster com o Dynamic Quorum ................. 238 
Entendendo a Necessidade do Quórum ........................................................................................ 238 
Os modelos de Quórum ................................................................................................................. 240 
Quórum e Witness Dinâmico ......................................................................................................... 243 
Quorum Dinâmico ...................................................................................................................... 244 
Witness Dinâmico ...................................................................................................................... 246 
Mão na Massa: Configurando o Quórum do Cluster ...................................................................... 250 
Conclusão ...................................................................................................................................... 254 
In-Memory OLTP 2016: O relançamento de uma potência ............................................................... 255 
In-Memory OLTP: o conceito e as motivações ............................................................................... 256 
O que tanto mudou do SQL Server 2014 para o 2016? .................................................................. 259 
Tamanho total suportado para tabelas in-memory ...................................................................259 
Mais escalabilidade na gestão de storage .................................................................................. 259 
O Garbage collector e sua relação com os checkpoint files ....................................................... 260 
Mais opções a nível de segurança .............................................................................................. 260 
Paralelismo ................................................................................................................................ 260 
AlwaysOn Availability Groups .................................................................................................... 261 
Manutenção de código e T-SQL Suportado ................................................................................ 261 
Ferramentas ............................................................................................................................... 262 
Conclusão ...................................................................................................................................... 263 
Deadlock no SQL Server ..................................................................................................................... 264 
Introdução ..................................................................................................................................... 264 
Definição Lock ................................................................................................................................ 265 
Lock Modes .................................................................................................................................... 265 
Shared Lock (S) ........................................................................................................................... 266 
Exclusive Lock (X) ....................................................................................................................... 266 
Update Lock ............................................................................................................................... 266 
Intent Lock ................................................................................................................................. 267 
Schema Lock .............................................................................................................................. 267 
Deadlock ........................................................................................................................................ 268 
Monitorando Deadlock .............................................................................................................. 270 
Conclusão ...................................................................................................................................... 286 
Referências .................................................................................................................................... 287 
 
Integrando SSIS com HDInsight e Azure Blob Storage 
Veja neste capítulo como util izar o SQL Server Integration Services para se comunicar 
com o HDInsight e o Azure Blob Storage, possibilitando criar Cluster de Hadoop, subir, 
processar e baixar arquivos do Azure Blob Storage. 
Por Diego Nogare 
http://www.diegonogare.net/ 
Revisão técnica por Ivan Lima Filho 
http://ivanglima.com/sql-server-nuvem/ 
Introdução 
O conector do Azure dentro do SSIS permite se comunicar com alguns recursos da plataforma de Cloud 
Computing da Microsoft para que arquivos sejam movimentados entre o computador de execução do 
pacote e um servidor no Azure, mais especificamente para dentro de um Blob Storage, ou então para criar 
e apagar clusters do HDInsight. Isso permite automatizar a ação de processamento em clusters de Hadoop 
através da plataforma da Microsoft, garantindo o máximo de economia pela possibilidade de desligar o 
cluster após seu uso, pagando apenas pelo uso real e não por um ambiente ocioso. 
Neste capítulo você aprenderá como instalar este componente no seu SSIS 2014, que já é nativo no 
SQL 2016. Depois de instalado, acompanhará a criação de um cenário fictício no qual será enviado para o 
Azure Blob Storage um arquivo de texto com alguns dados, em paralelo será disparado o comando para 
criar o cluster do HDInsight. Em seguida, uma consulta é disparada contra o arquivo enviado e um retorno 
é baixado para o local que está executando o pacote. Para finalizar, o cluster do HDInsight será destruído. 
O que você precisará para concluir este capítulo: 
 SQL Server Data Tools 2013; 
 Subscription ativa no Azure; 
 SSIS feature pack para Azure. 
 
http://www.diegonogare.net/
http://ivanglima.com/sql-server-nuvem/
Instalando e entendendo os componentes no SSIS 
Instalação 
Faça o download do Microsoft SQL Server 2014 Integration Services Feature Pack for Azure para ter 
acesso às tarefas que permitem a movimentação de dados e gerenciamento de atividades com o Azure. 
O link para download está na seção de Recursos, no final do capítulo. Após o término do download, faça 
a instalação padrão clicando em avançar até terminar. Ao final, abra o SQL Server Data Tools e confirme 
que os componentes estão disponíveis no SSIS Toolbox do Data Flow. 
Se os componentes abaixo estiverem disponíveis, a instalação aconteceu com sucesso. 
 
Componentes 
Estes são os componentes do Data Flow que foram adicionados à Toolbox do SSIS e suas principais 
configurações: 
Azure Blob Download Task: Permite que o SSIS faça download de arquivos que estão no Azure Blob 
Storage. É necessário informar: 
 AzureStorageConnection: Especifica ou cria uma conexão para um contêiner com o Azure Storage 
Account; 
 BlobContainer: O nome do contêiner que possui os arquivos a serem baixados; 
 LocalDirectory: Endereço local onde o arquivo será baixado; 
 FileName: Especifica o nome do arquivo completo, ou em partes, utilizando o caractere ‘*’ como 
wildcard (como é feito no Prompt de Comando) 
Azure Blob Upload Task: Permite que o SSIS faça upload de arquivos para uma conta no Azure Blob 
Storage. A configuração é semelhante à do componente Azure Blob Download Task. 
Azure HDInsight Create Cluster Task: Possibilita que sejam criados clusters do HDInsight em uma 
subscription do Azure. É necessário configurar: 
 AzureSubscriptionConnection: Especifica ou cria uma conexão para uma subscription do Azure que 
receberá o cluster do HDInsight; 
 AzureStorageConnection: Especifica ou cria uma conexão para um contêiner com o Azure Storage 
Account; 
 Location: É o local (região do Azure) que deseja armazenar o cluster. Deve ser o mesmo lugar 
escolhido no Storage; 
 ClusterName: Informa o nome do cluster do HDInsight que vai criar; 
 ClusterSize: A quantidade de nós do cluster; 
 BlobContainer: O nome do contêiner que possui os arquivos a serem trabalhados; 
 UserName: Espeficica o usuário que será o administrador do cluster; 
 Password: A senha para este usuário; 
Azure HDInsight Delete Cluster Task: Esta tarefa permite apaga um cluster de uma subscription. É 
necessário informar: 
 AzureSubscriptionConnection: Especifica ou cria uma conexão para uma subscription do Azure que 
receberá o cluster do HDInsight; 
 ClusterName: Informa o nome do cluster do HDInsight que vai apagar; 
Azure HDInsight Hive Task: Este componente permite rodar scripts Hive no cluster HDInsight. Existem 
duas formas de informar o script, uma é especificando diretamente na tarefa e outra com o script 
armazenado em um arquivo no Blob Storage. Neste exemplo será escrito o script na tarefa. 
 AzureSubscriptionConnection: Especifica ou cria uma conexão para uma subscription do Azure que 
já possui um cluster do HDInsight; 
 ClusterName: Informa o nome do cluster do HDInsight que vai receber e processar o código Hive; 
 In-Line Script: Colocar o Código Hive que será processado. 
Azure HDInsight Pig Task: Componente que permiteexecutar scripts Pig no cluster HDInsight. A 
configuração é semelhanto à do Hive Task. 
Agora que já se sabe o que cada componente novo faz, e o que é necessário configurar para cada um, 
é preciso começar o desenvolvimento do cenário comentado na introdução. Ao final do capítulo você terá 
criado um pacote utilizando quase todos (com exceção da tarefa de execução dos scripts Pig) os 
componentes do Data Flow do Azure Package instalados no SQL Server Data Tools e seu pacote terá uma 
aparência semelhante ao da imagem a seguir: 
 
 
Instalando o MakeCert 
Para o cenário de testes estou utilizando meu computador pessoal. Nele está instalado o Windows 10 
como sistema operacional e por isso preciso instalar componentes externos ao padrão do Windows no 
meu ambiente. 
Certifique-se que seu ambiente possui o MakeCert. Se encontrar, pode pular esta seção. Porém, pode 
ser que seu ambiente não tenha o MakeCert, como no meu caso. Para resolver isso é necessário baixar o 
Windows SDK, que você pode fazer o download a partir do link existente na seção de Recursos. 
Na hora da instalação não é necessário instalar todas as opções que o instalador oferece. Garanta ao 
menos a marcação da opção de Windows App Certification Kit, como na figura a seguir: 
 
Ao término da instalação, vá até a pasta informada e instale os softwares que foram apresentados 
como executáveis. Após as instalações, vá até a pasta do Windows SDK e garanta que o MakeCert existe. 
 
 
Criando um certificado digital 
Para se fazer a comunicação segura entre o SQL Server Integration Services e o HDInsight no Azure, é 
necessário ter um certificado digital que garantirá a comunicação segura dos dados trafegados. Para isso, 
é necessário ter um certificado que pode ser criado com o MakeCert e então enviado para o Azure. 
Nesta seção do capítulo será utilizada a ferramenta MakeCert para criar o certificado digital que será 
utilizado posteriormente para se comunicar de forma segura com o Azure. Caso precise, leia a seção 
Instalando o MakeCert e em seguida volte para este ponto. 
Para criar o certificado abra o prompt de comando. Garanta que está abrindo com permissão de 
Administrador. Para isso, clique com o botão direito e aponte o mouse para Executar como 
Administrador. 
 
Navegue até a pasta na qual você instalou o MakeCert, que por padrão do SDK do Windows é 
 
C:\PROGRAM FILES (X86)\WINDOWS KITS\10\BIN\X64 
 
 
Insira a linha de comando que cria o certificado. No caso deste capítulo criei um certificado com o meu 
nome através do comando: 
 
MAKECERT -SKY EXCHANGE -R -N "CN=NOGARECERTIFICATE" -PE -A SHA1 -LEN 2048 -SS MY 
"NOGARECERTIFICATE.CER" 
 
 
Se executar o processo com sucesso, vá até a pasta no qual instalou o SDK e o MakeCert e você terá o 
arquivo criado. Veja como ficou o arquivo criado neste diretório: 
 
 
Criando o pacote e se comunicando com o Azure 
Depois de tantos passos de preparação do ambiente para se comunicar com o Azure, nesta seção será 
criado o pacote que utilizará o conhecimento adquirido nas seções anteriores para concluir o capítulo. 
Os itens apresentados na solução proposta na introdução será criado em detalhes, com o objetivo de 
guiar no entendimento das tarefas específicas e ter uma visão macro de como esta solução resolve o 
problema geral. Para concluir esta seção, é necessário criar um pacote do SQL Server Integration Services 
e acompanhar os passos a seguir. 
Azure Blob Upload Task 
Depois de criado o pacote, arraste o componente do Azure Blob Upload Task. Clique com o botão 
direito no componente, e aponte para Edit. 
 
A primeira configuração a se fazer, é do AzureStorageConnection. Clique na seta para baixo, e em 
seguida, aponte o mouse para <New Connection...> 
 
Você precisa de um Storage no Azure para prosseguir. Vá até sua subscription e encontre os dados 
necessários para configurar a conexão do componente no SSIS. Caso não saiba onde estão estes dados, 
vá até seu Storage e clique em Manage Access Keys. Uma tela semelhante à esta imagem abaixo se abre 
com as informações que precisa. 
 
Copie a chave que aparece no Primary Access Key, e preencha no campo de Account Key do SSIS que 
será configurado. Faça a mesma coisa para o Storage Account Name. 
 
Depois de informar os dados solicitados, teste a conexão clicando no botão Test Connection. Se 
receber a mensagem de sucesso, você configurou com sucesso sua conexão. 
 
O próximo item a ser configurado é o BlobContainer e o BlobDirectory. Informe os respectivos nomes, 
de acordo com seu ambiente. O diretório é opcional, pode deixa-lo em branco, caso queira. 
 
Para finalizar esta configuração, informe o local de origem e o nome do arquivo que será enviado pelo 
SSIS para o Azure Blob Storage. 
 
Ao final da configuração, confirme clicando em OK. E repare que aquele alerta vermelho ao lado do 
componente já não existe mais. Isso significa que as configurações mínimas foram feitas. 
Para testar, execute o pacote que possui só este componente e aguarde o envio do arquivo. 
 
Ao final, quando um check verde ficar ao lado do seu componente, abra o Storage e confirme que o 
arquivo foi enviado ao servidor. 
 
Se você encontrar o arquivo dentro do Blob Storage, seu componente e a configuração inicial foram 
criados com sucesso. 
Azure Blob Download Task 
Depois de enviar o arquivo para o servidor, vamos configurar a tarefa irmã do envio, que é o download 
do arquivo a partir do servidor. Esta tarefa faz o processo inverso ao que foi executado na explicação do 
Azure Blob Upload Task, e sua configuração é muito semelhante. Acompanhe os passos a seguir. 
Arraste o componente do Azure Blob Download Task para uma área vazia dentro do pacote criado. 
 
Seguindo o mesmo processo do componente anterior, é necessário editar a tarefa para que ela faça o 
trabalho proposto. Abra as configurações para editar. Faça isso clicando com o botão direito na tarefa e 
apontando para Edit. 
Configure com as mesmas informações utilizadas anteriormente. 
 
O único detalhe nesta configuração é o caminho do destino. Coloquei em um sub-diretório dentro do 
caminho original, para garantir que é um arquivo diferente do que já possuía no meu ambiente de 
desenvolvimento. Preencha todos os dados de configuração, e confirme no Ok. 
Mais uma vez, repare que o alerta vermelho não está mais apresentado na sua tarefa. Para executar 
somente este componente, clique com o botão direito na tarefa de download, e aponte para Execute 
Task. 
 
Após a execução, um check verde será mostrado ao lado da tarefa e, se funcionou corretamente, na 
pasta que você informou de destino haverá uma cópia do arquivo que foi feito o download. 
 
Com isso, concluímos a movimentação de dados nos dois sentidos, entre o servidor que executa o 
pacote do SSIS e o ambiente do Azure. 
Azure HDInsight Create Cluster Task 
A criação dos clusters de HDInsight dentro do Azure permite que um cluster de nós de Hadoop seja 
disponibilizado através da sua subscription, para processar os scripts em Hive que serão enviados para o 
ambiente. Para começar esta ação, arraste o controle Azure HDInsight Create Cluster Task para uma área 
vazia do pacote. 
 
Seguindo os mesmos passos das tarefas anteriores, é necessário editar esta tarefa para que ela execute 
seu propósito. Clique com o botão direito e vá até Edit. 
A primeira configuração a ser feita é do AzureSubscriptionConnection 
 
Como ainda não foi criada a conexão para a subscription, é necessário criar uma do zero. Clique na 
combo box, e aponte para <New Connection...>. Uma nova tela se abrirá e então os dados devem ser 
preenchidos. 
 
Mais uma vez você precisará de informações específicas da sua subscription do Azure. A primeira delas 
é a Azure Subscription ID. Você consegue recuperar essa informação nas configurações da sua 
subscription. Procure as configurações da sua conta em uma tela parecida com estaabaixo: 
 
Perceba que durante a configuração do componente, ele pede um certificado digital. Este certificado 
é o que foi criado na seção Criando um Certificado Digital alguns parágrafos acima. É necessário que você 
envie este certificado para o Azure. Para isso, ainda na seção de configuração dentro do portal, abra a aba 
chamada Management Certificates. 
 
Ao clicar em Upload a Management Certificate, você deve informar o caminho daquele certificado 
criado anteriormente. E em seguida clicar no check para concluir o upload. 
 
Após alguns instantes, o certificado foi enviado e pode ser utilizado. 
 
É necessário copiar o valor informado no Thumbprint, e informar na tela de configuração do 
componente do SSIS. 
 
Pode manter o valor que já está preenchido em Management Certificate Store Location e Management 
Certificate Store Name, preenchendo somente os dados de Azure Subscription ID e Management 
Certificate Thumbprint. Após esta configuração, clique em Test Connection e garanta que recebeu a 
confirmação. 
 
Feche a janela de teste de conexão e confirme a conexão com a subscription do Azure clicando em OK. 
Você será devolvido para a tela inicial da configuração do Azure HDInsight Create Cluster Task. 
Na sequência, é necessário informar o Storage que vai receber os arquivos de processamento do 
cluster e se comunicar com o HDInsight. Já foi criado anteriormente a conexão do AzureStoreConnection, 
e pode (deve!) ser reaproveitada. 
 
A seguir é preciso informar a região (lugar onde deseja ter o serviço do Azure) que terá o cluster, nome, 
tamanho, Blob contêiner, usuário e senha. Obrigatoriamente o cluster deve ser na mesma região do 
Storage, e no caso deste capítulo, é East US. Veja na imagem abaixo a configuração realizada para esta 
tarefa. 
 
Confirme a configuração clicando em ok, e veja que não tem mais o alerta vermelho ao lado da tarefa. 
Para testar, clique com o botão direito na tarefa e aponte para Execute Task. 
 
Depois de alguns instantes, é possível acompanhar o cluster sendo criado dentro do serviço do 
HDInsight no Azure. 
 
Após a conclusão da tarefa no portal do Azure, e não havendo nenhuma falha, seu componente no 
SSIS será marcado com o check verde. 
Azure HDInsight Delete Cluster Task 
Seguindo a mesma idéia de criar o cluster no HDInsight, a tarefa de excluir o cluster permite que seja 
selecionado o momento de destruir o cluster e parar a cobrança do serviço. 
Para isso, arraste o componente Azure HDInsight Delete Cluster Task para uma área vazia do pacote 
criado. 
 
Mais uma vez, é necessário configurar a tarefa. Para isso, clique com o botão direito e aponte para 
Edit. 
As configurações desta tarefa são mais simples do que da tarefa de criação do cluster. Afinal, já existe 
uma AzureSubscriptionConnection configurada e funcionado. Reutilize esta conexão no item que 
AzureSubscriptionConnection, e informe também o nome do cluster que foi criado no campo 
ClusterName. No caso deste capítulo, HDInsightNoSSIS. Veja como ficou a configuração desta tarefa. 
 
Confirme a configuração clicando em OK, e repare que o alerta vermelho não existe mais. 
Para executar somente esta tarefa, clique com o botão direito e aponte para Execute Task. 
 
Ao término, seu cluster será apagado e você receberá a confirmação com o check verde. 
 
Azure HDInsight Hive Task 
Esta tarefa dispara comandos em HiveQL para o cluster existente. Para garantir que ela vá executar 
com sucesso, garanta que seu cluster está criado e ainda não tenha sido destruído. Caso você já tenha 
executado a tarefa de apagar o cluster, execute mais uma vez a tarefa que cria o cluster, como já foi feito 
mais acima. 
Arraste o controle Azure HDInsight Hive Task para uma área vazia do pacote, em seguida clique com o 
botão direito na tarefa e aponte para Edit. 
 
A configuração é relativamente simples, uma vez que já foi feita nas tarefas anteriores e você já deve 
estar familiarizado com elas. A configuração inicial e de informar o AzureSubscriptionConnection e o 
HDInsightClusterName. O item novo neste componente é o Script que fica dentro do In-Line Script. 
 
Clique nas […] para abrir uma caixa de texto no qual será inserido o script em Hive. No caso deste 
exemplo, que num primeiro momento vai só criar a base de dados, mas que até o final do capítulo vai ler 
o arquivo do blobStorage e jogar dentro de uma tabela de municípios, foi utilizado este código: 
 
CREATE DATABASE HDInsightNoSSIS; 
 
O nome HDInsightNoSSIS foi escolhido para dizer o que esta estrutura faz, mas não é necessário manter 
o nome HDInsightNoSSIS na criação do banco só porque criou o cluster com este nome. Fique à vontade 
para informar o que melhor atende sua necessidade. Confirme o script clicando em OK, e finalize a 
configuração da tarefa clicando em OK novamente. Veja que o alerta vermelho desapareceu. 
 
Este código acima é responsável por criar o repositório, porém, para deixar o projeto como no exemplo 
que está na seção de introdução, é necessário quadruplicar esta tarefa, para que outros comandos Hive 
sejam disparados ao cluster. Copie e cole o controle mais três vezes, para totalizar quatro tarefas de 
execução de Hive no pacote. 
 
Vá até a primeira tarefa copiada, e altere o script para este abaixo: 
 
CREATE TABLE HDInsightNoSSIS.Municipios 
(Ano INT 
, ufid INT 
, uf STRING 
, municipioid INT 
, municipio STRING 
, regiaometropolitana STRING 
, mesorregiaoid INT 
, mesorregiao STRING 
, microrregiaoid INT 
, microrregiao STRING 
, valoragropecuaria INT 
, valorindustria INT 
, valorservicos INT 
, valoradministracao INT 
, valorimpostos INT 
, pibcorrente INT 
, populacao INT 
, pibpercapita INT) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; 
 
Na segunda tarefa, garanta que tenha este script: 
 
LOAD DATA INPATH '/dadosBrutos.csv' INTO TABLE HDInsightNoSSIS.Municipios 
 
Já na terceira tarefa, mude o script para o seguinte: 
 
SELECT municipio, populacao 
FROM HDInsightNoSSIS.Municipios 
WHERE microrregiao = 'Mogi das Cruzes' and populacao IS NOT NULL 
 
Este código retornará os municípios e a população das cidades da região de Mogi das Cruzes, onde eu 
nasci. Por padrão, dentro do código em Hive, ele cria um arquivo de saída que pode ser lido dentro do 
Storage que o cluster processou. Procure o arquivo stdout. 
Criando a sequência de execução 
Para fazer o trabalho completo proposto na seção de introdução deste capítulo, uma ordem lógica 
deve ser seguida. Acompanhe abaixo a ordem de execução das tarefas para garantir o funcionamento 
adequado. 
Para facilitar o desenvolvimento, renomeie as tarefas para um nome amigável. Isso ajuda na 
identificação das tarefas na hora de criar o fluxo de atividades, e depois para ler os logs nas bases de 
sistemas do SQL Server. 
 
Em paralelo deve-se executar a tarefa de subir o arquivo para o Blob Storage e de criar o cluster de 
HDInsight. Após a conclusão de ambas tarefas, é preciso criar o banco de dados através de uma execução 
de código Hive. Conecte os componentes de modo a criar esta sequência. Este processo garante que o 
banco só será criado após o arquivo ser enviado para o Blob Storage e o cluster ser criado. 
 
Em seguida, conecte a tarefa de criação do banco com as outras tarefas de Hive, na sequência: Criar a 
tabela, carregar os dados, selecionar os dados. Após a criação do banco, é possível criar a tabela, popular 
e consultar. 
 
Por fim, conectamos a tarefa de selecionar os dados, que faz o select e salva o arquivo stdout, às tarefas 
Baixar o arquivo e Apagar o cluster. Ambas as tarefas podem ser chamadas simultaneamente. O download 
do arquivo stdout e a destruição do cluster acontecem em locais distintos do Azure. Um não interfere no 
outro e por isso podem ser disparados simultaneamente. 
 
Lembrando que, se você seguiu exatamente os passos anteriores como descritos, é necessário alterar 
o arquivo que será baixado para ostdout, que é gerado a partir do processamento do select no cluster. 
 
Ao executar o pacote completo o arquivo baixado ficou na pasta especificada. Ao ler o conteúdo do 
arquivo é possível encontrar os dados que foram retornados pelo cluster do HDInsight. 
 
 
Recursos 
Microsoft SQL Server 2014 Integration Services Feature Pack for Azure: 
https://www.microsoft.com/en-us/download/details.aspx?id=47366 
SQL Server Data Tools – Business Intelligence for Visual Studio 2013: https://www.microsoft.com/en-
us/download/details.aspx?id=42313 
Windows Software Development Kit (SDK) for Windows 10: https://dev.windows.com/en-
us/downloads/windows-10-sdk 
 
https://www.microsoft.com/en-us/download/details.aspx?id=47366
https://www.microsoft.com/en-us/download/details.aspx?id=42313
https://www.microsoft.com/en-us/download/details.aspx?id=42313
https://dev.windows.com/en-us/downloads/windows-10-sdk
https://dev.windows.com/en-us/downloads/windows-10-sdk
Criação de Logon no SQL Server 
Esse capítulo mostra passo passo, a criação de um login e usuário, como também seus 
acessos a(s) instância(s) no Banco de Dados. 
Por Sulamita Dantas 
https://sulamitadantas.com.br/ 
Revisão técnica por Vitor Fava 
https://vfava.wordpress.com/ 
O SQL Server possui algumas opções de autenticação na hora que o usuário vai acessar o sistema, sendo 
eles abaixo: 
Modos de Autenticação no SQL Server: 
Windows Authentication Mode 
 
Figura 1. 
Usando esse modo, a autenticação é feita com o usuário e senha já cadastrado no Windows, ou seja, 
geralmente é o mesmo login/senha, usado para acessar a estação de trabalho. 
Além dos usuários do Windows poderem se conectar no SQL Server, grupos de usuários desse sistema 
operacional também podem se conectar, através de um mapeamento no SQL Server. 
https://sulamitadantas.com.br/
https://vfava.wordpress.com/
Ao fazer a instalação do SQL Server no Windows, é feito um mapeamento de todos os Administradores 
locais, permitindo o acesso dos mesmos, para se conectarem ao SQL Server. 
A qualquer momento, pode ser cancelado o acesso do usuário do Windows ao software, para isso, é 
necessário primeiro excluir a conta do usuário no Windows, e depois cancelar o mapeamento dentro do 
SQL Server. 
Esse modo é o recomendado pela Microsoft, quando todos os usuários que, se conectam ao SQL Server 
usa o Windows, e assim toda a segurança, auditoria e gerenciamento de usuários fica por conta do 
Windows e suas ferramentas. 
Obs.: (A conta no Windows é gerenciada pelo Active Directory ou nos ícones ''Usuário'' e ''Senha''). 
Mixed Mode 
Nesse modo, o usuário se conecta ao sistema através de logins exclusivos do SQL Server. 
 
Figura 2. 
 
Papel do Servidor 
É muito importante estar atento quando se trabalha com logins e usuários, pois deve-se dar atenção 
com relação a segurança, e principalmente com relação aos dados e a instância. 
Através das funções do servidor, são concedidas as permissões, e os acessos as 
funcionalidades no nível da instância, ligados pelo login. 
Segue abaixo a lista das funções de servidor 
bulkadmin 
Realizam inserções em massa no banco de dados e executam a função BULK INESRT. 
dbcreator 
Os usuários dessa função podem criar, modificar, eliminar e restaurar banco de dados, podendo 
também, executar os seguintes comandos: ALTER DATABASE, CREATE DATABASE, DROP DATABASE, 
EXTEND DATABASE, RESTORE DATABASE E RESTORE LOG. 
diskadmin 
Gerencia os arquivos do disco, e podem usar os comandos: sp_addumpdevice e sp_dropdevice. 
Processadmin 
Controlam os processos do SQL Server, podendo também elimina - lós. 
securityadmin 
Gerencia os logins, dá permissão de banco de dados e pode ler o log de erros. Adiciona membros do 
securityadmin, dando os direitos de conceder e revogar permissões em nível de servidor e de banco de 
dados. Podem executar os comandos: sp_addlinkedsrvlogin, CREATE LOGIN, ALTER LOGIN, DROP LOGIN, 
sp_droplinkedsrvlogin, GRANT CONNECT, DENY CONNECT, sp_helplogins e sp_moteoption. 
serveradmin 
Define opções de configuração em nível de servidor e encerra o mesmo. Executam os comandos: DBCC 
FREEPROCCACHE, RECONFIGURE, SHUTDOWN, SP_CONFIGURE, SP_FULL_TEXT_SERVICE E 
SP_TABLEOPTION. 
setupadmin 
Gerencia os servidores vinculados, e controla os procedimentos de inicialização. 
sysadmin 
Tem o controle total sobre o SQL Server, ou seja, pode executar qualquer tarefa. 
 
Funções de Banco de Dados 
Essas funções são usadas quando se quer atribuir permissões no nível de banco de dados. Através 
dessas permissões, são definidos os acessos a logins e Usuários, a objetos e funcionalidades do servidor 
de BD (banco de dados), chamados de “Roles”. 
Segue abaixo algumas dessas funções de Banco de Dados: 
Public 
É a padrão de todos os usuários de BD, fornece o mínimo de permissão. 
db_accessadmin 
Pode adicionar ou remover logins no banco de dados. 
db_backupoperator 
Permite fazer backups. 
db_datareader 
Apenas faz uma leitura dos dados, e um SELECT nas tabelas do banco de dados. 
db_datawriter 
Permite adicionar, modificar e apagar os dados na tabela, através dos comandos: INSERT, UPDATE e 
DELETE. 
db_ddladmin 
Permite executar as funções relacionadas as DLL (Linguagem de Definição de Dados), com exceção dos 
comandos: GRANT, REVOKE ou DENY. 
Db_denydatareader 
Não deixa fazer a leitura dos dados, restringindo o acesso aos mesmos. 
Db_denydatawriter 
Impede a modificação do banco de dados pelo login. Não utiliza os seguintes comandos: INSERT, 
UPDATE e DELETE. 
Db_owner 
Possui todo o poder do banco de dados, sendo eles: atribuir permissões, modificar configurações do 
banco, realizar manutenções, e realizar qualquer função de administração, podendo até eliminar o banco 
de dados. 
Db_securityadmin 
Gerencia as funções, permissões e posse de objetos. 
Dbm_monitor 
Monitora o estado atual do espelhamento do banco de dados. 
 
Permissões 
O SQL Server possui três comandos relacionados com as permissões: 
GRANT 
Permite executar qualquer tarefa 
DENY 
Nega as permissões ao tentar executar qualquer tarefa 
REVOKE 
Remove a permissão GRANT, mas não impede que o usuário execute uma função ou tarefa. 
A configuração das permissões no SQL Server envolve três passos: 
Principal 
É o login, usuário, ou o papel que receberá a permissão 
Securable 
Aqui o objeto principal terá acesso, de acordo com as permissões dadas. 
Permission 
Permite quais opções, o principal terá sobre o Securable 
 
Logins 
Abra o SSMS (SQL Server Management Studio) 
Faça o Login no modo Windows Authentication, conforme Figura 3. 
 
Figura 3. 
Após logado no SSMS, clicar em new query ou use o comando Ctrl + N, conforme Figura 4. 
 
Figura 4. 
Digite o comando ''SELECT * FROM sys.syslogins'', e depois aperte F5, ou execute. Ao executar 
essa query, ela retorna todos os logins existentes no SQL Server, conforme Figura 5. 
 
Figura 5. 
Criando um login com um domínio no Windows via Transact – SQL 
Digite o comando abaixo: 
 
CREATE LOGIN [Nome da sua conta no windows\Escolha um nome para login] FROM WINDOWS 
Exemplo: CREATE LOGIN [Sulamita\Sula] FROM WINDOWS 
 
 
Figura 6. 
Ao ser executado o comando acima (Figura 6), acesse a aba logins (localizada dentro de Security), e 
verifique o novo login criado, conforme Figura 7. 
 
Figura 7. 
Para deletar o login criado, basta executar o comando: 
 
DROP LOGIN [Nome da sua conta no Windows\O login criado], conforme Figura 8. 
Exemplo: DROP LOGIN [Sulamita\Sula] 
 
 
Figura 8. 
Após executado o comando acima (Figura 8), pode ser verificado, conforme Figura 9, que o login criado 
foi excluído com sucesso. 
 
Figura 9. 
Esse login foi criado usando o Transact –SQL Server, agora o mesmo será criado abaixo, usando a 
ferramenta do SSMS. 
Criando um login com um domínio no Windows via ferramenta SSMS: 
Clique com o mouse direito em cima da aba Security>Logins>New Login, conforme Figura10. 
 
Figura 10. 
Como o login está sendo criado com o domínio do Windows, a opção Windows Authentication, deverá 
ser marcada, e logo em seguida, clique em search, conforme Figuras: 11 e 12. 
 
Figura 11. 
 
Figura 12. 
Ao selecionar a opção ''Avançado'', clique em ''Localizar agora'' e procure pelo login que foi criado no 
Windows, conforme Figuras 13, 14 e 15. 
 
Figura 13. 
 
Figura 14. 
 
Figura 15. 
Clique em ok. O login criado, pode ser verificado, abrindo as abas SECURITY>Logins, conforme Figura 
16. 
 
Figura 16. 
Pronto, criado o login pelo modo Authentication Windows, tanto via Transact-SQL, quanto via 
Ferramenta SSMS. 
Criando um login do próprio SQL Server via Transact – SQL 
Logue pelo Modo Windows Authentication Server, abra uma janela pelo comando Ctrl + N, ou clique 
em New Query. 
Após a janela aberta, digite o comando abaixo: 
 
CREATE LOGIN [Nome desejado] WITH PASSWORD = 'A senha desejada' 
Exemplo: CREATE LOGIN [Friends] WITH PASSWORD = 'ab123C#' 
 
 
Figura 17. 
Comando criado com sucesso. 
Depois de criado o login (Figura 17), faça uma nova conexão, e logue com o login criado, conforme 
Figura 18. 
 
Figura 18. 
Conexão feita com sucesso, conforme Figura 19. 
 
Figura 19. 
Para excluir o login pelo Transact – SQL, logue no modo Authentication Windows e digite o comando: 
 
DROP LOGIN [Login criado] 
Exemplo: DROP LOGIN [Friends] 
 
 
Figura 20. 
Para excluir o login pelo SSMS 
Abra as abas Security>Logins, escolha o login que deseja ser excluído, clique com o botão direito do 
mouse, selecione a opção DELETE, conforme Figura 21. 
 
Figura 21. 
 
Manipulação dos Usuários via Transact SQL 
De uma forma simples, será feita a criação dos Usuários, com e sem comandos, para isso, é necessário 
fazer a conexão com algum banco de dados através do comando abaixo: 
 
Use (Banco de dados criado) 
 
Exemplo: Use SQL 
 
Figura 22. 
O comando sp_helpuser, ao ser executado mostra quais usuários estão ligados a determinado banco, 
conforme Figura 23. 
 
Figura 23. 
Algumas opções para a criação do usuário 
Criando um Usuário pertencente a um domínio do Windows, conforme Figura 24. 
 
CREATE USER [Nome desejado] FOR LOGIN [Nome da conta do Windows\Login desejado] 
Exemplo: CREATE USER [Sula] FOR LOGIN [Sulamita\Sula] 
 
 
Figura 24. 
Para remover o usuário criado acima, utilize o comando: 
 
DROP USER Nome escolhido 
 
Exemplo: DROP USER Sula 
 
Figura 25. 
Criando um Usuário pertencente a um domínio do SQL Server, conforme Figura 26. 
 
CREATE USER [Nome escolhido] FOR LOGIN [Nome do login criado] 
Exemplo: CREATE USER [Sulamita] FOR LOGIN [Teste] 
 
 
Figura 26. 
Criando um Usuário sem mapear um Login 
 
CREATE USER Nome que deseja criar WITHOUT LOGIN 
Exemplo: CREATE USER Teste WITHOUT LOGIN 
 
 
Figura 27. 
Para excluir o Usuário criado acima, utilize o comando: 
 
DROP USER Nome criado 
Exemplo: DROP USER Teste 
 
 
Figura 28. 
Manipulação dos Usuários via SSMS 
Abra o SSMS, acesse a aba do Banco de Dados que deseja criar o usuário e, em seguida, abra a aba 
Security, clicando com o botão direito do mouse em cima de User>New user, conforme Figura 29. 
 
Figura 29. 
Após clicar em New User, aparecerá uma janela, no campo User Name, coloque o nome do usuário a 
ser criado, conforme Figura 30. 
 
Figura 30. 
Próximo passo será clicar no browser para verificar quais logins existem, e selecionar qual login do 
domínio esse usuário irá pertencer, conforme Figura 31. 
 
Figura 31. 
 
Figura 32 
Após selecionado o login desejado, conforme Figura acima (Figura 32), clique em ok. 
Repare que na Figura 33, aparece o login selecionado, dê ok. 
 
Figura 33. 
Após criado o usuário, e o ter vinculado ao login, abra a aba Database -> Banco de Dados -> Security, 
e confirme a criação do usuário, conforme Figura 34. 
 
Figura 34. 
Após criado o usuário, clique com o botão do mouse direito em properties -> Membership, para 
atribuir as permissões a nível de banco de dados (Roles), conforme Figura 35. 
 
Figura 35. 
Depois de dado as permissões para o usuário, clique nas abas: Security ->logins, selecione o login 
desejado, clique com o mouse direito em cima dele, e selecione properties, conforme Figuras 36 e 37. 
 
Figura 36. 
 
Figura 37. 
Selecione Server Roles(Figura 37), e escolha qual privilégio(s), o login poderá ter. 
 
Conclusão 
Esse artigo teve o objetivo de mostrar passo a passo a criação de um login e usuário, e seus acessos a 
instância, e permissões ao banco de dados! 
 
Column Store Index 
Veja neste capitulo o que é o columnstore índice e como implementá-lo. Você também 
irá aprender em que cenários são ideias o uso dessa poderosa funcionalidade no sql server. 
Por Thiago [tc] Alencar 
https://tcalencar.wordpress.com/ 
Revisão técnica por Luciano Moreira 
http://luticm.blogspot.com.br 
Necessidade de Negócios 
Nos dias atuais muito se fala sobre grande quantidade de dados sendo exposta para tomada de 
decisão, cálculos massivos, transformações de grandes volumes de dados e etc. Para atender essa 
necessidade a indústria de TI tem respondido com velocidade e disponibilizado diversos recursos para 
atender o business dos mais diversificados segmentos. 
Hoje a maioria dos negócios desejam aproveitar o máximo de infraestrutura contratada, sejam elas 
em seu próprio datacenter ou na nuvem, que é uma tendência cada vez mais comum nos dias atuais. 
Fazer com que o software que foi adquirido ou desenvolvido faça bom proveito dos recursos que, muitas 
vezes são limitados e é um desafio diário para que o negócio seja mais eficiente e a TI não seja apenas 
"custo". 
Levando em consideração que, ainda há as limitações em termos de recursos finitos que existem CPU, 
memória, disco e rede. Avanços como ColumnStore ajudam através do aumento da taxa de transferência 
de dados, capacidade de armazenamento utilizando o mesmo conjunto de recursos finitos. Além disso, o 
ColumnStore foi projetado para funcionar em qualquer implementação de hardware existente, 
garantindo que você não precisa necessariamente colocar todos os dados da tabela em memória (se você 
tiver recurso sobrando, isso seria ótimo). 
Como sabemos, uma das funções de um banco de dados é fazer cache de dados que significa: ler dados 
do disco e colocá-los na memória para que o acesso seja realizado de forma mais performática. Quando 
uma consulta é executada, o SQL Server deve optar pelos métodos de acesso que sejam mais eficientes 
para uma consulta e trazer os dados para memória. O ColumnStore index ajuda o SQL Server a endereçar 
essa utilização de memória de maneira eficiente com o hardware existente que você já possui, isso 
significa que: você pode ter ganhos de desempenho dependendo do seu workload (OLTP ou OLAP) sem 
necessidade de fazer novos investimentos de hardware. 
Aposto que seu gerente vai gostar de ouvir isso. 
ColumnStore índices funcionam muito bem para workloads que realizam mais leituras em grandes 
conjuntos de dados como por exemplo: sumarização de dados. Workloads que nasceram com 
características OLTP e por algum motivo ou solicitação do usuário é necessário a aplicação também 
possuir características analíticas. Geralmente consultas que realizam grandes varreduras de dados, no 
caso, SCANs. 
 
https://tcalencar.wordpress.com/
http://luticm.blogspot.com.br/
Aonde utilizar? 
ColumnStore índices no SQL Server podem ser utilizados para acelerar significativamente o tempo de 
processamento de consultas que armazenam seus dados, no caso, dados que são armazenados em B-Tree 
(árvore balanceada) ou HEAP, utilizados para gerar informação para sistemas de tomada de decisão. 
As técnicas normalmente utilizadas em sistemas de armazenagem de dados e de apoio à decisão para 
melhorar o desempenho são tabelas pré-calculadas, sumarizadas, views indexadas, cubos OLAP e assim 
por diante. Embora estes podem acelerarbastante o processamento de consultas, essas técnicas podem 
ser inflexíveis, difícil de manter, e devem ser projetadas especificamente para cada problema, o que pode 
trazer um caro trade-off na administração da solução. 
A vantagem do ColumnStore Index permite você obter desempenho das consultas de sumarização sem 
necessitar, por exemplo, do pré-cálculo dos dados da tabela, como citado anteriormente. 
 
Aonde não utilizar/evitar!! 
Se você costuma atualizar os dados em uma tabela, se é necessário atualizar uma grande massa de 
registros ou se as caraterísticas de suas consultas sempre retornam uma pequena quantidade de registros, 
o ColumnStore índice não se encaixa nas suas necessidades. Pois, se a maioria de suas consultas são 
pequenas, talvez buscar em um índice B-Tree pode ser mais rápido do que ColumnStore Index. 
Fique atento, pois, uma funcionalidade não pode resolver todos os problemas de um cenário. O que 
geralmente vejo em campo é uma avaliação malfeita de um determinado recurso. Por exemplo, o cliente 
tem um problema específico que deseja resolver, mas, elege uma funcionalidade para atendê-lo sem 
conhecer as limitações da mesma. Após a implementação percebe que o seu problema não foi totalmente 
sanado e faz uma avaliação equivocada do recurso. A minha dica aqui é: avalie todos os cenários que você 
precisa atender e teste. 
 
Armazenamento Atual 
No SQL Server, os índices são organizados como B-Tree. O nó superior da B-Tree é chamado de nó raiz 
(root). Os níveis inferiores dos nós no índice são chamados de folha (leaf level). Quaisquer níveis de índice 
entre o nó raiz e folha são conhecidos como níveis intermediários (intermediate level). Em um índice 
clusterizado, os nós folha contêm as páginas de dados da tabela propriamente dita. Os nós do nível 
intermediário e raiz contêm páginas de índice com “ponteiros” para os dados, isso significa que, páginas 
do nível intermediário e raiz não possuem dados e sim apontamentos para os mesmos. Cada linha de 
índice contém um valor de chave e um ponteiro para uma página de nível intermediário na árvore ou 
ponteiro para uma linha de dados no nível folha do índice. As páginas de cada nível do índice são 
vinculadas, conhecidas como "doubly-linked list" (lista duplamente encadeada). 
Dependendo dos tipos de dados da tabela, cada estrutura de índice clusterizado terá uma ou mais 
unidades de alocação para armazenar e gerenciar os dados de um particionamento específico. No mínimo, 
cada índice clusterizado terá uma unidade de alocação IN_ROW_DATA por particionamento. O índice 
clusterizado também terá uma unidade de alocação LOB_DATA por particionamento se contiver colunas 
LOB, e terá uma unidade de alocação ROW_OVERFLOW_DATA por particionamento no caso de haver 
colunas de comprimento variável excedendo o limite de tamanho de linha de 8.060 bytes. 
As páginas da cadeia de dados e as linhas são classificadas pelo valor da chave de índice clusterizado. 
Todas as inserções são feitas no ponto em que o valor de chave da linha inserida se ajusta à sequência de 
classificação entre as linhas existentes. 
Para um índice clusterizado, a página raiz na view de sistema 
sys.system_internals_allocation_units aponta para a parte superior do índice clusterizado de um 
particionamento específico. 
Esta ilustração mostra a estrutura de um índice clusterizado em um único particionamento. 
 
Figura 1 - Estrutura de um índice B-Tree 
https://technet.microsoft.com/pt-br/library/ms177443(v=sql.105).aspx 
 
https://technet.microsoft.com/pt-br/library/ms177443(v=sql.105).aspx
CSI VS B-Tree 
Quando os dados são armazenados de maneira colunar utilizando o columnstore índice, os dados 
muitas vezes podem ser comprimidos de forma mais eficaz do que quando armazenados no formato de 
linha. Tipicamente existe mais redundância dentro de uma coluna do que dentro de uma linha, o que 
geralmente significa que os dados podem ser comprimidos em uma taxa muito maior. Quanto mais 
compacto é o dado, menos IO é necessário para trazer os dados para a memória, isso significa maior 
quantidade de informação por segmento. Reduzir IO pode acelerar significativamente o tempo de 
resposta das consultas. Fazer com que maior parte dos seus dados permaneçam na memória também irá 
acelerar o tempo de resposta para consultas subsequentes que acessam os mesmos conjuntos de dados. 
Imagine o seguinte cenário: Se uma consulta apenas referência algumas das colunas da tabela, é 
necessário apenas para um subconjunto das colunas a serem buscados do disco ou na memória. Por 
exemplo, se uma consulta referente às 10 colunas de uma tabela com 100 colunas (ou seja, 10% das 
colunas), IO é reduzido em 90% (além de quaisquer benefícios de compressão). 
Por outro lado, o armazenamento de colunas em estruturas independentes significa que os dados 
devem ser combinados para retornar os dados como uma linha. Quando uma consulta "toca" apenas um 
(ou alguns) registro (s), tendo todos os dados para uma linha armazenada em conjunto pode ser uma 
vantagem, caso a linha possa ser rapidamente localizada com um índice B-tree. Armazenamento em linha 
pode oferecer melhor desempenho para consultas muito seletivos, tais como consultas que procuram 
uma única linha ou um pequeno intervalo de linhas (Estamos falando de Seeks, ok?). 
 
Archival Compression 
Columnstore índice e tabelas são sempre armazenados utilizando os algoritmos de compressão do 
Columnstore. Você pode reduzir ainda mais o tamanho dos dados Columnstore, configurando uma 
compressão adicional chamado de “archival compression”. Para realizar essa compressão o SQL Server 
utiliza o algoritmo de compressão XPRESS Microsoft (https://msdn.microsoft.com/en-
us/library/hh554002.aspx) sobre os dados. 
Use a compactação do “archival compression” somente quando você pode se dar ao luxo de usar 
recursos de tempo e CPU extra para comprimir e recuperar os dados ou para dados históricos com baixa 
utilização. 
Um cenário poderia ser onde existe uma tabela particionada em que as partições históricas não são 
acessadas com frequência ou não são mais acessadas por características do negócio e ocupam um espaço 
considerável de armazenamento. Nesse caso, avaliar a utilização do “archival Compression” pode fazer 
sentido. Nós iremos abordar mais nesse capitulo, mas, por enquanto essa introdução deve bastar junto 
com o link de referência. 
http://msdn.microsoft.com/en-us/library/cc280449(v=sql.120).aspx 
Column Store - Clustered e Non Clustered 
No SQL Server 2012 não era possível criar um ColumnStore índice clusterizado, apenas um índice não 
clusterizado. A partir do SQL Server 2014 é possível criar ColumnStore índice clusterizado e não 
clusterizado algo que não era possível até então. A sintaxe para criar ambos, são muito semelhantes, no 
entanto, eles possuem algumas pequenas particularidades e características. 
No caso do ColumnStore índice não clusterizado ele tem a limitação de quantidade de colunas, que 
atualmente não é possível ter mais que 1024 colunas para esse tipo de índice. Como um índice não 
clusterizado o ColumnStore índice não possui todas as colunas da tabela, pois, ele armazena apenas uma 
cópia dos dados para as colunas que fazem parte do índice. Embora seja uma boa prática criar um 
ColumnStore índice com todas as colunas, isso fará com que as consultas nessa tabela sejam “cobertas”. 
Uma grande novidade no SQL Server 2016 é que esse tipo de índice poderá ser atualizável, isso significa 
que, não é mais necessário destruir/desabilitar o índice para realizar atualizações/inserções de novos 
registros. No BOL (books online) tem uma lista de todas as limitações dessa funcionalidade. 
O ColumnStore clusterizado teve sua primeira aparição no SQL Server 2014, que se trata de um índice 
baseado em coluna que pode sofrer atualizações, no entanto, o ColumnStore índice clusterizado é “toda” 
a tabela. Isso significa que em uma mesmatabela não é possível ter um ColumnStore índice clusterizado 
e outro ColumnStore índice não clusterizado, limitação que foi removida na atual versão do SQL Server, 
no caso, o SQL Server 2016. Devido ao fator de ser clusterizado, todas as colunas fazem parte do índice, 
então esse tipo de índice é um índice totalmente coberto. 
A sintaxe para a utilização da instrução T-SQL CREATE COLUMNSTORE INDEX está especificada na 
documentação oficial do produto (https://msdn.microsoft.com/pt-br/library/gg492153.aspx). 
https://msdn.microsoft.com/en-us/library/hh554002.aspx
https://msdn.microsoft.com/en-us/library/hh554002.aspx
http://msdn.microsoft.com/en-us/library/cc280449(v=sql.120).aspx
https://msdn.microsoft.com/pt-br/library/gg492153.aspx
Row Groups e Segmentos 
Sei que até o momento nós falamos de bastante teoria e apenas conceitos, mas, lembre-se. Conceitos 
e teorias são extremamente importante e apenas "saber na prática", pode não lhe ajudar quando você 
encarar um problema no dia-a-dia. 
Para ter uma alta performance e uma alta taxa de compressão, o ColumnStore índice "corta/divide" a 
tabela dentro grupo de linhas, conhecidas como ROWGROUPS. Após isso, o SQL Server realiza a 
compressão em formato de colunas (segmento). O número de linhas armazenado no ROWGROUP deve 
ser grande o suficiente para ter ganho na taxa de compressão e pequeno o suficiente para se beneficiar 
da utilização de memória para criação dos segmentos. Por enquanto não se preocupe, nós iremos 
clarificar esses pontos mais à frente. 
Vamos tentar explicar isso de maneira muito simples. Imagine que você possui uma tabela com alguns 
milhões de linhas. O SQL Server "coloca" uma linha imaginária para "cortar" em aproximadamente 1 
milhão de linhas. Que ficará com algo parecido mais ou menos conforme a figura abaixo: 
 
Figura 2 - Criação de ROWGROUPs no Columnstore índice 
Depois que essa tabela é "cortada" o SQL Server separa em colunas, que conhecemos como 
segmentos: 
 
Figura 3 - Criação de segmentos. 
Depois que as colunas estão segmentadas, o SQL Server aplica a compressão em cada segmento. 
 
Figura 4 - Processo de criação do columnstore índice por fases. 
As imagens retiradas da documentação oficial da Microsoft (https://msdn.microsoft.com/en-
us/library/dn589806(v=sql.120).aspx). 
O ColumnStore índice armazena os dados em um formato de alta compressão conforme falado 
anteriormente. Com cada coluna mantida em um grupo de segmentos que ajuda a reduzir IO para a 
maioria das consultas que possuem a característica OLAP, pois, nesse tipo de cenário as tabelas Fatos 
possuem em média trinta colunas e a consulta tipicamente não precisa retornar todas elas. Quando temos 
um índice em uma b-tree e uma linha é retornada, todas colunas daquela linha são colocadas em 
memória. Geralmente também utilizamos o ColumnStore índice em tabelas de fatos que possuem muitas 
colunas, no entanto, sempre devemos testar antes de implementar algo. 
Basicamente o ColumnStore índice é composto por ROWGROUP e segmentos. Para clarificar esse 
tópico coloquei alguns pontos sobre eles: 
https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx
https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx
 Um ROWGROUP geralmente possui o máximo de linhas permitidas em um ROWGROUP, que é de 
1,048,576. 
 Segmentos são dados de uma coluna dentro de um determinado ROWGROUP. 
 Cada ROWGROUP contém uma coluna de um segmento para toda coluna da tabela por partição (se 
a tabela for particionada) 
 Cada segmento é uma unidade de transferência de IO que significa que o SQL Server irá colocar um 
segmento por vez na memória e não apenas alguns dados desses segmentos. 
 Ao contrário do In-Memory OLTP (a.k.a Hekaton) o ColumnStore índice utiliza os mecanismos de 
armazenamento do SQL Server para que cada segmento seja armazenado em disco. 
A seguir, nós iremos apresentar a implementação do ColumnStore índice e o ganho de desempenho 
de uma consulta feita sobre uma tabela que não possui o ColumnStore índice e outra tabela que possui o 
ColumnStore índice não clusterizado. 
Abaixo estamos criando uma tabela chamada CSTable (ColumnStore) 
 
CREATE TABLE CSTable 
( [SalesOrderID] [int] NOT NULL, 
 [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, 
 [CarrierTrackingNumber] [nvarchar](25) NULL, 
 [OrderQty] [smallint] NOT NULL, 
 [ProductID] [int] NOT NULL, 
 [SpecialOfferID] [int] NOT NULL, 
 [UnitPrice] [money] NOT NULL, 
 [UnitPriceDiscount] [money] NOT NULL, 
 [LineTotal] [money], 
 [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, 
 [ModifiedDate] [datetime] NOT NULL, 
) ON [PRIMARY] 
Vamos realizar a inserção dos dados na tabela CSTable. 
INSERT INTO CSTable(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, 
 SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, 
ModifiedDate) 
SELECT SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, 
 SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, 
ModifiedDate 
 FROM [AdventureWorks2012].Sales.[SalesOrderDetail] 
 
Os dados que foram utilizados para carregar essa tabela, são os dados da tabela Sales.SalesOrderDetail 
do banco de dados AdventureWorks2012. 
O banco de dados AdventureWorks2012 pode ser adquirido no CodePlex 
(http://msftdbprodsamples.codeplex.com/releases/view/55330) 
Agora vamos criar um índice clusterizado para a nossa tabela, conforme script a seguir: 
 
if not exists(select 1 from sys.indexes where object_id = object_id('CSTable') and 
index_id = 1) 
Begin 
CREATE CLUSTERED INDEX idxClusteredIndex ON dbo.CSTable 
( 
 [SalesOrderID] ASC, 
 [SalesOrderDetailID] ASC 
) 
END 
GO 
 
Vamos criar um ColumnStore índice não clusterizado com todas as colunas da tabela. 
 
if not exists(select 1 from sys.indexes where object_id = object_id('CSTable') 
and type_desc = 'NONCLUSTERED COLUMNSTORE') 
BEGIN 
-- Create Non clustered columnstore index take about 20 seconds 
Create NonClustered ColumnStore Index idxNonClusteredCSI 
on dbo.CSTable 
( [SalesOrderID], 
[SalesOrderDetailID], 
[CarrierTrackingNumber], 
[OrderQty], 
[ProductID], 
[SpecialOfferID], 
[UnitPrice], 
[UnitPriceDiscount], 
[LineTotal], 
[ModifiedDate] 
) 
END 
GO 
 
Agora que o nosso ColumnStore índice não clusterizado foi criado, nós iremos executar duas consultas 
de sumarização de dados para poder verificar qual o ganho de desempenho de ambas. Conforme script a 
seguir: 
 
DBCC DROPCLEANBUFFERS 
go 
SET STATISTICS TIME, IO ON -- + Ctrl + M 
GO 
select ProductID AS IdProduto, Sum(OrderQty) as SomaQuantidade, SUM(LineTotal) AS 
SomaLinhaTotal, 
Avg(UnitPrice) As MediaPrecoUnico, 
Avg([UnitPriceDiscount]) as MediaDesconto 
from [dbo].CSTable 
where [UnitPriceDiscount] <> 0.00 
Group by ProductID 
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX); -- Apenas para testes 
GO 
SET STATISTICS TIME, IO OFF 
GO 
DBCC DROPCLEANBUFFERS 
go 
SET STATISTICS TIME, IO ON -- + Ctrl + M 
GO 
select ProductID AS IdProduto, Sum(OrderQty) as SomaQuantidade, SUM(LineTotal) AS 
SomaLinhaTotal, 
Avg(UnitPrice) As MediaPrecoUnico, 
Avg([UnitPriceDiscount]) as MediaDesconto 
from [dbo].CSTable 
where [UnitPriceDiscount] <> 0.00 
Group by ProductID 
GO 
GO 
SET STATISTICS TIME, IO OFF 
 
Nós utilizamos um HINT IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX para o SQL Server NÃO 
utilizar o ColumnStore índice não clusterizado, no entanto, isso deve ser evitado. Também utilizamos o 
comando DBCC DROPCLEANBUFFERS para o SQL Server não ler os dados da memória e sim realizar o que 
chamamos de cold cache. Obs: NÃO FAÇA ISSO EM PRODUÇÃO. 
Para a consulta que não utilizou o ColumnStore índice nós tivemos um plano que executou um 
Clustered Index Scan e realizou as agregações com um HASH MATCH. Conforme podemos ver a seguir: 
 
Figura 5 –