Como alterar a collation da minha instância?

Introdução

Durante o meu dia de trabalho, tive a necessidade de alterar a collation de uma instância de SQL Server 2012. A causa era simples: Esta instância seria o DR de uma outra instância com uma collation diferente.

Principais consequências em alterar a collation
Infelizmente, para alterar a collation de uma instância é necessário fazer rebuild da base de dados de sistema “master”. Sim, a MASTER. E isso significa perder muita coisa…. como o os logins e o tracking das BDs montadas na instância (as BDs não são apagadas!).
Como faço para alterar a collation?
Temos duas opções:
  1. O referido rebuild da master.
  2. Reinstalar a instância.
Temos que medir o esforço e o tempo de indisponibilidade das duas opções, para fazer uma boa escolha…Isso varia dependendo da situação.
Aqui irei falar da opção 1 – O rebuild da master.
Basicamente o rebuild é uma tarefa simples. O seguinte comando deverá ser executado:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<ID da instância> /SQLSYSADMINACCOUNTS=<Contas de dominio que serão Sysadmin separadas por virgula> [/SAPWD=<Password para a conta SA, se aplicável>] /SQLCOLLATION=<Nova Collation>

Exemplo:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= DOMAINUser1,DOMAINUser2 /SAPWD=!37gh78ge /SQLCOLLATION=Latin1_General_CI_AS

Alguns factos importantes:

  • O comando deverá ser executado na console do Windows (cmd), na pasta que se encontram os binários de instalação do SQL Server.
  • O parâmetro “/INSTANCENAME” não é o nome da instância, e sim o seu ID.
    • Isso poderá ser descoberto ao executar o seguinte comando:
    • SELECTISNULL(ServerProperty(‘InstanceName’),‘MSSQLSERVER’)InstanceName
  • O serviço deverá estar offline.
  • Depois da execução do comando, o seguinte será mostrado, se o comando for bem sucedido:
    Microsoft (R) SQL Server 2012 11.00.2100.60
    Copyright (c) Microsoft Corporation.
    All rights reserved.
  • No caso de erro, uma mensagem será gerada, assim como um ficheiro com mais detalhes

Sumário

  • Se a instância é nova e o “problema” foi descoberto a tempo, esta tarefa é relativamente simples.
  • Se a instância já estiver bem consolidada, com logins, bases de dados, linked servers, etc… Teremos muito trabalho pela frente.
    • Se este for o seu caso, este link vai ajudar bastante na tarefa de guardar as definições da master.

 

 

Análise de backups

Introdução

Muitas vezes temos problemas com backups, mas na verdade o problema não é a operação de backup em sí ou o SQL Server. O grande vilão dos backups é o espaço reservado para esta operação.

Para um maior sucesso nestas operações, temos que levar em conta alguns factores, como tempo de retenção e o crescimento da base de dados.

Sobre o Script

O script que trago hoje ajuda neste trabalho, trazendo informações historiais sobre o tamanho do ficheiro de backup, o tempo que o backup leva para completar e é ainda o possível estimar o tamanho do backup, caso a opção de backup compression esteja activa.

Este script tem várias utilidades, e uma delas é verificar um padrão de crescimento do backup das bases de dados, o que permite prever as necessidades de disco no futuro. Outra possibilidades é o cálculo do tempo necessário estimado para a janela de backups, além de ser possível verificar se a activação do backup compression pode trazer ou não benefícios, em termos de espaço em disco.


SELECT TOP 30 database_name, 
              CASE
                WHEN type = 'D' THEN 'Full Backup'
                WHEN type = 'L' THEN 'Log Backup'
                WHEN type = 'D' THEN 'Database'
                WHEN type = 'I' THEN 'Differential database'
                WHEN type = 'L' THEN 'Log'
                WHEN type = 'F' THEN ' File or filegroup'
                WHEN type = 'G' THEN 'Differential file'
                WHEN type = 'P' THEN 'Partial'
                WHEN type = 'Q' THEN 'Differential partial'
              END
              AS backup_type, 
              backup_start_date, 
              backup_finish_date, 
              CONVERT(CHAR(5), backup_finish_date - backup_start_date, 108) 
              AS elapsed_time, 
              Cast(( backup_size / 1024 / 1024 ) AS DECIMAL(38, 2)) 
              AS backup_size_in_mb, 
              Cast(( compressed_backup_size / 1024 / 1024 ) AS DECIMAL(38, 2)) 
              AS
              compressed_backup_size_in_mb 
FROM   msdb..backupset 
ORDER  BY database_name, 
          backup_set_id DESC
Para fazer download do script clique aqui.

Sumário

Neste artigo foi passado uma forma de analisar o histórico de backups, de forma a recolher dados importantes para se traçar uma boa estratégia de backups.

Partition Offset e Cluster Size – Como verificar?

Boa tarde!

Várias vezes referi a importância de termos o offset das partições e o tamanho do cluster bem definidos.

Para uma performance óptima, do lado do SQL Server, temos que assegurar que o offset da partição é de 1024 Kb (ou 1048576 Bytes). Já no caso do cluster, o valor a se ter em conta é 64Kb.

O ideal é verificar estes valores antes da instalação do SQL Server, pois o “remédio”é reformatar os disco.

Como verificar estes valores?

Verificar o offset de todas as partições:

Execute na command line o seguinte:
wmic partition get BlockSize, StartingOffset, Name, Index

O output será algo como o seguinte:
BlockSize  Index  Name                   StartingOffset
512        0      Disk #4, Partition #0  1048576
512        0      Disk #5, Partition #0  135266304
512        1      Disk #5, Partition #1  17408
512        0      Disk #3, Partition #0  1048576
512        0      Disk #1, Partition #0  1048576
512        1      Disk #1, Partition #1  105906176
512        2      Disk #1, Partition #2  52428800000
512        0      Disk #2, Partition #0  1048576
512        0      Disk #0, Partition #0  1048576

A coluna StartingOffset devolve os valores que queremos. Para relacionar os discos com uma letra, verifique o valor na coluna “Name”, e compare o número do disco com o valor descrito na tool “Disk Management” do Windows.

Verificar o tamanho da allocation unit:

Execute na command line o seguinte:
fsutil fsinfo ntfsinfo <drive letter>
Exemplo:

fsutil fsinfo ntfsinfo f:

O output será equivalente ao seguinte:
NTFS Volume Serial Number :       0x66ac6ab3ac6a7d85
Version :                         3.1
Number Sectors :                  0x0000000055701fff
Total Clusters :                  0x000000000aae03ff
Free Clusters  :                  0x000000000963a5c8
Total Reserved :                  0×0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0×0000000000140000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0×0000000000000002
Mft Zone Start :                  0x00000000000c0140
Mft Zone End   :                  0x00000000000cc940
RM Identifier:        BE2060B1-AFBC-11DF-9B68-1CC1DE750143

A informação a reter aqui é “Bytes Per Cluster”.  Neste caso o disco está formatado com blocos de 4Kb. O está longe dos 64Kb recomendados.


A verificação destes pontos é muito importante e, acredite, isso influencia na performance do SQL Server, principalmente se o offset estiver mal definido.

Até a próxima!

Dica rápida: SET STATISTICS IO Vs. SET STATISTICS TIME

Boa tarde!!

O fim do mês tem sido sinónimo de muito trabalho ultimamente, então vou deixar uma dica rápida que vai ajudar no trooubleshooting de queries.

Existem duas opções que podem ser activadas antes da execução de um query, e que devolvem informações úteis para quem está focado na melhoria de performance.

As opções são as seguintes:

  • SET STATISTICS IO ON
    • Devolve informação referente a actividade do disco.
    • Exemplo de outputTable ‘ProductCostHistory’. Scan count 1, logical reads 5, physical
      reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • SET STATISTICS TIME ON
    • Devolve informação relativa ao tempo necessário para efectuar o parse, a compilação e executar cada statement.
    • Exemplo de output SQL Server parse and compile time:
      CPU time = 0 ms, elapsed time = 1 ms.
      SQL Server parse and compile time:
      CPU time = 0 ms, elapsed time = 1 ms.

Até a próxima!

Verificação de Integridade – Uma rotina necessária.

Hoje irei abordar o tema da verificação de integridade no SQL Server, ou simplesmente sobre o famoso CheckDB. 
O comando CheckDB é um Database Console Command, ou simplesmente DBCC, categorizado como um DBCC de verificação. Este comando é a única maneira de se ler todas as páginas de uma base de dados, sendo a sua execução muito custosa a nível de CPU, Memória e I/O. Vale ressaltar que no SQL Server Enterprise Edition, este comando é executado em múltiplas threads, o que faz a sua execução ser mais rápida.
Como o CheckDB é executado
Pouca gente tem essa curiosidade, mas o CheckDb não corre directamente na base de dados. É criado um snapshot da base de dados, que fica escondido, e as verificações correm sobre este snapshot.
Normalmente as pessoas só percebem isso quando o CheckDB falha, e ao verem a mensagem, percebem que um tal snapshot não pôde ser criado.
A propósito, no caso de haver um erro como este, temos duas opções:
  1. Criar um processo em que é criado um snapshot manualmente com posterior execução do CheckDB sobre este snapshot.
  2. Executar o CheckDB com a opção TABLOCK. Porém, isso poderá trazer problemas já que a BD será alvo de locks por parte do CHECKDB.
Onde quando executar?
Recomenda-se que o CheckDB seja executa no mínimo uma vez por semana. Este procedimento é necessário em todas as DBs, incluindo as system databases e DBs em read-only.

Alguns factos interessantes:

  • Os planos de manutenção do SQL Server ignoram a TempDB…Porém, se a TempDB for corrompida, a instância irá parar! Atenção a esta situação.
  • Ao se executar o CheckDB na master, estaremos a verificar a base de dados (escondida) Resource.

Qual o objectivo do CheckDB?
O CheckDb tem duas funções, a principal é a detecção de inconsistência, e a segunda a correcção das mesmas. Atenção que esta comando verifica a consistência da estrutura da base de dados, não dos dados.
Detecção de inconsistências
O comando CheckDB segue alguns passos na sua execução, passando pelas seguintes fases:
  • Verificação das estruturas alocadas, como GAM, SGAM, PFS, IAM.
  • Verificação de consistência de tabelas e todos os seus índices, assegurando que as páginas de uma tabelas podem ser lidas e tem uma estrutura válida.
  • Verificação dos catálogos de sistema.
Correcção de inconsistências
Após a sua execução, teremos informação sobre a presença de possíveis páginas corrompidas, o que o   próprio CheckDB pode “corrigir”. 
O CheckDB tem uma opção que permite a correcção da estrutura da base de dados, porém os dados existentes nest página serão perdidos. Esta opção é a “REPAIR_ALLOW_DATA_LOSS“, e basicamente a pagina corrompida é deixada de lado, enquanto uma nova página introduzida no seu lugar. Assim, a estrutura ficará consistente, mas os dados contos nesta página serão perdidos. Após a execução deste comando, uma verificação baseada em backups antigos deverá ser feita, de forma a se identificar os dados perdidos e recupera-los.
Outra opção para correcção de inconsistência é a “REPAIR_REBUILD“, que é utilizada para correcção de nonclustered indexes. Neste caso não há perca de dados.
Apesar de haverem opções para correcção, o CheckDB tem as seguintes limitações:
  • Páginas PFS, tabelas de sistema e erros detectados na utilização da opção data purity não podem ser reparados com o CheckDB.
  • Para execução de um comando de repair, a base de dados deverá estar em modo SINGLE_USER.
Opções de execução
Falando sobre opções, temos as seguintes opções na execução do CheckDB:
  • NO_INFOMSGS: Evita o output the mensagens informativas.
  • ALL_ERRORMSDGS: Permite o output de todas as mensagens de erro.
    • Comportamento padrão desde o SQL Server 2008 SP1.
  • NOINDEX: Não verifica os nonclusterd indexes.
  • DATA_PURITY: Valida os dados conforme o tipo de dados da coluna em que está contido.
    • Comportamento padrão desde o SQL Server 2005.
  • ESTIMATEONLY: Estima o valor a ser utilizado pela TempDB.
    • Segundo o Paul Randal, esta opção não esta funcionando correctamente nas versões actuais.
  • TABLOCK: Utiliza locks ao invés de criar um snapshot.
    • Útil quando há problemas na criação do snapshot.
  • EXTENDED_LOGICAL_CHECKS: validação extra em colunas XML e índices Spatial.
  • PHYSICAL_ONLY: Salta a maioria das verificações lógicas.
    • Utiliza muito menos recursos, mas uma execução “full” deverá ser feita constantemente, por razões obvias.

Outras Considerações
Sendo o CheckDB um processo que utiliza muitos recursos, em alguns casos pode demorar muito tempo para ser executado, é frequente não termos tempo para executa-lo dentro da janela de manutenção. Para dar a volta a este problema, recomenda-se que cada passo (interno) dado pelo CheckDB seja executado de forma isolada. Isso pode ser feito ao se executar os seguintes comandos: 
  • CHECKALLOC
  • CHECKTABLE
  • CHECKCATALOG
  • CHECKFILEGROUP
Executando cada dia um destes comandos, conseguimos garantir uma verificação completa, mas de forma faseada.

Outra boa abordagem é a utilização da opção “WITH CHECKSUM” nos backups, isso nos permitirá a validação dos checksums das páginas de uma DB, além da possibilidade de se efectuar um restore com a opção “VERIFY ONLY”. Este método é útil, mas não substitui o CheckDB.

Bem, eu tento sempre escrever o mínimo possível, mas sai o que sai… Espero que este post tenha sigo útil!! Qualquer coisa, estou disponível!!

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!

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”: