Passo-a-Passo: Instalação de uma instância SQL Server 2012 em cluster – Parte 2

Em continuação do nosso guia de instalação de uma instância de SQL Server 2012 em cluster, vamos agora começar a por as mãos na massa… Começando pelo primeiro nó!

O objectivo deste segundo artigo é mostrar como instalar o primeiro nó de uma instância de SQL Server 2012 e como gerir a mesma de uma forma básica, utilizando a ferramenta Failover Cluster Manager.

Para refrescar a nossa memória, esse á a nossa infra-estrutura:

Screen Shot 2013-12-12 at 12.03.53

Para mais detalhes sobre os pré-requisitos e uma explicação geral, de uma olhada na primeira parte desta série de artigos.

A instalação:  Começando pelo primeiro nó

Vamos lá! Se você ainda se recordar, nós temos dois passos a cumprir. Para já, iremos instalar uma nova instância em um dos nós, o que ira gerar uma nova “Role” no nosso WFC. Iremos trabalhar no nó W2012SRV03.

Seguem os passos:

  1. Com a media do SQL Server disponível, clique em “Setup”, como na image abaixo:Screen Shot 2013-12-09 at 12.38.54
  2. O “SQL Server Installation Center” irá abrir. Clique em “Installation” no menu a esquerda e, de seguida, em “New SQL Server Failover Cluster Installation”:
    Screen Shot 2013-12-09 at 12.39.28
  3. O instalador irá varrer o sistema em busca de algum possível constrangimento que impossibilite a instalação. No fim, clique em Ok.
    Screen Shot 2013-12-09 at 16.22.39
  4. Neste passo novas actualizações serão buscadas. Recomendo que estas sejam incluídas na instalação. Clique em “Next”.
    Screen Shot 2013-12-09 at 16.23.21
  5. Agora os ficheiros de supporte ao setup serão extraídos e instalados. Clique em “Install”.
    Screen Shot 2013-12-09 at 16.25.48
  6. Finalmente temos o setup pronto. Agora mais um check será executado para validar problemas com a instalação do SQL Server. Clique em “Next”.
    Screen Shot 2013-12-09 at 16.26.54
    Boa prática:
    Recomenda-se que exista um recurso MS DTC em cluster, assim como um recurso MS DTC dedicado a cada grupo do SQL Server.
    Segue um bom artigo sobre este tema: http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx

  7. Neste passo, mencione a chave do SQL Server e clique em “Next”.
    Screen Shot 2013-12-09 at 16.29.32
  8. Selecione a check box ao lado de “I accept the license terms” e clique em “Next”.
    Screen Shot 2013-12-09 at 16.30.08
  9. Para instalar uma instância de SQL Server, escolha a opção “SQL Server Feature Installation”. Clique em “Next”.
    Screen Shot 2013-12-09 at 16.31.27
  10. Aqui nós temos algumas opções para instalar features, divididas em dois grupos:  “Instance Features” e “Shared Features”.
    Em “Instance Features” escolha “Database Engine Services”.
    Em “Shared Features” escolha “Management Studio (complete)”. mais um ponto importante aqui é o disco/directoria que as “Shared tools” serão instalados. Uma boa prática é ter um disco local dedicado a estes binários do SQL Server. Neste caso, estarei utilizando o disco de sistema,  pois este é o meu laboratório, mas isso não é uma boa prática para ambientes produtivos! Clique em “Next”.Screen Shot 2013-12-09 at 16.38.20
  11. Neste passo, mais um check tentará identificar possíveis problemas na instalação das features seleccionadas. Aguarde o fim e clique em “Next”.
    Screen Shot 2013-12-09 at 16.39.02
  12. Este é um passo importante. Aqui será definido o Network Name da instância (um dos requisitos ditos no artigo anterior), assim como se iremos utilizar uma instância nomeada ou não e a directoria raiz da instância.
    Pontos a se considerar aqui:
    – Como vemos em “Detected SQL Server instances and Features on this computer” eu já tenho uma outra instância instalada. Então tenho que instalar uma instância nomeada e alterar o Instance ID para evitar conflitos.
    – Baseado na informação acima, para conectar na instância teremos que indicar o seguinte: SQL04DB.
    –  Outro ponto importante aqui é o “Instance root directory”: Como já disse anteriormente recomendo o uso de um disco dedicado aos binários do SQL Server. Evite utilizar o disco de sistema para este fim.
    Preencha todos os pontos e clique em “Next”.
    Screen Shot 2013-12-09 at 16.46.40
  13. Neste passo temos apenas uma verificação de que os nossos requisitos de espaço em disco estão ok. Clique em “Next”.
    Screen Shot 2013-12-09 at 16.47.02
  14. Agora devemos escolhe o nome do Cluster Resource Group a ser criado. O instalador sugere um nome, porém podemos altera-lo.
    Também podemos ver os nomes reservados e já utilizados. Escolha o seu nome preferido e clique em “Next”.Screen Shot 2013-12-09 at 16.47.37
  15. Agora os discos! Mais um dos requisitos… neste ponto nós teremos a informação de todos os discos que fazem parte do cluster, assim como os que estão disponíveis ou não. Para esta demo, criei apenas uma LUN, por conta disso tenho apenas um disco no meu cluster (meu Quorum é uma fileshare :). Seleccione o(s) disco(s) necessários e clique em “Next”.Screen Shot 2013-12-09 at 16.50.23
  16. Você se lembra de que mais um dos requisitos era um IP? Pois é.. chegou a hora de utiliza-lo!! Seleccione a rede desejada (que o IP faz parte), desmarque a opção DHCP e preencha o IP na coluna “Address”.
    E … “Next”!
    Screen Shot 2013-12-09 at 16.56.12
  17. Segue aqui o nosso último requisito: Contas de Serviço. Preencha a informação sobre as contas do SQL Server Engine e Agent (login e password).
    Nota: Para uma instância em cluster, o “Startup Type” deverá ser “Manual”. O Cluster Service irá ser o ponto de gestão dos serviços.
    Clique em “Next”.
    Screen Shot 2013-12-09 at 17.03.20
  18. No mesmo passo, Temos outra tab onde podemos definir a collation do nosso engine. Por padrão temos “Latin1_General_CI_AS”.
    Para mais info sobre collations clique aqui. Esta é uma escolha importante que, se for mal definida, irá gerar uma grande dor de cabeça para ser alterada após a instalação.Screen Shot 2013-12-09 at 17.05.59
  19. Agora precisamos definir o modo de autenticação da nossa instância.
    As opções são “Windows Authentication”, que tirará benefício dos logins da AD e do Windows  , outra opção é “Mixed Mode”, que além dos benefícios da anterior adiciona logins geridos pelo SQL Server (menos seguros). Se você escolher “Mixed Mode” um login chamado “sa”, membro da fixed server role “Sysadmin” será activado. Por esta razão você terá que definir uma password para este login. na caixa abaixo, devemos definir todos os logins (AD e Windows) que terão acesso como Sysadmin a instância.Screen Shot 2013-12-09 at 17.10.25
  20. Agora é hora de definir a estratégia dos nosso discos.
    Este é um passo que merece um artigo por si só…. Estou utilizando apenas um disco neste guia, mas a recomendação é utilizar um disco para casa ponto.
    A regra geral é: Quanto mais espalhado melhor.
    Podemos user o seguinte layout:
    Screen Shot 2013-12-16 at 17.02.52

    Deste layout, o unico que não podemos definir de raiz é o disco para guardar os non-clustered indexes (mais um tema para artigo aqui).
    Pontos chaves deste assunto:

    • isolar a TempDB em um disco rápido. Lembre-se: Apartir do SQL Servr 2012 podemso alocar nossa TempDB em um disco local para instalações em cluster!
    • Coloque os ficheiros de dados e de logs em discos separados.
    • Peça para a equipa de storage discos com o nível de RAID apropriado.
    • Atenção ao offset das partições e tamanho dos blocos, mesmo em instalações superiores a Windows 2008.

    Após considerar todos estes pontos, preencha os campos e clique em “Next”.
    Screen Shot 2013-12-09 at 17.10.25

  21. Aqui você irá definir se enviará os erros para a Microsoft ou não, para melhorias no produto. Clique em “Next”.Screen Shot 2013-12-09 at 17.11.40
  22. Neste passo, será verificado se a instalação poderá ser bloqueada. Após os check, clique em “Next”.Screen Shot 2013-12-09 at 17.12.21
  23. Reveja as opções escolhidas, se estiver de acordo clique em  “Install”.
    Screen Shot 2013-12-09 at 17.12.32
  24. Aguarde pela instalação.
    Screen Shot 2013-12-09 at 17.13.21
  25. Nesta janela teremos a confirmação se as features foram instaladas correctamente ou não.
    Screen Shot 2013-12-09 at 17.20.21

Nesta segunda parte, nós passamos por todos os passos para instalar o primeiro nó de uma instância em Cluster. Obviamente no seu ambiente existirão pequenas diferenças, mas passamos por todos os passos necessários para a instalação do primeiro nó.

O próximo passo agora será a adição dos outros nós a esta instância já criada no WFC.

Para gerir uma instancia em cluster, devemos utilizar a ferramenta Failover Cluster Manaeger.

Utilizando o Failover Cluster Manager
Como já temos um nó da nossa instância em cluster instalado, precisamos agora de gerir seus recursos e o Failover Cluster Manager é a ferramenta que suporta estas acções.

Siga os seguintes passos para abrir o FCM:

  • Clique em “Windows Key”+R.
  • Escreva “cluadmin.msc” e clique em “Ok”.
  • O Windows Failover Cluster será aberto.

Screen Shot 2013-12-17 at 12.39.52

Na imagem acima, podemos ver duas “Roles”, representando duas instâncias em cluster. A Role com nome de “SQL Server (DB)” é a que nós acabamos de instalar.

Seleccione esta Role para ver seus recursos (resources), como IPs, Discos, Serviçoes, etc..

Neste ponto, temos a instância em cluster instalada em um nó apenas, então não temos a opção de fazer um failover para outro nó!

Para parar o serviço do SQL Server, o que irá parar o serviço do Agente que é seu dependente, clique com o botão esquerdo sobre o serviço do SQL Server Engine e siga o demonstrado na imagem:

Screen Shot 2013-12-17 at 12.49.06

Para iniciar a instância, faça o mesmo, mas escolha a seguinte opção:
Screen Shot 2013-12-17 at 12.51.49
Nota: Quando o SQL Server é iniciado, o serviço de Agente deve ser iniciado manualmente. Para reduzir os passos necessários, o mais fácil é iniciar o serviço do SQL Server pelo Agent. Mas porque isso? Como o SQL Server Agent depende do Engine para funcionar, se enviarmos o comando para o Agent ser iniciado, o WFC irá levantar a sua dependência, no caso o SQL Server Engine. Assim em um passo levantamos dois serviços.

Outra questão muito importante são as dependências do SQL Server.

Para verificar as dependencias:

  • Clique com o botão esquerdo no serviço do SQL Server Engine.
  • Clique em Properties.
  • Seleccione a tab “Dependencies”.
Screen Shot 2013-12-17 at 13.11.50
Desta forma podemos ver todos os recursos dos quais o SQL Server engine é dependente. Olhando para imagem, verificamos que se ambos “Cluster Disk 1″ ou “SQL Server network name (SQL04)” falharem, a nossa instância irá efectuar um failover ou parar!!

Todos os recursos de cluster podem ter dependências baseadas em outros recursos. Por exemplo, “SQL Server network name (SQL04)” é dependente do IP. Se o IP falhar, o “SQL Server network name (SQL04)” irá ficar offline. Por sua Vez o SQL Server é dependente do “SQL Server network name (SQL04)”… Assim no final desta cadeia de dependências, o SQL Server irá parar porque o recurso de IP falhou.

O que tirar daqui?
Verifique as dependências e condições, pois uma configuração apropriada poderá aumentar a taxa de disponibilidade da instância.


Um exemplo prático:
Discos de backup são importantes, mas não vitais para o serviço do SQL Server. Porém, se o SQL Server depender do disco de backup e este falhar, o SQL Server irá parar. Isso não faz sentido.
Tirando a dependência do disco de backup, os backups podem falhar, mas o serviço continuará online. Desta forma podemos corrigir o problema do disco sem efeitos colaterais para o cliente final.

Então, de atenção a estas opções!

Próximos passos?
Ao completar este segundo passo, nós já temos uma instância em cluster a funcionar em um nó… Precisamos completar isso!

Irei postar mais duas partes:

  • Adicionar outro nó a uma instalação de uma WFCI
  • Configuração de um MS DTC dedicado a Role do SQL Server.

Se você perdeu o primeiro artigo da série, fica o link:
Passo-a-Passo: Instalação de uma instância SQL Server 2012 em cluster – Parte 1

Até a próxima!

Passo-a-Passo: Instalação de uma instância SQL Server 2012 em cluster – Parte 1

Introdução
Neste artigo irei mostrar como é feita a instalação de uma instância de SQL Server 2012 em cluster. No nosso caso iremos utilizar um cluster com dois nós.

No geral, a instalação e divide em duas partes distintas:

  1. Instalação de uma nova instância em cluster.
  2. Adição dos outros nós a instância já instalada (no ponto 1).

Para um cluster com mais do que dois nós, o segundo ponto será executado em todos os nós, excepto o que foi utilizado para a execução do ponto 1.

O que é uma inst|ancia em cluster?
Basicamente, uma instância em cluster é uma instalação do SQL Server engine e agent baseada no serviço de Windows Failover Cluster (WFC). O motivo principal de um serviço de WFC é proteger nosso sistema de falhas de hardware.

Em um cenário de um cluster com dois nós, estamos a falar de dois servidores, como configurações de hardware similares, ligados por um serviço de WFC. Tendo uma instância de SQL Server instalada sobre esta solução, podemos chamar esta instância de Clustered Instance, ou Instância em Cluster.
Esta instância em cluster, poderá estar activa em apenas um nó, ficando os outros nós IDLE, a espera de um possível failover.

Outro ponto importante é o facto de que o WFC aceita storage partilhado, tipicamente SAN, para guardar os ficheiros das bases de dados. Porém, os binários gerados pela instalação do SQL Server deverão ficar em um disco local, pertencente ao nó em questão.

Além da SAN, podemos também utilizar SMB Fileshares para uma instalação em cluster. Clique aqui para mais informações.

Também apartar do SQL Server 2012, podemos alocar a nossa TempDB em um disco local, o nos garante alguns benefícios.

Baseado nas informações dadas, conseguimos perceber que o WFC é uma solução e alta disponibilidade (HA), e isso não inclui balanceamento de carga (LB) ou recuperação de desastres (DR). Para atingirmos um nível em que as três solução estarão em conjunto, o AlwaysON é uma excelente solução.

Pressuposto
Estarei assumindo que neste ponto nós já temos um cluster montado, com dois ou mais nós.
Normalmente os DBAs recebem o ambiente pronto, criado pelos administradores de sistema. De qualquer forma, após esta série, irei escrever um ártigo de como se criar um cluster de dois nós do zero. Fique ligado!!

Pré-Requisitos
Antes de começarmos, temos que ter os seguintes pontos garantidos:

  • Um hostname virtual. No nosso exemplo usaremos: “SQL04″.
  • Um IP virtual, a.k.a vIP. No nosso exemplo usaremos: 192.168.123.124.
  • Shared Storage disponível. A boa prática é ter, pelo menos, um disco dedicado aos ficheiros de dados, outro para logs e uma para a TempDB. Essa seria uma configuração mínima aceitável para produção. No nosso exemplo irei utilizar um disco para tudo, mas isso é um péssima ideia.
  • Contas de Serviço: 
    • Uma para o SQL Server Engine outra para SQL Server Agent (boa prática). 
    • Usaremos as seguintes contas: SSLABSVCSQLSRVENG e SSLABSVCSQLAGT.
    • Atenção: as contas de serviço são contas de domínio. Não existe outra hipótese, nosso cluster deverá fazer parte de um domínio!

Nosso Ambiente
Neste guia, iremos utilizar o seguinte ambiente, baseado em máquinas virtuais:

  • Dois nós com Windows Server 2012 R2:
    • W2012SRV03 – 192.168.123.205
    • W2012SRV04 – 192.168.123.206
  • Ambos os nós irão fazer parte do seguinte cluster:
    • W2012CLT02 – 192.168.123.111
  • Storage:
    • Como estou utilizando um ambiente de laboratório, A minha SAN é uma Synology Diskstation. Apenas para informação, o IP da SAN é: 192.168.123.103.
  • Para o SQL Server:
    • Virtual Hostname – SQL04
    • vIP – 192.168.123.124
    • Versão do SQL Server: Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64)  -  Enterprise Edition


Permissões para o login utilizado durante a instalação
Para instalar o SQL Server, estarei utilizando um login pertencente ao domínio, SSLABDBA.
Este login tem permissões administrativas em anos os nós do cluster (W2012SRV03 e W2012SRV04). 
o login “SSLABdba” é um simples utilizador do domínio e não tem nenhum privilégio especial no domínio SSLAB.

Próximo post
Fica já marcado para a próxima terça-feira a continuação deste artigo. Passaremos finalmente para o primeiro passo: “Instalação de uma nova instância em cluster”.

Até a próxima!!

SMB File Share e SQL Server: Uma opção a se considerar

Introdução
Quando relacionamos file shares e bases de dados, muitas pessoas torcem o nariz – mas hoje a realidade é outra, e pormos utilizar file shares, em alguns casos, em produção.
A Microsoft passou os últimos anos a trabalhar em melhorias no SMB (Service Message Block), que passou a ser muito mais confiável hoje em dia.

No passado, era possível a utilização de shares para alojamento de ficheiros dados e logs, porém a trace flag 1807 deveria estar activa. De qualquer maneira, isso não era oficialmente suportado, por conta do risco de que erros na rede pudessem danificar a integridade das base de dados, alem de implicações a nível de performance.

Hoje em dia a realidade é diferente. Apartir do SQL Servr 2012 é possível utilizar file shares de forma suportada para alojar as bases de dados de sistema, assim como user databases. Isso tudo graças as melhorias de feitas no SMB durante os últimos anos. E ainda melhor! Esta opção se aplica a instâncias stand-alone e em cluster ! Aliás, hoje em dia é possível se criar um cluster inteiro baseado em file shares (mesmo para o Quorum do WFC).

Em cada versão do Windows, uma versão nova do SMB “vem agarrada”, e no actual Windows Server 2012 R2 nós já estamos na versão 3.02, mas foi na versão 3.0 que as grandes melhorias a nível de performance e funcionalidade vieram.

Segue uma lista com as versões do Windows e SMB e a melhoria mais relevante trazida, no meu ponto de vista:

  • Windows Server 2008 (SMB 2.0)
    Durabilidade, que ajuda na recuperação de falhas ma rede.
  • Windows Server 2008 R2 (SMB 2.1)
    Melhorias de performance significantes, focando em estilos de carga como OLTP.
  • Windows Server 2012 (SMB 3.0)
    Suporte a failover transparente da file share, com zero downtime.
  • Windows Server 2012 R2 (SMB 3.02)
    O MTU vem activo por padrão, o que melhora significativamente a performance de grandes ficheiros sequenciais, a exemplo de data warehouse além de backups ou restores.


Como funciona?

Para começar a utilizar file shares para alojar as suas bases de dados, algumas regras devem ser seguidas. Para começar, as contas de serviço do SQL Server Engine a Agent devem ter FULL CONTROL  e permissões NTFS nas pastas partilhadas.

Outra informação importante é sobre os Universal Naming Convention (UNC) suportados:

  • \ServerNameShareName
  • \ServerNameShareName

Já os seguintes  não são suportados:

  • Não podemos utilizar loopback, ou seja, a share não pode pertencer a nenhum dos nós do cluster.
  • Shares administrativas, como \\servername\x$
  • Outros formatos de UNC, como \\?x:\
  • Network drives mapeados.

O processo de se instalar uma instância utilizando SMB é tão simples quanto indicar a UNC ao invés de um disco mapeado por uma letra, como se mostra na imagem abaixo:


outra opção é executar o comando “CREATE DATABASE” apontando para os ficheiros de dados e log que estão na rede:

Note que para instalações de cluster as file shares não irão fazer parte do grupo do SQL Server, ou seja, não serão controladas pelo cluster.


Atenção!

Ao se utilizar file shares como opção de storage, a performance da rede passa a ser mais do que crítica. Em uma situação normal, nós já temos um bom trafego na rede, mas agora temos um tempero a mais – o acesso aos ficheiros é feito pela rede!

Então o que se fazer? 
Eu tenho duas sugestões:

  1. Criar uma rede dedica para acesso a file share. Desta forma a conexão dos clientes a instância, seus acessos RDP, a transferencias de ficheiros (como SP ou Hot-fixes) não irá interferir com o acesso aos dados.
  2. Monitorar! Como não temos controle do que acontece no servidor de file share, temos que monitorar o máximo possível, desta forma podemos despistar problemas. Podemos monitorizar os seguintes pontos:
    • Contadores do disco.
    • Utilização do CPU.
    • Utilização da memória.

Para finalizar, como podemos aproveitar esta possibilidade?

Antes de qualquer coisa, em um ambiente em cluster,  a utilização da SAN é a melhor opção, assim como em standalone discos locais são preferíveis.

De qualquer forma, o uso do SMB pode ser benéfico em alguma situação específicas:

  • Montar instancias low-cost em cluster.
  • Servidores não produtivos ou de DR.
  • Alojamento de uma Bases de dados leve.
  • Ficheiros de dados com histórico.
  • Storage temporário – para emergências.
  • Migrações.

Em Resumo
Temos hoje mais uma opção para alojar as nonas bases de dados, para infâncias em cluster ou standalone. De qualquer forma, devemos planear com cuidado as nossas estratégias, porque infelizmente, o melhor ainda é moais custoso (€€).
Tudo depende do orçamento, do propósito das bases de dados e suas características.

Espero que o artigo tenha sido útil!

Até a próxima!!

SQL Server Vs. Anti-Virus #sqlserver

Na semana passada, durante a minha sessão nas 24HOP em Português, estive a falar sobre a relação entre o SQL Server e uma solução de AV instalada na mesma máquina. O que não é invulgar de se encontrar, porém, se não for abalizado com cuidado pode gerar problemas!

Porque não utilizar AV junto com o SQL Server?

  • O licenciamento custa €€. 
  • A manutenção custa tempo. 
  • Pode causar problemas. 
  • Não protege de zero-day exploits.

Qual é o grande problema para o SQL Server?

  • Mais uma aplicação concorrendo por recursos. 
  • Os ficheiros do SQL Server podem ser sujeitos a varrimento e mesmo ficar bloqueados.

Qual seria nossa opção?

Basicamente a ideia é criar um perímetro de segurança a volta do SQL Server, fechando assim os caminhos de uma forma que o servidor fique o mais protegido possível. Os seguintes pontos ajudam a atingir este ponto:

  • Manter os servidores actualizados. – Ficamos protegidos de falhas conhecidas.
  • Configurar a firewall correctamente – Abra somente as rotas necessárias para o servidor. 
  • Restringir o acesso ao servidor. – Apenas Sysadmins e DBAs autorizados devem ter acesso. 
  • Podemos instalar o AV… em workstations apenas!

Mas eu gosto de AV! O que posso fazer para manter o SQL Server e o AV juntos?

A solução será adicionar excepções. Segue a lista básica de algumas delas:

  • Ficheiros de dados e log do SQL (.mdf, .ndf e .ldf). 
  • Ficheiros de backup (.bak and .trn). 
  • Ficheiros do Full-text Catalog. 
  • Ficheiros Trace (.trc), XE (.xem, .xel) e Audits (.sqlaudit). 
  • Os ficheiros de ERRORLOG. 
  • A pasta contendo os binários do SQL Server. 
  • A pasta do Filestream.

Mais detalhes em: http://support.microsoft.com/kb/309422

Quick Tip: Orphaned Users #sqlserver

Um Orphaned User… Isso é um problema que praticamente todos os DBAs passam uma vez na sua carreira, geralmente quando são ainda DBAs junior. 

Felizmente, no SQL Server 2012 foram introduzidos os Contained Users, o que ajuda em casos de migração de bases de dados. Mas este é um assunto para um próximo artigo.
Basicamente, existem duas coisas distintas no SQL Server: Logins e Users. 
  1. Login: De uma forma geral: estão definidos a nível da instancia, e podemos chama-los de “ponto de entrada” de um utilizador no SQL Server.
  2. User: Vivem dentro de um ecossistema isolado, denominado “Base de Dados” e, até o SQL Server 2012, tinham que estar obrigatoriamente associados a um login para um utilizador conseguir utiliza-lo. O users tem outras utilidades, mesmo que órfãos, que não entram neste âmbito.
O que acontece é que temos ainda muitas instâncias com, versões até 2008 R2 em funcionamento, ou mesmo 2012 em que não é interessante activar os contained users, em que ao se migrar uma base de dados ou se fazer um refresh de um ambiente surgem problemas com os users. As tentativas de conexão com a base de dados começam a falhar, mesmo com o login bem definido. Isso é causado porque o SID do login na nova instância é diferente do SID do login na instancia antiga, mesmo que com o mesmo nome, password, etc… Por isso os users, dentro das bases de dados passam a ficar órfãos: Um user sem login associado. Para arranjar isso, basta seguir estes passo:
Executando o seguinte comando, dentro da BD em questão, iremos ter um relatório de todos os users órfãos:

sp_change_users_login ‘report’

Para corrigir o problema, execute a mesma SP, mas com os seguinte parâmetros:

sp_change_users_login ‘update_one’, ‘<USER>’, ‘<LOGIN>’

E pronto! tudo funcionará novamente, sem necessidade de se “dropar” users e recriar definido todas as permissões.
Espero ter ajudado!!

O básico sobre Transparent Data Encryption (TDE)

Seguindo o post do Etienne de Como Encriptar as Database Connections, hoje irei passar um complemento a esta solução: Transparent Data Encryption.

A encriptação é o processo de ofuscar dados através do uso de uma chave ou password. Esta técnica permite tornar os dados inúteis sem a correspondente chave/password. Esta abordagem não aumenta o nível de segurança para acesso ao servidor, no entanto, aumenta a segurança limitando a disponibilidade da informação caso o sistema de segurança falhe.
Neste artigo iremos abordar o TDE (Transparent data encryption), que é o modelo de encriptação utilizado pelo SQL Server para cifrar arquivos de dados e logs de uma forma transparente, ou seja, sem a necessidade de alteração de código do software ou dos meios de acesso a instância. 
Como funciona o TDE?
O método de encriptação TDE cifra instantaneamente a informação contida nos arquivos de log e de dados nas operações de I/O. A criptografia é feita baseada em uma “database encryption key” (DEK), que fica registrada na página de boot da base de dados, o que permite a sua disponibilidade durante o recovery do BD.

Mais informações sobre a “boot page”: http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-20-Boot-pages-and-boot-page-corruption.aspx

A DEK é uma chave simétrica protegida por um certificado guardado no “master” ou por uma chave assimétrica protegida pelo módulo EKM.
O TDE protege os dados nos ficheiros de dados e log, o que faz com que sejam cumpridas algumas leis, regulamentos e guias estabelecidos em vários ramos da indústria. Assim, é permitido aos desenviolvedores a utilização dos algorítmos AES e 3DES sem alteração do código do software. 
A encriptação é feita ao nível da página, isto é, as páginas de uma base de dados são cifradas antes de serem escritas no disco e decifradas quando lidas para a memória. 
Algumas considerações:
  • O TDE passou a existir a partir do SQL Server 2008.
  • O TDE não cifra a comunicação (deve-se utilizar a solução proposta no artigo do Etienne).
  • O tamanho da base de dados não irá aumentar com a ativação do TDE.
  • Backups comprimidos terão pouca efetividade.
  • Nenhum filegroup pode estar marcado como read-only, caso contrário o processo de ativação do TDE irá falhar.
  • Os dados FILESTREAM não são cifrados, mesmo quando TDE é habilitado.
  • A tempdb passa a ser cifrada, se houver pelo menos uma base de dados cifrada na instância. Por este motivo, a performance em BDs não encriptados pode ser prejudicada.
  • Logo após a ativação do TDE é regra a execução do backup do certificado e chaves, caso contrário a base de dados poderá ser perdida em uma situação de desastre.
 
Utilizando o TDE
Para ativar o TDE em um banco de dados, deve-se executar o seguinte:
  1. Criar a master key.
  2. Criar ou obter um certificado protegido pela master key.
  3. Criar uma DEK (databse encryption key) e protege-la com o certificado do ponto 2.
  4. Habilitar o uso da criptografia no BD.
Como fazer isso na prática?
Abaixo segue o código passo-a-passo de como ativar o TDE em uma pseudo base de dados “Certtest”. 
Criei uma pasta “temp” no disco “D” com objetivo de guardar os backups, que serão usados na segunda parte do exemplo.
No código acima, além dos passos básicos, foram feitos backups do certificado e chaves, o que garante que não iremos perder a base de dados, caso haja um problema grave com a instância. 
Feito isto, podemos verificar a ativação do TDE através das seguintes queries, que usam DMVs como recurso: 
 
A query deverá retornar duas linhas, com informação sobre o tempdb e Certtest, onde campos de interesse são: key_algorithm (algorítmo usado) e encryptor_thumbprint (hash do certificado que protege a chave). 
 
Restaurar uma base de dados encriptado para outra instância 
Um grande problema dos BDs cifradas é a movimentação entre instâncias, por exemplo, um refresh de desenvolvimento. O que acontece é que se o BD está cifrada, a outra instância deverá ter o mesmo certificado e master key. Aqui temos duas opções:
  • Desativar o TDE do banco antes de fazer a cópia e depois reativa-lo, o que não é recomendado.
  • Restaurar as chaves e certificado na outra instância e manter o TDE ativo
A primeira abordagem não é elegante, mas é funcional. A segunda opção é elegante, mas no caso exemplificado (refresh de produção para uma instancia de desenvolvimento) pode fazer com que elementos da equipe de dev tenham acesso ao certificado e chaves de produção, isso porque muitas vezes os desenvolvedores fazem parte da server role Sysadmin na instância de desenvolvimento. 
Uma forma de dar a volta a este assunto, é restaurar a base de dados e depois desativar o TDE, apagando o certificado e chaves, ou até mesmo usar uma instância intermédia para copiar a base de dados cifrada, desativar o TDE e somente depois fazer o refresh. 
Independentemente do que for feito, segue o código para restaurar o certificado, as chaves e por fim a base de dados com o TDE ativo (levando em conta o primeiro exemplo):
 
Após esta operação, você terá uma cópia da base de dados com o TDE activo em outra instância. 
Espero com este artigo ter atingido os principais pontos do TDE. Obviamente existe uma infinidade de situações que podem aparecer, como erros no restore da BD cifrada, mas é difícil expor tudo aqui. Caso surja alguma dúvida, sugestão ou correção de algo estarei disponível para ajudar ou atualizar o texto.
Scripts utilizados: 
Links e referências:

MS DTC Vs. SQL Server

O serviço MS DTC (Distributed Transaction Coordinator) é um componente das versões “mais recentes” do Windows, que é responsável por coordenar transacções que abrangem vários gestores de recursos, como bases de dados, filas de mensagens e sistemas de ficheiros.

O MS DTC foi incluído a partir do Windows 2000 e também foi disponibilizado para Windows NT 4.0. Este recurso desempenha a função de coordenador de transacções de arquitecturas COM e .NET.

No Windows Server 2008, o componente MS DTC suporta clusters activo/activo em que o recurso MS DTC, em cluster, coordena transacções distribuídas em um grupo do cluster Windows.

Existem duas categorias de clusters para Windows Server: ativo/ativo e activo/passivo.
No activo/passivo, o nó passivo é utilizado apenas quando o activo falha, já o activo/activo todos os nós são usados.

As últimas versões do Windows Server suportam o MS DTC em um cluster activo/passivo. Porém, existem limitações. Por suportar apenas um recurso MS DTC por cluster, alguns pontos negativos passaram a transparecer:

  • Podem ocorrer problemas de isolamento, pois aplicativos diferentes compartilham a mesma DTC para a coordenação.
  • Como apenas um recurso MS DTC serve todo o cluster, se alguma coisa der errado com o cluster DTC, por conta de uma falha aplicacional, todas as aplicações com transacções coordenadas através do cluster MS DTC podem ser afectadas.
  • O desempenho pode ser degradado, por conta da “falta de habilidade” de algumas aplicações em reagir a situações de failover. Por exemplo, quando um cluster MS DTC coordena uma transacção de uma aplicação que não está sendo executada no mesmo nó, pode existir alguma latência na mensagem de coordenação, o que pode gerar um timeout aplicacional.

Desde o Windows Server 2008, já é possível a criação de vários DTCs em um failover cluster, seguindo os seguintes passos:

  1. Criando múltiplos cluster groups no failover cluster.
  2. Criando um recurso MS DTC para cada um destes grupos.

Para se aproveitar ao máximo a potência de processamento do cluster, deve-se criar tantos grupos quantos nós existentes no cluster, e distribuir estes grupos pelos nós disponíveis. Segue-se uma lista de benefícios desta abordagem:

  • A falha de uma aplicação em cluster, não irá necessariamente prejudicar as outras aplicações. No Windows Server 2008 existe a possibilidade de se criar um recurso MS DTC por cada grupo.
  • Não existem limitações de configuração para recursos MS DTC em cluster. Como é possível ter uma instância DTC em cada nó, alguns problemas, como a instalação de aplicações COM+ e sua configuração em cluster foram resolvidos.
  • Como cada grupo pode ter seu próprio recurso MS DTC, as latências antes existentes praticamente deixam de existir, por este motivo é possível que se note uma melhoria de performance aplicacional.
  • E o que é melhor para o SQL Server? A Microsoft sempre recomendou que não houvessem “misturas” no resource group do SQL Server, especialmente os que alteram as dependências. Porem, o sugerido é criar um recurso DTC por cada instância de SQL Server existente no cluster, adicionar este recurso no grupo da sua respectiva instância e apontar o log para um disco disponível no grupo.
  • Não é nem um pouco recomendável que o SQL Server ou o SQL Server Agent sejam dependentes do DTC, pois qualquer alteração a nível do DTC, como alterações de propriedades ou até instalações de hotfixes, irá fazer com que os serviços citados sejam reiniciados. Como esta configuração o SQL Server terá um MS DTC dedicado ajudando em transacções, que possam gerar bottlenecks, entre servidores.

Alguns factos:

  • Se não existirem recursos MS DTC no cluster, o SQL Server irá iniciar a conexão pela instância local do MS DTC.
  • Se existe um recurso MS DTC no cluster, então a primeira instância a ser usada será a padrão, utilizada pelas instâncias SQL Server. A instância MS DTC padrão poderá ser alterada através da console “Component Services Management”.
  • Se existe um MS DTC no grupo do SQL Server, este será usado.

Criando um recurso MS DTC dentro de um grupo SQL Server:

  1. No Failover Cluster Management, clique no respectivo resource group com o botão direito do mouse.
  2. Clique em Add resource->Other resources->Distributed Transaction Coordinator.
  3. Crie as dependencias do recurso MS DTC (network name e disco).
  4. Clique com o botão direito sobre o recurso MS DTC e em seguida clique em “Bring Online”
  5. Código para testar um transação distribuída (deverá retornar a mensagem “Command(s) completed successfully.”):

Begin Distributed transaction;
Commit Transaction;

Deixo aqui um guia com a resolução de alguns problemas relacionados com o MS DTC:
http://www.sqlwebpedia.com/content/msdtc-troubleshooting

Espero que tenha sido útil! Até a próxima!

Clustered Shared Volumes (CSV) #SQL2014

Cara nova no SQL Server 2014, a tecnologia “Clustered Shared Volumes”, ou simplesmente CSV, não é tão nova assim no mundo Microsoft. CSVs eram até então utilizados para facilitar a gestão de máquinas virtuais, facilitando o acesso aos ficheiros VHD através de um volume partilhado e acessível a todos os nós. Suportado desde o Windows 2008 R8 esta possibilidade é agora aceite pelo SQL Server 2014!

Basicamente, os CSV simplificam a gestão de storage mantendo um volume acessível por todos os nós de um cluster, em simultâneo.  Sendo que um nó é o owner do volume – o “Coordinator Node”.
O Coordinator Node tira vantagem do protocolo SMB (Server Message Block) para gerir as operações de I/O entre os nós do cluster sobre o volume. Todas as operações de escrita de meta dados são geridas e passadas pelo Coordinator, já as operações de escrita e leitura de dados são passadas de forma directa ao storage partilhado.

Esta estratégia também aumenta a robustez de um cluster, pois é aberto mais um caminho de acesso alternativo aos dados contidos no volume. Este caminho é utilizado caso haja uma falha, que por sua vez pode ser detectada através do sistema de detecção e recuperação de falhas de I/O. O cluster irá escolher o melhor caminho para efectuar as operações de I/O, escolhendo a rede com o melhor tempo de resposta – o que também pode ser definido manualmente.

O CSV é um “NTFS reparse point”, assim como um mountpoint, e é acessível através da path %SystemDrive%ClusterStorage, ou seja, não é montado como um disco – o que tem suas vantagens e desvantagens. Podemos verificar, na imagem seguinte, dois CSV montados.

O caminho de acesso ao volume será o mesmo em todos os nós do cluster e estarão, em condições normais, sempre activos e acessíveis para leitura e escrita – Obviamente respeitando as permissões de acesso aos ficheiros. 
Como o volume já está montado e acessível em todos os nós, o failover de uma instância de SQL Server terá o seu tempo muito reduzido, pois não será mais preciso “desmontar” o volume de um nó e “montar” no futuro nó activo. Assim sendo, apenas os serviços do SQL Server irão ficar offline em um nó e online em outro nó. Se juntarmos a esta festa um disco local para a TempDB, teremos um startup bem eficiente.
Como criar um CSV?
Não há mistério! Uma vez tendo discos disponíveis no cluster, basta clicar “sobre” o disco desejado, com o botão direito, e aceder as opções associadas a este disco, assim sendo ao seleccionar a opção “Add to Cluster Shared Volumes” o trabalho estará feito. Simples assim, como mostra a seguinte imagem.

Na conclusão do processo, podemos verificar que a coluna “Assigned To” irá apresenta o texto “Clustered Shared Volume”, ao invés de uma “Role” específica. O disco estará acessível por todos os nós.

No SQL Server, o CSV desejado deverá ser especificado durante a instalação. Estamos ainda no CTP1 do SQL Server 2014, e pelo menos na minha instalação tive alguns comportamentos estranhos, que também podem ser questões de permissões no Windows.

Seguem alguns pontos a estudar:

  • Adicionando outro CSV ao cluster, não consigo adicionar ao SQL Server.
  • Pela GUI do SQL Server (SSMS), não consigo navegar até a root do CSV. 
  • Consigo criar BDs no CSV, porém, tenho que inserir a path manualmente (nada de seleccionar com a interface). Outra opção é por script.
  • Ainda não fiquei esclarecido se um CSV pode ou não ser utilizado por mais do que uma instância, já que o mesmo não depende de uma role.
  • Seria possível escolher outro disco senão o C: para servir de base?

Como disse, esta é a CTP1 e é óbvio que isso tudo será resolvido. Irei continuar sobre o assunto de forma a esclarecer estes pontos e testar os limites do CSV no SQL Server.

Espero que o post seja interessante, e se você já tem alguma experiência sobre o assunto, a comunidade agradece o seu comentário neste post.

Até a próxima (no PASS Summit 2013!!!)

Bases de Dados de Sistema – Resource (Final)

Pois e, chegamos ao último artigo sobre está séria de DBs de sistema.
O alvo da vez é a misteriosa DB, chamada RESOURCE. O que? Que DB é esta? Nunca vi isso!
É verdade, esta DB é invisível, e muita gente nem sabe que ela existe.

Provas de sua existência.
Pesquisadores Tailandeses foram capazes de encontrar vestígios da existência desta DB no sistema. Basicamente, é possível encontrar os ficheiros mssqlsystemresource.mdf e mssqlsystemresource.ldf na pasta de instalação de sua instância. (<drive>:Program FilesMicrosoft SQL ServerMSSQL11.<instance_name>MSSQLBinn)

Os ficheiros da Resource devem estar na mesma pasta que os ficheiros da Master, caso contrário, a instância não irá funcionar.

Mas para que serve esta DB?
A Resource é a casa de todos os objectos de sistema da instância, e é apenas utilizada para leitura (é mesmo Read-only).
Todos os objectos que pertencem ao schema “sys” estão, na verdade, guardados nesta DB, apesar de parecer que estão replicados em todas as DBs.

Certo, mas qual a razão desta DB não ser visível?
Em versões anteriores do SQL Server, ao ser feito um upgrade da instância todos os objectos de sistema deveriam ser “dropados” e recriados. Com esta “nova” estratégia, o processo de upgrade é mais fácil e rápido, sendo feita a cópia de uma nova Resource para o sistema, sobrepondo a antiga DB.

Como explorar o conteúdo desta DB?
O mesmos pesquisadores Tailandeses que descobriram vestígios da existência da Resource desenvolveram uma “técnica ninja”, em parceira com DBAs Japoneses, para explorar o conteúdo da Resource. Antes de explicar esta técnica, devo referir que se o seu objectivo é ver o conteúdo de algum objecto de sistema, o modo mais fácil é executar a seguinte query:

SELECT OBJECT_DEFINITION(OBJECT_ID(‘<Nome do objecto, por ex: sys.databases>’));
GO

Segue a forma de se aceder a Resource:

*** ATENÇÃO: Estes passos devem ser feitos apenas para fins didácticos. Não é boa ideia brincar com servidores produtivos. ***

Na prática, iremos montar uma cópia da Resource, seguindo os seguintes passos:

  1. Para o serviço do SQL Server
  2. Faça uma cópia dos ficheiros mssqlsystemresource.mdf e mssqlsystemresource.ldf, que estão em “<drive>:Program FilesMicrosoft SQL ServerMSSQL11.<instance_name>MSSQLBinn”.
  3. A cópia pode ser para a mesma pasta, com outro nome.
  4. Inicie o serviço do SQL Server
  5. Faça “attach” dos ficheiros que foram copiados no passo 2:
  6. sp_attach_db ‘Resource_test’,’e:Rscmssqlsystemresource.mdf’,’e:Rscmssqlsystemresource.ldf’ (Substitua os paths!)

  7. Brinque até não aguentar mais :)

Existe uma outra abordagem, mais simples:

  1. Pare o serviço do SQL Server
  2. Inicie o serviço em “single user mode”
  3. Abra uma nova query e escreva:
  4. USE mssqlsystemresource
    go

  5. O acesso está garantido! Faça as queries que quiser (e for permitido :)

Com este pequeno artigo, finalizo esta série. Espero ter ajudado profissionais que tinham algumas dúvidas sobre as system databases e futuros DBAs com alguma informação nova!

Até a próxima!

Outros posts da série “Bases de Dados de Sistema”: