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 Castro
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
[email protected]. Boa Leitura, Diego Nogare http://www.diegonogare.net/ Edvaldo Castro http://edvaldocastro.com/ Demétrio Silva 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
[email protected]. Roberto Fonseca 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
Í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 utilizar 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.
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 permite executar 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 F ILES (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 "NOGARECERTIFICATE .CER"
-N "CN=N OGARE CERTIFICATE " -PE -A
SHA 1
-LEN 2048 -SS M Y
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
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 . 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 esta abaixo:
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 o stdout, 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 https://www.microsoft.com/en-us/download/details.aspx?id=47366
Pack
for
Azure:
SQL Server Data Tools – Business Intelligence for Visual Studio 2013: https://www.microsoft.com/enus/download/details.aspx?id=42313 Windows Software Development Kit (SDK) for Windows 10: https://dev.windows.com/enus/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.
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 funcionalidades no nível da instância, ligados pelo login.
as permissões, e os
acessos as
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 Figura 10.
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.
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 acelerar bastante 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
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/enus/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 mesma tabela 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).
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/enus/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:
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 (http://msftdbprodsamples.codeplex.com/releases/view/55330)
ser
adquirido
no
CodePlex
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 – Plano de execução da consulta que não utilizou o ColumnStore índice
Mas o que mais impressiona é o consumo de recursos que o SQL Server utilizou para essa consulta. Verifique a saída do comando: SET STATISTICS TIME, IO ON. IO Table 'CSTable'. Scan count 1, logical reads 26113, physical reads 1, read-ahead reads 26109, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. CPU SQL Server Execution Times: CPU time = 1281 ms, elapsed time = 3055 ms.
Avaliando a segunda consulta podemos perceber que o SQL Server utilizou um operador de Columnstore Index Scan, conforme a seguir:
Figura 6 - Plano de execução da consulta que se beneficiou do ColumnStore índice
E se tratando de recursos ele utilizou muito menos recurso que o Clustered Index Scan da primeira consulta: Table 'CSTable'. Scan count 1, logical reads 663, physical reads 19, read-ahead reads 864, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 109 ms, elapsed time = 255 ms.
Conclusão Como podemos ver o ColumnStore índice pode nos trazer ganhos de performance quando utilizado no cenário ideal, sendo assim, podemos dizer que o ColumnStore índice atende as consultas que devem ser tratadas com características analíticas, mesmo estando em um ambiente OLTP, e para modelos de dados Star Schema e SnowFlake. O ColumnStore índice pode ser uma poderosa funcionalidade em cenários de big data e data warehousing, onde se é necessário lidar com uma imensa quantidade de dados e possíveis cargas D-1. Esse capítulo trouxe uma breve introdução do ColumnStore índice, mas, ainda existem diversos conceitos que podem ser abordados com essa funcionalidade, como por exemplo: data store, delta store, tuple mover e etc. Como lição de casa, existem diversas fontes que podem ajudá-los no dia-a-dia. Eu particularmente gosto das três fontes a seguir: MSDN, Technet e uma outra referência bem legal deste assunto é o blog do Niko (http://www.nikoport.com/columnstore/), que é Data Plataform MVP e desenvolvedor de software que vive em Portugal.
Referências https://technet.microsoft.com/pt-br/library/ms177443(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/cc280449(v=sql.120).aspx https://msdn.microsoft.com/pt-br/library/gg492153.aspx https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx http://www.nikoport.com/columnstore/
Lock Escalation no SQL Server Este artigo oferece uma análise sobre o funcionamento do mecanismo de lock escalation no SQL Server, descrevendo alguns cenários onde o efeito pode trazer impacto negativo ou benefício para o ambiente. O capítulo é composto por scripts para que o leitor possa testar e analisar o lock escalation em seu laboratório, permitindo ganhar uma maior densidade técnica a certa do tema. Por Luciano Moreira http://luticm.blogspot.com.br/ Revisão técnica por Cibelle Castro https://cibellecastro.wordpress.com/
Introdução O objetivo deste artigo é explicar o que é o lock escalation, como funciona no SQL Server e citar alguns cenários que envolvem o lock escalation, e potencialmente podem trazer problema para seu ambiente. Este é um artigo de nível intermediário e parte do pressuposto que o leitor já possui um conhecimento básico dos modelos de concorrência utilizados em bancos de dados relacionais. Lock escalation, ou escalação de travas (ficarei com o nome em inglês ao longo do artigo), é um mecanismo comum em bancos de dados relacionais, no qual o motor relacional opta por trocar uma série de travas de menor granularidade (ex.: travas de registros) por uma trava de maior granularidade (ex.: trava de tabela), com o intuito de facilitar o gerenciamento das travas, ao custo de uma maior concorrência, com menos operações acessando simultaneamente os dados de um objeto. Tecnicamente a tradução do termo “lock” como “bloqueio” não é a ideal, pois quando o banco de dados protege o registro ou outro objeto qualquer, não necessariamente teremos uma nova requisição tentando acessar o mesmo elemento, o que resultaria em uma operação sendo bloqueada. Portanto a tradução mais adequada deve ser trava, e esta será usada em todo o artigo. Dito isso, confesso que ainda estou me adaptando a usar o termo, tentando tirar o vício de anos falando bloqueio ou simplesmente lock, produzindo frases com uma miscelânea de Português e Inglês. Para exemplificar um pouco da motivação do lock escalation, vamos utilizar o script 01, que cria uma tabela, insere 10 milhões de registros. Em seguida, com lock escalation desabilitado, é feita uma atualização de todas as linhas da tabela com a hint rowlock, mantendo travas de registros durante toda a transação. Vale ressaltar que foi tomado o cuidado de limitar a memória do SQL Server a 4 GB (4096 MB), que será útil para ilustrarmos outro ponto mais à frente neste artigo.
Script 01 – 10M atualizações sem o comportamento de lock escalation USE master GO IF (DB_ID('Mastering') IS NOT NULL) DROP DATABASE Mastering GO CREATE DATABASE Mastering go
USE Mastering GO IF (OBJECT_ID('dbo.LockEscalation') IS NOT NULL) DROP TABLE dbo.LockEscalation GO CREATE TABLE dbo.LockEscalation (Codigo INT IDENTITY NOT NULL PRIMARY KEY, Nome VARCHAR(100) NOT NULL, DataHora DATETIME2 NOT NULL DEFAULT SYSDATETIME()) GO INSERT INTO dbo.LockEscalation (Nome) VALUES ('Sr. Nimbus'), ('SQL Server'), ('Expert') GO INSERT INTO dbo.LockEscalation (Nome) SELECT TOP 1000000 XMP.name + XOC.name FROM sys.dm_xe_map_values AS XMP CROSS APPLY sys.dm_xe_object_columns AS XOC GO 10 SELECT TOP 100 * FROM dbo.LockEscalation; SELECT COUNT(*) FROM dbo.LockEscalation; GO ALTER TABLE dbo.LockEscalation SET (LOCK_ESCALATION = DISABLE); GO EXEC SP_CONFIGURE 'MAX SERVER MEMORY', 4096 RECONFIGURE GO DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome SELECT @@TRANCOUNT -- Verificar a conexão de monitoramento antes do commit COMMIT TRANSACTION /* Conexão de monitoramento */ SELECT * FROM SYS.dm_os_memory_clerks ORDER BY pages_kb DESC SELECT COUNT(*) FROM sys.dm_tran_locks Go
Consultando as DMVs sys.dm_os_memory_clerks e sys.dm_tran_locks (figura 01) podemos ver que o SQL Server alocou quase 2GB de memória para o lock manager, espaço necessário para manter 10061066 travas. Naturalmente que não vemos o número de travas exatamente igual ao número de registros na tabela, pois também existem intent locks nas páginas de dados e no objeto. Para os mais curiosos, um pouco do detalhe de funcionamento do SQL Server: Nas versões de 64 bits do produto são necessários 64 bytes para identificar o lock owner (dono da trava), que é a identificação da sessão/transação que requisitou a trava, e 128 bytes para o lock resource (recurso da trava), responsável por representar qual a estrutura está sendo protegida e sua identificação (ex.: objectid da tabela, RID do registro, entre outros). Ressalto que existem outras informações nessas estruturas de dados, mas por simplicidade a explicação acima é suficiente para este artigo. Uma dúvida comum dos profissionais é se o SQL Server primeiro escala as travas para o nível de página, posteriormente chegando ao nível de tabela. E a resposta é NÃO, o SQL Server NUNCA escala as travas para o nível de página, somente para a tabela ou para a partição, caso esteja adequadamente configurado para isto.
No entanto, caso o lock manager opte por pegar travas de páginas ao invés de proteger o registro, o lock escalation ainda pode acontecer, promovendo a trava para a tabela ou partição. Neste artigo você não verá isto acontecendo porque está sendo forçado a trava de registro com a hint ROWLOCK, contudo este comportamento é facilmente confirmado colocando-se a hint PAGLOCK ou quando o lock manager opta por proteger as páginas de dados.
Figura 01 – Consumo do lock manager sem lock escalation
Utilizando o script 02, inserimos mais 10 milhões de registros no banco de dados e fazemos novamente a operação de atualização de todos os registros. Dessa vez, ao invés da transação completar com sucesso, recebemos esta exceção: “Msg 1204, Level 19, State 2, Line 105 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.” Um resultado nada bom para nossa transação, onde atingimos o limite de espaço em memória que o lock manager pode alcançar (com base no 4GB definido para max server memory), causando o rollback da transação. Isto acontece pelo fato da operação estar impossibilitada de conseguir mais uma trava, só restando ao SQL Server desfazer todas as “X” milhões de atualizações executadas até aquele momento, dentro do mesmo contexto transacional. O total de memória alocado para o lock manager foi de aproximadamente 2,5 GB (o que equivale a algo em torno de 60% de 4GB), atingindo o limite permitido pelo SQL Server para esta área de memória, conforme figura 02.
Script 02 – 20M atualizações sem o comportamento de lock escalation USE Mastering GO INSERT INTO dbo.LockEscalation (Nome) SELECT TOP 1000000 XMP.name + XOC.name FROM sys.dm_xe_map_values AS XMP CROSS APPLY sys.dm_xe_object_columns AS XOC GO 10 DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome SELECT @@TRANCOUNT -- Erro na transação... COMMIT TRANSACTION /*
Conexão de monitoramento */ SELECT * FROM SYS.dm_os_memory_clerks ORDER BY pages_kb DESC SELECT COUNT(*) FROM sys.dm_tran_locks GO
Figura 02 - Consumo do lock manager após atingir o limite de memória
Vamos agora executar a mesma operação (script 03) permitindo que o lock manager faça o lock escalation, quando a engine achar necessário. Isso é feito configurando a propriedade “LOCK_ESCALATION” do objeto para “AUTO”, que é a configuração padrão que havia sido alterada no script 01.
Script 03 – 20M atualizações com o comportamento de lock escalation habilitado USE Mastering GO DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO ALTER TABLE dbo.LockEscalation SET (LOCK_ESCALATION = AUTO); GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome SELECT @@TRANCOUNT -- Verificar a conexão de monitoramento antes do commit COMMIT TRANSACTION /* Conexão de monitoramento */ SELECT * FROM SYS.dm_os_memory_clerks ORDER BY pages_kb DESC SELECT COUNT(*) FROM sys.dm_tran_locks SELECT * FROM sys.dm_tran_locks GO
Neste caso, ao fim da execução não recebemos mais a exceção. E de acordo com a figura 03, podemos notar dois aspectos: 1. O consumo de memória do lock manager está muito baixo, com aproximadamente 2 MB; 2. Existem somente 7 travas para todo o SQL Server, sendo que a única relevante é a trava em modo exclusivo (request mode X) no recurso 82099333, que é o object_id da tabela “LockEscalation”.
Figura 03 – Consumo do lock manager após lock escalation
Durante a execução da transação o SQL Server decidiu que seria interessante fazer o lock escalation, isto é, ao invés de manter em memória as estruturas de dados necessárias para representar milhares de travas de registros, o lock manager opta por utilizar uma trava na tabela inteira (“escalar a trava”), minimizando o custo do gerenciamento dos lock resources, sacrificando a concorrência no acesso ao objeto. É este trade-off do lock escalation que é importante ressaltarmos, gerenciar menos estruturas de dados diminuindo o acesso concorrente, ou ficar com um custo alto de gerenciamento das travas e permitir acessos concorrentes em diferentes pontos da tabela (desde que não exista acessos simultâneos aos mesmos registros). Você, caro leitor, talvez esteja se perguntando: Este parece ser um mecanismo desnecessário, afinal as máquinas com o SQL Server em produção têm muito mais memória que 4GB e nunca um desenvolvedor ou administrador de banco de dados faria uma transação envolvendo milhões de registros.... HAHAHAHAHAHAHAHAHAHAHAHAHAHA.... Desculpe-me ...HAHAHAHAHAHAHAHAHAHAHA.... Agora que recuperei o fôlego, devo dizer que já presenciei esse cenário diversas vezes, então dado a quantidades de ambientes existentes que eu não administro, diariamente existe algum DBA presenciando cenário semelhante.
Lock Escalation – limites e funcionamento Se você ler a documentação do SQL Server ou livros como o SQL Server 2012 Internals (http://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560), verá que existem duas referências de limites para o SQL Server tentar fazer o lock escalation: Quando o número de travas em um objeto, que uma única instrução conseguiu, ultrapassar o valor de 5000.
Quando a memória utilizada para armazenar os lock resources ultrapassar 24% da memória utilizada pela engine do SQL Server. Note que estes são valores internos de referência para a o SQL Server e que podem ser alterados em versões ou Service Packs futuros. Sabendo dos limites acima, para verificar quando efetivamente acontece o lock escalation, vamos fazer uso dos Extended Events (xEvents ou XE), monitorando dois eventos: lock_acquired e lock_escalation, tomando o cuidado de filtrar pelo identificador do banco de dados onde estamos conduzindo os testes, que durante a montagem deste artigo foi o database_id de número 7 (ajuste o seu script de acordo com seu ambiente).
Script 04 – xEvents para monitorar lock escalation USE Mastering GO -- SQL Server 2014 SELECT @@VERSION SELECT DB_ID() go CREATE EVENT SESSION xMonitorLocks ON SERVER ADD EVENT sqlserver.lock_acquired (WHERE sqlserver.database_id = 7) , ADD EVENT sqlserver.lock_escalation (WHERE sqlserver.database_id = 7) ADD TARGET package0.ring_buffer GO ALTER EVENT SESSION xMonitorLocks ON SERVER STATE = START GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE Codigo < 10000 SELECT @@TRANCOUNT COMMIT TRANSACTION SELECT name, target_name, CAST(xet.target_data AS xml) FROM sys.dm_xe_session_targets AS xet JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address) WHERE name = 'xMonitorLocks' GO ALTER EVENT SESSION xMonitorLocks ON SERVER STATE = STOP GO DROP EVENT SESSION xMonitorLocks ON SERVER Go
Aqui o detalhamento fica um pouco mais complicado, pois no XML de saída do monitor foram registrados 6252 eventos, sendo que a penúltima entrada é o evento de lock escalation. Isto é, 6250 travas foram adquiridas antes do lock escalation acontecer, e provavelmente você esperava que o lock escalation acontecesse depois de 5000 eventos registrados no ring buffer do xEvent.
Figura 04 – Saída do monitoramento xEvent
Infelizmente mesmo depois de investigar bastante eu não consegui encontrar a resposta definitiva para justificar este comportamento, pode ser porque o SQL Server verifica se e é possível fazer lock escalation antes de pegar a trava de número 5000, ou então que considera somente quando houver 5000 travas de registros e não intent locks. Ressalto que o importante é notar que para transações que obtenham mais de 5000 travas (a partir de 6250 em geral), o SQL Server vai continuamente tentar fazer o lock escalation a cada 1250 novas travas. Se você estiver com coragem e quiser validar quando o SQL Server verifica se pode fazer um lock escalation, sugiro uma pequena brincadeira. Utilize o Windbg e coloque um breakpoint em “sqlmin!IsEscalationPossible”, disparando diferentes transações e monitorando as travas que pertencem a sua transação (filtro por request_owner_lockspace_id), conforme o script 05. Assim você vai notar que somente em alguns momentos o SQL Server verifica se é possível fazer o lock escalation (exatamente a cada 1250 chamadas do método GetLock, a partir de 2500 travas do mesmo dono), encontrando uma stack trace semelhante ao da figura 05. Isso evita que a engine gaste tempo precioso validando a todo momento se vale a pena fazer lock escalation.
Script 05 – Análise do threshold para lock escalation SELECT * FROM sys.dm_tran_locks WHERE request_owner_lockspace_id = '0x00000000C1B3AC10:1:1' ORDER BY resource_type BEGIN TRANSACTION -- Nenhum bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- 2499 locks sem bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- 2500 locks e um bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- 4999 locks e dois bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- 5000 locks e três bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- 6249 locks e três bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE -- lock escalation e quarto bp hit UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE SELECT @@TRANCOUNT COMMIT TRANSACTION
Codigo <= 2000 Codigo <= 2484 Codigo <= 2485 Codigo <= 4970 Codigo <= 4971 Codigo <= 6214 Codigo <= 6215
Figura 05 – Stack trace do breakpoint IsEscalationPossible
Cenários que envolvem o lock escalation O entendimento dos limites explicados na seção anterior é importante para evitarmos alguns cenários de lock escalation que podem causar impactos na concorrência dos acessos aos dados, ou melhor, codificarmos transações ou procedimentos pensando nas consequências, caso aconteça um lock escalation.
Cenário 01 O primeiro cenário, e provavelmente mais comum, seria uma transação que segue um esquema parecido com a estrutura definida no pseudocódigo abaixo: BEGIN TRANSACTION UPDATE de 10.000 registros da tabela de vendas (principal objeto do banco) SELECT de 1 registro na tabela B COMMIT TRANSACTION
Caso o lock escalation aconteça no primeiro UPDATE e a transação fique bloqueada por trinta segundos ao tentar ler um registro da tabela B, toda e qualquer operação de registro de venda ficará bloqueada até que esta transação se conclua, o que não seria nada bom para uma empresa de e-commerce, por exemplo. Você poderia minimizar o potencial problema desta transação diminuindo o número de registros atualizados, ou tentando colocar o SELECT fora da transação ou antes do UPDATE (caso o negócio permita).
Cenário 02 Neste cenário temos transações concorrentes que impedem o lock escalation, de acordo com o código abaixo. Como as transações estão em execução antes de atingir o limite para o lock escalation, o SQL Server vai verificar que não é possível escalar as travas, pois existe outra transação bloqueando registros e impedindo o lock escalation acontecer. Caso as operações sejam grandes e o lock manager fique sem
memória disponível, o resultado será novamente o erro “Msg 1204, Level 19, State 4, Line 4 - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time.”, para ambas as transações. Script 06 – Cenário 02 BEGIN TRANSACTION SELECT @@TRANCOUNT UPDATE dbo.LockEscalation WITH -- Outra transação executa seu UPDATE dbo.LockEscalation WITH COMMIT BEGIN TRANSACTION SELECT @@TRANCOUNT UPDATE dbo.LockEscalation WITH -- Outra transação executa seu UPDATE dbo.LockEscalation WITH COMMIT
(ROWLOCK) SET Nome = Nome WHERE Codigo <= 1 primeiro update (ROWLOCK) SET Nome = Nome WHERE Codigo <= 10000000
(ROWLOCK) SET Nome = Nome WHERE Codigo = 10001000 primeiro update (ROWLOCK) SET Nome = Nome WHERE Codigo >= 10001001
Cenário 03 Caso tenhamos diversas operações dentro da mesma transação, em que cada operação manipule um conjunto de registros que não chegue ao limite de travas adquiridas para causar o lock escalation, mesmo que a transação acabe com cem mil travas o lock escalation não acontecerá, conforme script 07. Note que o lock escalation ainda está sujeito a acontecer devido ao consumo de memória, portanto dependendo do seu ambiente e da quantidade de travas gerenciadas pelo lock manager, o lock escalation ainda pode acontecer seguindo fielmente este exemplo. Script 07 – Cenário 03 DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO SELECT @@TRANCOUNT BEGIN TRANSACTION DECLARE @I INT = 0 WHILE (@I <= 100000) BEGIN UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE Codigo > @I AND Codigo <= (@I + 5000) SET @I = @I + 5000; end -- Verificar a conexão de monitoramento antes do commit SELECT @@TRANCOUNT SELECT COUNT(*) FROM sys.dm_tran_locks COMMIT TRANSACTION
É razoável que em alguns casos o comportamento desejado pelo desenvolvedor seja exatamente este, o de não atingir o limite do lock escalation, porém para muitos o exemplo acima parecerá estranho, por imaginar que o lock escalation aconteceria.
Cenário 04 Este cenário não traz um problema com o lock escalation, pelo contrário, mostra que em casos onde o volume de registros manipulados é muito grande, o custo para a engine do SQL Server gerenciar os recursos é alto, e quando simplificado pode trazer ganhos significativos de desempenho.
Utilizando o script 07 é possível comparar o desempenho de 10 milhões de updates e a diferença de desempenho entre uma execução com lock escalation e outra sem. No meu ambiente de teste, a comparação de performance é a seguinte: 28 segundos sem lock escalation contra 7 segundos quando o lock escalation acontece. Script 08 – Cenário 04 CHECKPOINT DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO ALTER TABLE dbo.LockEscalation SET (LOCK_ESCALATION = DISABLE); GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE Codigo <= 10000000 SELECT @@TRANCOUNT -- Média de 28 segundos COMMIT TRANSACTION CHECKPOINT DBCC FREESYSTEMCACHE('ALL') DBCC DROPCLEANBUFFERS GO ALTER TABLE dbo.LockEscalation SET (LOCK_ESCALATION = AUTO); GO BEGIN TRANSACTION UPDATE dbo.LockEscalation WITH (ROWLOCK) SET Nome = Nome WHERE Codigo <= 10000000 SELECT @@TRANCOUNT -- Média de 7 segundos COMMIT TRANSACTION
Conclusão Quando estiver desenvolvendo seu código T-SQL, sempre que for manipular milhares de registros, procure analisar em que momentos o lock escalation pode acontecer e qual será efeito para a aplicação e outras transações concorrentes, pois um lock escalation no momento errado pode causar sérios problemas de concorrência no seu banco de dados. Friso sempre que possível a importância de conhecermos em detalhes as características do produto que estamos trabalhando, e neste artigo minha contribuição foi explorar um pouco o funcionamento do lock escalation no SQL Server. Ressalto que ainda existem algumas características que não exploramos, como os trace flags 1211 e 1224, ou lock escalation para o nível da partição, mas acredito que com a introdução deste artigo você possa continuar explorando o comportamento do lock escalation no SQL Server.
Como monitorar seu ambiente SQL Server de forma eficiente e proativa Neste artigo serão abordadas as principais técnicas para o gerenciamento eficiente de múltiplas instâncias em um ambiente de banco de dados SQL Server 2014, utilizando para isso a ferramenta Data Collector. Além disso, será demonstrado de forma prátic a como configurar a ferramentas e também como extrair e analisar as métricas coletadas. Por Vitor Fava https://vfava.wordpress.com/ Revisão técnica por Edvaldo Castro http://edvaldocastro.com Com o crescimento exponencial da quantidade de dados gerados pelas aplicações, aumentou-se a complexidade no gerenciamento dos ambientes de banco de dados para o administrador de banco de dados. Este crescimento, combinado com a baixa no custo dos meios de armazenamento nos servidores, iniciou um cenário no qual o administrador de banco de dados deixou de administrar algumas dezenas de bancos de dados e passou a administrar centenas deles. Como as principais responsabilidades de um administrador de banco de dados são garantir a integridade, o desempenho e a estabilidade de todas as instâncias do SQL Server sob sua administração, quanto maior o número de instâncias e bancos de dados utilizados dentro de uma empresa, maior a dificuldade em monitorar e administrar tal ambiente de forma pró ativa e automatizada. Para este tipo de cenário, o SQL Server possibilita a coleta de métricas de desempenho de todas as instâncias e bancos de dados existentes, através da ferramenta Data Collector (DC).
O que é o Data Collector? O Data Collector (DC) é um dos principais componentes do conjunto de ferramentas para coleta de dados fornecido pelo SQL Server. Com o DC podemos definir um ponto centralizado para o armazenamento de todas as métricas coletadas através das instâncias de banco de dados SQL Server existentes em sua infraestrutura, sendo que estas métricas podem ser de várias origens e não apenas relacionadas a métricas de desempenho. Para aumentar a eficiência das métricas coletadas, deve-se ajustar o DC de acordo com cada ambiente existente em sua infraestrutura (desenvolvimento, homologação, produção). O DC armazena todas as informações coletadas em um datawarehouse de gerenciamento (MDW) e permite que sejam configurados diferentes períodos de retenção para cada métrica que será coletada. Como o DC possui uma interface de programação (API), podemos customizar coletas para qualquer outro tipo de métrica desejada. Porém, neste capítulo nos concentraremos apenas nas três coletas de sistema do DC: Disk Usage, Query Activity e Server Activity. A Figura 1 mostra como o DC encaixa-se na estratégia para coleta e gerenciamento de dados em um ambiente de banco de dados SQL Server.
Figura 1. Estratégia de coleta de dados
Arquitetura do Data Collector Antes de iniciar a implementação do DC, é necessário entender quais componentes fazem parte desta funcionalidade. São eles: Target: Uma instância de banco de dados SQL Server que suporte o processo de coleta de métricas através da utilização do DC; Target Type: Define o tipo de target do qual serão coletadas as métricas. Por exemplo, uma instância de banco de dados SQL Server possui métricas diferentes do que as métricas coletadas de uma base de dados SQL Server; Data provider: Uma origem de dados que proverá métricas para o collector type; Collector Type: Um delimitador lógico para os pacotes do SQL Server Integration Services (SSIS) e que fornece o mecanismo para a coleta e armazenamento das métricas no MDW; Collection Item: É um item de coleta no qual são definidas quais as métricas serão coletadas, com que frequência esta coleta será realizada e qual o tempo de retenção da métrica armazenada; Collector Set: Um conjunto de Collection Items; Collection Mode: A forma que as métricas serão coletadas e armazenadas no MDW. As métricas podem ser coletadas de forma contínua (Cached Mode) ou de forma esporádica através de um agendamento (Non-Cached Mode); Management Data Warehouse (MDW): O banco de dados relacional utilizado para o armazenado de todas as métricas coletadas. Um collector type está associado à um determinado target, e que este relacionamento também define como as métricas serão coletadas e qual o esquema de armazenamento destas métricas, pois o collector type também fornece a localização do MDW, que pode ser no servidor que está executando a coleta ou em um servidor centralizado. Já um collection item possui uma frequência de coleta pré-definida e só pode ser criado dentro de um collector set. O collector set, por sua vez, é criado na instância de banco de dados SQL Server que será monitorada através do DC e é composto por um ou mais collection items. A coleta do conjunto de métricas definidas no collector set é realizada através de Jobs executados pelo serviço SQL Server Agent, e as métricas coletadas são armazenadas no MDW periodicamente por meio de agendamentos pré-definidos.
A Figura 2 mostra um collector set de sistema chamado Disk Usage, no qual visualizamos que a configuração foi realizada com o collection mode definido como Non-Cached, utilizando dois collection items do tipo Generic T-SQL Query Collector Type, e que as métricas serão coletadas a cada 60 segundos, com retenção destas métricas no MDW por 730 dias.
Figura 2. Definição do collector set de sistema Disk Usage
Management Data Warehouse (MDW) Para que possamos utilizar a coleta de métricas através do DC, é necessário primeiro realizar a configuração do MDW, que será o banco de dados relacional responsável por armazenar todas as métricas coletadas pelos collector sets. Para isso, podemos utilizar um banco de dados relacional já existente e configurá-lo como um MDW, porém é recomendável que seja definido um novo banco de dados, pois durante o processo de configuração do MDW diversos esquemas e tabelas referentes ao DC serão criadas. Os esquemas gerados automaticamente após a configuração do DC são o core e o snapshot. Um terceiro esquema, de nome custom_snapshots, será criado quando um collectior set customizado for definido pelo administrador do banco.
O principal esquema do MDW é o core, pois possui as tabelas, stored procedures e views que ficam disponíveis para todos os collector types e que também serão utilizadas para organizar e identificar as métricas coletadas. Para garantir a integridade e segurança do MDW, todos os objetos de banco de dados pertencentes ao esquema core só poderão ser alterados pelos membros dos perfis de banco de dados db_owner e mdw_admin. A Tabela 1 lista todas as tabelas existentes no esquema core e suas respectivas descrições.
Tabela 1. Tabelas do esquema Core
O esquema snapshot, por sua vez, possui os objetos necessários para o armazenamento das métricas coletadas através dos collection sets de sistema. As tabelas deste esquema só podem ser alteradas pelos membros pertencentes ao perfil de banco de dados mdw_admin.
A Tabela 2 ilustra quais tabelas são utilizadas pelos collection sets de sistema Server Activity e Query Statistics, criados após a configuração do DC.
Tabela 2. Tabelas utilizadas pelos collection sets de sistema.
Já o esquema custom_snapshot possui as tabelas e views que foram criadas quando um collection set customizado foi configurado. Qualquer collection set customizado que necessitar de uma nova tabela para armazenar métricas coletadas poderá criar tabelas neste esquema. As tabelas podem ser adicionadas por qualquer membro do perfil de banco de dados mdw_writer.
Configurando o Data Collector Para exemplificar a coleta de métricas utilizando o DC, teremos a instância VITADB\SQLCMS, responsável por hospedar o banco de dados MDW, e as instâncias VITADB\SQLINSTANCE1 e VITADB\SQLINSTANCE2, que terão suas métricas coletadas através dos collector sets de sistema. Dito isso, a primeira etapa para configuração do DC é a criação do MDW na instância VITADB\SQLCMS, conforme os passos a seguir: Através do Object Explorer, selecione a pasta Management Clique com o botão direito na opção Data Collection Task Configure Management Datawarehouse; Na caixa de diálogo que será exibida (veja a Figura 3), selecione a instância VITADB\SQLCMS e crie o banco de dados MDW através do botão New; Selecione quais logins terão acesso ao banco de dados MDW (de acordo com a Figura 4) e clique em Finish;
Figura 3. Criação do MDW.
Figura 4. Definição de permissões ao MDW.
Após a criação e configuração do MDW na instância VITADB\SQLCMS, será necessário iniciar o processo de coleta de métricas nas instâncias VITADB\SQLINSTANCE1 e VITADB\SQLINSTANCE2, configurando os collector sets de sistema em cada uma das instâncias e direcionando as métricas coletadas para o banco de dados MDW. Para a configuração dos collector sets de sistema, temos os seguintes passos: Através do Object Explorer, selecione a pasta Management; Clique com o botão direito na opção Data Collection Tasks Configure Data Collection; Na caixa de diálogo exibida (Figura 5), conecte-se na instância VITADB\SQLCMS, selecione o banco de dados MDW, o collector set desejado e clique em Finish. Para este exemplo utilizaremos o collector set de sistema System Data Collection Sets, que será criado automaticamente após a configuração da coleta.
Figura 5. Definição do collector set.
Finalizada a configuração da coleta, temos a criação de três collector sets de sistema: Disk Usage, Query Statistics e Server Activity. O collector set Disk Usage coleta métricas sobre o crescimento dos arquivos de dados (.mdf e .ndf) e dos arquivos de log (.ldf) dos bancos de dados de usuário e de sistemas existentes na instância monitorada pelo DC. Com estas informações é possível saber qual a tendência de crescimento diário, em MB, dos arquivos analisados. A Tabela 2 mostra as propriedades do collector set de sistema Disk Usage.
Tabela 2. Propriedades do collector set de sistema Disk Usage.
Por sua vez, o collector set de sistema Server Activity coleta métricas de atividades do servidor, estatísticas, performance, cadeias de bloqueio, informações gerais de memória, CPU e rede. A Tabela 3 mostra as propriedades do collector set de sistema Server Activity.
Tabela 3. Propriedades do collector set de sistema Server Activity.
Por fim, o collector set de sistema Query Statistics coleta métricas referentes às consultas executadas no banco de dados monitorado pelo DC, como estatísticas, planos de execução, consultas mais custosas em relação à utilização de disco, CPU, memória e as consultas que mais tempo demoraram para serem finalizadas. A Tabela 4 mostra as propriedades do collector set de sistema Query Statistics.
Tabela 4. Propriedades do collector set de sistema Query Statistics.
Visualizando as métricas coletadas As métricas coletadas pelo DC podem ser acessadas diretamente por consultas T-SQL. No entanto, após a configuração dos collector sets de sistema, alguns relatórios padronizados se tornam disponíveis para visualização. Para acessá-los é necessário clicar com o botão direito na opção Data Collection Reports Management Data Warehouse. Após a configuração dos collector sets de sistema, três relatórios estarão disponíveis. Analisaremos cada um deles nos subtópicos a seguir.
Server Activity History Todas as informações disponíveis neste relatório são referentes à utilização de recursos do servidor de banco de dados, como total de CPU ou memória alocados, quais os maiores wait types existentes no SQL Server, qual o valor de IOPs, entre outros. Todas essas informações são extremamente úteis para um processo de troubleshooting e tuning.
A Figura 6 mostra a parte superior do relatório Server Activity History, extraído da instância VITADB\SQLINSTANCE2.
Figura 6. Parte superior do relatório Server Activity History.
No topo do relatório visualizamos de qual instância SQL Server são as métricas exibidas e em que data e hora foram solicitadas. Abaixo desta informação é possível selecionar qual o período de tempo, no qual as métricas foram coletadas, deve ser carregado no relatório. Em cada um dos gráficos apresentados, existem informações sobre o sistema operacional (linhas de cor verde) e sobre o SQL Server (linhas de cor azul). A Figura 7 mostra a parte inferior do relatório Server Activity History, extraído da instância VITADB\SQLINSTANCE2.
Figura 7. Parte inferior do relatório Server Activity History
Estes relatórios também são extremamente úteis no processo de análise de desempenho e troubleshooting, pois exibem quais os maiores wait types e quais os principais tipos de atividades que ocorrem na instância. A partir de qualquer um desses relatórios visualizamos mais detalhes selecionando uma das linhas ou barras de dados e realizando um drill-down na informação desejada.
Disk Usage Summary Este relatório lista o tamanho dos bancos de dados monitorados pelo DC e qual a média de crescimento dos mesmos durante um período de tempo. As métricas exibidas pelo relatório estão separadas por arquivos de dados e pelos arquivos de log dos bancos de dados monitorados. Como mostra a Figura 8, cada um dos arquivos de dados e arquivos de log possui a informação do tamanho inicial, do tamanho atual e a média de crescimento por dia em MB.
Figura 8. Relatório Disk Usage Summary.
Query Statistics History O motivo mais comum para os problemas de desempenho encontrados no SQL Server é a escrita de comandos T-SQL de forma ineficiente. Portanto, a coleta de métricas de desempenho destas consultas é uma parte essencial para o processo de tuning. Por padrão, são exibidas as 10 consultas que mais consumiram CPU, mas é possível alterar este filtro e visualizar as consultas que realizaram mais operações de IO, mais tempo ficaram em execução, realizaram mais leituras físicas ou realizaram mais escritas lógicas. A Figura 9 mostra o relatório Query Statistiscs History, extraído da instância VITADB\SQLINSTANCE2.
Figura 9. Relatório Query Statistics History
Recomendações para configuração do DC Para garantir que haja o mínimo impacto durante processo de monitoração do ambiente de banco de dados do SQL Server pelo DC, adote as seguintes recomendações: Utilize um servidor centralizado para o MDW, pois isto permite que exista apenas um único local para a execução e visualização dos relatórios; Todos os servidores de banco de dados SQL Server que serão monitorados pelo DC devem fazer parte do mesmo domínio; Quando criar um collector set customizado utilizando o collector type Generic SQL Trace, defina um conjunto de filtros para que somente as métricas realmente necessárias sejam coletadas, pois desta forma o MDW não armazenará informações desnecessárias; Antes de criar um collector set customizado utilizando contadores de desempenho, tenha certeza de que o collector set de sistema Server Activity já não esteja coletando esta métrica; Caso existam coletas de métricas através de várias consultas T-SQL e estas sejam executadas com a mesma frequência, combine-as em um mesmo collector set. Fazendo isso diminuiremos a quantidade de memória utilizada pelo executável do DC (DCCEXEC.exe) durante a coleta de métricas. De maneira similar, combine vários collection items do tipo Performance Counters em um único collection item sempre que possível; Combine vários collection items em um único collector set sempre que possível. O único motivo para criarmos collector sets separados é se houverem períodos de retenção diferentes ou agendamento de coletas diferentes; Um collector set que utilize o collection mode configurado como Cached sempre manterá um processo de coleta em execução. Se as métricas forem coletadas frequentemente, isto é mais eficiente do que iniciar e parar o processo de coleta sempre que novas métricas devam ser coletadas. Em contraste, o collection mode configurado como Non-Cached não terá um processo de coleta em execução durante a maior parte do tempo, ou seja, um novo processo de coleta será iniciado de acordo com o agendamento pré-definido e então será parado novamente, evitando a utilização excessiva dos recursos de hardware do servidor. Assim, caso a coleta de métricas ocorra raramente, o collection mode definido como Non-Cached é mais eficiente do que deixar o processo de coleta em espera a maior parte do tempo. Como regra geral, se a métrica necessita ser coletada a cada cinco minutos ou mais frequentemente que isso, considere configurar um collector set que utilize o collection mode: Cached. Caso a coleta de métricas possa ser realizada com uma frequência maior do que cinco minutos, é recomendado configurar collector set que utilize o collection mode: Non-Cached; Quanto maior a frequência de coleta, maior será a sobrecarga no servidor de banco de dados. Deste modo, opte sempre por configurar a menor frequência possível que atenda a necessidade de coleta.
Conclusão Conforme descrito no capítulo, podemos utilizar o Data Collector para facilitar o gerenciamento de um ambiente de banco de dados SQL Server composto por múltiplas instâncias. Com o DC temos uma funcionalidade que coleta métricas de todas as instâncias SQL Server e as armazena em um banco de dados centralizado, chamado Management Data Warehouse. Através da configuração do DC e do Management Data Warehouse, são criados três collector sets de sistema que coletam métricas referentes à utilização dos recursos de hardware do servidor (CPU, memória, disco e rede), crescimento dos arquivos de dados e log dos bancos de dados monitorados além das consultas TSQL mais custosas executadas no servidor de banco de dados e esta coleta é realizada por meio de Jobs definidos no SQL Server Agent. Vale destacar que o DC fornece também uma grande diversidade de relatórios, para que as métricas coletadas pelos collector sets possam ser avaliadas durante os processos de troubleshooting e tuning. Por fim, podemos constatar que o DC é uma ferramenta de monitoração completa, mas que precisa ser configurada da melhor maneira possível para evitar uma alta sobrecarga nos servidores de banco de dados.
Pensando fora da caixa – SQL Server FCI usando o File Share (SMB3.0) como opção de Storage Neste capítulo será apresentado o protocolo SMB 3.0 e como podemos tirar vantagens, aumentar a disponibilidade e performance do ambiente investindo menos em infraestrutura. É uma nova alternativa de Storage para cluster SQL Server onde podemos usar n ossa infraestrutura com mais eficiência e reduzir pontos de falhas no cluster Por: Marcelo Fernandes https://marcelodba.wordpress.com/ Revisão técnica: Nilton Pinheiro http://www.mcdbabrasil.com.br/
Tipos de Storage para SQL Server Antes de iniciarmos com o “SMB 3” vamos primeiro entender alguns tipos de storages que podemos utilizar no SQL Server e a diferença entre eles. Existem diversos tipos de Storage que podem ser utilizadas com o SQL Server: Discos Internos – HDD tradicional ou SSDs Placa de armazenamento PCI-E Direct-attached storage (DAS) – HDD ou SSDs Storage area networks (SAN) - HDD ou SSDs Server Message Block (SMB) 3.0 file shares o Disponível no Windows Server 2012 e 2012 R2 o SQL Server suporta o uso do File Share a partir da versão 2012 (stand-alone ou Failover Cluster Instances (FCI)) Como podemos notar o SQL nos permite usar diversos tipos de armazenamento para a instalação do nosso SQL Server e armazenamento das bases de dados. Existem também algumas tecnologias que nos permitem o uso de Failover Clustering SEM a necessidade de uma shared Storage: iSCSI Target Server SMB FileShare (usando SMB 3SMB 3ou 3.02) Cluster Shared Volume (CSV)
Neste capítulo focaremos no SMB que como vimos podemos usar em um SQL Server standalone ou em cluster (FCI)
Storage HDD vs SSD É muito importante em cenários com gargalos de I/O entendermos que disco temos em nosso ambiente e qual a performance oferecida por estes discos, então vamos comparar a performance entre os discos SSD e os discos mecânicos (HDD): HDD – Desempenho para I/O sequencial aceitável o 100-200MB/sec. por disco HDD - Baixo desempenho para I/O randômico o 100-200 IOPS por disco SSD – Bom desempenho para I/O sequencial o 6Gbps SAS/SATA pode atingir até 550MB/sec. por disco o 3Gbps SAS/SATA pode atingir até 275MB/sec. por disco o Placa PCI-E pode atingir até 6.5GB/sec. SSD – Excelente para I/O randômico o 6Gbps SAS/SATA pode atingir até 100,000 IOPS o PCI-E storage cards pode atingir até 1.3 milhões IOPS Como podemos notar o HDD não tem bom desempenho em I/O randômico o SSD tem excelente desempenho para o I/O randômico, as placas PCI-E são as melhores. Obviamente que o disco mais lento é mais barato e o disco com melhor desempenho mais caro, logo, para alguns cenários de gargalo de I/O a simples troca do disco por um SSD ou placas PCI-E não são tão viáveis devido ao custo.
Comparativo de Desempenho de I/O Para comparar os desempenhos dos discos citados anteriormente, executei um simples teste de I/O em um storage compartilhado em um data center o que é um cenário muito comum nos ambientes atuais, nem toda empresa tem um capital para dedicar um storage para um determinado serviço, executei também o mesmo teste em um disco SSD local e em um disco de uma Storage High-End dedicada para o SQL. Como podemos perceber, temos 3 tipos de solução onde a mais barata é a storage compartilhada com discos SAS e a mais cara (e põe cara nisto ) é a storage high-end que custa alguns milhões de reais. Nota: Teste executado com a ferramenta DiskSpd com a seguinte configuração: diskspd -b8K -d180 -o16 -t4 -h -r -w25 -L -Z1G -c10G D:\iotest.dat
Comparativos de Storages Read IO 32768 8192 2048 512 128 32 8 2 0,5 MB/s
Write IO
I/O per s AvgLat MB/s
Storage Comp.
Total IO
I/O per s AvgLat MB/s SSD
I/O per s AvgLat High-end
Read IO 66,25 8479,36 6,978 146,66 18772 2,698 181,5 23232,6 2,469 Write IO 22,1 2828,59 1,7 49 6271,93 2,113 60,61 7758,36 0,853 Total IO 88,34 11308 5,658 195,66 25043,9 2,552 242,12 30991 2,064 Lendo a tabela acima observamos que a melhor solução é o storage high-end que custa muito caro, mas olhando com atenção o disco SSD perde pouco para o storage high-end e o disco SSD me custou 150 dólares o que não é tão distante em preço da solução de storage compartilha oferecida pela maioria dos datacenters. Observando apenas a coluna de total I/O temos:
Neste caso, se analisarmos com cuidado, o custo x benefício do disco SSD é mais vantajoso que o disco compartilhado.
Escolhendo a Storage baseado no Workload Seguem uma lista de dicas que lhe ajudará a escolher o melhor disco para o seu ambiente SQL Server baseado em workloads: SSD oferece um melhor desempenho para I/O randômicos o Também oferece melhor desempenho sequencial em relação ao HDD o SSD é mais caro que o HDD (por GB) o O preço do SSD está cada vez mais acessível HDD oferece um razoável desempenho para I/O sequenciais o HDD têm uma performance ruim para I/O randômico (que pode ser “mascarado” através do cache da controladora) o Cache baseado em Flash pode prover melhor desempenho para o HDD SSD é a melhor escolha se “o custo” não for um problema o Recomendado para ambientes com alto overhead de I/O randômicos o Recomendado para ambientes que apresentam gargalos de I/O
SMB 3SMB 3File Shares Vimos nos tópicos anterior que o SQL suporta basicamente dois tipos de discos: o HDD e o SSD, mas que tal instalar o SQL Server em uma pasta compartilhada de um File Server? No passado muitos se assustariam com esta ideia, pois historicamente as estatísticas do SMB (Server Message Block) não são muito boas, podemos destacar alguns pontos negativos que muitos administradores de infraestruturas citavam sobre o SMB: File Share é lento A conexão com o File Share pode falhar ou cair SMB consome muita CPU No novo SMB 3 estes pontos negativos foram tratados ou eliminados, abaixo alguns pontos chaves sobre o SMB 3 que detalharemos no decorrer deste capítulo: Suporta I/O usando múltiplas placas de rede simultâneas, Tolerância a falhas de interfaces de rede, Integrado ao Windows Failover Clustering Windows Server 2012 suporta “SMB Direct”, que permite o uso de placas de rede com suporte a “Remote Direct Memory Access” (RDMA) o RDMA requer SMB Multichannel o RDMA oferece alta velocidade com baixa latência e baixo consumo de CPU
SQL Server FCI usando o File Share (SMB3.0) como opção de Storage No Windows Server 2012 foi disponibilizado a nova versão do Server Message Block, o SMB 3SMB 3e atualizado para 3.2 no Windows 2012 R2. Somente a versão SQL Server 2012 ou superior suportam o SMB como solução de storage para o SQL que pode ser um SQL Server Standanlone ou FCI.
Figura 1.1: Exemplo de ambiente utilizando File Server como opção de Storage
Na figura 1.1 temos uma imagem que ilustra um ambiente com SQL Server em cluster utilizando um File Server (SMB) como solução de storage para o SQL. Na imagem podemos notar que estamos utilizando o SOFs (Scale-Out File Server) que é uma nova solução de armazenamento de arquivo e um cluster SQL Server com 3 nodes Esta é uma solução de armazenamento de nível empresarial pois é escalável, confiável e sempre disponível. É uma solução de fácil provisionamento e gerenciamento, pois podemos usar as ferramentas familiares da Microsoft como System Center Configuration Manager (SCCM), PowerShell para administrar o ambiente. Utiliza as mais recentes tecnologias de rede (Ethernet convergente, RDMA) o que nos proporciona maior flexibilidade, É uma solução que reduz as despesas de capital e operacionais, pois temos um storage corporativo que pode ser utilizado por outros serviços. Uma solução com SMB nos oferece algumas features que não temos em soluções convencionais:
SMB Transparent Failover - Disponibilidade contínua se um nó falhar SMB Scale-out - clusters de servidor de arquivos Ativo / Ativo, automaticamente balanceados SMB Direct (SMB sobre RDMA) - Baixa latência, alto rendimento, baixo uso de CPU SMB Multichannel - Aumento do throughput da rede e tolerância a falhas SMB Encryption - Transmissão de dados segura, sem a custosa infraestrutura PKI VSS para SMB File Shares - Backup e Restore usando a estrutura VSS existente SMB PowerShell, VMM Support - Capacidade de gerenciamento e suporte através do System Center e PowerShell
SMB Transparent Failover Um dos pontos que não tem muita proteção em um cluster tradicional é o disco, se ocorrer alguma falha no storage ou no meio de comunicação entre o storage e o servidor (Fibra, HBA, etc.…) o serviço do SQL Server que esta clusterizado irá falhar! Pensando nisto, o SMB inclui um recurso que sem dúvidas, esta é uma das features mais legais do SMB 3 que nos permite efetuar o Failover da aplicação que utiliza o SMB (no caso de nosso exemplo o SOFs) com zero downtime para o SQL Server, existem um pequeno e rápido delay de I/O durante o Failover O Transparente Failover ocorre para Failover planejado ou não planejados, isto significa que aumentaremos o nível de disponibilidade de nossa aplicação. Isto não significa que se por um acaso ocorrer um Failover do SQL Server este processo será transparente para a aplicação, o SQL Server tem downtime, o que não tem downtime é o File Server que estamos usando como alternativa de storage, ou seja, eliminamos o problema de proteção existente entre o meio de comunicação do Storage e o Servidor.
Figura 1.2: Simulação do Transparent Failover no SMB 3
Na figura 1.2 temos uma ilustração do processo do Transparent Failover, temos um cluster SQL Server que foi instalado alocando seus bancos de dados em diretório do File Server (\\fileserver\SQLteste) e neste cenário notamos que o serviço estava ativo no primeiro nó e foi transferido (Failover) para o outro nó e nesta ação o serviço do SQL Server não é afetado e continua online. Este tipo de proteção atualmente só é possível com o SMB 3!
SMB Direct (SMB sobre RDMA) Antes de iniciarmos com o SMB sobre o RDMA (Remote Direct Memory Access) vamos primeiro entender o que é o RDMA. O RDMA como o nome diz, é um protocolo que permite o acesso a memória de um computador remoto. Ou seja, se você tiver uma placa de rede com o suporte RDMA o cliente SMB tem acesso direto à memória do servidor SMB transformando a transferência de arquivo extremamente rápida e com baixíssimo consumo de CPU. Os benefícios do RDMA são: Baixa latência Alto rendimento Capacidade de cópia zero SO / by-pass Podemos usar as seguintes tecnologias com o Hardware RDMA InfiniBand iWARP: RDMA sobre TCP / IP RoCE: RDMA sobre Ethernet convergente
Figura 1.3: Ilustração do funcionamento do RDMA no SMB 3
SMB Scale-out Com Scale-out File Server (SOFs) você pode compartilhar a mesma pasta em vários nós de um cluster. No exemplo da figura 1.2, temos um cluster de File Server de dois nós usando a Scale-out do protocolo SMB, um computador executando Windows Server 2012 R2 ou Windows Server 2012 poderá acessar File Server em qualquer um dos dois nós. Isso é possível devido os novos recursos do Windows Failover Cluster juntamente com o protocolo SMB 3SMB 3do File Server. Podemos então oferecer o SOFs que estará sempre online e em caso de aumento de demanda podemos simplesmente adicionar novos servidores no cluster e tudo isto em ambiente de produção com a operação totalmente transparente para as aplicações Os principais benefícios fornecidos pelo servidor de SOFs incluem: File Server ativo-ativo Todos os nós de cluster podem aceitar e atender a solicitações do cliente SMB com Failover transparentes entre os nós. Largura de banda aumentada A largura de banda máxima de um compartilhamento corresponde à soma da largura da banda da placa de rede de todos os nós do cluster File Server. Nas versões anteriores do Windows Server, a largura de banda total era limitada à largura de banda de um único nó de cluster. Você pode aumentar a largura de banda total adicionando novos nós. CHKDSK com zero downtime Houve uma melhora significativa do CHKDSK no Windows Server 2012 que reduz de forma drástica o tempo que um sistema fica off-line para reparos, os CSVs (Clustered Shared Volumes) eliminam a fase do off-line. Cache de Clustered Shared Volumes Os CSVs no Windows Server 2012 introduzem o suporte a um cache de leitura melhorando significativamente a performance. Gerenciamento mais simples Com o SOFs, você pode adicionar novos CSVs e criar diversos compartilhamentos. Não é mais preciso criar vários cluster de File Servers cada um com discos de cluster separados. Rebalanceamento automático de clientes do SOFs No Windows Server 2012 R2, o rebalanceamento automático melhora a escalabilidade e capacidade de gerenciamento do SOFs. As conexões dos clientes SMB são controlados por compartilhamento ao invés de servidor, deste modo, os clientes são redirecionados para o nó com melhor acesso ao volume daquele compartilhamento, deste modo temos uma melhora significativa na performance e reduz o trafego entre os servidores.
SMB Multichannel O SMB Multichannel é outra grande feature do SMB 3, pois com nos proporciona aumento do throughput da rede e tolerância a falhas. Vamos imaginar dois cenários onde temos um ambiente com 4 cores e uma placa de rede de 10 GB sem a tecnologia Multichannel e outro ambiente com a tecnologia Multichannel. No primeiro cenário, vamos imaginar que temos uma sessão com vários I/Os veríamos um alto uso de apenas um core enquanto temos 3 sem uso, isto ocorre porque na versão antiga do SMB não tínhamos suporte ao Multichannel e o SMB cria apenas 1 conexão TCP. Se eu executar o mesmo teste no segundo cenário com o SMB 3 suportando o Multichannel veremos a distribuição do uso dos Cores, isto ocorre, pois o SMB 3 detecta que a placa de rede tem a feature Receive Side Scaling (RSS) e cria múltiplas conexões TCP distribuindo a carga entre os CPUs. Não é necessário fazer alterações ou configurações em seu ambiente para a utilização do Multichannel, o SMB 3 detecta o RSS e cria automaticamente as múltiplas conexões. A figura 1.4 ilustra este comportamento do SMB Multichannel.
Figura 1.4: Comparativo de comportamento de ambiente com e sem Multichannel (imagem gentilmente cedidas pelo José Barreto [http://blogs.technet.com/b/josebda/])
Vamos imaginar outros 2 cenários, 1 sem o SMB 3 com dois File Servers o primeiro com duas placas de rede com suporte RSS e outra com duas placas de rede sem o RSS. O outro cenário é exatamente igual o primeiro, mas com o suporte ao SMB 3 Multichannel, a imagem 1.5 ilustra este cenário. No cenário sem o suporte ao SMB 3 Multichannel, apesar de ter duas placas de redes nos dois clusters de File Server, não temos o Failover automático que está presente somente no SMB 3, vamos imaginar que temos uma sessão com vários I/Os, neste caso usaremos apenas 1 placa de rede (tanto no cluster
com o suporte ao RSS e no outro sem o suporte ao RSS) e teríamos o cenário de alto consumo de um único core devido a criação de apenas 1 conexão TCP. No cenário com o suporte ao SMB 3 Multichannel, temos o Failover automático e podemos usar toda a banda disponível no servidor pois o Multichannel utilizará as duas placas de redes aumentando a largura da banda. No cluster com as placas de redes sem o suporte ao RSS, mas com o SBM 3, o Multichannel utilizará as 2 placas de redes abrindo apenas uma conexão em cada uma delas. A figura 1.5 ilustra este comportamento do SMB Multichannel com múltiplas placas de rede.
Figura 1.5: Comparativo de comportamento de ambiente com e sem Multichannel usando múltiplas NICs (imagem gentilmente cedidas pelo José Barreto [http://blogs.technet.com/b/josebda/])
Os pontos chaves do SMB Multichannel são: Full Throughput Agregação de largura de banda com várias NICs Utiliza vários núcleos do CPUs quando se utiliza Receive Side Scaling (RSS) Failover automático SMB Multichannel implementa detecção de falhas end-to-end Aproveita NICs teaming se presente (mas não exige) Configuração automática SMB detecta e usa vários caminhos de rede
Performance do SMB Multichannel O Jose Barreto que é Principal Program Manager na Microsoft e ajudou no desenvolvimento do SMB 3 fez um ótimo teste de performance com o SMB3 com as seguintes configurações: Windows Server 2012 utilizando 4 placas de rede 10GbE Dimensionamento linear da largura de banda: o 1 NIC – 1150 MB/sec o 2 NICs – 2330 MB/sec o 3 NICs – 3320 MB/sec o 4 NICs – 4300 MB/sec. Placas de rede com suporte ao RSS (Receive Side Scaling) Largura de banda para pequenos I/O causam gargalos no CPU O resultado obtido com os testes foi espetacular, note a performance com o Multichannel
MB/sec
SMB Client Interface Scaling - Throughput 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0
I/O Size
1 x 10GbE
2 x 10GbE
3 x 10GbE
4 x 10GbE
Figura 1.6: Performance do SMB Multichannel (imagem gentilmente cedidas pelo José Barreto [http://blogs.technet.com/b/josebda/])
Notamos que para I/Os pequenos não conseguimos muita vantagem aumentando a quantidade de placa de rede, para I/O maiores o ganha é significativo conforme aumentamos o número de placa de redes, alcançando cerca de 4500 Mb/s com 4 placas de rede para I/O size maior que 16384 Lembra da tabela de testes comparando alguns tipos de storage que comentamos no início do capítulo? Tipo Storage
IOPS
MB/s
Avg. Lat.
Compartilhada
11.308
88.34
5.62
SSD
25.043
195.66
2.55
High-End
30.991
242.12
2.06
Obviamente que o teste que fiz com o Diskspd foi diferente do teste que o José Barreto fez, mas nos serve de parâmetro para quebrar alguns paradigmas, observe a coluna MB/s do storage vs o SMB 3, você terá uma grata surpresa. Não estou lhe dizendo para sair da SAN e ir para o SMB 3 amanhã pois isto teria um certo impacto financeiro e burocrático, mas já vale um teste em seu ambiente, correto? E que tal considerar o SMB3 para os novos projetos? O SMB 3 é mais uma alternativa que temos de storage com o diferencial da performance, disponibilidade e escalabilidade. Sendo assim “pense fora da caixa! ” Conforme apresentado, temos uma maior proteção dos discos com o Failover transparente e utilizamos toda a largura de banda disponível nos servidores.
SQL Server FCI usando o File Share (SMB3.0) como opção de Storage A partir do SQL Server 2012 ou superior, foi adicionado o suporte a utilização de “file share” SMB3.0 (Windows Server 2012) ou 3.02 (Windows Server 2012 R2) como opção de storage para bancos de dados de sistemas ou usuários, suportado para instâncias stand-alone ou em cluster e tem o desempenho otimizado para workloads OLTP. Suporte também para failover transparente do SOFs provendo downtime 0 e é otimizado para “random read/write I/O”. Recomendado uso a partir do Windows Server 2012 R2 (SMB 3.02). Antes de iniciar o setup do SQL Server utilizando o SMB, segue abaixo uma visão geral do ambiente de laboratório para o SQL em SMB 3
Figura 1.7: Visão geral da infraestrutura do laboratório de SQL sobre SMB
Na figura 1.7 temos os detalhes de cada servidor / cluster que utilizaremos no laboratório, para este laboratório estou usando um servidor como AD e também como iSCSI Server, temos um cluster Windows 2012 R2 para o SOFs e o nosso Cluster Windows 2012 para o SQL Server 2016 que será instalado usando o SMB com storage. Nota: Neste laboratório focarei no setup do SQL sobre o SMB, sendo assim não detalharei o setup do AD e iSCSI Server, mas você pode ter maiores informações nos links abaixo: http://social.technet.microsoft.com/wiki/contents/articles/5119.instalacao-do-active-directory-nowindows-server-2012-pt-br.aspx http://blogs.technet.com/b/meamcs/archive/2012/03/30/installing-and-configuring-target-iscsiserver-on-windows-server-8-beta.aspx
Configurando o Scale-Out File Server (SOFs) Siga as etapas abaixo para configurar o SOFs, como dito acima, neste capitulo não demonstrei como criar e configurar o iSCSI Server e o AD, mas você pode utilizar os links informados para criar o seu ambiente e apresentar os discos para o cluster de File Server que criaremos a seguir: 1. Crie um cluster para o File Server, instale o Windows Server 2012 R2 com no mínimo dois nós e apresente 3 discos para o cluster: SQLP1-BIN SQLP1-DADOS SQLP1-LOG
(40 Gbs) (20 Gbs) (10 Gbs)
2. Conecte-se em um dos nós do cluster de File Server e converta os discos para Shared Volumes, você poderá ter maiores informações sobre o CSV no livro “SQL Server 2014: Alta Disponibilidade na Prática com AlwaysOn Failover Cluster Instances”, de minha coautoria juntamente com Nilton Pinheiro. 3. Em Storage clique sobre os discos com o botão direito e escolha a opção “Add to Cluster Shared Volumes” (execute o processo para os 3 discos)
Figura 1.8: Transformando os discos em CSV
4. Clique sobre “Roles” como botão direito e escolha a opção “Configure Role...”
Figura 1.9: Criando uma nova Role
5. Na tela “Before You Begin” clique sobre o botão “Next” 6. Na tela “Select Role” selecione a opção “File Server” e clique em “Next”
Figura 1.10: Criando uma nova Role para o File Server
7. Na tela “File Server Type” selecione “Scale-Out File Server for application data” e clique em “Next”
Figura 1.11: Criando o SOFs
8. Na tela “Client Access Point” informe o nome virtual para o compartilhamento e clique em “Next”, este será o nome do host que os clientes acessarão, ex. \\SQLSMB\data
Figura 1.12: Informando o Client Access Point
9. Na tela “Confirmation” clique sobre o botão “Next” 10. Na tela “Summary” clique sobre o botão “Finish” e o nosso SOFs esta criado e será acessado via \\SQLSMB\ Se você acessar o Failover Cluster e clicar sobre Roles, verá que foi criado uma nova Role chamada “SQLSMB” e selecionando esta role veremos que temos dois recursos um Nome e o SOFs.
Figura 1.13: Recursos do SOFs
11. Após criação do SOFs, devemos criar os nossos compartilhamentos para a instalação do SQL, criaremos os seguintes compartilhamentos: \\SQLSMB\SQLBIN \\SQLSMB\SQLDATA \\SQLSMB\SQLLOG
Para isto, clique com o botão direto sobre a role “SQLSMB” e escolha a opção “Add File Share”
Figura 1.14: Adicionando um novo Share ao SOFs
12. Na tela “Select the profile for this share” selecione a opção “SMB Share - Applications” e clique sobre o botão “Next”
Figura 1.15: Selecionando o profile mais adequado para o uso do compartilhamento.
13. Na tela “Select the server and path for this share” devemos selecionar o disco em que o share será criado, note que apesar de ser um share com o mesmo netbios para acesso (\\SQLSMB) podemos e devemos dividir o compartilhamento de acordo com a boa prática de discos separados para dados e logs, desta maneira não haverá concorrência de LOG com DADOS. Como o share que estamos criando será o \\SQLSMB\SQLBIN selecionaremos o discos que criamos para o BIN, no caso o “Volume1” que possui 40 Gbs. E clique sobre o botão “Next”
Figura 1.16: Separando os shares conforme o workload.
14. Na tela “Specify share name” informe o nome SQLBIN para o “Share Name” e clique sobre o botão “Next”
Figura 1.17: Informando o nome do share.
15. Na tela “Configure share settings” podemos escolher algumas opções de segurança como criptografia, vamos deixar a opção default, clique sobre o botão “Next”
Figura 1.18: Configuracao do share.
16. Na tela “Specify permissions to control access” você pode customizar as permissões, como boa prática, configure as permissões conforme o direcionamento da área de segurança de seu ambiente, neste laboratório deixe leitura para alguns usuários e adicionei a conta de serviço do SQL Server contoso\SQLService que criei previamente, com full control do share, clique sobre o botão “Next”.
Figura 1.19: Configurando as permissoes do share.
17. Na tela “Confirme selections” clique em “Create”
Figura 1.20: Tela de confirmacao informando o protocolo SMB e o Continuous avalability.
18. Na tela “View results” verifique se as tarefas foram concluídas com sucesso e clique sobre o botão “Close”
Figura 1.20: Tela de conclusão da criação do share.
19. Repita as etapas 11 a 18 para criar os próximos shares selecionando os discos específicos: \\SQLSMB\SQLDATA \\SQLSMB\SQLLOG
disco volume2 (20 GBs) disco volume3 (10 GBs)
20. Após criação dois outros dois compartilhamentos você deverá ter um resultado final semelhante à figura 1.21 abaixo:
Figura 1.21: Lista de compartilhamentos SMB com os repectivos discos.
Instalando o SQL Server em cluster sobre SMB Após a criação e configuração do SOFs no tópico acima, agora nos resta criar um novo cluster SQL Server selecionando o SOFs como storage. Para este laboratório vou utilizar o SQL Server 2016, como foi mencionado anteriormente o SQL Server já oferece o suporte ao SMB desde a versão 2012. Não detalharei passo a passo como criar um cluster, mas caso você não saiba ou tem dúvidas como criar um cluster, recomendo a leitura do livro “SQL Server 2014: Alta Disponibilidade na Prática com AlwaysOn Failover Cluster Instances”, de minha coautoria juntamente com Nilton Pinheiro. Siga as etapas abaixo para instalar o SQL Server em cluster sobre SMB, 1- Crie um cluster para o SQL Server, instale o Windows Server 2012 R2 com no mínimo dois nós, não apresente discos para este cluster, iremos utilizar o SOFs! 2- Inicie o Setup do SQL Server e selecione a opção “Installation” e “New SQL Server Failover cluster installation” para iniciar o setup do SQL Server em cluster.
Figura 1.22: Iniciando a instalação do SQL Server em cluster
3- Na tela “Product Key” informe a o cdkey do SQL Server e clique em “Next” 4- Na tela “License terms” marque a opção “I Accept the license terms” clique em “Next” 5- Na tela “Microsoft update” se o seu servidor tiver acesso à internet, você poderá marcar a opção “Use Microsoft Update to check for update” desta maneira o setup do SQL efetuará o download das atualizações do SQL e instalará. Neste laboratório não tenho acesso a internet, neste caso apenas clique em “Next” 6- Na tela “Product Update” e clique em “Next”. 7- Na tela “Installation Failover Cluster Rules” assegure que não existe nem um item em falha e clique em “Next”
Figura 1.23: Tela de verificações do cluster.
8- Na tela “Feature Selection” selecione os itens que deseja instalar e clique em “Next”.
Figura 1.24: Tela para selecao de features do SQL Server.
9- Na tela “Instance Configuration” informe o SQLINSTP1 para “SQL Server Network Name” e P1 para “Named Instance” e clique em “Next”
Figura 1.25: Tela de configuração da instancia.
10- Na tela “Cluster Resource Group” informe o nome da role a ser criada no cluster, informe SQLINSTP1 e clique em “Next”
Figura 1.26: Informando o nome da role que será criada no Failover Cluster.
11- Na tela “Cluster Disk Selection” observe que o setup não lista os discos pois não foi apresentado discos no Failover Cluster, clique no botão “Next”.
Figura 1.27: O setup não lista os discos, pois não foi apresentado discos no cluster.
12- Na tela “Cluster Network Configuration” informe o IP para o nome virtual do SQL Server.
Figura 1.28: Informando o IP para o nome virtual do SQL.
13- Na tela “Server Configuration” informe a conta de serviço do SQL Server e caso deseje alterar o Collation, clique sobre a aba “Collation” altere conforme sua necessidade e clique em “Next”
Figura 1.29: Informando a conta de serviço do SQL Server
14- Na tela “Database Engine Configuration” selecione as configurações de segurança conforme o vosso padrão e selecione a aba “Data Directories”
Figura 1.30: Configuração de acessos do SQL Server
15- Na aba “Data Directories” começa a configuração do SQL Server sobre SMB, informe os nomes dos compartilhamentos criados no SOFs conforme a figura 1.31 abaixo:
Figura 1.31: Configuração setup do SQL Server nos compartilhamentos do SMB
Será exibido uma mensagem alertando sobre as permissões do compartilhamento clique sobre o botão “Yes”
Figura 1.32: Tela de alerta sobre as permissoes no compartilhamento.
16- Na janela “Ready to Install” revise os itens e clique em “Install” note que o diretório da instalação são os caminhos criados no SOFs
Figura 1.33: Tela de verificacao da configuração da intalacao do SQL
17- Na tela “Complete” clique em “close” concluindo a instalação de SQL Server.
Figura 1.34: Tela de conclusão do setup do SQL.
Executando o setup do SQL Server no segundo nó (Add Node) Concluído a instalação do SQL Server sobre SMB no primeiro nó, basta executar o setup no segundo nó para adicionar o segundo nó. 1- Acesse o segundo nó do Cluster do SQL Server e execute o setup do SQL Server selecionando a opção “Add node to a SQL Server Failover cluster”
Figura 1.35: Setup para add node do SQL em cluster.
2- Na tela “Product Key”, informe o cdkey e clique em “Next”. 3- Na tela “License Terms” selecione a opção “I accept the license terms” e clique em “Next” 4- Na tela “Microsoft Update” clique em “Next” 5- Na tela “Product Updates” clique em “Next” 6- Na tela “Add Node Rules” verifique se todos os itens estão validados e clique em “Next” 7- Na tela “Cluster Node Configuration” selecione a instância “P1” e clique em “Next”.
Figura 1.36: Selecionando a instância que sera efetuado o add node.
8- Na tela “Cluster Network Configuration” clique em “Next” 9- Na tela “Service Accounts” informe a senha da conta de serviço do SQL Server e clique em “Next”.
Figura 1.37: Informe a senha da conta de serviço
10- Na tela “Ready to Add Node” revise o setup e clique em “Install”, observe que neste tipo de setup não existe a informação do SMB, isto porquê estamos adicionando um nó em cluster existente e esta informação já esta implícita.
Figura 1.36: Tela de informações sobre o setup
11- Na tela “Complete” clique em “close” concluindo a instalação do Add Node do SQL Server.
Figura 1.37: Tela conclusão do add node do SQL Server
Pronto! Neste ponto temos o nosso ambiente operacional e rodando no SMB. Crie um database DBTeste e verifique os dados no File Server CREATE DATABASE [DBTeste] ON PRIMARY (NAME = N'DBTeste', FILENAME = N'\\sqlsmb\sqldata\MSSQL13.P1\MSSQL\Data\DBTeste.mdf') LOG ON (NAME = N'DBTeste_log', FILENAME = N'\\sqlsmb\sqllog\MSSQL13.P1\MSSQL\Data\DBTeste_log.ldf') GO
Figura 1.38: Compartilhamento do binário do SQL Server
Podemos notar na figura 1.38 que o diretório \\SQLSM\SQLBIN temos uma pasta MSSQL13.P1 que intencionalmente foi mantida desta forma pois aqui temos um outro benefício do SQL sobre SMB, posso instalar mais de uma instância em cluster no mesmo disco!! Aliás posso instalar várias instâncias neste compartilhamento. No cluster com disco dedicado eu preciso ter um grupo de disco para cada instância. Este reaproveitamento é excelente para um uso otimizado de espaço no storage, mas devemos ter o cuidado de criar as nomenclaturas corretas para evitar futuros problemas e também para uma boa documentação, como podemos notar nas figuras 1.39 foi criado uma pasta MSSQL13.P1\MSSQL\Data para armazenamento dos dados isto evita confusões caso eu instale mais de uma instancia no mesmo compartilhamento.
Figura 1.39: Compartilhamento de DADOS com a organização de pasta para instância
Verificando o SMB Conectado em um dos nós do cluster de SQL Server inicie a janela do PowerShell e execute o cmdlet abaixo: Get-SmbConnection O retorno deve ser parecido como o resultado abaixo: ServerName ShareName UserName Credential ---------- --------- ----------------sqlsmb sqlbin CONTOSO\sqlservice CONTOSO.COM\sqls... sqlsmb sqldata CONTOSO\sqlservice CONTOSO.COM\sqls... sqlsmb sqllog CONTOSO\sqlservice CONTOSO.COM\sqls...
Dialect ------3.02 3.02 3.02
Como podemos notar no resultado acima, temos 3 conexões SMB 3.02 para a credencial do SQLService. Abaixo, na figura 1.40, se olharmos o Failover Cluster do SQL Server notaremos que não temos recurso de discos, apenas o Nome, IP e os serviços do SQL Server.
Figura 1.40: Failover Cluster do SQL Server não contém discos.
Efetue um teste de Failover no SQL Server e ao final mantenha o serviço rodando no nó SMBSQLN1, note que este tipo de Failover causa indisponibilidade no SQL! Após os testes de Failover, execute o T-SQL abaixo para verificar o status atual do cluster. select * from sys.dm_os_cluster_nodes select sqlserver_start_time,GETDATE() as hora_atual from sys.dm_os_sys_info
O Resultado deve ser parecido com a figura 1.41, note que o nó ativo é o SQLSMBN1 e o Serviço foi iniciado as 21:28.
Figura 1.41: verificando qual o nó ativo e quando foi iniciado
Após os testes de Failover, execute o T-SQL abaixo para criar um loop para criar um pouco de I/O nos servidores do File Serves(SOFs) e com o loop em execução vamos fazer um Failover do SOFs e ver o que acontece com o SQL Server. Observe que teremos um I/O nos compartilhamentos SMB e durante este I/O vou fazer um Failover. use DBTeste go --cria tabela de teste create table TBteste (id int identity(1,1), data datetime default getdate(), campo1 int) go --loop para gerar i/o declare @x int =0 While @x < 999999 begin insert into TBteste (campo1) values (@x) set @x = @x + 1 checkpoint end
Deixe o comando acima rodando no SQL Server e conecte-se em um dos nós do cluster de File Server, inicie a janela do PowerShell e execute o cmdlet abaixo para executar o Failover Move-ClusterGroup SQLSMB Execute o cmdlet algumas vezes (no mínimo umas 5 vezes) você verá que o Failover do SMB é extremamente rápido. Feito os Failovers, verifique o que ocorreu com o serviço do SQL Server.
Figura 1.42: Script continua executando mesmo com failover do SOFs
Como podemos observar na figura 1.42, o SQL Server não sofreu impactos com os diversos failovers do SOFs, aqui ocorre o Transparent Failover do SMB! Se você reiniciar o nó ativo do cluster de File Server, mesmo assim o SQL Server continuará disponível e não será impactado por stop e start no serviço do File Server que estamos utilizando no SQL Server para armazenar os dados do Binário e dados do database de teste. Você pode parar o script e executar novamente o T-SQL para verificar o status do cluster de SQL Server select * from sys.dm_os_cluster_nodes select sqlserver_start_time, GETDATE() as hora_atual from sys.dm_os_sys_info
Figura 1.43: Script para verificar o status do cluster pós failover do SOFs
O Resultado deve ser parecido com a figura 1.43, note que o nó ativo ainda é o SQLSMBN1 e o Serviço foi iniciado as 21:28, ou seja, não houve falha no cluster de SQL Server. Esta é a maravilha do Transparent Failover do SMB. Bom, espero que em seu próximo projeto você possa considerar o SMB como alternativa para os discos em seu ambiente, ele têm diversos benefícios que os sotrages convencionais não têm além poder aumentar significativamente a performance de I/O com o RSS e o Multichannel.
AlwayOn Availability Groups - Conceitos e Cenários Neste capítulo será apresentado uma breve descrição dos componentes e conce itos acerca do AlwaysOn Availability Groups, bem como uma visão sobre cenários de utilização, prós e contras desta features que foi lançada à partir do SQL Server 2012. Por Edvaldo Castro http://edvaldocastro.com/ Revisão técnica por Nilton Pinheiro http://www.mcdbabrasil.com.br/
Fonte: https://msdn.microsoft.com/en-us/library/ff877884.aspx
Introdução Desde as primeiras versões lançadas o SQL Server sempre teve diversas opções para o provimento de Alta Disponibilidade (HA – High Availability) e Recuperação de Desastres (DR – Disaster Recovery). As principais opções até antes do surgimento do AlwaysOn Availability Groups (AG) eram, dentre outras:
SQL Server Failover Cluster Instances (FCI)
Fonte: https://technet.microsoft.com/en-us/library/hh270278%28v=sql.110%29.aspx
A principal característica de uma solução de Alta Disponibilidade do SQL Server utilizando-se o Failover Cluster Instances, é que esta baseia-se principalmente na disponibilidade do serviço do SQL Server, juntamente com um grupo de recursos que trabalham em conjunto como um único servidor, ficando transparente para as aplicações que este serviço encontra-se em uma solução de HA. Uma solução de Alta Disponibilidade com FCI depende diretamente de um Windows Server Failover Cluster (WSFC), que suporta a capacidade de uma (ou mais) instância(s) do SQL Server estar ativas em vários nós (servidores) (um por vez).
Database Mirroring
Fonte: https://msdn.microsoft.com/en-us/library/ms189852.aspx
Uma solução de Replicação de dados baseada em uma feature interna do SQL Server que independe de recursos externos (como o caso do SQL Server FCI que depende do Windows Server Failover Cluster). A principal característica do Database Mirroring é o espelhamento de um único banco de dados por vez, sendo possível também incluir uma instância como testemunha para monitorar o serviço de espelhamento entre as instâncias, o que possibilita o failover automático em caso de falhas.
Log Shipping
Fonte: http://blogs.technet.com/b/sql_server_isv/archive/2010/11/03/sql-server-2008-r2-high-availability-options-for-temenos-t24-part-3-of-4-logshipping.aspx
O princípio básico de uma solução de log shipping, é a criação automática de backups de log em uma instância primária, cópia também automática destes para uma segunda instância e restore na instância secundária. As instâncias que recebem o shipping destes logs podem ficar disponíveis para leitura, porém quando é executado o restore dos logs no momento do agendamento, todas as conexões existentes na base são perdidas.
Replicação¹
Fonte: https://technet.microsoft.com/en-us/library/ms152567%28v=sql.110%29.aspx
Considerada por muitos uma solução de alta disponibilidade, e não considerada por outros tantos, a replicação baseia-se na criação de artigos contendo tabelas que são replicadas de diversas formas (transacional, snapshot, merge, etc). Os que não a consideram como uma solução de alta disponibilidade, justificam isso basicamente pela complexidade na administração e principalmente pela necessidade de intervenção humana no caso de uma falha da instância primária.
AlwaysOn O AlwaysOn Availability Groups foi lançado na versão 2012, edição Enterprise do SQL Server com a premissa de prover Alta Disponibilidade (HA) e Recuperação de Desastre (DR) em uma única solução. Assim como o SQL Server FCI, o SQL Server AG também depende diretamente do WSFC, porém, esta é uma das pouquíssimas semelhanças entre essas duas soluções, que infelizmente tem o mesmo nome com sobrenomes diferentes, fazendo com que muitos profissionais confundam e desconheçam a diferença entre elas. O AlwaysOn AG provê solução de HADR pelo agrupamento de bancos de dados, que tem seus dados replicados de forma sincrona ou assincrona para replicas secundarias que podem ou não serem utilizadas para aliviar o workload da réplica primária, tendo as conexões de leitura redirecionadas para estas. Alta Disponibilidade (HA) A alta disponiblidade dá-se pela criação de uma Role no WSFC para cada AG criado, nesta Role, existem apenas um recurso referente ao Availability Group criado na instância (lembrando que podem ser criados diversos grupos de bancos de dados em cada instância), um IP referente ao Nome Virtual (caso seja configurado um Listener) que pode ser criado por grupo de disponibilidade, e o próprio nome virtual do AG.
Em uma eventual falha na instância (assim como no FCI), serão direcionados às réplicas secundárias somente os grupos de disponiblidade com seus respectivos bancos de dados (dependendo das configurações de replicação). Os bancos de dados que que não fizeram parte do grupo de disponibilidade somente poderão ser acessados pelo nome da instância que pertencem ou quando o Listener de algum dos A.G. estiver disponível nesta instância, pois não participam de nenhum AG e consequentemente não tem réplicas em nenhum outro servidor. Pelo fato de não carregar consigo diversos recursos (como é o caso do FCI) na Role do WSFC, um failover de uma role com um AG tende a ser muito mais rápido do que um failover de uma role que contém um serviço do SQL Server como FCI, pois este precisa ser movido (em caso de falha, ou move group manual) juntamente com discos, nome e ip virtuais, MSDTC, etc. O principal requisito para que haja um failover automático em uma solução com AG, é que a replicação esteja em modo síncrono e configurada para failover automático. Recuperação de Desastre (DR) Quando há a ocorrência de um desastre, independente da natureza do mesmo, o sucesso no funcionamento da solução de alta disponibilidade (redirecionamento automático das conexões) por si só já é a primeira fase da recuperação de um desastre, pois se ocorreu um desastre e houve um failover automático para uma segunda réplica síncrona, significa que apesar de o desastre ter ocorrido, o SQL Server recuperou-se do mesmo rapidamente através de sua solução de Alta Disponiblidade. Apesar de ser verídica a informação supracitada, quando se fala em DR, o principal foco é para as situações onde não necessariamente existe a disponibilidade de uma replicação síncrona (replicação para um site remoto distante, por exemplo). Nestes casos, geralmente é necessário intervenção manual para que o sistema possa ser reestabelecido. O AlwaysOn AG, protege contra falhas, os bancos de dados que estão inseridos em um AG, através de replicação de todos seus dados. Se esta replicação se dá de forma assíncrona, a réplica secundária de uma solução de HADR utilizando o AlwaysOn AG, não deixará o banco disponível automaticamente, para isso é necessário que haja ação manual para síncronizar os dados desta base, o que devido ao fato de poder não estar em sincronismo com a réplica primária, “pode” haver perda de dados. A possibilidade de perda de dados, dependerá muito das características da aplicação, pois mesmo estando com uma configuração assíncrona, quando uma transação é submetida à réplica primária, ela é imediatamente replicada para as secundárias quando possível, o que difere uma replicação síncrona de uma replicação assíncrona, é que a segunda não não exige que uma transação seja consistida na replica secundária antes de considerar a transação completada e retornar para a aplicação . Normalmente as réplicas pertencentes a uma solução com AlwaysOn AG que são destinadas à recuperação de desastres, ficam configuradas com replicação de modo assíncrono e em sites remotos, com relação ao site (datacenter) principal, onde se encontra a réplica primária com o AG. Principais Conceitos Após uma sucinta introdução ao funcionamento do SQL Server AlwaysOn Availability Groups e seus antecessores, a seguir falarei um pouco sobre cada um dos principais componentes de um AlwaysOn AG, bem como algumas particularidades de cada um destes componentes.
Availability Groups (AG) Um Availability Group, ou Grupo de Disponibilidade na tradução literal, consiste em um grupo de banco de dados de usuários (bancos de dados de sistema não podem fazer parte de um AG), que estão configurados juntamente no mesmio grupo de disponbilidade e que em caso de falha ou intervenção manual, fazem failover juntos. Alguns dos grandes benefícios do AlwaysOn AG em relação ao AlwaysOn FCI é a velocidade no Failover que por não necessitar gerenciar com paradas e inícios os discos e serviços do SQL Server, tende a ser muito mais rápido, e também a replicação das bases de dados de um AG de acordo com a quantidade de réplicas pertinentes ao AG em questão. Um dos grandes pronto fracos do SQL Server AlwaysOn AG, é a não possibilidade de utilização do MSDTC (Microsoft Distributed Transaction Coordinator), o que impossibilita a utilização em cenários onde há necessidade de existência de transações distribuídas. Esta restrição existiu nas versões 2012 e 2014 do SQL Server, porém nos primeiros CTPs (Community Technical Previews) do SQL Server 2016 já havia sido anunciado (na data em que este texto foi escrito) a compatibilidade do AlwaysOn AG com o MSDTC. Por ser o AlwaysOn AG uma solução para disponbilidade de grupos de bancos de dados, itens pertencentes à instância do SQL Server (Logins / Senhas / Jobs) não são replicados, devendo estes serem feitos manualmente. Availability Replicas Um Availability Réplica são as instâncias do SQL Server que fazem parte da solução de HADR de um determinado AG. Um AG obrigatóriamente tem 1 (uma) réplica primária e até 4 (quatro) réplicas secundárias na versão 2012 do SQL Server Enterprise Edition, e até 8 (oito) réplicas secundárias na versão 2014 do SQL Server Enterprise Edition. Comumente as réplicas em uma solução de HADR com o AlwaysOn AG são instaladas como Stand Alone, porém também é possível em um cenário um pouco mais complexo, insegir uma instância FCI como uma réplica de um AG, neste cenário as réplicas FCI não podem ser configuradas para Failover automático. Um excelente uso das réplicas secundárias, é que além de fornecer duplicidade de dados, podendo ser crucial em momentos de desastres, estas também podem ser utilizadas para leitura, e consequentemente alívio na carga da réplica primária, podendo esta ter seu trabalho focado para a utilização de sistemas transações (insert, update e delete). Availability Database Cada um dos bancos de dados que fazem parte de um AG recebe o nome de Availability Database. Geralmente os bancos são agrupados no mesmo AG por tipo de negócio e uma falha a nível de banco de dados não causa failover de um AG, tendo para isto o mesmo comportamento de uma solução de SQL Server FCI. Nas versões CTP (Community Technical Preview) do SQL Server 2016, já estava previsto a possibilidade de configuração, para que o Failover pudesse acontecer em caso de falha a nível de banco de dados.
Listener O Listener funciona para o AG assim como o Virtual Server Name para uma solução de FCI. É criado um nome virtual para cada AG, e este nome faz o redirecionamento das conexões comuns para a réplica primária, e o redirecionamento das conexões somente leitura (com o parâmetro Aplication Intent = Readonly na string de conexão) para as réplicas secundárias configuradas para receber tais tipos de workloads) Por padrão, via SSMS (SQL Server Management Studio) é possível criar apenas um Listener por AG, porém através da ferramenta de Administração do Cluster do Windows, é possível criar Listeners adicionais para o mesmo AG. Uma das grandes vantagens na criação de múltiplos Listeners para um único AG é atender às necessidades de conexão de aplicações legadas, com código fechado que não podem ser alteradas. Esta é uma solução de contorno para casos específicos, toda a administração do Alwayson AG deve ser realizada estritamente via SSMS.
Cenários Conforme o titulo deste capitulo, bem como já falado no decorrer do mesmo por diversas vezes, o AlwaysOn AG é uma das opções (sim, provavelmente a melhor e mais completa) para suprir necessidades de contingenciamento (geográfico ou não) dos bancos de dados e também necessidades de Alta Disponibilidade desses mesmos dados. Os principais e mais propícios cenários para utilização do AlwaysOn Availability Groups são os semalhantes aos exemplos infracitados: Disponibilidade principalmente dos dados e não somente do Serviço (SQL Server) Não utilização de transações distribuídas que utilizem o MSDTC (Microsoft Distributed Transaction Coordinator). Não utilização de cross database transactions. Necessidade de criação de solução que combine HA e DR onde não existe disponibilidade para storage compartilhada (Necessario no FCI, por exemplo). Existem diversos cenários onde a aplicabilidade do AlwaysOn AG não se justifica, seja por não atendimento das demandas de forma objetiva e direta, seja por motivos de ordem financeira (Licenciamento alem do necessário e etc).
O SQL Server AlwaysOn AG é sempre a melhor escolha? “Nada é bom pra tudo” – Autor Desconhecido Não existe uma solução, produto, serviço ou qualquer coisa que vá ser sempre a melhor opção em todos os cenários. No caso do AlwaysOn AG não é diferente, o AlwaysOn AG é uma solução muito boa, que atende a diversos cenários onde há requisitos de soluções de Alta disponibilidade e Recuperação de Desastres, mas existem casos onde não se aplicam tais soluções. Uma grande falha de um profissional que está desenhando um projeto é o desconhecimento de opções alternativas para adequação à demanda que lhe é proposta, isto faz com que o profissional tenha sempre em mente a utilização de um determinado conceito ou solução (neste caso o AG) para toda e qualquer situação, e nem sempre é a única nem mesmo melhor opção a ser utilizada.
É preciso levar-se em conta diversos fatores como: Recursos Financeiros do projeto, demanda por Alta Disponibilidade e Recuperação de Desastres, skill técnica dos profissionais acerca das possíveis soluções a serem implementadas, dentre diversos outros. Tais fatores determinam se para determinadas demandas, haverá melhor aplicabilidade de umas solução de AG, FCI, Log Shipping e etc. Portanto o AlwaysOn Availability group pode nem sempre ser a solução mais barata, mais apropriada e nem a que mais se adequa aos requisitos propostos, é preciso ter bastante cuidado na fase de projeto para implementação de uma solução de Alta Disponibilidade, Recuperação de Desastres ou até mesmo de replicação de dados.
Benefícios de utilização do AG A seguir uma descrição sucinta sobre as principais vantagens em se utilizar o AlwaysOn Availability Groups como solução de Alta Disponibilidade e Recuperação de Desastre. Fácil configuração e administração. Dispensa o uso de storage compartilhada. Podem ser utilizados servidores atuais já instalados com instâncias standalone. Segurança física dos dados, devido ao fato de que cada banco envolvido em um AG tem varias cópias. Correção automática de páginas corrompidas, substituindo a página corrompida por uma íntegra de uma das réplicas. Alívio de carga no servidor primário, podendo redirecionar workloads de leitura para as réplicas secundárias, dentre outros. Possui uma excelente compatibilidade com outras features do SQL Server.
Pontos “negativos” do AG Talvez “pontos negativos” não seja a expressão mais adequada, mas aqui são listados os esforços necessários para que uma solução com o AlwaysOn AG seja implantada em um ambiente. Storage: Cada nova réplica secundária adicionada ao AlwaysOn AG, existe que o armazenamento seja duplicado em relação aos bancos que fazem parte de um AG na réplica primária. MSDTC: Conforme já mencionado previamente, o AlwaysOn AG não trabalha com transações distribuídas (até a versão 2014). Logins, senhas, jobs e outros itens pertencentes somente à instância não são replicados, neste caso é necessário criar uma solução alternativa para sanar esta necessidade. Os pontos supracitados são os principais “poréns” encontrados durante o planejamento e execução de um Alwayson Availability Group, o que em determinadas situações pode até ser um fator que impossibilite sua implantação.
Conclusão Não somente o AlwaysOn Availability Groups, AlwaysOn Failover Cluster Instances ou qualquer outra solução ou produto tem seus prós e contras, seus benefícios e cuidados a serem tomados, durante um planejamento e momento de decisão de qual caminho seguir e qual solução adotar. É preciso ter calma, conhecimento e parcimônia em momentos com este, em que uma decisão mal tomada, um planejamento mal feito, podem levar a prejuízos e consequências muito piores no futuro. O AlwaysOn Availability Group é uma excelente ferramenta que já está disponível a quem possuir licença do SQL Server 2012 ou 2014 (únicos disponíveis até a redação deste texto) na versão Enterprise Edition. Com todos os cuidados necessários e um bom planejamento é possível construir soluções confiávels, resilientes e robustas utilizando-se do AlwaysOn AG.
Referências deste capítulo: Windows Server Failover Clustering (WSFC) with SQL Server https://technet.microsoft.com/en-us/library/hh270278%28v=sql.110%29.aspx AlwaysOn Professional http://blogs.msdn.com/b/alwaysonpro Edvaldo Castro’s Blog http://edvaldocastro.com
Boas práticas em configuração de servidores de banco de dados Nesse artigo vamos abordar alguns itens referente á boas práticas em servidores de banco de dados. O intuito de melhor aproveitar os recursos de hardware do servidor e evitar pontos de contenções decorrentes de configurações default que na maioria das vezes não atendem os ambientes corporativos que rodam o SQL Server. Por Leonardo Pedroso Costa http://sqleo.com.br/ Revisão técnica por Edvaldo Castro http://edvaldocastro.com/
Introdução Antes e após realizar a instalação do SQL Server em seu ambiente é importante verificar alguns pontos para que nenhuma surpresa desagradável venha a acontecer. O SQL Server por padrão já vem instalado com opções de fábrica e alguns itens de configuração não condizem com a realidade da maioria dos ambientes. Nesse artigo irei explanar itens como configuração de memória, paralelismo, tempdb e autogrowth de banco de dados, dentre outros.
Ajuste do Allocation Unit Size nos discos do Windows Verificar o alinhamento das partições entregues ao S.O. Essa opção deve ser verificada antes de instalar o SQL Server, caso contrário, temos o trabalho administrativo para mover as bases de dados para outra unidade, formatar o disco com o tamanho correto e voltar os bancos para a partição original. O SQL Server trabalha melhor com o alinhamento de 64kB e na maioria das vezes, a unidade apresentada ao Windows é criada com o valor default de 8192 bytes. Para verificar o tamanho do Allocation Unit Size basta clicar com o botão direito sobre a partição desejada e clicar em Format:
Ao clicar nessa opção, abrirá uma tela com as informações sobre a partição e no label Allocation Unit Size mostrará o valor atual da configuração: 8192 Bytes e as opções disponíveis para essa partição: 512, 1024, 2048, 4096 e 8192 byes, além de 16, 32 e 64 kilobytes.
Basta selecionar a opção 64 kilobytes e clicar em Start. Lembrando que essa opção apagará todos os dados que existirem dentro dessa unidade, portanto faça isso antes de instalar o SQL Server ou mova os dados que contiver nessa partição para outra área de disco disponível. Fonte: https://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx
Desabilitar o usuário SA Por razões de segurança, considere desabilitar o usuário SA e criar um grupo no Active Directory como sysadmin e incluir somente os DBA's responsáveis pela instância nesse grupo. Outra opção é habilitar somente o Windows Authentication Mode no momento da instalação, e só depois habilitar o Mixed Mode, assim não será preciso digitar a senha do SA e o mesmo será criado com o status desabilitado.
[Na imagem acima temos a opção de manter habilitado apenas o Windows Autentication Mode evitando que o usuário SA seja criado no momento da instalação.]
Configuração do banco de dados TEMPDB O SQL Server possui um banco de dados chamado tempDB que é o responsável por armazenar requisições oriundas de todos os bancos de dados da instancia como: tabelas temporárias globais e locais, cursores, versionamento de linhas quando os bancos utilizando o snapshot isolation level, operações online de índices, dentre outras. Pode ser encontrado em: Databases \ System Databases \ tempdb
Como boas práticas para evitar ou corrigir contenção no banco de dados tempdb, sugerimos que: Os arquivos de dados e log do tempdb sejam acomodados em um disco separado fisicamente dos arquivos de dados e log de bancos de usuário:
Criar múltiplos arquivos de dados para o banco tempdb. Essa configuração é importante e ajuda a diminuir contenções que venham a ocorrer no tempdb. Para chegar ao número correto de arquivos de banco de dados seguimos a seguinte regra: Processadores lógicos Menor ou igual a 8
Quantidade de arquivos 8
Maior que 8 8 + múltiplos de 4 Por default o banco de dados vem configurado com 1 arquivo de dados e 1 de log, configurações de Autogrowth/Max Size e Initial Size (MB) com tamanhos pré definidos, conforme imagem abaixo:
No exemplo abaixo o tempdb foi divido em 5 arquivos para melhorar o throughput das operações nele realizadas. Note que o tamanho inicial e o Autogrowth devem estar do mesmo tamanho para garantir o crescimento uniforme dos bancos.
Essa configuração pode ser realizada com o SQL Server em funcionamento que o mesmo já se beneficiará dos novos arquivos instantaneamente. Na versão 2016 essa configuração poderá ser realizada no momento da instalação do SQL Server, podendo configurar os seguintes itens: Quantidade de arquivos Tamanho inicial (em MB) Autogrowth (em MB) Localização dos arquivos
Fonte: https://support.microsoft.com/en-us/kb/328551 https://support.microsoft.com/en-us/kb/2154845
Paralelismo As opções Cost Threshold for Paralelism e Max Degree of Paralelism controlam a maneira como o SQL Server paraleliza suas requisições. A primeira opção representa o custo da query como um limite entre paralelizar ou não e aceita valores de 0 a 32.767, o valor default é 5. A segunda opção representa a quantidade de cores utilizados para o paralelismo, recomenda-se que a configuração do MAXDOP (Max Degree of Paralelism) seja ajustada de acordo com número de cores presentes em um NUMA NODE. “For servers that have NUMA configured, the maximum degree of parallelism should not exceed the number of CPUs that are assigned to each NUMA node. This is because the query is more likely to use local memory from 1 NUMA node, which can improve memory access time.” Para mais informações, us/library/ee210547(SQL.105).aspx.
consulte:
https://msdn.microsoft.com/en-
Configuração de memória Configurar corretamente as opções: Max Server Memory e Min Server Memory no servidor SQL, por default a opção Max Server Memory vem com o valor 2147483647MB setado, sendo este fora da realidade da maioria dos ambientes. Ao definir o tamanho da memória alocada para o SQL, é de suma importância separar uma parte dela para o Windows, visto que existem vários processos rodando em background tais como agentes de backup e monitoria. O Min Server Memory é o valor que o SQL Server vai considerar como o limite para liberar memória ao Windows, quando há pressão por memória no servidor, o Windows emite um sinal ao SQL Server solicitando que o mesmo devolva memória para ser utilizado em outros processos, caso esse valor esteja preenchido, o SQL Server não cederá esse espaço para o Windows ou outro processo que esteja rodando no servidor. No exemplo abaixo temos a configuração para um servidor com 8GB de memória:
Importante: Ao mudar a configuração do Max Server Memory os caches SQL Plans, Object Plans e Boud Trees são zerados, portanto tenha ciência disso quando for realizar tal operação.
Compressão de backup Essa opção faz com que a instância trabalhe de forma nativa com a compressão de backup. Entre as vantagens estão a redução do tamanho e tempo necessário para realização do backup de banco de dados, porém utiliza-se de mais CPU para realizar tal operação. Por default essa opção vem desabilitada e pode ser alterada via interface gráfica ou command line, confome exemplos abaixo: Via linha de comando: EXEC sys.sp_configure N'backup compression default', N'1' GO RECONFIGURE WITH OVERRIDE GO
Via interface gráfica: Habilitar a opção Compress Backup.
Para efeitos de comparação, faremos um script de backup rodando com a opção desabilitada, e logo após o mesmo script porém coma opção habilitada: --Habilita a compressão de backup na instância EXEC sys.sp_configure N'backup compression default', N'1' GO RECONFIGURE WITH OVERRIDE GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\bkp\advworks_com_compressao.bak' WITH NOFORMAT, INIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO --Desabilita a compressão de backup na instância EXEC sys.sp_configure N'backup compression default', N'0' GO RECONFIGURE WITH OVERRIDE GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\bkp\advworks_sem_compressao.bak' WITH NOFORMAT, INIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO
No nosso exemplo, o tamanho do backup com compressão chegou a ser 4 vezes menor que o backup sem compressão:
Tempo gasto em milissegundos para realizar o backup: Sem compressão: SQL Server Execution Times: CPU time = 108 ms, elapsed time = 3376 ms. Com compressão: SQL Server Execution Times: CPU time = 15 ms, elapsed time = 2146 ms.
Além do espaço em disco reduzido devido á compressão do backup, o tempo necessário para realizar tal operação diminui consideravelmente pelo simples fato do backup compactado ser menor que o tradicional e utilizar menos operação de escrita em disco, conforme citação abaixo retirada do MSDN: -“Como um backup compactado é menor do que um backup não compactado dos mesmos dados, a compactação de um backup normalmente requer menos operações de E/S do dispositivo e, portanto, normalmente aumenta significativamente a velocidade do backup.” Fonte: https://msdn.microsoft.com/pt-br/library/bb964719(v=sql.120).aspx
Configurar o Blocked Process Threshold Essa opção especifica o tempo em segundos no qual serão gerados os relatórios referente a sessões bloqueadas no SQL Server Profiler. Por default não é gerado nenhum report desse tipo de atividade, sendo necessário habilitar Caminho: Propriedades do Servidor -> Advanced - > Miscellaneous
Ou preferir vinha linha de comando: sp_configure 'blocked process threshold', 5 reconfigure
Para verificar os relatórios de processos bloqueados, basta abrir o SQL Profiler e conectar na instancia desejada, depois clicar em Show All Events, conforme imagem abaixo:
Na opção Erros and Warnings, clique em Blocked Process Report e depois em Run:
Após realizar essa parametrização, um evento como esse será gerado no profiler sempre que houver algum bloqueio acima do valor configurado no parâmetro Blocked Process Threshold.
Ajustar os arquivos dos bancos Master, Model e Msdb Master Os bancos de dados Master e MSDB vem configurados com os valores default para autogrowth, initial size e max filesize. Para evitar fragmentação no log e esperas por disco em casos de crescimento do arquivo, é recomendável que aumente os valores de autogrowht de acordo com o seu ambiente. No exemplo abaixo o crescimento dos arquivos do banco MSDB foram alterados para 1024MB de log e dados, porém estes valores condizem com a realidade de um ambiente em específico e não é padrão para todas as instalações. Analise se o banco tem crescido fora do normal e ajuste um tamanho adequado para seu ambiente.
O mesmo deve ser feito para o banco de dados master:
Em geral o banco de dados MSBD possui muito mais dados do que o banco Master, isso ocorre porque é no banco MSDB que ficam salvos os históricos de Jobs e informações referente a backups (full,
diferencial e logs), dentre outras informações. Por esse motivo o tamanho do banco MSDB tende a ser maior que o Master e em consequência disso, o autogrowth é realizado com mais frequência, justificando ter seu autogrowth configurado com um valor maior. Em relação ao banco model, devemos tomar cuidado ao realizar tais alterações, pois todos os bancos que venham a ser criados na instância usam essa base como modelo para sua configuração. Portanto se o banco model estiver com os arquivos configurados com o tamanho de 10GB, o mesmo será replicado para os demais bancos que forem criados após a configuração do banco model.
Aumentar a quantidade de arquivos do ERRORLOG Cada vez que o SQL Server é reiniciado, um novo arquivo de log é criado. Por default o SQL Server armazena 6 arquivos de log históricos mais o atual, portanto considere aumentar esse número máximo de “error log files” para não perder informações do seu ambiente em caso de vários restarts seguidos, pois os arquivos trabalham de maneira circular e quando o limite é atingido, os arquivos mais antigos são sobrescritos. Caminho: Server -> Management -> SQL Server Logs, botão direito em SQL Server Logs e clique em configure:
Nesse exemplo colocamos o SQL Server para guardar até 20 arquivos de log ao invés de apenas 6!
Configurar a opção Instant File Initialization Essa opção tem como objetivo aumentar o desempenho em criação de arquivos de dados, pois quando um arquivo é criado no Windows ele precisa ser preenchido com zeros em sua totalidade, portanto em
algumas operações o tempo para execução pode demorar consideravelmente mas se essa opção não estiver configurada para a conta de serviço que esteja rodando o SQL Server. As opções mais comuns são: Criar banco de dados Adicionar arquivos de dados ou log a um banco existente Aumentar o tamanho dos arquivos (inclusive autogrowth) Restore de banco de dados Para configurar basta acessar as policies: Iniciar - > Executar: gpedit.msc Acessa a opção: Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment e clique duas vezes em Perform Volume Maitenance Taks:
Clique em Add User or Group e procure pelo usuário que esteja rodando o servido do SQL Server! Clique em Ok, Aplicar e Ok novamente.
Feito isso a opção já estará configurada para usufruir desse recurso, porém é preciso reiniciar o serviço do SQL para que as alterações sejam efetivadas. Testes comparativos com a opção habilitada e http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/
desabilitada:
Fonte: https://msdn.microsoft.com/en-us/library/ms175935.aspx
Configurar a conexão DAC Em situações críticas em que o servidor não esteja respondendo a requisições de clientes, o SQL server reserva uma quantidade de recursos e uma porta específica para viabilizar a conexão via DAC (Dedicated Administrator Connection). A conexão só é permitida a partir de um cliente (SSMS ou sqlcmd) no próprio servidor e, para viabilizar conexões remotas, podemos habilitar a opção: Remote Admin connections via sp_configure:
Para conectar no servidor usando a conexão DAC, basta logar no servidor e digitar ADMIN: antes do nome da instancia:
Desabilitar o XP_CMDSHELL Essa opção permite que o usuário utilize recursos interativos com o Sistema Operacional diretamente do SQL Server. Isso pode ser útil em alguns casos mas geralmente traz mais riscos à instancia do que benefícios, pois através de um simples ataque de SQL Injection o usuário poderia acessar recursos do Sistema Operacional através dessa opção, caso a mesma esteja habilitada. Para verificar se a opção está desabilitada, basta executar o comando abaixo, o esperado é que os campos config_value e run_value estejam = 0.
Caso a opção estiver ativa, basta executar o comando abaixo para desabilitá-la: sp_configure 'xp_cmdshell', 0 reconfigure
Se atente ao fato de alguma aplicação ou pacote do SSIS (SQL Server Integration Services) estar usando esse recurso, o ideal é adaptar a solução para que a mesma não utilize o XP_CMDSHELL e logo depois desabilitar. Caso você desabilite essa opção e algum recurso a utilize, um erro será retornado para a aplicação e vai e impactar no processo.
Conclusão Sempre que concluir uma instalação do SQL Server não deixe de realizar os ajustes finos em configurações a nível de banco e instância. Por mais que seu servidor possua muitos recursos como CPU, Memória e Disco, a má configuração fará com que o SQL não utilize os recursos da melhor maneira. No artigo foram citados itens que considero mandatórios após a instalação de qualquer instância, existem outras configurações e abordagens a serem exploradas e que podem ser encontradas nos demais posts e na edição anterior do livro.
Criando dashboards com o Power BI Neste artigo será apresentado(a) a ferramenta/serviço na nuvem chamada Power BI. A o longo do capítulo iremos mostrar um passo a passo de como configurar e usar o Power BI para criar dashboards na sua empresa. Por Demétrio Silva https://demetriosilva.wordpress.com/ Revisão técnica por Felipe Ferreira http://www.templar.com.br/blogs/felipe/
Introdução “O Power BI é um conjunto de ferramentas de análise de negócios para analisar dados e compartilhar ideias. Monitore seu negócio e obtenha respostas rapidamente com painéis avançados disponíveis em cada dispositivo.” https://powerbi.microsoft.com/pt-br/ O escopo deste capítulo é mostrar como criar uma conta e configurar o Power BI para criação de Dashboards. Além da criação dos Dashboards este artigo mostrará como configurar o Power BI para realizar o refresh dos dados usados, sendo que, estes dados estarão armazenados no ambiente on-premises e o Power BI é uma ferramenta que funciona na Nuvem.
Configuração do Ambiente Para a demonstração dos exemplos nós usaremos uma base de dados chamada Northwind_PT-BR que pode ser baixada neste link http://1drv.ms/1XMvUwB . O database usado nos exemplos é um backup do SQL Server da base Northwind_PT-BR. Caso ainda não o SQL Server instalado você pode baixar um trial, que funciona por 180 dias, https://www.microsoft.com/pt-br/evalcenter/evaluate-sql-server-2014. Basicamente será necessário o SQL Server Engine e o SSMS. Caso tenha dúvida em como instalar o SQL Server siga este link https://msdn.microsoft.com/en-us/library/ms143219.aspx . Para o nosso exemplo temos uma instância padrão do SQL Server 2014 instalada em um computador chamado CLIENT81. Após baixar o backup da base Northwind_PT-BR nós iremos realizar os passos abaixo para disponibilizála em nosso ambiente.
Criação do database Neste passo iremos realizar o restore da base Northwind_PT-BR na instância padrão. Abra o SSMS - SQL Server Management Studio e, conectado na instância, clique com o botão direito em databases e depois em restore conforme a Figura 1:
Figura 1 – Operação de restore
Na tela seguinte clique em no (...) ao lado do campo Device conforme Figura 2:
Figura 2 – Seleção do backup
Clique em “Add” conforme Figura 3
Figura 3 – Adicionar dispositivo de backup
Vá até a pasta onde você salvou o backup indicado no tópico 2 - Configuração do Ambiente conforme Figura 4.
Figura 4 – Local do backup
Clique em OK conforme Figura 5
Figura 5 – Escolha do arquivo de backup
Clique no checkbox logo abaixo do Label “Restore” conforme Figura 6.
Figura 6 – Informações do backup
Clique na opção “Files” no canto superior esquerdo e logo em seguida em “Relocate all files to folder”. Isso transfere os arquivos de dados e log do backup para o local padrão de arquivos de dados e log que você configurou na instância. Veja o exemplo na Figura 7.
Figura 7 – Local dos arquivos de dados e log
Volte à tela inicial, guia “General”, e clique em OK. Ao final do processo de restore será exibida uma tela igual a Figura 8.
Figura 8 – Restore realizado com sucesso
Volte a guia “Databases” na raiz do SSMS e veja que o banco de dados Northwind_PT-BR foi criado com sucesso conforme Figura 9.
Figura 9 – Banco de dados Northwin_PT-BR
Criação da conta no Power BI Nesta etapa será realizada a criação da conta no serviço Power BI na nuvem, para isso, acesse o site www.powerbi.com. Com o site aberto, cliqe em “Comece gratuitamente” conforme Figura 10.
Figura 10 – Criação da conta no Power BI
A Figura 11 exibe duas opções de uso do Power BI, sendo uma delas um download e outra um cadastro. A opção de download é chamada Power BI Desktop. Basicamente esta versão permite realizar análises usando várias funcionalidades do Power BI, porém tudo fica na máquina de algum usuário, ou em algum servidor de arquivos. Poderíamos fazer uma comparação bem simplória ao Excel. Tanto o Power BI Desktop quanto o Excel permitem a criação de relatórios porém ambos são aplicações desktop, logo, o compartilhamento, versão e segurança destes relatórios é de difícil administração em um ambiente corporativo. Já o Power BI, que é o BI da Microsoft na nuvem oferece o mesmo conjunto de visualizações do Power BI desktop porém funciona na nuvem da Microsoft e pode ser acessado através de Apps no Android, IOS e Windows Phone. Para os exemplos do livro usaremos o Power BI na nuvem como plataforma de BI e corporativo e o Power BI Desktop será usado na criação dos relatórios. Vale citar que o Power BI Desktop pode ser usado para criar relatórios e publicar no Power BI na nuvem, conforme veremos adiante. Escolha a opção “Inscrever-se” conforme Figura 11
Figura 11 – Opções do Power BI
Na Figura 12, informe seu e-mail corporativo e clique em “Inscrever-se”. Obs.: o Power BI não aceita e-mails de domínios @hotmail, @globo, etc., apenas e-mails corporativos.
Figura 12 – E-mail Corporativo
Após clicar em “Inscrever-se” um e-mail com um link é enviado para o e-mail corporativo informado no passo anterior e uma tela igual a Figura 13 é exibida.
Figura 13 – E-mail enviado
Abra seu e-mail corporativo e veja que você recebeu um e-mail da Microsoft com o texto conforme a Figura 14. Clique em “Sim, sou eu”
Figura 14 – Texto do e-mail.
Ao clicar no link enviado através do e-mail você será direcionado ao site de cadastro do Office 365. Preencha os dados da Figura 15 e clique em “Iniciar”
Figura 15 – Cadastro no Office 365
Agora aguarde a criação da sua conta no Office 365 e o deploy do Power BI conforme Figura 16
Figura 16 – Deploy
Ao finalizar o deploy você verá a tela abaixo, indicando que seu espaço no Power BI já foi criado e encontra-se pronto para uso.
Figura 17 – Power BI criado
Configuração do Gateway Um Gateway do Power BI funciona como uma camada intermediária entre os dados locais de sua organização e o serviço do Power BI na nuvem. Basicamente o gateway permite que os dados no Power BI sejam atualizados conforme os dados de seu banco de dados local seja alterado. Por exemplo, você cria relatórios no Power BI usando dados que estão no SQL Server de sua organização. Nestes relatórios você usa a tabela de vendas do seu database DB_VENDAS. Sabemos que vendas são realizadas de forma diária
e que os dados usados no Power BI logo ficarão defasados e é ai onde entra o Gateway do Power BI, com ele é possível, por exemplo configurar o Power BI para atualizar, de forma segura, diariamente os dados da tabela de vendas. Utilizando o gateway enterprise é possível usar o direct query pra que as consultas sejam executadas sempre no ambiente on-premise, logo, os dados não necessariamente precisam estar na nuvem. Com o Direct Query e o Gateway o Power BI cria uma conexão online entre a nuvem da Microsoft e o seu SGBD que contêm os dados on-premise. É possível, por exemplo, manter os dados em um banco de dados SQL Server on-premise e configurar o Power BI para executar as consultas diretamente no SGBD SQL Server de forma online. Eu sempre precisarei de um Gateway? Não. Apenas quando seu Power BI usar fontes de dados locais diretamente ( DirectQuery )e que sejam compatíveis com atualização através do Gateway. Veja mais em https://powerbi.microsoft.com/ptbr/documentation/powerbi-personal-gateway/#preciso-de-um-gateway. Iremos agora realizar a configuração do Gateway para a nossa organização: Na página do Power BI clique em Power BI Gateways conforme Figura 18
Figura 18 – Download do Gateway
Existem dois tipos de Gateways, o pessoal e o corporativo. Basicamente a diferença é que o corporativo permite uma administração centralizada dos acessos. Para o nosso exemplo iremos usar o pessoal. Clique em download conforme Figura 19.
Figura 19 – Tipos de gateways
Após finalizar o download dê um clique duplo no arquivo e clique em “Run” conforme Figura 20.
Figura 20 – Instalação do gateway
Figura 21 – Progresso da instalação
Na tela a seguir clique em “Next”
Figura 22 – Início da instalação
Aceite os termos de licença e clique em “Next” conforme Figura 23.
Figura 23 – Termos de licença
Informe o local que deseja instalar o gateway e clique em “Next” conforme Figura 24.
Figura 24 – Local da instalação
Neste ponto teremos o gateway instalado. Clique em “Launch” para abrir a tela de configuração do gateway.
Figura 25 – Gateway instalado
Na tela inicial de configuração do gateway é informado que será necessário efetuar login no Power BI antes de configurá-lo.
Figura 26 – Configuração do gateway
Neste passo você deve informar seu login e senha do Power BI e clicar em “Sign in”.
Figura 27 – Login no Power BI
Pronto. O gateway do Power BI está configurado.
Figura 28 – Gateway configurado
Power BI Desktop Neste passo iremos realizar a instalação do Power BI Desktop. Conforme já foi citado anteriormente, usaremos o Power BI Desktop para criar os relatórios e publicar no Power BI na Nuvem. Para realizar o download do aplicativo vá na página inicial do Power BI clique em “Power BI Desktop” conforme a Figura 29.
Figura 29 – Power BI Desktop
Após finalizar o download clique duas vezes no executável.
Figura 30 – Power BI Desktop Clique em “Run”
Figura 31 – Power BI Desktop instalação
Clique em “Seguinte”.
Figura 32 – Assistente do Power BI Desktop
Na tela seguinte aceite os termos de licença e clique em “Seguinte”.
Figura 33 – Termos de licença
Selecione o local de instalação.
Figura 34 – Local de instalação
Marque a opção “Criar atalho do ambiente de trabalho” e clique em “Instalar” conforme Figura 35.
Figura 35 – Instalar Power BI Desktop
O Power BI Desktop foi instalado com êxito. Deixa a opção “Iniciar Microsoft Power BI Desktop” marcada e clique em “Concluir”.
Figura 36 – Iniciar Power BI Desktop
A tela de boas vindas abaixo pode ser fechada clicando no “X” no canto superior direito.
Figura 37 – Tela de boas vindas
Após fechar a tela de boas vindas nós teremos a tela inicial do Power BI Desktop aberta. Neste passo nós iremos importar os dados da base de dados Northwind_PT-BR, que foi apresentada no item “Configuração do ambiente”. Na tela inicial clique em “Obter Dados” e em seguida em “SQL Server” conforme Figura 38.
Figura 38 – Obter dados
Neste ponto nós iremos informar em qual instância do SQL Server está o nosso banco de dados, o nome do banco de dados e a query. Neste caso a conexão ao SQL Server é realizada no formato Windows Authentication. Para o exemplo do livro o nome da instância é CLIENT81, do banco de dados Northwind_PT-BR e a query, chamada “0 – Consultar Vendas” pode ser obtida neste link http://1drv.ms/1XMvUwB.
Figura 39 – Query de importação
Uma curiosidade! O Power BI Desktop, assim como o PowerPivot e a instância tabular do Analysis Services Tabular armazenam os dados em um Data Model. Basicamente as 3 ferramentas acima são um container para o Data Model. Para ver o tamanho do arquivo Data Model você pode renomear o arquivo, disponível neste link, do Power BI Desktop “Dados BI Livro.pbix” para “Dados BI Livro.zip”. Deixando um pouco mais claro, o Power BI Desktop é uma ferramenta que presta os serviços de ETL (Power Query), Dados ( Data Model ) e Relatórios. Para os exemplos do livro nós usaremos o Power BI Desktop apenas para servir como repositório dos dados. A Figura 40 exibe o arquivo Data Model dentro do do arquivo zip. Desta forma é possível saber o tamanho do Data Model, seja no PowerPivot ou no Power BI Desktop.
Figura 40 – Data Model
A próxima imagem, Figura 41, exibe um preview dos dados que serão importados. É possível editar os dados através do botão “Editar”, usando assim o Power Query, que é a ferramenta de ETL do Power BI. Para manter o foco do nosso capítulo iremos apenas clicar em “Carregar”. Desta forma, os dados serão importados para dentro do Power BI Desktop.
Figura 41 – Importar dados
É possível ligar o Power BI aos dados da sua corporação de duas formas. Importar: Importa os dados para o arquivo do Power BI Desktop. Para manter os dados importados atualizados é possível configurar o refresh automático utilizando o gateway que configuramos anteriormente. DirectQuery: Ao invés de importar, conecta o Power BI diretamente à fonte de dados em tempo real. Iremos usar o modelo de importação, sendo assim, clique em “Importar” conforme Figura 42.
Figura 42 – Tipo de conexão.
Os dados foram importados. Nosso próximo passo será alterar o nome da tabela importada. Selecione o nome da tabela e clique em “Mudar o nome” conforme Figura 43.
Figura 43 – Nome da tabela
Dê o nome de “Pedidos” conforme abaixo.
Figura 44 – Tabela pedidos
Salve o arquivo com o nome Dados BI Livro.pbix conforme Figura 45
Figura 45 – Projeto Power BI Desktop
OneDrive Existem diversas formas de conectar o Power BI aos dados que serão analisados, por exemplo, Planinhas do Excel/PowerPivot, On-premise data sources ( SQL Server, SSAS, etc. ), Hadoop, Cloud Services ( ZenDesk, SalesForce, etc. ). Para o exemplo do livro nós iremos conectar o Power BI ao arquivo do Power BI Desktop criado no tópico anterior. Para isso, faremos o upload do arquivo “Dados BI Livro.pbix” para o OneDrive e, dentro do Power BI iremos apontar para este arquivo que estará no OneDrive. Lembram do Data Model? É ele que contêm os dados de vendas que importamos anteriormente. Abra o seu OneDrive, pode ser o OneDrive ou OneDrive For Business. No caso deste livro usaremos o OneDrive pessoal, vide Figura 46. Caso ainda não tenha OneDrive então crie gratuitamente uma conta no Outlook.com.
Figura 46 – OneDrive
Crie uma nova pasta no seu OneDrive chamada BI conforme Figuras 47 e 48.
Figura 47 – Nova pasta
Figura 48 – Pasta BI
Em seguida, clique na pasta criada e depois em “Carregar”.
Figura 49 – Pasta criada
Figura 50 – Carregar arquivos
Localize o arquivo que foi salvo na Figura 45 e clique em “Open” conforme Figura 51.
Figura 51 – Upload do arquivo
Pronto. Agora seu arquivo do Power BI Desktop está salvo no OneDrive e pronto para ser usado no Power BI na nuvem.
Criação dos relatórios e dashboards Importação dos dados Neste ponto nós já temos todo o ambiente configurado para usar o Power BI. Iniciaremos agora na Home Page do nosso Power BI e em seguida iremos clicar na opção “Obter” logo abaixo do item “Arquivos”. A idéia neste ponto é conectar o Power BI ao nosso arquivo “Dados BI Livro.pbix” que foi salvo no OneDrive anteriormente.
Figura 52 – Conectar ao OneDrive
Ao clicar em arquivos o Power BI oferece vários formas de conectar, Arquivo Local ( Txt, Excel, etc. ), Arquivos no OneDrive / OneDrive For Business e Arquivos em blibliotecas do SharePoint. No nosso exemplo iremos conectar ao arquivo do Power BI Desktop que está no nosso OneDrive Pessoal. Clique em OneDrive Pessoal conforme Figura 53.
Figura 53 – Tipo de conexão
O OneDrive solicitará que você informe login e senha para conectar e após conectado a a tela abaixo é exibida, mostrando as pastas e arquivos disponíveis no seu OneDrive. Clique em “BI” conforme Figura 54.
Figura 54 – Conectar na pasta BI
Em seguida selecione o arquivo chamado “Dados BI Livro.pbix” clique em “Conectar”. Após este passo o seu Power BI fará uma importação do Data Model que está dentro do arquivo “Dados BI Livro.pbix”.
Figura 55 – Dados importados.
Atualização dos dados O Power BI cria um dataset com os dados importados do nosso arquivo. Como os dados foram importados eles são estáticos e, por padrão, não são atualizados. Eventualmente será necessário realizar um refresh dos mesmos, visto que, os dados de vendas de uma empresa aumentam a cada dia. Iremos então programar a atualização dos dados de forma automática. Explicando de forma bem simples, a atualização funciona da seguinte forma: De acordo com o agendamento configurado, o Power BI solicita ao OneDrive que abra o modelo de dados, conecte na base usando a string de conexão configurada através do Power BI Gateway e execute a consulta novamente, atualizando os dados do modelo. Clique em “Dados BI Livro”, logo abaixo de “Conjunto de dados” e em seguida clique em “Programar Atualização”.
Figura 56 – Programar atualização
O gateway do Power BI não funciona na versão gratuita, logo, teremos que usar a versão Pro. É possível usar a versão Pro gratuitamente por 60 dias, para isso, clique em “Experimento o Pro gratuitamente” conforme Figura 57.
Figura 57 – Experimentar versão Pro
Clique em “Iniciar a avaliação”.
Figura 58 – Iniciar avaliação
Pronto. A avaliação de 60 dias da versão Pro foi iniciada. Agora você já pode usar o gateway do Power BI para realizar o refresh dos dados.
Figura 59 – Avalidação estendida
Novamente, clique em “Dados BI Livro”, logo abaixo de “Conjunto de dados” e em seguida clique em “Programar Atualização”.
Figura 60 – Visualizar refresh
Desta vez a opção de configuração do refresh dos dados é exibida. Note que o Power BI informa o status do nosso gateway configurado anteriormente.
Figura 61 – Status do gateway
O passo a seguir é configurar as credenciaris que serão usadas no Data Source. Na guia “Credenciais da fonte de dados” clique em “Editar credenciais”.
Figura 62 – Editar credenciais
Informe o método de autenticação “Windows” e clique em “Entrar”.
Figura 63 – Método de autenticação
Na guia “Agendar atualização” clique em deixe as opções configuradas conforme Figura 64 e clique em “Aplicar”. Neste caso, estamos agendando a atualização para executar diariamente às 04h da manhã, no fuso horário de Brasília e enviar e-mail em caso de falhas na atualização.
Figura 64 – Agendamento
A atualização que definimos anteriormente é referente aos dados que estão na nossa base de dados Northwind_PT-BR, porém, no caso do OneDrive, existe uma outra atualização que pode ser configurada: atualização de arquivo. Este tipo de atualização verifica, por exemplo, se o Data Model que está no OneDrive ainda é o mesmo e, caso alguma alteração seja realizada no arquivo “Dados BI Livro.pbix”, do OneDrive, esta atualização será refletida no Power BI. A Figura 65 mostra como configurar esta atualização. Também é possível usar o Cortana junto ao Power BI. Clique em “Aplicar” conforme Figura 65.
Figura 65 – Atualização do OneDrive e Cortana
Novamente clique em “Dados BI Livro”, logo abaixo de “Conjunto de dados” e em seguida clique em “Atualizar Agora”, isso força uma atualização não agendada.
Figura 66 – Atualização não agendada
Para visualizar o histórico de atualizações clique em “Dados BI Livro”, logo abaixo de “Conjunto de dados” e em seguida clique em “Programar Atualização”.
Figura 67 – Visualizar histórico de atualizações
Expanda a guia “Agendar Atualização” e em seguida clique em “Histórico de Atualização” conforme Figura 68.
Figura 68 – Histórico de atualização
A tela abaixo será exibida, mostrando todas as atualizações realizadas, quando, tipo e status.
Figura 69 – Status das atualizações
Criação dos relatórios Nesta etapa final nós realizaremos a criação dos relatórios no Power BI usando os dados importados anteriormente. Na tela inicial do Power BI, logo abaixo de “Conjunto de Dados”, clique em “Dados BI Livro”. Isso mostrará a “tabela” de pedidos no lado direto da tela, bem como, os seus campos.
Figura 70 – Criação do relatório
No canto direito da tela, selecione os campos Categoria e Quantidade e em seguida clique no símbolo do pincel e na guia geral aumente o tamanho da fonte para 27pt conforme Figura 71:
Figura 71 – Relatório inicial
Agora clique em “Arquivo” em sem seguida em “Salvar”
Figura 72 – Salvar relatório
Escolha um nome, no nosso caso “Report Livro”.
Figura 73 – Nome do relatório
Agora iremos testar se a atualização está funcionando corretamente. Para isso abra o SQL Server Management Studio e conecte na instância CLIENT81 ( ou o nome da sua instância configurada no tópico 2, no início deste capítulo ) na sua rede e execute o script “1 - Alterar Pedidos.txt”, que pode ser baixado neste link.
Figura 74 – Alteração dos pedidos
Agora faça uma atualização manual. Clique em “Dados BI Livro”, logo abaixo de “Conjunto de dados” e em seguida clique em “Atualizar Agora”, isso força uma atualização não agendada.
Figura 75 – Teste gateway
E visualize o relatório novamente. Note que na Figura 72 o total era 51317 e após o update e refresh o valor é 61304 conforme Figura 75. Isso comprova que o Power BI atualizou os dados corretamente.
Figura 75 – Dados atualizados
Agora vamos criar um gráfico e adicionar ambos a um painel
Figura 76 – Criação de gráficos
Em seguida clique em salvar
Figura 77 – Salvar gráfico
Iremos agora fixar estes itens no nosso dashboard. Para isso, ainda no relatório, clique em “Fixar painel” conforme Figura 78.
Figura 78 – Fixar painel
Selecione o dashboard que deseja exibir esta tabela. No nosso caso criaremos um novo dashboard.
Figura 79 – Novo dashboard
Pronto. Agora já temos nossa tabela disponível no dashboard chamado “Painel Livro”. Iremos agora adicionar o gráfico de pizza ao mesmo dashboard. Para isso, clique em “Fixar painel” conforme Figura 80.
Figura 80 – Fixar pizza
Selecione “Painel existente” e em seguida informe o nome “Painel Livro” conforme Figura 81.
Figura 81 – Adicionar pizza ao dashboard
Agora volte à tela inicial do Power BI e na opção “Painéis” clique em “Painel Livro”. A tela com o dashboard será exibida conforme Figura 82
Figura 82 – Dashboard livro
Usando o Q&A Novo no Power BI o Q&A, sigla em inglês para Questions and Answers, que significa “Perguntas e Respostas”, esta funcionalidade permite que o usuário visualize os dados apenas “Perguntando” ao Power BI, ao invés de criar relatórios de forma manual. Por exemplo, caso o usuário queira saber quantos pedidos foram vendidos por categoria ele poderia realizar a pergunta “Pedidos by categoria” e, caso queira que o resultado seja exibido em formato de barras pode perguntar “Pedidos by categoria as barchart” conforme a Figura 83. Neste caso, o Power BI procura as colunas no nosso dataset e gera os gráficos. Esta integração do Q&A também funciona com o Cortana, que ainda não estava disponível no Brasil no momento que este capítulo foi escrito.
Figura 83 - Pedidos por categoria
Abaixo uma outra pergunta, “pedidos by categoria as barchart where categoria = bebidas”. Neste caso estamos perguntando ao Power BI quantos pedidos foram vendidos, agrupados por categoria, em formato e barra filtando apenas a categoria bebidas.
Figura 84 – Q&A, pedidos da categoria bebidas
Podemos também gerar gráficos do tipo pizza. Para isso basta adicionar o predicado as pie.
Figura 85 – Pedidos pizza
Vale lembrar que os predicados ( where, as pie, as baarchart, etc. ), no momento, estão dispiníveis apenas em inglês.
Insights Uma das funcionalidades do Power BI na nuvem é o Insights Rápidos, onde o Power BI irá automaticamente analisar o modelo de dados e gerar um dashboard com algumas informações interessantes sobre os dados que ele detectou. Logo abaixo de “Conjunto de dados” clique em “Dados BI Livro” e em seguida “Insights Rápidos”
Figura 86 – Insights rápidos
Após finalizar, logo abaixo de “Conjunto de dados” clique em “Dados BI Livro” e em seguida “Exibir Insights”
Figura 87 - Exibir Insights
Diversos padrão são encontrados pela análise
Figura 88 – Insight 1
Figura 89 – Insight 2
Figura 90 – Insight 3
Conclusão Power BI é “A ferramenta” de BI da Microsoft, recebendo atualizações semanalmente, provando o quanto a Microsoft está empenhada na sua evolução. Conforme descrito no artigo, é possível criar relatórios e dashboards de forma simples e intuitiva usando o Power BI Desktop ou diretamente no navegador. Não deixe de acompanhar meu blog @DemetrioSQLDBA para ver mais novidades.
http://demetriosilva.wordpress.com
e
Twitter
Aumentando a Disponibilidade do SQL Server Failover Cluster com o Dynamic Quorum Veja neste artigo porque o quórum é importante para o seu Failover Cluster, os modelos de quórum existentes no Windows Server 2012/2012R2 e como selecionar o melhor modelo para seu ambiente. Veja também porque o Dynamic Quórum aumenta a disponibilidade do seu cluster, como ele funciona e um passo-a-passo para sua implementação. Por Nilton Pinheiro www.mcdbabrasil.com.br/ Revisão Técnica por Marcelo Fernandes https://marcelodba.wordpress.com/
Entendendo a Necessidade do Quórum Antes de abordar o assunto “Quórum Dinâmico” é importante entender porque o quórum é importante para um cluster. Você sabe porque o cluster precisa de um Quórum? Mas afinal, o que é o quórum? Muito bem, para entender porque o quórum é importante para o cluster, vamos considerar um cenário como o apresentado pela figura 1.1.
Figura 1.1: Exemplo de um cluster distribuído geograficamente
Na figura 1.1 temos a configuração de um Failover Cluster composto por quatro servidores (ou nós), distribuídos geograficamente entre dois sites: São Paulo e Rio de Janeiro. Vamos imaginar que devido a um problema de comunicação de rede entre os dois sites os nós do site Rio de Janeiro não conseguem mais se comunicar com os nós do site São Paulo. Neste cenário os nós de um site pensarão que os nós do outro site não estão mais disponíveis ou online e o serviço de cluster tentará subir os recursos em cada site para que possam atender as requisições dos ambientes. Esta situação é conhecida como “split-brain” e na prática faz com que o cluster seja dividido em dois clusters independentes e que não estarão visíveis um ao outro. Já dá para imaginar o problema que isso
causará? Neste caso, você teria o SQL Server online nos dois sites, cada um operando de forma independente, tratando as requisições dos clientes dos respectivos sites e, o pior, cada um escrevendo nos discos de suas respectivas storages. Certamente isso causaria um grande transtorno, incluindo a perda de dados/informações e não é uma situação desejada em cenários de alta disponibilidade. Então, para evitar essas situações de “split-brain” foi desenvolvido o mecanismo de votação em um cluster. Ao aplicar o mecanismo de votação terá acesso aos recursos do cluster o site que constituir um quórum, ou seja, o site que tiver a maioria dos elementos votantes online e em comunicação. Dessa forma, somente um dos sites poderá prover os serviços às aplicações. Quando configuramos um Failover Cluster cada nó do cluster assume o direito de voto. Dependendo do modelo de quórum utilizado, além dos nós que compõem o cluster votos também podem ser atribuídos a um disco (conhecido como “disk witness”) ou um compartilhamento de rede (conhecido como “File Share Witness ou FSW”). Então, baseado na quantidade de nós existentes no cluster e no modelo de quórum escolhido, o cluster sabe quantos votos ele precisa para constituir um quórum. Um quórum é constituído quando a fórmula (número de nós/2)+1 resulta em mais da metade dos elementos votantes Online e em comunicação, ou seja, dizemos que o cluster tem quórum quando mais da metade dos elementos votantes estão online e em comunicação uns com os outros e que não tem quórum se mais da metade dos elementos votantes ficarem indisponíveis ou perderem a comunicação entre eles. Neste caso, dizemos que o cluster perdeu o quórum e consequentemente causará a indisponibilidade dos serviços ou aplicações em execução no cluster. Então, podemos dizer que na prática o quórum determina o número mínimo de nós que devem se manter online e em comunicação para que o cluster funcione! Voltando ao exemplo da figura 1.1, temos que o cluster é composto por quatro nós! Considerando que cada nó conta 1 voto, o cluster possui então 4 votos no total. No entanto, ao perder a comunicação entre os sites, cada site fica com apenas 2 nós online. Nenhum dos sites manterá a maioria (que seria: 4/2+1=3), ou se preferir 50% dos nós +1, e neste caso o cluster não terá quórum e ocorre a indisponibilidade total do cluster. Isso evita então o problema do “split-brain”!
Os modelos de Quórum Quando configuramos o quórum de um cluster no Windows Server 2012/R2 podemos optar por quatro modelos de quórum a seguir: Node Majority: Nesta configuração cada nó do cluster conta 1 voto. O cluster possui quórum e permanece online enquanto tiver a maioria dos votos, ou seja, enquanto mais da metade dos elementos votantes que formam o cluster estiverem online e em comunicação. Este é o modelo recomendado para cluster que possui um número ímpar de nós, por exemplo, 3, 5 ou 7 nós, Node and Disk Majority: Nesta configuração cada nó do cluster e um disco ("disk witness") contam 1 voto. É a configuração recomendada para cluster que possui um número par de nós, por exemplo 2 ou 4 nós. Node and File Share Majority: Nesta configuração cada nó do cluster e um file share ("file share witness" ou FSW) contam 1 voto. Este modelo é semelhante ao “Node and Disk Majority” com a diferença que ao invés de utilizar um disco, utiliza-se um file share. É a configuração recomendada para cluster que possui um número par de nós, por exemplo 2 ou 4 nós e não têm um disco de storage para a configuração do “disk witness, ou ainda se está configurando um cluster onde os nós estão geograficamente distribuídos. No Majority: Disk Only: Nesta configuração o cluster funcionará enquanto pelo menos 1 nó estiver online e em comunicação com o disco ("disk witness"). Neste caso, dizemos que o disco é um ponto único de falha pois mesmo que todos os nós estejam online e em comunicação, se o disco ficar off-line todo o cluster cairá. Devido a este ponto crítico de falha esta não é mais uma configuração recomendada. Jogando estas informações em uma tabela chegamos à seguinte definição apresentada na tabela 1.1:
Tabela 1.1: Característica do cluster vs. Recomendação para o quórum
Então, para um melhor entendimento o mecanismo de votação e constituição do quórum de um cluster, vamos utilizar como exemplo a figura 1.2
Figura 1.2: Exemplo do mecanismo de votação de um cluster
Observando a figura 1.2 podemos notar que o cluster possui 4 servidores e duas instâncias virtuais de SQL Server: VSQLINST1 e VSQLINT2. Considerando que o cluster possui um número par de nós, este está configurado com o modelo de quórum recomendado para a sua quantidade de nós, o “Node and Disk Majority”, portanto, o cluster possui 5 elementos votantes, sendo 1 voto para cada servidor (nó) + 1 voto para o disco, o “disk witness”, formando então um número ímpar de elementos votantes. Você pode estar se perguntando, ao invés de utilizar o disco eu poderia ter utilizado um file share, adotando o modelo Node and File Share Majority? Sim, poderia! Neste caso você precisa apenas de um compartilhamento de rede em qualquer servidor da sua rede, servidor este que não pode fazer parte do cluster sendo configurado. Bom, considerando então que para um cluster permanecer online e operacional com este modelo de quórum é preciso que mais da metade dos elementos votantes estejam online e em comunicação, chegamos à conclusão que para o cluster da figura 1.2 permanecer operacional é preciso manter no mínimo três votos online (número de nós/2+1=3). Neste cenário, supondo que o nó SQLNODE3 fique indisponível, a instância de SQL Server em execução neste nó será automaticamente transferida para um dos nós restantes e o cluster passará a ficar com 4 votos. Consequentemente o cluster mantém quórum e continua online! No entanto, se após perder o nó SQLNODE3 o cluster também perder os nós SQLNODE1 e SQLNODE2, o cluster ficará com apenas 2 votos (1 voto do nó SQLNODE4 + 1 voto do disco), ou seja, terá menos da metade dos elementos votantes online e consequentemente perderá o quórum, causando assim a indisponibilidade do cluster e das instâncias de SQL Server. Felizmente a partir do Windows Server 2012 surgiram significativas melhorias com relação às opções de configuração do quórum e a configuração do modelo de quórum ficou bastante flexível. Então, quando configurando o quórum em um Cluster com Windows Server 2012 ou 2012R2 através da ferramenta “Configure Cluster Quorum Wizard” você terá três opções de configuração possíveis (figura 1.3): Use default quorum configuration: Nesta opção o cluster automaticamente atribuirá um voto para cada nó e gerenciará dinamicamente os nós votantes. Dependendo da quantidade de nós no cluster e sendo identificada a presença de um disco de storage, o cluster selecionará automaticamente um “disk witness”. Em muitos casos esta é a opção mais recomendada pois permitirá que o próprio cluster
configure o melhor modelo de quórum e se necessário um “disk witness”, garantindo para seu cluster a maior disponibilidade possível. Select the quorum witness: Selecione esta opção sempre que desejar alterar a configuração do modelo de quórum em vigor, seja adicionando, alterando ou removendo um witness. Podendo este witness ser um “disk witness” ou um “file share witness” (FSW). Nesta opção o cluster também atribuirá um voto para cada nó e gerenciará automaticamente os elementos votantes. Advanced quorum configuration: Selecione esta opção sempre que você desejar executar alguma configuração específica nas configurações do quórum. Através dessa opção você pode alterar o tipo de witness, determinar quais nós devem ou não contar votos no cluster (muito útil para configurações em ambientes geograficamente distribuídos) e também determinar se o cluster deverá ou não gerenciar automaticamente os elementos votantes no cluster.
Figura 1.3: Opções para configuração do quórum do cluster
Agora que você já sabe da importância do quórum para um cluster, dos seus modelos e opções de configuração, vamos ao quórum dinâmico.
Quórum e Witness Dinâmico Como visto no tópico anterior, por padrão, cada nó do cluster recebe um voto e para que um cluster mantenha-se online ele precisa satisfazer a regrinha (número de nós/2+1). Dessa forma, em um cluster com 5 elementos votantes é possível perder até 2 servidores ou elementos votantes que o quórum continuará sendo mantido e o cluster continuará operacional. Ou seja, baseado no número de elementos votantes o cluster sabe o número mínimo de votos que ele precisa ter para se manter operacional.
Figura 1.4: Quorum é mantido com 3 ou mais votos online
Usando como exemplo a figura 1.4 onde temos um cluster com 5 elementos votantes, o quórum é mantido enquanto três ou mais elementos votantes estiverem online. No exemplo dois servidores foram “perdidos”, no entanto, o cluster continua operacional porque ainda possui a maioria dos elementos votantes online e em comunicação. No entanto, se outro servidor ficar indisponível o cluster perderá o quórum e os recursos do cluster ficarão indisponíveis. Vimos que com o Windows Server 2012/R2 é possível utilizar o “Configure Cluster Quorum Wizard” e redefinir quais servidores devem ou não contar votos no cluster, então, ao perder dois servidores é possível reconfigurar o quórum do cluster e assim garantir uma maior disponibilidade do ambiente. No entanto, você precisará reconfigurar isso manualmente. Na figura 1.5 tem-se um exemplo de como ficaria a votação do quórum após a reconfiguração dos elementos votantes. Ao reconfigurar, apenas 3 elementos passam a contar voto e a necessidade mínima de votos para manter o quórum será recalculada considerando os novos elementos votantes. Assim garantimos que o cluster continuará operacional enquanto pelo menos dois elementos votantes estiverem online.
Figura 1.5: Quorum é mantido com 2 ou mais votos online
Neste tópico abordaremos duas excelentes funcionalidades implementadas no Windows Server 2012 e Windows Server 2012R2 que além de simplificar significativamente a configuração do quórum, aumenta muito as chances do cluster sobreviver a falhas. Estamos falando do “Dynamic Quorum” e “Dynamic Witness”.
Quorum Dinâmico Implementado a partir do Windows Server 2012 o quórum dinâmico ou “Dynamic Quorum” provê ao Failover Cluster a funcionalidade de poder gerenciar automaticamente a atribuição de votos no quórum baseado no status de cada nó. Ou seja, você não precisa mais ficar reconfigurando o quórum manualmente e tudo será feito de forma automática considerando se o nó está online ou off-line. Na prática o que acontece é que quando o nó fica indisponível ele deixa de contar voto e a necessidade mínima de votos para manter o quórum é recalculada automaticamente. Quando o nó volta a ficar disponível ele volta a contar voto e o cálculo é refeito considerando então o novo elemento votante. Voltando ao exemplo da figura 1.4 onde a reconfiguração dos elementos votantes precisou ser feita manualmente, com o “Dynamic Quorum” a reconfiguração passa a ser dinâmica. E mais, a idéia com o “Dynamic Quorum” é que ao permitir que a atribuição de votos e o recalculo do número mínimo de votos necessários para se manter o quórum seja feito dinamicamente, o cluster possa sobreviver a falhas sequenciais dos nós mesmo que ao final reste apenas um único nó online no cluster, o que é referenciado como “last man standing”. O “Dynamic Quorum” é ativado por padrão durante a criação do cluster e dois pontos que se deve ter em mente são: 1. O “Dynamic Quorum” somente atua sobre nós que estão definidos como elementos votantes no cluster. Isso significa que se durante a configuração ou reconfiguração do quorum você selecionar um nó como “não votante”, o “Dynamic Quorum” não atuará sobre ele e não o redefinirá dinamicamente como um elemento votante. 2. O “Dynamic Quorum” somente atua enquanto a indisponibilidade ou falha dos servidores se derem de forma sequencial, ou seja, se o cluster perder simultaneamente dois ou mais nós o “Dynamic Quorum” não conseguirá ajustar dinamicamente a atribuição dos votos e consequentemente não conseguirá recalcular o número mínimo de votos necessários para manter o cluster operacional. Para melhor entender o funcionamento do “Dynamic Quorum”, considere novamente um cluster 5 nós. Para obter as informações de votação do cluster utilize o comando do PowerShell, Get-ClusterNode conforme abaixo: PS C:\Users\Administrator.SQLNET> Get-ClusterNode | FT Name, DynamicWeigth, NodeWeight, ID, State -AutoSize
Neste resultado, o valor da coluna DynamicWeight igual a 1 indica que o nó possui voto dinâmico. Já a coluna NodeWeight igual a 1 indica que o nó deve ser considerado para contar voto no cluster. Então, por padrão quando todos os nós do cluster estiverem online, ambas as colunas deverão possuir o valor 1 e o valor de NodeWeight não se alterará a menos que reconfigurando o quórum manualmente você selecione que o nó não deverá ser considerado para votação no cluster.
Imaginando então que os servidores SQLNODE2 e SQLNODE3 sofrem uma falha e são desligados, o “Dynamic Quorum” entra em ação e o resultado da votação será a seguinte:
Note que para os servidores SQLNODE2 e SQLNODE3 a coluna DynamicWeight ficou como 0 (indicando para o cluster que esses servidores não possuem mais voto). Com isso, ao recalcular o número mínimo de votos necessários para manter o quórum, o cluster passa a utilizar a coluna DynamicWeight e não a coluna NodeWeight (como seria se o Dynamic Quorum não estivesse ativo). Com isso, temos que para o cluster continuar operacional a partir desse ponto é preciso que pelo menos dois servidores permaneçam online. Considerando agora que o servidor SQLNODE1 também falhe, teremos a reconfiguração dos votos como segue:
Observe que ficando agora com apenas dois nós, foi removida também a configuração de voto do servidor SQLNODE4, mesmo com ele estando online. Isso acontece porque quando restam apenas dois nós online, o Dynamic Quorum tem como regra remover também o voto de um dos dois nós restantes. Isso acontece de forma randômica e garante que na falha do nó que ficou sem voto (o SQLNODE4), o outro nó ainda possa continuar online e atendendo às demandas do ambiente, provendo ao cluster uma chance de 50% de sobreviver a uma nova falha. A partir desse ponto, o cluster pode perder a comunicação entre os dois nós restantes ou ter uma falha do nó que não conta voto, que ainda assim continuará operacional. Em ambos os casos, entra em ação o “last man standing”, ficando o cluster online e operacional apenas com o nó que possui o voto (o SQLNODE5).
Neste momento você pode estar se fazendo a seguinte pergunta: O que acontece se restando apenas dois nós ocorrer uma falha no servidor que possuir o voto? Neste caso, as seguintes situações podem ocorrer: a) Se o nó que mantém o voto sofrer uma falha inesperada ou for desligado de forma abrupta todo o cluster ficará indisponível uma vez que o nó restante não possui voto e não poderá sustentar o cluster, b) Se o nó que mantém o voto sofrer uma reinicialização ou for desligado de forma planejada, o cluster atribuirá voto ao nó que se manter online, ficando o cluster ainda operacional apenas com um nó restante. Como podemos imaginar, em um ambiente real existe uma grande possibilidade de que o item a) aconteça, então, a partir deste momento a recomendação é reconfigurar o quórum adicionando witness (disco ou file share). Desta forma você estará adicionando mais um elemento votante ao cluster mantendo-o com um número ímpar de votos e provendo uma maior proteção ao cluster.
Witness Dinâmico Entendido o funcionamento do “Dynamic Quorum”, a boa notícia é que a partir do Windows Server 2012 R2 existe também o “Dynamic Witness”. Isso mesmo, o “Dynamic Witness” tem exatamente o mesmo comportamento do “Dynamic Quorum”, porém, ele atua somente sobre a votação do witness, seja ele um “disk witness” ou um “file share witness”. No Windows Server 2012 R2 se o cluster estiver configurado para utilizar “Dynamic Quorum” (o default), o voto do witness também será atribuído ou removido dinamicamente. Dessa forma, quando o número de nós votantes no cluster for ímpar, o witness não contará voto. Por outro lado, se o número de nós votantes no cluster for par, o witness contará voto e assim garantirá um balanceamento do cluster deixando-o sempre com um número ímpar de votos. Então você pode estar se perguntando...Mas o que acontece se o witness ficar “off-line”? Neste caso, da mesma forma que com o “Dynamic Quorum” os votos são ajustados baseado no status de cada nó, com o “Dynamic witness” o voto do witness também é ajustado baseado o status do witness, ou seja, se o recurso de witness falhar ou por qualquer motivo ficar off-line, o cluster removerá o voto do witness. Quando o recurso de witness voltar a ficar online, seu voto será reatribuído caso o número de nós votantes no cluster seja par. Lembre-se que com um número par de elementos votantes é sempre recomendado adicionar um witness para balancear o cluster e deixá-lo com um número ímpar de votos. É isso que o “Dynamic witness” faz, procura sempre manter o balanceamento do cluster. Você pode identificar se o witness está contado voto ou não no cluster executando o comando PowerShell Get-Cluster e verificando o parâmetro WitnessDynamicWeight. Assim como verificando o parâmetro DynamicQuorum você identifica se o “Dynamic Quorum” está ativo ou não. PS C:\Users\Administrator.SQLNET> Get-Cluster | FT Name, DynamicQuorum, WitnessDynamicWeight -AutoSize
Embora não seja recomendado, caso necessário você pode desativar o “Dynamic Quorum” utilizando o comando Get-Cluster como abaixo. Para reativá-lo basta reexecutar o comando definindo o DynamicQuorum=1. PS C:\Users\Administrator.SQLNET> (Get-Cluster).DynamicQuorum=0
E lembre-se que o “Dynamic Witness” somente atuará se o “Dynamic Quorum” estiver ativo e mesmo assim o cluster somente atribuirá voto ao witness se necessário. Então, não se espante se mesmo com o “Dynamic Quorum” ativo você identificar o WitnessDynamicWeight como 0. Como você pode observar, o “Dynamic Quorum” e “Dynamic Witness” são duas excelentes funcionalidades que reduzem significativamente as chances de um cluster ficar indisponível por causa de uma falha nos nós ou mesmo do recurso de witness. Eles também adicionam uma grande simplicidade e flexibilidade na configuração do quórum, removendo definitivamente a necessidade de uma intervenção manual na configuração ou reconfiguração pois agora o próprio cluster fará isso de forma dinâmica. Considerando que estes recursos estarão ativos por padrão no Windows Server 2012/R2, procure sempre tirar vantagem destes recursos mantendo-os ativos independente do número de nós em seu cluster. Neste momento você pode estar se perguntando: Mas e quando Eu tiver um cluster com apenas dois nós e estiver utilizando a configuração de “Node Majority”, mesmo assim devo considerar o uso do “Dynamic Quorum”? Bom, primeiramente, considerando um cluster com apenas dois nós, como já vimos aqui o recomendado é que você configure um terceiro voto, seja colocando um disco ou um “file share” como witness. No entanto, caso por qualquer motivo você insista em manter a configuração do quórum como “Node Majority”, a resposta é SIM! Como já vimos no decorrer deste tópico, para um cluster se manter online e operacional ele precisa satisfazer a fórmula (número de nós/2+1). Então, com apenas dois nós teremos 2/2+1=2, ou seja, seu cluster se manterá Online somente enquanto os dois servidores estiverem Up. Então, sua possibilidade de falhas de nós para este cluster é 0. Um restart em um dos servidores já será o suficiente para derrubar seu cluster e causar a indisponibilidade do SQL Server. Você pode comprovar isso facilmente executando o comando abaixo para desativar o “Dynamic Quorum” no Windows Server 2012/R2 e posteriormente executar um restart em um dos nós. PS C:\Users\Administrator.SQLNET> (Get-Cluster).DynamicQuorum=0
Por outro lado, se você mantiver o “Dynamic Quorum” ativo, notará nas configurações do cluster que apenas um dos nós (no exemplo da figura 1.6 o nó SQLNODE2) mantém a coluna “Current Vote” igual a 1 (isso é feito aleatoriamente). Na ferramenta Failover Cluster Manager a coluna “Current Vote” corresponde ao “DynamicWeight” do comando PowerShell Get-ClusterNode.. Então, isso significa que apenas um dos nós está mantendo voto no cluster.
Figura 1.6: Apenas o servidor SQLNODE2 contando voto no cluster
Como neste caso a atribuição dos votos é dinâmica, se por acaso o nó que não está contando voto (SQLNODE1) cair ou for desligado, mesmo assim o nó SQLNODE2 continuará ativo e o cluster se manterá online e operacional. Neste caso teremos a atuação do “last man standing”, lembra? Por outro lado, se por acaso o nó que mantém o voto (SQLNODE2) for “desligado” ou reiniciado, o “DynamicQuorum” entrará em ação e “passará” o voto para o nó que se manter online e o cluster continuará operacional. Temos novamente a atuação do “last man standing”. Isso pode ser visto na figura 1.7 após ser desligado o servidor SQLNODE2.
Figura 1.7: Voto atribuído ao servidor SQLNODE1
Neste momento você pode estar se perguntando: Bom, então quando eu poderei perder o meu cluster se os votos são reatribuídos dinamicamente e o cluster sempre se mantém online? Vou responder esta pergunta apresentando a você 4 possíveis possibilidades do que pode acontecer quando temos um cluster com apenas dois nós e usando “Node Majority” como configuração do quórum. Para isso vamos usar o exemplo da figura 1.6 onde o nó SQLNODE2 é o nó que mantém o voto do cluster. As possibilidades são: 1. Se o servidor SQLNODE1 sofrer um restart ou for desligado, o cluster continuará online e operacional com o servidor SQLNODE2, 2. Se os servidores perderem a comunicação entre eles, o cluster continuará online e operacional com o servidor SQLNODE2,
3. Se o servidor SQLNODE2 sofrer um restart ou for desligado, o cluster removerá o voto do servidor SQLNODE2 passando-o para o servidor SQLNODE1 dinamicamente e o cluster continuará online e operacional com o servidor SQLNODE1, 4. Se o servidor SQLNODE2 cair de forma “abrupta”, então o cluster não conseguirá atribuir o voto para o servidor SQLNODE1 e neste caso o cluster ficará indisponível. Então, você perderá o seu cluster apenas se o servidor que mantiver o voto cair de forma inesperada e abrupta, pois neste caso o cluster não terá tempo para remover o voto do servidor que caiu e atribuir o voto para o servidor que permaneceu online. Neste caso todo o cluster cairá, causando a indisponibilidade de todos os serviços. Para se prevenir desta possibilidade de falha inesperada e queda do cluster, entra então a recomendação de configuração de mais um elemento votante no cluster, como por exemplo, a configuração de um disco ou file share witness.
Mão na Massa: Configurando o Quórum do Cluster Agora que você sabe todo o conceito sobre o quórum, veja nos passos seguintes todo o passo a passo para a configuração do quórum em um cluster. No Failover Cluster Manager, clique com o botão direito do mouse sobre o nome do cluster e selecione as opções “More Actions | Configure Cluster Quorum Settings...” como apresentado na figura 1.8
Figura 1.8: Configurando o Quorum do cluster
Na janela “Configure Cluster Quorum Wizard”, na página “Before You Begin” simplesmente clique em Next para prosseguir. Em seguida, na página “Select Quorum Configuration Options”, você deve selecionar uma das três opções apresentadas. Estas opções já foram abordadas no tópico sobre os modelos de quórum. Vale ressaltar que na opção “Advanced quorum configuration” você poderá selecionar quais os nós que deverão ser considerados como elementos votantes no cluster e o tipo de witness. Você também poderá optar para que nenhum nó seja elemento votante, mas neste caso você será obrigado a utilizar um “disk witness”. O disco será então o seu ponto único de falha no cluster, ou seja, se por qualquer motivo o disco falhar ou os servidores não conseguirem acesso a ele, todo o cluster falhará. Para nosso exemplo, vamos manter todos os nós como elementos votantes e apenas adicionar um witness. Então, selecione a opção conforme apresentado na figura 1.9.
Figura 1.9: Opções para configuração do quorum
Na página “Select Quorum Witness”, figura 1.10, você tem a opção de selecionar qual o tipo de witness deseja configurar para o cluster. Podendo optar por um “disk witness” (requer um disco), “file share witness” (requer um compartilhamento de rede) ou simplesmente não configurar um witness. Para configurar um disco, mantenha selecionada a opção “Configure a disk witness” e clique em Next.
Figura 1.10: Selecionando o tipo de witness como “disk”
Vale destacar que ao relacionar as opções apresentadas na figura 1.10 com os modelos de quórum abordados no tópico sobre os modelos de quórum, chegamos ao apresentado na tabela abaixo:
Tabela 1.2: Opções de configuração do quorum e modelo de quorum
Após selecionar o tipo de witness e clicar em Next na janela da figura 1.10, você deverá selecionar na página “Configure Storage Witness” qual o disco que a ser utilizado para o quórum, o “disk witness”. Lembre-se que ao selecionar um disco, ele contará como mais um elemento votante no cluster e também armazenará uma cópia do banco de dados do cluster, banco de dados este que é sincronizado com todos os nós do cluster.
Figura 1.11: Selecionando o disco de quórum
Após selecionar o disco de quórum, clique em Next na página “Confirmation” e aguarde a configuração do quórum. Após alguns segundos será apresentada a janela de sumário com o status da configuração executada. Clique então sobre o botão Finish para concluir.
Figura 1.12: Confirmando as informações para configuração do quórum
Concluída a configuração você poderá visualizar as informações sobre o modelo de quórum em uso na janela principal do Failover Cluster Manager com apresentado na figura 1.13.
Figura 1.13: Cluster utilizando o modelo de quorum “Node and Disk Majority”
Conclusão Como você viu, a configuração do quórum em um cluster é bastante simples e fácil de ser executada. Vimos também que o “Dynamic Quorum” e “Dynamic Witness” são ótimas funcionalidades a serem usadas como default em todos os seus cluster uma vez que elas permitem prover uma tolerância a falhas bem maior para os cluster, reduzindo significativamente as chances do cluster ficar indisponível por falhas nos nós ou mesmo do recurso de witness. Eles também adicionam uma grande simplicidade e flexibilidade na configuração do quórum, removendo definitivamente a necessidade de uma intervenção manual na reconfiguração pois agora o próprio cluster fará isso de forma dinâmica. Caso você queira saber mais sobre o assunto, duas ótimas referências são: Configure and Manage the Quorum in a Windows Server 2012 Failover Cluster (http://technet.microsoft.com/enus/library/jj612870.aspx) e o livro “SQL Server 2014: Alta Disponibilidade na Prática com AlwaysOn Failover Cluster Instances”, este último de minha coautoria juntamente com Marcelo Fernandes
In-Memory OLTP 2016: O relançamento de uma potência Não demorou muito para o In -Memory OLTP chamar atenção, assim que foi lançado no SQL Server 2014. Porém, a euforia deu lugar a alguma decepção, devido a sua extensa lista de limitações. Com o anúncio do SQL Server 2016, muitas novidades virão, e o In -Memory OLTP acumula uma grande parte delas. Neste artigo serão abordados não só as melhorias desta funcionalidade no SQL Server 2016, assim como as motivações que levaram ao desenvolvimento do In-Memory OLTP. Por Murilo Miranda http://murilo.pt/ Revisão Técnica por Luan Moreno e Vitor Fava O lançamento do SQL Server 2014 trouxe algumas novidades, sendo o In-Memory OLTP uma das “estrelas da companhia”, carregando a fama de ser uma tecnologia revolucionaria que pela primeira vez iria permitir que tabelas, índices e dados fossem inteiramente alocados em memória durante todo o seu ciclo de vida. De fato a tecnologia trazida pelo In-Memory OLTP era revolucionaria, assim como a outra tecnologia da mesma família, lançada com o SQL Server 2012, mas focada em sistemas OLAP: o Columnstore. Mesmo sendo uma tecnologia que prometia muito, sua implementação tem sido escassa, muito por conta de suas imensas e inconvenientes limitações. Apesar de ser provado que na prática o ganho de performance realmente ocorre, é difícil encontrar um caso de uso que mude por inteiro o panorama de uma base de dados. Desde o início, ficou claro que a Microsoft lançou a funcionalidade inacabada e com o objetivo ir desenvolvendo melhorias em paralelo. Uma decisão inteligente por um ponto, já que desta forma a aceitação do In-Memory OLTP poderia ser testada, da mesma forma que sistemas compatíveis com as limitações poderiam tirar proveito da funcionalidade de imediato. Mas existe o outro lado da moeda…. Vi muitos clientes desistindo do In-Memory OLTP por conta de suas limitações, o que pode ter abalado a imagem de uma funcionalidade revolucionária. Felizmente o SQL Server 2016 está aí, trazendo muitas coisas interessantes. Na minha opinião, uma das melhores versões do SQL Server lançadas até hoje. Muitas novidades, melhorias e mais integração com o Azure… E é claro, o In-Memory OLTP ganhou (muitas) melhorias, gerando a expectativa de um verdadeiro relançamento desta poderosa funcionalidade.
In-Memory OLTP: o conceito e as motivações Mas afinal, o que é o In-Memory OLTP? Se é tão bom, porque a Microsoft não desenvolveu esta funcionalidade antes? Observando a evolução do hardware, é possível perceber que houveram dois fatos interessantes: O preço por gigabyte da memória RAM vem caindo desde o inicio dos anos 2000. A evolução dos processadores (CPU) mudou de rumo, tendo estagnado na mesma taxa de clock desde o inicio deste século. Hoje em dia podemos comprar memoria RAM, com uma grande capacidade, um tempo de resposta muito bom e um preço indiscutivelmente mais baixo do que a 10 anos atrás. O seguinte gráfico mostra que o preço por gigabyte no ano 2000, comparado com o preço atual, era aproximadamente 20 vezes mais caro.
Além de ser financeiramente viável configurar um servidor com memoria ao nível de Terabytes hoje em dia, outra situação motivou a criação do In-Memory OLTP: a performance de bases de dados tradicionais deixou de ser aceitável em muitos casos. Isso acontece por vários motivos, porém é possível apontar dois dos fatores principais: os mecanismos de lock e o tempo desperdiçado nas operações em disco. De forma a manter as transações consistentes, o DBMS tem a necessidade de proteger as estruturas de acesso partilhado. E isso é feito através de locks. Existem vários tipos e níveis de lock, que por si poderiam render várias páginas dedicadas ao tema. De uma forma e linguagem muito simples, imagine a seguinte situação:
Existem três processos distintos 1, 2 e 3. Todos estes processos têm como objetivo a mesma tabela e farão operações na mesma linha, identificada com o ID “45”. O processo (1) quer executar um DELETE da linha, o outro processo (2) ler a linha e o último (3) alterar o valor de uma das colunas. Não é possível executar todos ao mesmo tempo. O que fazer para resolver este conflito? É necessário uma estratégia que não permita que mais do que uma operação seja executada em simultâneo nesta linha. Por este motivo o SQL Server usa locks. Desta forma, o primeiro processo a conseguir o lock requisitado prosseguirá com a sua operação, “trancando” a linha durante a operação e liberando a após a conclusão de seu trabalho. Os outros dois processos ficaram em espera durante este tempo, e assim que o lock foi liberado, um deles irá conseguir o lock, e assim sucessivamente. O lock é um artificio usado para evitar que hajam conflitos de operações diferentes numa mesma estrutura partilhada. Porém, como visto no exemplo anterior, em um ambiente altamente concorrente, sempre existirão processos parados a espera de um certo recurso, que estaria “trancado” por um outro processo. Esta situação pode parecer simples, mas em grande escala, pode trazer muita dor de cabeça… O outro grande culpado por problemas de performance, muitas vezes é o disco. Basicamente operações de escrita e leitura de dados é custosa, mesmo com os melhores SSDs (Solid State Drive) do mercado. Os dois problemas referidos resultaram em toda uma estrutura baseada em extents, pages, locks e latches, o que penaliza sistemas altamente concorrentes, em nome da integridade dos dados. Com o objetivo de fugir da complexidade de estruturas em disco, assim como de tirar proveito da excelente performance da memória RAM, a Microsoft iniciou o desenvolvimento do Projeto Hekaton, o que resultou no In-Memory OLTP. Muito se fala que o SQL Server já usa a memoria RAM para guardar os dados, e de fato isso é verdade. Se um servidor tiver um montante de memória RAM suficiente para alocar uma base de dados inteira, a performance irá certamente melhorar. Por isso a estratégia de muitas empresas é adicionar mais memória ao invés de investir em tuning – resolve-se o problema, não a causa. De uma forma geral, quando uma query é executada, o SQL Server irá primeiro verificar se as páginas contendo os dados requeridos já estão em memória, se sim, o disco nem será acessado. Este é um dos motivos porque o tempo de execução de uma query é maior na sua primeira execução ou depois que a instancia é reiniciada, já que as respectivas estruturas em memória, incluindo o Buffer Pool, serão recriadas. Outra possibilidade muito comentada é o comando “DBCC PINTABLE”, este comando permitia a fixação de páginas de uma tabela em memória, de forma a minimizar acessos ao disco. Esta possibilidade foi removida no SQL Server 2005, o que motivou formas criativas de manter-se uma tabela inteiramente em memória. Um exemplo: Criar um job para executar um simples “SELECT * FROM tabela” periodicamente ao longo do dia.
Tudo isso pode ser feito, porém nada é comparável ao que foi feito para o In-Memory OLTP, sendo que no final das contas a estrutura que estará na memória é a mesma que está no disco, o que não é otimizado para estar em memória. De forma a tirar todo proveito da memória RAM, o In-memory OLTP foi construído de raiz, sendo uma engine completamente nova. Existem rumores de que a Microsoft cogitou o lançamento do Hekaton como um produto paralelo ou complementar ao SQL Server, mas felizmente isso não aconteceu, o que resultou num atrativo muito grande que diferencia o SQL Server de outros DBMS (Database Management Systems): Tabelas em memoria e tabelas em disco podem compartilhar a mesma base de dados e até “conversarem” entre si. Em resumo, dentre outros, seguem quatro motivos para o In-Memory OLTP ser tão rápido: Os dados estarão inteiramente em memória, o que exclui a penalização de escrita e leitura em disco. Novas estruturas de dados foram criadas, permitindo que a excelente performance da memória RAM seja explorada ao máximo. Modelo Otimista: Não existem locks nem latches. Os objetos são compilados, o que evita o passo extra de interpretação que o T-SQL tradicional requer.
O que tanto mudou do SQL Server 2014 para o 2016? Como era previsto, a Microsoft manteve o investimento no In-Memory OLTP no SQL Server 2016, trazendo melhorias essenciais. Tais melhorias trouxeram o que faltava no In-Memory OLTP no SQL Server 2014, acabando com as principais limitações quase por completo. Segundo a estratégia de cloud-first adotada pela Microsoft, todas as modificações e novas funcionalidades com foco no SQL Server, serão disponibilizadas no Azure SQL Database, sendo que estas irão ser disponibilizadas na versão on-premises do SQL Server após um período de maturação, em um ambiente com escala global e com um comprometimento elevado a nível SLA. Sem mais delongas, vamos ao que interessa: o que mudou no In-memory OLTP no SQL Server 2016.
Tamanho total suportado para tabelas in-memory No SQL Server 2014, existe uma limitação no tamanho total das memory-optimized tables de 256 Gb. Este valor não é relativo a uma tabela apenas, os 256 Gb compreendem todas as tabelas in-memory em uma única base de dados. Desta forma podemos ter, por exemplo, uma tabela ocupando o espaço de 256 Gb da mesma forma que poderíamos ter quatro tabelas de 64 Gb. Como referido, este é um limite por base de dados, o que permitiria termos duas bases de dados com uma soma de 512 Gb de dados inmemory. Mesmo sendo um limite relativamente alto para uma tabela de um ambiente OLTP, a Microsoft trabalhou na melhoria deste cenário no SQL Server 2016, aumentando o limite de tabelas in-memory para 2 Tb por base de dados. O que permitiria a migração de uma de uma VLDB na integra para memória.
Mais escalabilidade na gestão de storage O In-memory OLTP não é 100% memória. Afinal alguém disse que o disco não é usado? Infelizmente é. De forma a garantir a durabilidade dos dados, a serviço de tabelas in-memory duráveis, checkpoint files são mantidos no disco. Os checkpoint files são compostos por dois arquivos: Data e Delta. Todas as adições, alterações e remoções de dados irão ser registradas no transaction-log e persisidas nos checkpoint files. Estes arquivos são mantidos em containers e alimentados por uma um processo chamado Log Reader thread, que basicamente aplica os registros de log que vão sendo gerados pelas transações, presentes no tail do transaction-log. Este processo passou a ser identificada como um possível gargalo, já que não é escalável no SQL Server 2014. O problema é simples: Em sistemas com um hardware mediano, a velocidade na qual os registros de log vão chegando é moderada, sendo, desta forma, a velocidade de processamento da Log Reader thread suficiente. Porém, quando existe a necessidade de um melhor desempenho e o hardware é melhorado, a velocidade na qual são gerados registros de log passa a ser maior, e um único processo de Log Reader thread não consegue consumir tudo o que é produzido a tempo. Isso gerou uma limitação no SQL Server 2014, na qual o processamento ótimo é limitado a um servidor de 2 sockets e 64 cores, não mais do que isso.
No SQL Server 2016 este limite será expandido, trazendo suporte a sistemas com até 4 sockets, o que possibilita uma solução escalável, ao nível do hardware que melhor serve às necessidades aplicacionais. Como isso é feito? Com algumas mudanças no código do SQL Server 2016, cada container criado terá uma Log Reader thread associada, trazendo uma mais flexibilidade a nível de processamento dos registros de log que vão sedo gerados.
O Garbage collector e sua relação com os checkpoint files Já que os checkpoint files foram introduzidos no ponto anterior, podemos revelar mais um problema que foi detectado a sua volta no SQL Server 2014. Conforme dito, os checkpoint files estão armazenados em containers. A gestão destes containers é feita com base numa solução já existente no SQL Server: O Filestream. Porém, o processo de Garbage Collector (coletor do lixo) gerado pelos checkpoint files não é eficiente, sendo que o Garbage Collector do FIlestream não liberta o espaço em disco imediatamente, o que pode causar problemas de falta de espaço para a manutenção de checkpoint files ativos, ou criação de novos arquivos. Este lixo é gerado pela operação de Merge que é executada periodicamente de forma a remover do disco dados que já não são mais referenciados por tabelas in-memory duráveis, o que pode ser gerado por comandos de DELETE , UPDATE, DROP TABLE e ALTER TABLE. De forma a resolver este problema, a Microsoft desacoplou a gestão de storage do FileStream, no SQL Server 2016, permitindo que os checkpoint files não utilizados sejam apagados imediatamente após não terem mais apontadores os referenciando no transaction log.
Mais opções a nível de segurança Um dos adoption-blockers do In-Memory OLTP está relacionado ao nível de segurança. Não porque o In-Memory OLTP no SQL Server 2014 tenha falhas de segurança, mas porque Transparent Data Encryption, ou simplesmente TDE, não é suportado. Para alguns ramos da indústria, o TDE é essencial de forma a se obter certificações importantes que permitem o negocio atingir altos níveis de competitividade e aceitação por parte dos clientes. Por este motivo, mesmo que a melhoria de performance seja a prioridade número um, a falta de suporte ao TDE ou de uma tecnologia semelhante impede que muitas bases de dados passem a usufruir das capacidades do In-Memory OLTP. Seria o mesmo que dirigir um carro de Formula-1 sem capacete e cinto de segurança. De forma a abranger o número de funcionalidades essenciais suportadas pelo In-Memory OLTP, o suporte ao Transparent Data Encryption foi adicionado ao SQL Server 2016, desta forma o problema exposto foi resolvido. Perfeito! Agora podemos ter velocidade com segurança.
Paralelismo No SQL Server 2014, planos de execução envolvendo tabelas otimizadas para memória nunca irão usar paralelismo. Já no SQL Server 2016 esta situação foi melhorada, sendo que algumas operações usando índices in-memory o tipo HASH poderão ser executadas em paralelo, desde que não seja executada por uma Stored Procedure compilada nativamente.
AlwaysOn Availability Groups Ao utilizar o Availability Groups em bases de dados contendo tableas in-memory, no SQL Server 2014, era possível verificar um atraso no sincronismo face a tabelas tradicionais. Esta problema foi solucionado, sendo que no SQL Server 2016 todas as tabelas, independentemente do tipo, serão sincronizadas em simultâneo.
Manutenção de código e T-SQL Suportado Imagine uma tabela que foi mal planejada, com tipos de dados não convenientes, ou mesmo um novo requisito trouxe a necessidade de se adicionar uma nova coluna. É simples alterar uma tabela certo? Agora pense num relatório que funcionaria melhor se um simples índice fosse adicionado. Uma boa solução que pode trazer ótimos resultados, certo? E se existisse um SGBD no qual não fosse possível a alteração de tabela, ou criação de índices. Você iria escolher esta plataforma para o seu projeto? Pois é exatamente isso que acontece no In-Memory OLTP para SQL Server 2014. Não podemos alterar tabelas, não podemos renomear tabelas nem ao menos alterar ou adicionar novos índices. Estas limitações fazem tarefas simples como responder a mudanças nos padrões de dados ou modificações aplicacionais serem um pesadelo. Esta grande limitação está no Top-5 dos motivos de rejeição do in-memory OLTP, em conjunto com comandos básicos de T-SQL que não são suportados. Coisas simples, que já estamos acostumados, mas que não podemos viver sem. Seria como tentar respirar em lugar sem oxigénio. Felizmente muitos dos problemas descritos foram resolvidos no SQL Server 2016, trazendo um novo fôlego ao In-Memory OLTP. Finalmente, o comando ALTER será suportado! No In-Memory OLTP você poderá apreciar não apenas o poder de um comando ALTER TABLE, assim como um ALTER PROCEDURE. O famoso sp_recompile também será suportado. Em conjunto com o ALTER TABLE, os comandos ADD/DROP/ALTER INDEX poderão ser usados, o que permitira seguir o dinamismo dos requisitos técnicos e resolver problemas de performance. Ainda sobre índices, outra preocupação era uma opção para alterar o bucket_count dos índices In-Memory do tipo HASH, o que afeta diretamente a performance. Isso será possível através de um rebuild deste índice. É de grande importância referir que ao se executar o comando ALTER TABLE, a alteração será feita offline e requer espaço livre em memória igual ao ocupado pela tabela. Uma limitação a esta operação, mas que provavelmente será trabalhada no futuro. Infelizmente a execução de sp_rename ainda não será suportado. Em relação a comandos T-SQL, a seguinte lista mostra o que não era suportado no SQL Server 2014 e passou a ser no SQL Server 2016: CREATE PROCEDURE DROP PROCEDURE ALTER PROCEDURE
SELECT e INSERT SELECT SCHEMABINDING e BEGIN ATOMIC (stored procedures com compilação native requeridas) NATIVE_COMPILATION Parametros e variáveis poderão ser declaradas como NOT NULL Parâmetros table-valued. GRANT e DENY suportados em tabelas e procedures Aninhamento de Stored Procedures nativas RIGHT OUTER JOIN, LEFT OUTER JOIN, INNER JOIN, e CROSS JOIN em cláusulas SELECT Operadores NOT, OR, e IN nas cláusulas SELECT, UPDATE e DELETE UNION ALL e UNION SELECT DISTINCT Cláusula GROUP BY, sem funções de agragação, suportadas na cláusula SELECT. COLUMNSTORE COLLATE AFTER DML triggers – compilados nativamente Outra importante nota é que no SQL Server 2016 todas as collations serão suportadas, ao contrário do SQL Server 2014 que obrigava o uso da collation BIN2.
Ferramentas Para fechar o assunto, algumas melhorias a nível de ferramentas foram introduzidas, incluindo: Assistente de migração para In-memory OLTP – o In-Memory OLTP Migration Checklist. Tabelas otimizadas para memória serão suportadas pelo SQL Best Practices Analyzer. O SQL Server Management Studio (SSMS) irá suportar a criação e gestão de tabelas in-memory através do Table Designer.
Conclusão Após abordarmos as melhorias trazidas no SQL Server 2016, facilmente conseguimos compreender o quão difícil seria adotar o In-Memory OLTP no SQL Server 2014, face as muitas limitações. Existem alguns casos de uso nos quais a tecnologia se encaixa com perfeição, porém uma adoção maior seria muito difícil. Outra conclusão é de que a evolução no SQL Server 2016 é notável, e aborda praticamente todos os pontos nos quais o In-Memory OLTP foi mais criticado no SQL Server 2014, trazendo uma resposta, quase que subliminar, a quem não acredita no poder desta funcionalidade assim como no forte investimento nesta área que vem sido feito por parte da Microsoft. A documentação oficial e completa do In-Memory OLTP, para SQL Server 2016, poderá ser encontrada no seguinte link: https://goo.gl/o3Nhcv.
Deadlock no SQL Server Veja neste artigo quais os tipos de locks encontrados no SQL Server 2008/2008 R2/2012, o que é deadlock e como acontece um deadlock. Por fim, veja como receber um alerta no seu e-mail quando este evento ocorrer no ambiente de produção. Por Cibelle Castro https://cibellecastro.wordpress.com Revisão técnica por Luciano Moreira http://luticm.blogspot.com.br/
Introdução O artigo tem como objetivo demostrar como receber notificações por e-mail quando a sua instância de SQL Server sofrer um deadlock. A princípio irei conceituar um pouco sobre locks, os tipos de locks, para finalmente entrarmos propriamente dito no tema deadlock. O mesmo também será conceituado e depois começo a demonstrar a solução adotada para recebermos por e-mail os dados que participaram diretamente do deadlock.
Definição Lock O gerenciamento de locks é uma função crucial para qualquer sistema de banco de dados. Os locks são utilizados em ambos os modelos otimista e pessimista, embora os processos sejam diferentes. No modelo pessimista escritores sempre bloqueiam leitores ou escritores, e os leitores podem bloquear escritores. Em outras palavras, um sistema que comporta-se de maneira pessimista assume que um conflito irá ocorrer. Para evitar esses conflitos o modelo pessimista adquire locks nos dados que estão sendo lidos, então nenhum outro processo pode modificar esses dados. Ele também adquire locks em dados que estão sendo modificados de modo que nenhum outro processo pode acessar esses dados para leitura ou modificação. No modelo otimista o único bloqueio que ocorre é que os escritores bloqueiam outros escritores. O modelo otimista usa um método de controle de concorrência chamado de multiversion concurrency control (MVCC) usado para o versionamento de linha para permitir que os leitores possam ver o dado no estado anterior a modificação ocorrer. Um processo que modifica os dados não é afetado por um processo de leitura, porque o leitor está acessando a versão anterior das linhas de dados. O SQL Server usa o tempdb para armazenar as versões de todas as linhas alteradas e mantém essas cópias enquanto existirem quaisquer transações que precisam ser acessadas. O espaço utilizado no tempdb para as versões anteriores é chamado de version store. Antes de utilizar o row versioning deve-se considerar cuidadosamente as vantagens e desvantagens da utilização deste modelo de concorrência. Uma vez que será necessário monitorar mais de perto o aumento do uso do tempdb para armazenamento das versões. Outra coisa que deve ter em mente é que o controle de versão diminui o desempenho de operações de alteração de dados, por causa do trabalho extra envolvido na manutenção das versões antigas. Quando você usa o row versioning, leitores não bloqueiam escritores e escritores não bloqueiam leitores, embora escritores continuem a ter bloqueios e bloquear outros escritores.
Lock Modes O SQL Server pode bloquear os dados utilizando diferentes modos de bloqueios, são mais conhecidos como lock modes. O lock mode mostra o quão restritiva um lock é, e que outras ações são possiveis enquanto o lock é mantido. Uma comparação interessante foi a do livro Professional SQL Server 2012 Internals and Troubleshooting [5], ele fala que os dados em um banco de dados não é igual a de um livro que só pode estar na posse de uma pessoa ao mesmo tempo. Se você está lendo um livro, ele está nas suas mãos e outras pessoas que desejam lê-lo não podem simplesmente tirar das suas mãos e começar a ler. Precisam esperar você acabar a sua leitura e disponibilizar o livro para a próxima pessoa interessada. Traduzindo para o SQL Server seria por exemplo você querendo fazer uma operação de leitura (read operation) em uma tabela qualquer, neste momento adquire o chamado shared lock, se fosse uma operação de escrita (write operations) iria adquirir um exclusive lock e por fim se fosse uma operação de atualização (update lock) seria adquirido durante a parte inicial da operação de update, enquanto SQL Server está em busca dos dados para realizar o update. SQL Server adquire e libera esses tipos de locks automaticamente. Assim como gerencia a compatibiliade entre os modos de bloqueio, resolve deadlocks e escala locks caso seja necessário. Ele
controla bloqueio em tabelas, nas páginas das tabelas, nas chaves de indices (index keys) e em linhas individuais de dados. [1]
Shared Lock (S) Por padrão o SQL Server adquire um Shared Lock (S) quando faz a leitura de dados. Um Shared Lock pode ser mantido em uma tabela, uma página, um index key ou uma linha individual [2]. Quando o pedido para a leitura de uma linha é feito, o SQL Server irá solicitar um lock no modo shared. Esse modo é compatível com a maioria dos outros locks, uma vez que só é permitido a leitura da linha na página de dados. Muitos processos podem segurar um shared lock nos mesmos dados, mas nenhum processo pode adquirir um exclusive lock no dado que tem um shared lock sobre ele (a menos que o processo que está solicitando um exclusive lock seja o mesmo processo que está segurando o shared lock [3]). No nível de isolamento padrão, os bloqueios compartilhados são liberados assim que os dados forem lidos, mas esse comportamento pode ser alterado usando hints ou um nível de isolamento diferente.
Exclusive Lock (X) Os bloqueios exclusivos (exclusive locks) são usados para modificação de dados através dos comandos de INSERT, UPDATE e DELETE. No caso do exclusive lock, ele não é compativel com qualquer outro tipo de lock, incluindo outros exclusive locks. Todos os locks devem experar até que o exclusive lock seja liberado antes que possam continuar. Bloqueios exclusivos são mantidos até o final da transação seja ela um commit ou rollback. Porém, você pode ler dados com um exclusive lock basta usar o hint NOLOCK ou usar o nível de isolamento uncommited. Isso acontece porque os comandos DML precisam primeiro ler os dados de serão modificados, ou seja, você sempre achará um exclusive lock acompanhado de um shared lock nos mesmos dados. Por exemplo, ao utilizar o comando update para modificar as linhas de uma tabela usando um “join” para outra tabela, o update neste caso, solicita um shared lock nas linhas lidas na tabela do “join” e depois solicita um exclusive lock nas linhas que serão atualizadas [8].
Update Lock Os bloqueios de atualização (update locks) são uma forma hibrida do shared locks e exclusive locks. Eles são adquiridos quando uma operação de modificação de dados é solicitada. A primeira coisa que o SQL Server deve fazer é procurar a tabela que precisa ser modificada. O processo é simples, o SQL Server usa o update lock para localizar os dados e em seguida impede que outros processos atualizem esses dados. Como o update lock obstruiu todos os outros bloqueios de modificação, tudo o que precisa fazer é esperar até ele poder ser convertido para um exclusive lock, quando o último shared lock (se houver) for liberado. Update locks oferecem compatibilidade com outros leitores, permitindo que o processo de modificação dos dados tenha garantido que os dados não foram alterados desde a última leitura. Um update lock não é suficiente para permitir que você altere os dados. Todas as modificações exigem que o recurso que está sendo modificado tenha um exclusive lock. [4] Apesar do nome ser “update locks”, ele não se refere apenas a operações de UPDATE. O SQL Server usa update locks para qualquer operação de modificação de dados que exige uma pesquisa de dados antes da modificação real. Tais operações incluem updates e deletes condicionais, bem como “inserts into” em
uma tabela sem índice cluster (clustered index). Neste último caso, o SQL Server deve primeiro procurar os dados (usando o índice cluster) para encontrar a posição correta para inserir a nova linha. Mesmo que o SQL Server só esteja pesquisando, ele usa update locks para proteger os dados. Só depois de achar o local correto e começar a inserir é que ele faz a conversão do update lock para um exclusive lock [1].
Intent Lock Esse tipo de bloqueio não é um modo diferente, o termo “Intent” é um qualificador para os modos que foram descritos anteriormente. O SQL Server pode conceder lock em vários níveis ou graus de granularidade e esses níveis são usados para formar uma hierarquia dentro do SQL. Por exemplo, uma linha está na parte inferior dessa hierarquia e ela pertence a uma página, a página pertence a uma tabela e assim por diante. O propósito do Intent Lock é indicar aos níveis mais altos da hierarquia de bloqueios que uma parte do recurso tem um lock mantido contra ele. Seria mais ou menos assim: Se um exclusive lock é adquirido em um registro, a página e a tabela terão um intent exclusive lock (IX) mantido contra eles. Caso outro processo queira adquirir um table lock, ele pode ver que existe um intent exclusive lock no nível da tabela e consequentemente sabe que a operação está bloqueada, sem ter que fazer um scan na tabela inteira procurando por locks conflitantes. Esse tipo de lock não é a mesma coisa que um shared lock ou exclusive lock, eles servem de indicadores para o SQL Server, mostrando que um lock foi obtido em um nível mais baixo da hierarquia para um recurso. [5]
Schema Lock Esse tipo de bloqueio é um pouco diferente dos outros bloqueios mencionados acima, uma vez que são adquiridos no nível do objeto e metadados. Por exemplo, imagine que você tem uma tabela e quer excluir uma coluna porque houve uma mudança no negócio e o dado contido nela não é mais necessário, mas ao mesmo tempo tem uma outra pessoa tentando criar um índice que inclui a coluna que você pretende excluir. Neste caso nós temos duas pessoas tentando alterar o mesmo objeto simultaneamente. O schema lock (Sch-M) é similar ao lock exclusive uma vez que eles são mantidos até o final da transação. Essa informação é importante para quando formos executar instruções DDL dentro de transações explicitas (aquelas que você define o início e o término da transação “formalmente”), pois em caso de erro é possível desfazer as alterações de schema e também impede qualquer acesso aos objetos afetados até que a transação seja confirmada [13]. Existem dois modos de schema locks: schema modification (Sch-M) e schema stability (Sch-S). Quando uma consulta é compilada os locks de schema stability impedem que outros processos adquiram locks de schema modification que são usados quando a estrutura de uma tabela está sendo modificada. Eles são disparados por diferentes processos, mas basicamente se resumem a mesma coisa.
Deadlock O deadlock ocorre quando uma transação bloqueia um recurso e em seguida tenta adquirir um lock em outro recurso, mas é bloqueado por outra transação e não será capaz de concluir a sua operação até que a segunda transação seja concluída e liberar seus bloqueios. Porém, se a segunda transação faz algo que precisa esperar a primeira transação, eles vão ficar esperando para sempre. Felizmente, essa situação é detectada pelo Database Engine e um dos processos é terminado. O SQL Server permite que uma transação seja completada e elimina a outra transação com uma mensagem de erro, notificando ao usuário que ele foi escolhido como vítima do deadlock (deadlock victim). Imagine a seguinte situação: Você vê em um site qualquer uma promoção imperdível de uma viagem que quer fazer faz muito tempo, mas a mesma só possui duas passagens. Imediatamente você manda mensagem para seu namorado falando que vai comprar as passagens e já faz todo o cadastro para poder garantir a compra. Ao tentar selecionar os acentos você só consegue visualizar uma poltrona. Porém, do outro lado da cidade está seu namorado (que por coincidência do destino também viu a mesma promoção) está tentando fazer uma surpresa para o aniversário de namoro que está próximo. Só que ao tentar selecionar as duas poltronas restantes só existe uma poltrona a outra está reservada! 1. Você tentando reservar as poltronas A1 e B1. Ou seja, você (Transação 1) está bloqueando as poltronas, ao mesmo tempo o seu namorado (Transação 2) também está tentando reservar as poltronas. 2. Você (Transação 1) conseguiu selecionar a poltrona A1, mas ao tentar reservar a poltrona B1 não consegue, pois a mesma está bloqueada pelo seu namorado (Transação 2). Ou seja, você (Transação 1) entra em estado de espera. 3. Seu namorado (Transação 2) tenta acessar a poltrona B1 para fazer a reserva, mas ela já está bloqueada por você (Transação 1). Seu namorado (Transação 2), neste caso também entra em estado de espera. Caso nada aconteça a Transação 1 e o Transação 2 vão ficar esperando indefinidamente.
Figura 1. Cenário Deadlock
O deadlock não é exatamente este cenário que acabei de descrever, pois existem detalhes que estou omitindo, na realidade o SQL Server deverá escolher entre você ou seu namorado para matar um dos processos, gerando o erro 1205 para informar ao cliente que o processo foi interrompido. Segundo Fritchey [11] um deadlock é um cenário especial de bloqueio em que dois processos são bloqueados entre si. Cada processo tenta acessar o recurso que está bloqueado pelo outro processo enquanto mantem seus próprios recursos. No SQL Server 2012 os deadlocks podem envolver outros recursos além dos bloqueios. Por exemplo, se o Processo X segura um bloqueio (lock) na tabela T1 e espera por memória disponível, mas o Processo Y tem alguma memória que não pode liberar até que consiga um bloqueio na tabela T1. Podemos dizer que deadlock acontece em um cenário especial de bloqueio, no qual dois processos se bloqueiam simultaneamente. Cada processo mantém seus recursos e tentam acessar um recurso que está com lock por outro processo. Isto irá levar a um cenário conhecido como “deadly embrace” [6]. Os deadlocks também ocorrem quando dois processos tentam escalar seus locks no mesmo recurso. Por exemplo, dois processos possuem um shared lock em um recurso, tal como RID (Row IDentifier) e cada um dos processos tenta promover o shared lock para um exclusive lock. Porém nenhum deles pode concluir essa ação até que o outro libere o shared lock. Uma vez o deadlock detectado, o SQL Server seleciona um dos processos participantes do deadlock como uma vítima para quebrar o bloqueio circular. Existe uma thread separada no SQL Server conhecida como LOCK_MONITOR, ela é responsável por verificar se ocorreu um deadlock a cada cinco segundos. Esse intervalo pode ser reduzido até menos que 100 milissegundos dependendo da frequência que ocorrem os deadlocks [1]. Quando o deadlock é detectado o Database Engine termina uma das threads para resolver o deadlock. Existem 21 níveis diferentes de prioridade, de -10 até 10. O valor baixo (low) equivale a 5, nomal (normal) é 0 e alto (high) é 5 [7]. Se as sessões possuem diferentes prioridades de deadlock, a sessão com menor prioridade será escolhida como vítima. Caso, ambas as sessões possuam a mesma prioridade de deadlock, o SQL escolhe a sessão que é menos cara para fazer o rollback. Porque se o SQL Server deve “matar” uma transação, qualquer trabalho feito até o momento deve ser revertido para colocar o banco de dados em um estado consistente. Neste caso, o SQL Server faz o rollback da sessão que usa menos espaço no log de transação, olhando o valor de LOG USED (Figura 2). A sessão que foi finalizada recebe um erro 1205 e a seguinte mensagem é exibida: Error 1205: Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Figura 2. Rollback da transação com menor LOG USED
Depois que o processo é “morto”, a transação abortada e seus locks liberados, o outro processo envolvido no deadlock pode terminar o trabalho e liberar os seus locks.
Lembrando que o nível de prioridade pode ser controlado através da instrução: SET DEADLOCK_PRIORITY. Essa configuração é definida em tempo de execução e não quando acontece o parse [12]. Ou seja, você define a importância da sessão caso ocorra um deadlock com outra sessão.
Monitorando Deadlock Agora que sabemos o que é deadlock, podemos pensar em qual a melhor maneira de identificarmos se nosso ambiente está sofrendo desse tipo de problema. Na realidade existem várias maneiras tais como: system health, extend events, profiler ou habiltando alguns trace flags. Irei descrever a solução que adotei para poder receber notificações por e-mail da minha instância quando ocorresse algum deadlock. O objetivo era descobrir qual a frequência desse evento e depois traçar um plano para descobrir o motivo desse deadlock e, consequentemente, buscar uma solução para evitar ou pelo menos diminuir a incidência desse problema em nosso ambiente. A primeira coisa que precisamos saber seria quando o deadlock ocorre, uma vez com essa informação precisamos pegar as consultas envolvidas no deadlock, para, por fim, enviar um e-mail com essa informação. Na Figura 3 descreve como nossa solução será montada.
Figura 3. Solução deadlock
Criação Alerta A primeira coisa que devemos fazer será criar um alerta na nossa instância para o evento de deadlock. Alertas não servem apenas para erros ou eventos, você pode criar um alerta para ser acionado em resposta a uma condição especifica. Neste caso, você diz que métrica o alerta deve monitorar, define um threshold (valor limite) para o alerta e o comportamento para do contador que irá disparar o alerta. Existem duas maneiras de configurar o SQL Server Agent Alert, uma é usando a interface gráfica e a outra é utilizando a stored procedure sp_add_alert. No nosso acaso um alerta deve ser enviado cada vez
que ocorrer um deadlock e um job será executado. Lembrando que essa feature está disponível somente nas edições Enterprise e Standard do SQL Server. 1. Iniciei o SQL Server Management Studio 2. Selecione a opção SQL Server Agent -> Alerts -> New Alert
Figura 4. Alerta SQL Server
3. Na guia “General “, preencha o campo “name” com o nome que você quer dar para o alerta. 4. No campo “type” selecione a opção “SQL Server performance condition alert”.
Figura 5. Propriedades Alerta SQL Server
5. Na guia “Performance condition alert definition”, especifique a métrica de desempenho que você deseja ser alertado e o threshold, conforme figura 5. 6. Na guia “Response” se marcarmos o check no combo “Execute Job” irá mostrar todos os jobs criados (na próxima seção criaremos o job).
Figura 6. Habilitar Job
Criação JOB O próximo será criar o job que o alerta irá disparar. No JOB iremos fazer a chamada de uma stored procedure que será a responsável por enviar o e-mail contendo o conteúdo do trace flag 1204. Existem dois trace flags que capturam as informações de deadlock e jogam no error log do SQL Server. São eles: 1204 – Fornece as informações sobre os nós envolvidos no deadlock. Cada nó tem uma seção dedicada e uma seção final que descreve o deadlock victim. 1222 – Retorna as informações do deadlock no formato xml. Use o comando abaixo para habilitar o trace flag 1204. Quando ocorrer uma nova situação de deadlock, você receberá uma mensagem de erro no error log. DBCC TRACESTATUS (1204); GO DBCC TRACEON (1204,-1) GO
Uma vez habilitado quando ocorrer um deadlock o seu error log estará mais ou menos assim:
Figura 7. Error Log com trace flag habilitado
Lembre-se que se houver um restart do seu SQL Server este trace flag não estará mais habilitado. Se você precisa que este trace flag esteja ativo após um reboot, failover ou restart por “n” motivos. Existe a opção de “startup parameters” nas propriedades do serviço do SQL Server. Uma vez nesta guia você poderá dizer quais trace flags quer estejam habilitados após o banco de dados estiver ativo.
Figura 8. Inclusão trace flag 1204
Na procedure do alerta iremos fazer um insert na tabela TB_DEADLOCK. Essa tabela será responsável por armazenar todos os eventos de deadlock ocorridos na instância. Criei essa tabela com o objetivo de depois desenvolver um relatório com a média por semana, mês ou ano, relatando a frequência com que ocorrem deadlocks (a criação do relatório não será contemplada neste artigo). CREATE TABLE [dbo].[TB_DEADLOCK]( [ID_DEADLOCK] [int] IDENTITY(1,1) NOT NULL, [DT_DEADLOCK] [datetime] NOT NULL,
[CNTR_VALUE] [bigint] NOT NULL, CONSTRAINT [PK_TB_DEADLOCK] PRIMARY KEY CLUSTERED ( [ID_DEADLOCK] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY] ) ON [PRIMARY] GO
O insert é feito através de um select na dmv sys.dm_os_performance_counters. Esta view de sistema retorna uma linha para cada contador de performance mantido pelo SQL Server [9]. Na Figura 9 podemos ver a consulta realizada:
Figura 9. Consulta dmv sys.dm_os_performance_counters
Uma vez concluído o insert na tabela a procedure ira pegar as informações do deadlock inseridas no error log do SQL Server e irá enviar por e-mail em uma tabela para os usuários definidos na variável @EMAILUSUARIO. Abaixo o código para a criação da procedure: CREATE PROCEDURE [dbo].[USP_MONITORAMENTO_DEADLOCK] AS /* =========================================================================== RESPONSAVEL: CIBELLE CASTRO DATA DE CRIAÇÃO: 24/02/2015 DATA ALTERAÇÃO: 24/02/2016 CONTATO: [email protected] OBJETIVO: PROCEDURE INSERE NA TABELA TB_DEALOCK O CNTR_VALUE E A DATA. DEPOIS DISPARA O EMAIL COM O TEXTO INSERIDO NO ERRORLOG ============================================================================== */ /* 1 - VERIFICAR SE O TRACE ESTA HABILITADO DBCC TRACESTATUS (1204); GO DBCC TRACEON (1204,-1) GO */ BEGIN INSERT INTO dbo.TB_DEADLOCK (DT_DEADLOCK, CNTR_VALUE) SELECT GETDATE(), CNTR_VALUE FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'NUMBER OF DEADLOCKS/SEC' AND INSTANCE_NAME = '_Total' PRINT 'DEADLOCK OCORREU!! INSERE CNTR_VALUE NA TB_DEADLOCK' DECLARE @TMP_ERRORLOG AS TABLE ( ID_TMP_DEADLOCK BIGINT IDENTITY (1,1), LOGDATE DATETIME, PROCESSINFO VARCHAR(100), ERRORTEXT VARCHAR(MAX)
) INSERT INTO @TMP_ERRORLOG EXEC MASTER.DBO.SP_READERRORLOG 0, 1 DECLARE @TMP_DEADLOCK AS TABLE ( ID_TMP_DEADLOCK BIGINT IDENTITY (1,1), LOGDATE DATETIME, PROCESSINFO VARCHAR(100), ERRORTEXT VARCHAR(MAX) ) INSERT INTO @TMP_DEADLOCK SELECT LOGDATE, PROCESSINFO, ERRORTEXT FROM @TMP_ERRORLOG WHERE ID_TMP_DEADLOCK >= ( SELECT MAX(ID_TMP_DEADLOCK) FROM @TMP_ERRORLOG WHERE ERRORTEXT LIKE '%DEADLOCK ENCOUNTERED%' ) AND PROCESSINFO NOT IN ('LOGON', 'BACKUP') AND ERRORTEXT NOT LIKE '%LOGIN%' --SELECT * FROM @TMP_DEADLOCK DECLARE @SERVER AS VARCHAR(100) SET @SERVER = (SELECT 'SERVER\INSTANCIA: '+@@SERVERNAME ) DECLARE @EMAILUSUARIO AS VARCHAR(256) SET @EMAILUSUARIO = '[email protected]' DECLARE @BACKGROUND AS VARCHAR(50) SET @BACKGROUND = '#FFFFF0' DECLARE @MSG AS VARCHAR(MAX) DECLARE @QTROWS AS NUMERIC DECLARE @ROWATUAL AS NUMERIC SELECT @QTROWS = COUNT(*) FROM @TMP_DEADLOCK PRINT '@QTROWS: ' + CAST(@QTROWS AS VARCHAR(10)) SET @ROWATUAL = 1 -- MONTA HEADER DA TABELA SET @MSG = '' SET @MSG = '' SET @MSG = @MSG + '' SET @MSG = @MSG + '<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; CHARSET=UTF-8">' SET @MSG = @MSG + '' SET @MSG = @MSG + '' SET @MSG = @MSG + '' SET @MSG = @MSG + '' --COR CABECALHO SET @MSG = @MSG + '| SERVIDOR | ' SET @MSG = @MSG + 'LOGDATE | ' SET @MSG = @MSG + 'PROCESSINFO | ' SET @MSG = @MSG + 'ERRORTEXT | ' SET @MSG = @MSG + '
' DECLARE @LOGDATE VARCHAR(100), @PROCESSINFO VARCHAR(100), @ERRORTEXT VARCHAR(MAX) PRINT 'LINHA ATUAL : ' + CAST(@ROWATUAL AS VARCHAR(10)) PRINT 'QUANTIDADE DE LINHAS' + CAST(@QTROWS AS VARCHAR(10)) WHILE (@ROWATUAL <= @QTROWS) BEGIN SELECT @LOGDATE = CASE WHEN LOGDATE IS NULL THEN 'NULL' ELSE CAST(LOGDATE AS VARCHAR(100)) END, @PROCESSINFO = CASE WHEN PROCESSINFO IS NULL THEN 'NULL' ELSE CAST(PROCESSINFO AS VARCHAR(100)) END, @ERRORTEXT = CASE WHEN ERRORTEXT IS NULL THEN 'NULL' ELSE CAST(ERRORTEXT AS VARCHAR(MAX)) END FROM @TMP_DEADLOCK WHERE ID_TMP_DEADLOCK = @ROWATUAL; SET @MSG = @MSG + ''
SET @MSG = @MSG + '' + @SERVER + ' | ' SET @MSG = @MSG + '' + @LOGDATE + ' | ' SET @MSG = @MSG + '' + @PROCESSINFO + ' | ' SET @MSG = @MSG + '' + @ERRORTEXT + ' | ' SET @MSG = @MSG + '
' SET @ROWATUAL = @ROWATUAL + 1 PRINT 'LINHA ATUAL' + CAST(@ROWATUAL AS VARCHAR(10)) END -- SET @MSG = @MSG + '
' SELECT @MSG AS MGS IF @QTROWS > 0 BEGIN -- EXEC MSDB.DBO.SP_SEND_DBMAIL @PROFILE_NAME = 'DBMAIL_DBA', @RECIPIENTS = @EMAILUSUARIO, @BODY = @MSG, @BODY_FORMAT = 'HTML', @SUBJECT = N'[Alerta] Relatório de Deadlock' END -- END
O próximo passo será criar o job com a procedure criada. 1. Abra o Management Studio e vá no ícone do SQL Server Agent -> Job. Clique com o botão direito e selecione a opção New Job.
Figura 10. Criação Job
2. Na Guia “General” preencha o campo Name, com o nome que deseja dá para o job. No nosso caso colocarei o nome: JOB_MONITORAMENTO_DEADLOCK. 3. Na guia “Steps” clique no botão “New” e inclua a procedure que criamos anteriormente.
Figura 11. Criação Step
4. Na guia “Scheduler” não será necessário fazer nada, pois o alerta que irá disparar a execução do job quando ocorrer um evento de deadlock. 5. Na aba de “Notifications” selecione o operado que você deseja que receba um e-mail caso o job venha falhar por algum motivo. Para maiores informações de como criar um operador basta lê esse artigo no site do MSDN [10].
Figura 12. Notificação Job
6. Clique no botão “OK” e seu job será criado. 7. Uma vez o job, criado vamos no alerta que criamos e na guia “Response” vamos selecionar o job que ele irá executar.
Figura 13. Alerta dealock monitor
Interpretando o resultado Agora vamos simular um deadlock para visualizarmos se o e-mail irá chegar no momento que ocorrer o evento de deadlock. Para montar o ambiente da nossa simulação devemos baixar o banco de exemplos AdventureWorks2012, o mesmo pode ser adquirido acessando o site do codeplex através do link: http://sql2012kitdb.codeplex.com/releases/view/86805 1. Abra o SSMS e selecione a opção “New Query”. 2. Nessa sessão copie e cole a seguinte consulta: USE AdventureWorks2012 GO BEGIN TRAN UPDATE [Person].[Address] SET AddressLine2 = '5672 Hale Dr.' WHERE AddressID = 26156 WAITFOR DELAY '00:00:10' SELECT * FROM [Person].[Address] WHERE AddressLine2 IS NOT NULL AND City = 'Bellevue'
3. Selecione o botão “New Query” novamente e copie e cole a query abaixo: USE AdventureWorks2012 GO BEGIN TRAN UPDATE [Person].[Address] SET AddressLine1 = '6387 Scenic Avenue' WHERE AddressID = 24852 UPDATE [Person].[Address] SET AddressLine1 = '1254 Scenic Avenue' WHERE AddressID = 26156 WAITFOR DELAY '00:00:10'
4. Execute a consulta no step 2 e logo em seguida execute o step 3. Logo após alguns segundos, a mensagem de erro 1205 é mostrada na sessão que foi escolhida como vítima do SQL Server.
Figura 14. Mensagem de erro 1205
Lembram que anteriormente havia falado que o SQL Server faz o rollback da sessão que usa menos espaço no log? A título de curiosidade executei a query da listagem 1, que traz o valor da coluna database_transaction_log_bytes_used onde contem o número de bytes utilizados no log para a transação. Nesse exemplo a sessão que sofreu o rollback foi a de número 55, conforme figura 15. Veja que o SPID 51 possui 716 bytes,e o SPID 55 têm 700 bytes portanto ela é a transação mais rápida para fazer o rollback. USE AdventureWorks2012 GO SELECT ST.SESSION_ID, DT.DATABASE_TRANSACTION_LOG_RECORD_COUNT, DT.[DATABASE_TRANSACTION_LOG_BYTES_USED], SQLTEXT.TEXT, QP.QUERY_PLAN FROM SYS.DM_TRAN_DATABASE_TRANSACTIONS DT INNER JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS ST ON DT.TRANSACTION_ID = ST.TRANSACTION_ID INNER JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = ST.SESSION_ID LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS ER ON ER.SESSION_ID = ST.SESSION_ID INNER JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ST.SESSION_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT (EC.MOST_RECENT_SQL_HANDLE) SQLTEXT OUTER APPLY SYS.DM_EXEC_QUERY_PLAN (ER.PLAN_HANDLE) QP WHERE DT.DATABASE_ID = DB_ID() ORDER BY DT.DATABASE_TRANSACTION_BEGIN_TIME Listagem 1. Consulta log used
Figura 15. Tamanho log used
Depois que o deadlock ocorreu você irá receber no seu email uma tabelinha igual a imagem abaixo:
Figura 16. Email com relatório de deadlock
Legal!! Estamos recebendo o e-mail com a saida do error log. Porém precisamos interpretá-los para que tomarmos uma decisão do que deverá ser feito para diminuir a incidência desse evento ou sanar o problema de vez. Na listagem 1 mostra os dados gerados após o evento de deadlock: Deadlock encountered .... Printing deadlock information Wait-for graph NULL Node:1 PAGE: 8:1:24117 CleanCnt:2 Mode:IX Flags: 0x3 Grant List 2: Owner:0x00000002741B5F00 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x0000000276C5C738 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 8 Input Buf: Language Event: BEGIN TRAN UPDATE [Person].[Address] SET AddressLine1 = '6387 Scenic Avenue' WHERE AddressID = 24852 UPDATE [Person].[Address] SET AddressLine1 = '1254 Scenic Avenue' WHERE AddressID = 26156 WAITFOR DELAY '00:00:10' -- ROLLBACK TRAN Grant List 3: Requested by: ResType:LockOwner Stype:'OR'X des:0x0000000276C66890 Mode: S SPID:51 BatchID:0 ECID:0 TaskProxy:(0x00000002698EC638) Value:0x65bc0340 Cost:(0/1044) NULL Node:2 KEY: 8:72057594043826176 (a42839bc1d8b) CleanCnt:2 Mode:X Flags: 0x1 Grant List 3: Owner:0x0000000265BBF0C0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:51 ECID:0 XactLockInfo: 0x0000000276C668C8 SPID: 51 ECID: 0 Statement Type: SELECT Line #: 11 Input Buf: Language Event: BEGIN TRAN
UPDATE [Person].[Address] SET AddressLine2 = '5672 Hale Dr.' WHERE AddressID = 26156 WAITFOR DELAY '00:00:10' SELECT * FROM [Person].[Address] WHERE AddressLine2 IS NOT NULL AND City = 'Bellevue' Requested by: ResType: LockOwner Stype:'OR'X des:0x0000000276C5C700 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0000000265DBA638) Value:0x741b5680 Cost:(0/1048) NULL Victim Resource Owner: ResType: LockOwner Stype:'OR'X des:0x0000000276C66890 Mode: S SPID:51 BatchID:0 ECID:0 TaskProxy:(0x00000002698EC638) Value:0x65bc0340 Cost:(0/1044) Listagem 2 . Saída Error Log
Toda essa informação pode ajudá-lo a identificar exatamente como ocorreu o deadlock. Essa informação é um pouco mais difícil de ler do que quando vemos um deadlock graph (é a saída gráfica das informações sobre as sessões e os recursos que foram envolvidos no deadlock), mas o conjunto de informações é muito similar, apenas mais detalhada. Perceba que existem dois nós na saída do error log, o node 1 e node 2, cada nó representa um recurso bloqueado. Podemos observar que os detalhes da situação do deadlock são descritos tais como: SPID: ID do processo que possui o lock. Mode: Refere-se ao modo que o lock estava. S para Shared, X para Exclusive, U para Update, IS, para Intent Shared, IX para Intent Exclusive e assim por diante [8]. Statement Type: Define os tipos de statement. Os valores possíveis são: NOP, SELECT, UPDATE, DELETE, INSERT e UNKNOWN. Input Buf: Define o tipo de evento e o tipo de instrução esta sendo executada. Os valores possíveis são: Language, RPC e None. Este é a consulta que cada processo estava executando quando o deadlock ocorreu. Assim, podemos ver que no node 2 tem um intent exclusive (IX) lock que está sendo mantido pelo SPID 55 em um objeto com o ID 72057594043826176. Ou seja, nós podemos extrair as informações binárias da saída do deadlock. Por exemplo, para sabermos de qual objetos estamos nos referindo, basta consultar as DMV’s sys.objects e sys.partitions. Na opção KEY do segundo nó, você consegue ver o dado com o seguinte formato: DatabaseID:ObjectID em alguns casos ela pode aparecer assim: DatabaseID:ObjectID:IndexID. Para saber qual de que objeto trata-se essa identificação, vamos executar a listagem 2. SELECT O.OBJECT_ID, O.NAME AS TABELA, P.HOBT_ID, P.OBJECT_ID AS OBJECT_ID, P.INDEX_ID AS INDEX_ID FROM SYS.PARTITIONS P INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = P.OBJECT_ID WHERE P.HOBT_ID = 72057594043826176; GO Listagem 3. Consulta sys.objects e sys.partitions
Como resultado podemos visualizar na Figura 17. Nela podemos perceber que o formato apresentado anteriormente de DatabaseID:ObjectID. Esse formato nos traz todas as informações necessárias para sabermos quais objetos estão envolvidos nos bloqueios.
Figura 17. Consulta na chave KEY:8:72057594043826176
Ao final do node 1, podemos ver a opção Requested by, que detalha os pedidos dos recursos que não podem ser concedidos, devido ao bloqueio. Veja que o SPID 51 está esperando por um shared read lock na tabela Address, porém ela está bloqueada por um update lock mantido pelo SPID 55. No node 2, podemos ver que o SPID 55 está esperando adquirir um update lock na página de dados, mas a mesma está bloqueada pelo shared read lock realizado pelo SPID 51. Como podemos ver na figura 17, a primeira sessão segura um exclusive lock (X) em uma linha. A segunda sessão está tentando adquirir um update lock na mesma linha e está sendo bloqueado devido a incompatibilidade de bloqueios. Neste artigo não abordei nada sobre lock compatibility, sugiro que deem uma leitura na documentação do SQL Server ou nos livros de Internals do SQL Server [17].
Figura 17. Incompatibilidade bloqueios
No ode 1 ao invés de mostrar a opção KEY que observamos anteriormente, está mostrando a opção PAG, referente a página de dados. O formato para PAG é: DatabaseID:FileID:PageID.
Figura 17. Consulta PAG:8:1:24117
Utilizando o DBCC PAGE podemos descobrir exatamente qual objeto está envolvido no evento de deadlock. Esse comando é bem explorado pelo Paul Randal nos artigos dele no site do SQLSkills [14] No script abaixo a primeira coisa que fazemos é executar o trace flag 3604 para que a saída do comando do DBCC PAGE possa ser visualizada no SSMS. O DBCC PAGE também possui uma estrutura, tal qual o formato da página que temos no node 1. Agora, para descobrirmos de qual objeto se trata, devemos executar o comando da Listagem 4.-- DATABASEID:FILEID:PAGEID DBCC TRACESTATUS(3604) /* DBCC PAGE ( ['DATABASE NAME'|DATABASE ID], NOME OU ID DO BANCO DE DADOS FILE NUMBER, PAGE NUMBER, NUMERO DA PAGINA PRINT OPTION [0|1|2|3] – OPÇÃO DE VISUALIZAÇÃO; CADA OPÇÃO MOSTRA DIFERENTES NIVEIS DE VISUALIZAÇÃO. ) */ --> PAGE: 8:1:24117 DBCC TRACEON(3604) DBCC PAGE(8,1,24117 ,1) DBCC TRACEOFF(3604) Listagem 4. Consulta DBCC PAGE
A saída do DBCC PAGE é dividida em quatro seções principais: Buffer, Page Header, Data e Offset Table. A seção que nos interessa neste momento é a Page Header, para ser mais especifica a opção “Metadata: ObjectId” e “Metadata: IndexId”. Nestas opções nós conseguiremos obter o nome do objeto e do índice que obtiveram um lock IX.
Figura 19. Resultado DBCC PAGE
Uma vez com essa informação podemos executar as consultas da Listagem 5, para descobrirmos o nome do objeto que está envolvido no evento de deadlock. -- m_objId (AllocUnitId.idObj) = 440 m_indexId (AllocUnitId.idInd) = 256 --Metadata: AllocUnitId = 72057594066763776 --Metadata: PartitionId = 72057594066763776 --Metadata: ObjectId = 373576369 SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = 373576369 SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = 373576369 Listagem 5. Consulta sys.objecs e sys.indexes
Os resultados das consultas podem ser visualizados na Figura 19, onde podemos finalmente concluir que o objeto que estávamos procurando é a tabela Address e o índice não-cluster IX_Address_01.
Figura 20. Nome tabela e nome do índice
No final da listagem 1 podemos ver a opção Victim Resource Owner. Essa opção mostra qual SPID foi escolhido como vítima do deadlock. No nosso exemplo foi o SPID 51. Compreender as consultas que estão sendo executadas e os objetos envolvidos no deadlock é fundamental para solucionar o problema. Uma vez que você analisar a causa dos bloqueios, o próximo passo é determinar as possíveis soluções. Tente otimizar as consultas executadas pelo bloqueio e os processos bloqueados. Fazendo isso você irá reduzir o tempo que os bloqueios são mantidos pelos processos. Tenha em mente que a redução não quer dizer que não haverá mais bloqueios, porém, a execução das consultas estará em um limite aceitável e uma pequena quantidade de bloqueios poderá ser ignorada. Uma outra abordagem que pode ser adotada para resolver problemas de bloqueios pode ser a utilização de um nível de isolamento inferior. Por exemplo, suponhamos que você quer fazer uma leitura de dados na maior tabela da sua base de dados, uma vez que executar o comando select na transação você obterá um shared lock (S) na linha de dados que deseja fazer a pesquisa, se o nível de isolamento fosse reduzido para o read uncommitted o bloqueio requisitado anteriormente não seria mais necessário na instrução select. Porém, como nada vem de graça essa solução possui alguns problemas. Você pode
fazer o que chamamos de dirty reads. Dirty reads é uma leitura “suja”, ou seja, quando lemos dados que ainda não foram confirmados (uncommitted). Isso pode significar que os dados retornados na instrução select está sendo alterado e a sua leitura está sendo comprometida, pois os dados não são consistentes. O read uncommitted é uma forma muito utilizada para reduzir a contenção, mas vem com altos custos em termos de precisão de dados. Precisamos estar cientes dos custos dessa prática antes de habilitarmos nos nossos bancos de dados. Em um ambiente multiuser é importante garantir que as operações não segurem os recursos do banco de dados por um longo tempo. Isso permite que a base de dados suporte um grande número de operações simultaneamente sem uma degradação séria de performance [15]. Algumas bases de dados possuem um grande volume de dados que precisam ser acessados a todo momento, uma forma de minimizar os bloqueios é a utilização de particionamento. O particionamento tem como objetivo facilitar o gerenciamento de grandes tabelas ou índices permitindo o acesso e o gerenciamento de subconjuntos de dados de forma rápida e eficaz [16]. Os dados particionados são divididos horizontalmente, isto é, o particionamento é feito em cima de certos valores, por exemplo, a tabela de alunos de uma escola pode ser particionada pela data da matricula e os dados divididos nos meses do ano. Isso permite que as transações sejam executadas simultaneamente nas partições individuais, sem bloquear um ao outro. Essas partições são tratadas como uma unidade única para consultar, atualizar e inserir. Lembre-se que o particionamento só está disponível nas versões developer e enterprise do SQL Server.
Conclusão O objetivo desse artigo era demonstrar uma forma rápida e fácil de monitorar seu ambiente quando ocorressem deadlocks. Uma vez identificado a frequência que esse evento ocorre você deve definir quais estratégias deverão ser tomadas para solucionar ou minimizar esse tipo de problema no seu servidor de banco de dados. Ressalto que é necessário estudar um pouco mais sobre locks, os modos, a compatibilidade, os níveis de isolamento e etc., para conseguirmos resolver outros tipos de deadlocks no nosso ambiente. Acredito que o artigo deva dá um ponta pé inicial para entendermos um pouco mais sobre o comportamento do SQL Server no nosso ambiente.
Referências Randal, P.; et al. Microsoft SQL Server 2012 Internals pag. 774 Bolton C.; et al. Professional SQL Server 2008 Internals and Troubleshooting. Professional. pag 598 Delaney, K. SQL Server Currency. SQL Handbooks. pag 44 Delaney, K.; et al. Microsoft SQL Server 2012 Internals. Professional. pag 776. Bolton C.; et al. Professional SQL Server 2012 Internals and Troubleshooting pag 168 Fritchey, G. SQL Server 2012 Query Performance Tuning. Apress. 393 p. SQL Server Currency pag 135 Lock Modes, Disponível em: . Acesso em: 24 de fevereiro de 2016. sys.dm_os_performance_counters (Transact-SQL), Disponivel em: . Acesso em: 20 de fevereiro de 2016. Create Operator, Disponível em: . Acesso em: 25 de fevereiro de 2016. Fritchey, G. SQL Server 2012 Query Performance Tuning. Apress. 521 p. SQL Server Deadlock Priority Configuration, Disponível . em: 28 de fevereiro de 2016.
em: Acesso
Korotkevitch, D.Pro SQL Server Internals. Apress. 443 p. Find a table name from a page ID. Disponível em: . Acesso em: 31 de março de 2016. Fritchey, G. SQL Server 2012 Query Performance Tuning. Apress. 388 p. Conceitos de tabelas e índices particionados. Disponível em: https://technet.microsoft.com/ptbr/library/ms190787(v=sql.105).aspx Acesso em : 01 de abril de 2016. Lock Compatibility (Database Engine). Disponível em: < https://technet.microsoft.com/enus/library/ms186396(v=sql.105).aspx>. Acesso em: 02 de abril de 2016.
