Gerando o script de CREATE LOGIN com os SIDs

Atenção

Migramos este artigo para o novo site:

http://www.sqlfromhell.com/usuarios-orfaos-gerando-o-script-de-create-login-com-os-sids/

 
 
 
 
 

Boas práticas para adoção do Policy Based-Management

De que se trata o artigo:

Neste artigo veremos que com um planejamento orientado por algumas perguntas e checklist é possível adotar soluções com Policy Based-Management respondendo necessidades do cenário, de forma que esta não se torne uma “feature órfã”, por permitir melhorias e acompanhar a evolução e mudanças do cenário.

Para que serve:

O planejamento na adoção do Policy Based-Management permite adequar o cenário às normas organização e às boas práticas de disponibilidade, capacidade, continuidade e segurança.

Em que situação o tema útil:

Mesmo que o Policy Based-Management seja uma feature que demande de pouca complexidade, o seu planejamento permitirá melhor entendimento do cenário, de forma que soluções com esta feature atendam adequadamente contratos de SLA, as normas organização e boas práticas de disponibilidade, capacidade, continuidade e segurança, acompanhando até mesmo a evolução do cenário.

Introdução

O Policy Based-Management (PBM) é uma feature introduzida no SQL Server a partir da versão 2008, a qual permite os administradores de banco de dados obterem o controle dos seus ambientes com a aplicação de regras e padrões sobre uma ou várias instâncias do SQL Server, além da possibilidade de reutilizar estas regras e padrões em outros cenários e versões anteriores do SQL Server (2000/2005).

Mas antes de implementar qualquer solução com esta feature, é necessário planejamento e entendimento do cenário, no qual, respostas a perguntas simples “por que, o que, como” poderão ajudar numa adequada implementação desta feature, de forma que as soluções implementadas não venham proporcionar qualquer resultado positivo ao cenário.

Este artigo vem a oferecer um conjunto de boas práticas tanto para o planejamento e adoção do PBM, como para o desenvolvimento de soluções e monitoramento das mesmas, auxiliando a responder as perguntas “por que, o que, como” desta feature.

Para um melhor entendimento, os termos disponibilidade, capacidade, continuidade e segurança utilizados neste artigo seguem algumas releituras do ITIL (Information Technology Infrastructure Library, http://en.wikipedia.org/wiki/ITIL):

Disponibilidade	Disponibilidade é determinada pela confiabilidade, sustentabilidade, funcionalidade do serviço, desempenho e segurança. Normalmente calculada a partir da medição de transações de negócio geradas por um serviço de TI e a indisponibilidade deste serviço.
Capacidade	O máximo de rendimento que um serviço consegue fornecer. Pode ser calculado a partir de medição de um tamanho ou volume (disco), ou esforço de trabalho que o serviço se limita.
Continuidade	Avaliação dos riscos que podem impactar o serviço. Termo normalmente associado com manutenção.
Segurança	Garantia da confidencialidade, integridade e disponibilidade dos dados e informações.

Estes termos se traduzem como os objetivos da administração de banco de dados, assim como de qualquer serviço da área de TI.

No que se refere à estrutura deste artigo, ela se aproxima a algumas de práticas de resolução de problemas, como o PDCA (Plan-Do-Check-Act), adaptadas para adoção de novas soluções, onde o ciclo planejar, implementar e acompanhar (avaliar e analisar), necessitou somente de uma ênfase na estratégica por traz do planejamento, e na segurança, por questões específicas do PBM.

Por quê? Estratégia

As justificativas para adoção de qualquer feature têm que responder muitos porquês, que podem simplesmente evitar que seja implantada qualquer solução, mas respondendo estes porquês, será possível entender a importância desta feature.

Um das justificativas comuns para adoção do PBM é a importância de adequar o cenário às normas organização ou projeto e boas práticas de disponibilidade, capacidade, continuidade e segurança. Lembrando que todos os cenários existem normas, mesmo quando estas não estão documentadas, elas ainda se fazem presentes.

Também é possível encontrar a organização que se baseiam suas normas em contratos de SLA (Service Level Agreement ou Acordo de nível de serviço, http://en.wikipedia.org/wiki/Service_level_agreement), exigindo um mínimo de requisitos (disponibilidade, capacidade, continuidade e segurança) dos serviços prestados aos seus clientes, e por consequência, exigindo dos administradores de bancos de dados, o uso do PBM e outras ferramentas para monitorar várias instâncias do SQL Server e seus bancos de dados.

Quando a adoção do PBM não é justificada por normas, mudanças e problemas da organização, é interessante pensar se boas práticas de disponibilidade, capacidade, continuidade e segurança recomendadas pelo fornecedor, no caso a Microsoft, estão presentes nas normas do ambiente, pois uma simples adoção do PBM pode ser justificado por perguntas simples como:

• O último backup é recente?
• Existe espaço de disco para suficiente para os bancos de dados?

Ou até algumas perguntas um pouco mais complexas:

• Existe alguma configuração que pode estar comprometendo com o desempenho ou segurança do banco de dados e do servidor? Pelo menos, tem certeza que não existe nenhum login configurado de forma a permitir uma senha fraca?
• Existe alguma feature deprecated configurada? Para uma relação destas, é interessante acompanhar: http://msdn.microsoft.com/en-us/library/cc280407.aspx
• Existe alguma stored procedures criadas por usuários cuja nomenclatura ofereça problemas de desempenho? Sim, isso pode ocorrer com stored procedures de nomenclatura sp_*, pois o SQL Server procurará stored procedures com esta nomenclatura primeiramente no banco de dados master e depois no banco de dados na qual elas são executadas, até mesmo podem surgir alguns erros inesperados ou algumas stored procedures não serem encontradas quando executadas.

Estas perguntas podem ajudar a identificar algumas boas práticas, mas se estas boas práticas ainda estão claras, é possível encontrar no site do TechNet e MSDN, uma série de boas práticas, sendo algumas genéricas (segurança, backup…) e outras para cenários específicos (virtualização, BI…), que podem ajudar a identificar quais são úteis para cada cenário. Sempre lembrando que as organizações podem adotar práticas que até mesmo violam ou ignoram as boas práticas e recomendações pelo fornecedor, no caso a Microsoft.

Boas práticas, TechNet:
http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

Boas práticas, MSDN:
http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

Na pasta C:\Program Files\Microsoft SQL Server\110\Tools\Policies (em ambientes x64, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies) existem uma série de arquivos XML que podem ser importados pelo PBM em forma de regras, com a finalidade de validar boas práticas para administração do SQL Server. Uma breve descrição de cada um destes arquivos é possível de ser encontrada no seguinte link:
http://msdn.microsoft.com/en-us/library/cc645723.aspx

O que? Planejamento

Ao se pensar no que será feito, é importante entender o que a feature pode oferecer e por outro lado, avaliar se não existe outra solução que responda de forma mais adequada as necessidades do cenário. Esta avaliação é importante, visto que uma feature como o Policy Based-Management ser passível de ser substituída por consultas, relatórios, jobs e triggers, outras ferramentas ou até mesmo solução de terceiros, mas o tempo, custo, flexibilidade e complexidade de desenvolver uma solução do “zero” ou adotar uma solução de terceiros são pontos a serem levados em consideração, já que muitos dos potenciais desta feature esta na praticidade de adotá-la.

Mas o que esta feature pode fazer? Basicamente, o PBM proporciona soluções de automatização com a finalidade de diminuir o tempo que o administrador de banco de dados deveria se dedicar à manutenção, que pode se estender às regras de configuração no nível de servidores até objetos do banco de dados, como tabelas e índices. O PBM oferece algumas soluções para gerenciar as regras implementadas pelo administrador de banco de dados, que ser monitoradas por meio de agendamento ou por demanda (executada pelo SQL Server Management Studio), gerar alertas ou até mesmo prevenir que as regras sejam violadas por algum usuário.

As regras do PBM são compostas por três estruturas básicas:

• Target Types, estruturas que representam os tipos de objetos manipulados pelo PBM, exemplo: Tables, Views e Databases.
• Management Facets, estruturas que representam grupos de propriedades de determinados Target Types, como por exemplo, Name, Table Options, Database Option e Surface Area, que são utilizadas para criar condições/filtros.
• Condition ou Condições/Filtros, estruturas que representam expressões lógicas por meio das Facets, a fim de aplicar filtros e validações.

As possibilidades de criar regras no PBM não estão limitadas ás suas facets, visto que há a possibilidade de customizações com consultas T-SQL e WMI. Mas é importante levar em consideração que estas customizações demandam de bom senso dos envolvidos, para evitar que sejam desenvolvidas soluções muito complexas ou que interfiram na disponibilidade e segurança do servidor.

No blog oficial de PBM, é possível obter uma relação das facets e os target types que elas manipulam:
http://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

A partir do momento que se entende o potencial da feature, é possível definir um checklist para determinar a solução a ser desenvolvida:

1. Justificativa: Existem normas ou boas práticas a serem adotadas em relação à disponibilidade, capacidade, continuidade e segurança do SQL Server?
2. Extensão: Qual a extensão destas normas e boas práticas (Servidores, Instâncias, Bancos de dados, Tabelas…)?
3. Ferramenta: Existem facets que atendem estas normas ou boas práticas, ou será necessária customização (comandos T-SQL ou WMI)?
4. Alternativa: Se for necessária uma customização, não existe uma alternativa (uma feature já presente no SQL Server ou feature de terceiros) que não exija esta customização? A atenção a este ponto pode evitar arrependimentos futuros.
5. Correção: O que deverá ser feito quando for encontrada uma situação que entre em desacordo com as regras estabelecidas? Em um segundo passo, defina os responsáveis e o prazo para as ações corretivas.
6. Exceções: Existem exceções? Quais? Cuidado, estas exceções podem se tornar a regra, então se a norma deve ser obedecida, exceções devem ser as mínimas possíveis e bem definidas (por que ela existe e quais os critérios para identificá-la?).

Para entender melhor como responder este checklist, segue abaixo alguns exemplos:

Cenário A:

1. Justificativa	Existe uma norma do projeto Icaro, que determina que todos os objetos do banco de dados utilizem a nomenclatura CamelCase (http://en.wikipedia.org/wiki/CamelCase). Para que esta seja respeitada, os novos objetos somente serão criados obedecendo a nomenclatura CamelCase, e os objetos que ainda não obedecem esta nomenclatura serão identificados pelo PBM a fim de serem corrigidos.
2. Extensão	Todos os objetos do banco de dados
3. Ferramenta	Esta norma está relacionada às facet “Multipart Name” e “Name”, mas será necessário criar uma função para testar se esta nomenclatura é utilizada, para tratar esta norma.
4. Alternativa	É possível criar uma consulta sobre os objetos do banco de dados, mas ainda será necessário criar uma função para testar a nomenclatura e a consulta não permitirá a mesma praticidade do PBM de tratar este problema.
5. Correção	Os objetos que estiverem fora do padrão serão relatados à equipe de desenvolvimento para ajustes.
6. Exceções	Alguns objetos não estão no padrão estabelecido por serem de software de terceiros. Uma relação dos objetos que serão mantidos fora do padrão será armazenada em uma tabela do banco de dados, com a finalidade de desconsidera-las nas regras.

Cenário B:

1. Justificativa	Para que seja utilizada a solução de Log Shipping nos bancos de dados do cliente X, é necessário que os bancos de dados deste cliente estejam configurados como “Recovery Mode” diferente de “Simple”. Desta forma, o PBM será utilizado para identificar os bancos de dados que estejam configurados inadequadamente.
2. Extensão	Os bancos de dados das instâncias X\SQL2008 e X\SQL2008R2
3. Ferramenta	Esta definição esta associada à facet “Database”, propriedade “RecoveryMode”, não sendo necessária customização.
4. Alternativa	É possível criar manualmente esta validação, mas demandará mais tempo.
5. Correção	Encontrar um banco de dados fora do padrão, ajustar imediatamente o “RecoveryMode” para “Bulk-logged”.
6. Exceções	O banco de dados BRCEP é somente leitura e não precisará ser replicado. Uma relação dos objetos que serão mantidos fora do padrão será armazenada em uma tabela do banco de dados, com a finalidade de desconsidera-las nas regras.

Nos cenários apresentados, todos apresentam possibilidades simples de adotar o PBM, e uma das vantagens permitidas por esta feature, é capacidade reutilizar estas regras em outros ambientes quando se fizer necessário, visto que elas podem ser exportadas e importadas por meio de arquivos XML.

Como? Implementação

Na criação de qualquer estrutura no PBM, não pode deixar de lado questões de nomenclatura e uma boa descrição das condições e regras criadas. Cenários robustos podem favorecer até mesmo na criação de regras que validam a nomenclatura e a existência de descrição das regras e condições do PBM, assim como versionamento destas regras.

Infelizmente, não existe uma solução de versionamento nativa para o PBM, mas é possível utilizar SharePoint Server, Visual Studio Team Foundation Server ou outra solução de SVN para manter um histórico dos arquivos XML das regras desenvolvidas.

Quando se pensar nas regras além de uma boa nomenclatura e descrição, é recomendado que estas sejam devidamente categorizadas, para o entendimento do que estas regras estão monitorando (servidor, banco de dados, tabelas, usuários…), saber quem será responsável por estas regras e onde/quando elas são aplicadas (verificações periódicas ou em tempo real, servidores/bancos de dados de clientes, ambientes de produção, de homologação ou de desenvolvimento).

A tabela abaixo, obtida de forma resumida do blog oficial de PBM, é possível identificar as facets que podem ser utilizadas de forma preventiva (de forma a evitar que alguma ação que burle a regra seja executada) ou que sejam “logáveis” quando sofrem alterações:

Facets	Preventiva	Log
Application Role	X	X
Asymmetric Key	X	X
Database Option		X
Database Role	X	X
Endpoint	X	X
Login Options	X	X
Multipart Name	X	X
Resource Pool	X	X
Schema	X	X
Server Configuration		X
Stored Procedure	X	X
Surface Area		X
Table Options	X	X
User Defined Function	X	X
User Options	X	X
View Options	X	X
Workload Group	X	X

A partir dos cenários do tópico anterior e uma análise de como será implantado, é possível detalhar a implementação desta solução a partir de um checklist simples, como demonstrado abaixo:

Cenário A:

Categoria	ProjetoIcaro
Ambientes	Servidor de desenvolvimento
Regras	1: NomenclaraCamelCase1 2: NomenclaraCamelCase2
Descrição da regra	1: Validação preventiva da norma do projeto que determina que todos os objetos do banco de dados utilizem a nomenclatura CamelCase (http://en.wikipedia.org/wiki/CamelCase). 2: Validação periódica (1 vez por semana) da norma do projeto que determina que todos os objetos do banco de dados utilizem a nomenclatura CamelCase (http://en.wikipedia.org/wiki/CamelCase).
Responsáveis	Responsável pela criação e manutenção das regras: João da Silva, DBA da organização. Responsável por orientar as correções e informar exceções das regras: José da Silva, Analista do projeto.
Eventualidade	1: Verificação executada de forma preventiva, por meio da facet “Multipart Name”. 2: Verificação executada de forma periódica, por meio da facet “Name”.
Descrição da implementação	Criada a função dbo.ValidaCamelCase, que permite validar qualquer texto de acordo com a nomenclatura CamelCase. 1: Utilizada a facet Multipart Name, permite execução preventiva, mas possui uma abrangência limitada (Sequence, StoredProcedure, Synonym, Table, UserDefinedFunction, UserDefinedType, View, XmlSchemaCollection). 2: Criada para encontrar objetos fora do padrão já existentes e de forma mais abrangente. Utilizada a facet Name, não permite execução preventiva, mas possui uma abrangência maior que a facet Multipart Name (ApplicationRole, AsymmetricKey, Certificate, DatabaseRole, Default, Index, Rule, Schema, Sequence, SqlAssembly, StoredProcedure, SymmetricKey, Synonym, Table, Trigger, User, UserDefinedFunction, UserDefinedType, View, XmlSchemaCollection).

Cenário B:

Categoria	Manutencao
Ambientes	Servidor de produção
Regras	RequisitosLogShipping
Descrição da regra	Para que seja utilizada a solução de Log Shipping no SQL Server do cliente X, é necessário que os bancos de dados deste cliente estejam configurados como “Recovery Mode” diferente de “Simple”.
Responsáveis	Criação e manutenção das regras, e ajustes que no servidor: José Lins, DBA do cliente X.
Eventualidade	Verificação periódica (1 vez do dia).
Descrição da implementação	Utilizada a facet “Database Option’, para que se necessário seja adaptada para “logar” as alterações.

Segurança

Após a implementação do PBM, algumas questões de segurança devem ser analisadas, visto que com um usuário com acesso indevido para criar/modificar regras e condições do PBM, é possível desde adulterar e burlar regras ou até mesmo criar regras e condições nocivas ao ambiente, dada a possibilidade executar comandos T-SQL por meio do PBM.

Assim, não permita que usuários indevidos se tornem membros da Database Role PolicyAdministratorRole do banco de dados msdb (role que permite a criação e edição de regras e condições do PBM), ou que sejam dadas permissões/senhas indevidas aos logins do PBM (##MS_PolicyEventProcessingLogin##, ##MS_PolicyTsqlExecutionLogin##).

Caso seja necessário executar alguma consulta que o PBM não tenha permissão, dê somente as permissões que se fizerem necessárias ao login ##MS_PolicyTsqlExecutionLogin##, que é o responsável pela execução de consultas T-SQL do PBM.

Acompanhamento

Depois de implantado o PBM, adequadas as questões de segurança, criadas as soluções de regras e condições de acordo com o que foi planejado, estabeleça com os responsáveis pelo ambiente, uma agenda para avaliar os resultados obtidos e identificar/analisar melhorias. Para orientar esta agenda, busque responder algumas perguntas, como:

• Esta solução realmente tem atendido adequadamente o que foi estabelecido e com o cenário atual? Caso não esteja atendendo adequadamente, verifique se a feature realmente respondeu as justificativas de sua implantação, pois o escopo da solução é facilmente esquecido com o tempo.
• A implementação desta feature trouxe algum problema para o cenário? Tanto processualmente e tecnicamente, a implementação do PBM pode criar problemas, como a geração de conflito com a cultura da organização ou complexidade da solução criar gargalos de desempenho ou conflitos com outras soluções.
• As regras e condições são seguidas corretamente? Caso não tenha sido acompanhado este ponto pelo responsável, é possível verificar pelo próprio PBM, alguma views ou até mesmo algumas ferramentas de terceiros (EPM Framework) se as regras e condições implementadas são seguidas corretamente e a evolução destas em determinado período. Se as regras e condições não são seguidas ou não houve correções no cenário, verifique se elas realmente estão de acordo com o cenário atual e se o processo de correção pode ser realizado de acordo com o planejado.
• Existem exceções se tornado regra? Exceções, como informado anteriormente, podem se tornar a regra, então se a norma deve ser obedecida, exceções devem ser as mínimas possíveis.
• Alguma melhoria para o cenário atual? Melhorias também devem responder as mesmas perguntas que definem a implantação da feature (“por que, o que, como”), pois adaptações mal planejadas podem tornar o cenário demasiadamente complexo em comparação ao retorno obtido e esperado, além de gerar problemas técnicos e/ou processuais em muitos casos.
• Alguma melhoria para o cenário futuro? Ao se pensar no cenário futuro, esteja atento ás estratégias da organização ou projeto, pois a escalabilidade na área de TI tem tendências mais reativas do que proativas quando se trata de estratégia, desta forma, metas da organização devem estar ao lado de métricas e metas de TI. Exemplo disso, uma organização que planeja triplicar o número de clientes em um ano, precisa que sua infraestrutura esteva preparada a este mesmo crescimento, como, servidores suportando o triplo dos dados, backups e transações que os atuais.
• Existe alguma solução ou mudança que possa justificar a substituição desta feature? Features também sofrem depreciação, como por exemplo, a feature utilizada na sua solução pode não estar presentes nas próximas versões do SQL Server ou podem surgir features que atendam de forma mais adequada o cenário. Procure deixar de lado um pouco do paternalismo para com as soluções que você e sua equipe implantou, para que se necessário sejam adotadas outras soluções que venha a substituir parcialmente ou completamente a feature atual.

Uma ferramenta muito útil para acompanhar a evolução do PBM é o EPM Framework, disponível gratuitamente no CodePlex (http://epmframework.codeplex.com), que permite relatórios bem úteis do dados históricos e correntes das regras do PBM:

Também é possível criar sua própria solução para acompanhar o PBM, utilizando as views do PBM (http://technet.microsoft.com/en-us/library/bb510742.aspx).

Em relação ao acompanhamento do PBM pela própria Microsoft, apesar de existir algumas sugestões de melhorias relacionadas a esta feature no Microsoft Connect, o CTP1 do SQL Server “Denali” (ou SQL Server 2011) não apresentou resposta a estas sugestões e também não foram divulgadas mudanças nesta feature. Mas pelo grande potencial desta feature, é até difícil pensar em melhorias que podem ser grandemente relevantes para futuras versões.

Conclusão

Este artigo tratou de permitir um entendimento de como por meio de um planejamento orientado por algumas perguntas e checklist, é possível adotar soluções com o a feature PBM, respondendo de forma adequada as necessidades do cenário, devidamente justificada, contando até mesmo com exceções e permitindo que a solução depois implantada não se torne uma feature órfã, por permitir melhorias e acompanhar a evolução e mudanças do cenário.

Com apoio de outras features nativas do SQL Server 2008+, como o Resource Governor e o Management Data Warehouse, é possível rapidamente aumentar o arsenal do administrador de banco de dados, oferecendo maior controle da disponibilidade dos recursos do SQL Server e relatórios para acompanhar da evolução dos cenários, tendo cada vez mais controle sobre o ambiente e recursos para entender como responder de forma adequada questões de disponibilidade, capacidade e continuidade dos cenários. Mas apesar da praticidade de adotar qualquer features, nunca deixe de lado o planejamento antes de adotá-las.

Identificando consultas demoradas com o SQL Server Profiler

Antes de publicar os artigos sobre eventos com SMO (Trace e Events) que serão os temas a serem trabalhados nas próximas semanas, identifiquei a necessidade de mostrar o funcionamento do SQL Server Profiler, antes que alguém tente criar um SQL Server Profiler com o SMO.

O SQL Server Profiler é uma ferramenta normalmente utilizada por DBAs para identificar consultas lentas, diagnosticar problemas com deadlock e desempenho, e gerar cargas de consultas com o Database Engine Tuning Advisor.

Caso você nunca tenha falado de SQL Server Profiler, fique tranquilo, só pense que ele permite capturar eventos do SQL Server, como no caso, execução de consultas e procedures.

Para um exemplo bem simples, vamos executar o “Performance Tools\SQL Server Profiler”:

Ir em “File/New Trace…”:

Conectar a um servidor SQL Server:

E vamos nos deparar com a seguinte tela:

Especifique nesta tela um nome para seu Trace e o template a ser utilizado, no meu caso, o “TSQL_Duration”:

E você também pode definir um arquivo ou tabela para salvar o trace, exemplo:

Na aba “Events Selecion”, será possível definir filtros dos eventos a serem monitorados, assim como adicionar/remover eventos e especificar os dados a serem capturados de cada evento:

Prosseguindo com o botão “Run”, será possível ver em tempo real o que esta sendo executado no SQL Server e o tempo de execução de cada evento:

Ferramenta interessante, não? Então após se divertir com ela e conhecer um pouco mais de cada template, procure aprender como utilizar filtros e ordenação, criar seus próprios templates, assim como identificar recursos desta ferramenta para diagnosticar gargalos de desempenho e consultas lentas, e como trabalhar com ele junto com o Perfmon e o Database Engine Tuning Advisor.

Após utilizar o SQL Server Profiler, não se esqueça de parar o Trace, para não o deixar rodando sem necessidade no seu SQL Server.

Compartilhando o CTRL+C/CTRL+V e Drives com servidores em TS

Uma dica muito boa que me foi dada pelo André Gava há alguns anos, foi como habilitar a funcionalidade de compartilhar o clipboard (responsável pelo CTRL+C/CTRL+V) e também drives da máquina local com servidores em acesso remoto (Terminal Service, Remote Desktop Connection, mstsc), evitando a necessidade FTP em cenários simples.

Visto que é possível que algumas pessoas não saibam como fazer isso, elaborei abaixo um tutorial simples.

Para permitir o compartilhamento do clipboard, bastará marcar a seguinte caixa Clipboard nas configurações do Remote Desktop Connection/Local Resources:

Para a questão dos drives (drivers?), clicando no botão “More…” desta mesma aba,

Será possível selecionar quais drives serão compartilhados:

E quando você se logar no servidor, será possível transferir arquivos de uma forma mais amigável, exemplo:

Em relação ao fato do clipboard não funcionar algumas vezes, é possível encontrar informação junto ao blog do time de Remote Desktop Services (Terminal Services) Team Blog:

http://blogs.msdn.com/b/rds/archive/2006/11/16/why-does-my-shared-clipboard-not-work-part-1.aspx

Change Tracking – Demo

No artigo anterior (Change Tracking – Overview), tivemos uma visão geral do Change Tracking, feature presente no SQL Server 2008 e superiores.

Neste, teremos a demonstração desta feature, desde a configuração e operações básicas á sincronização de um ambiente replicado (sem a utilização do Sync Framework).

Como primeiro passo, vamos criar primeiramente um banco de dados. Em seguida, habilitar “Change Tracking” e “Snapshot Isolation” (este último, recomendável para o processo de sincronização dos dados).

Criamos uma tabela com alguns dados, e habilitamos o Change Trancking para monitorá-la:

Obtendo a versão inicial do banco de dados e a versão após INSERT/UPDATE/DELETE de alguns registos:

Obtendo informações sobre o que foi alterado na tabela “Salarios” a partir da versão “0”:

Relacionando os registros alterados por meio de um LEFT JOIN:

Obtendo mais informações:

Identificando qual a coluna atualizada numa operação de UPDATE (no caso, a coluna “Salario” foi atualizada):

Utilizando Change Context, para adicionar informações sobre a alteração realizada (no caso, PDV0029).

Replicando e sincronizando ambientes

Agora veremos um exemplo simples de ambiente replicado que após algumas alterações será sincronizado.

O primeiro passo, criar um repositório para identificar a versão inicial do ambiente no momento da importação dos dados, e outro repositório, onde ficarão estes dados.

Importação dos dados (lembrando de utilizar uma transação neste processo):

Realizando algumas operações na tabela original:

Versão do banco de dados e versão dos dados replicados:

Identificando as operações realizadas entre o banco de dados e o ambiente replicado:

Exemplo simples de sincronização dos dois ambientes:

Verificando a ressincronização:

Finishing

Finalizando a demonstração, desabilitando o Change Tracking e excluindo o banco de dados:

Script Completo

Change Tracking – Overview

Uma alternativa para quem quer monitorar INSERT/UPDATE/DELETE em tabelas do SQL Server 2008, até mesmo na edição EXPRESS, é o Change Tracking. Mas o que difere ele das outras features, como SQL Server Audit e Change Data Capture? Além do fato dele funcionar até na edição EXPRESS, ele monitora especificamente a “versão”, a chave primária e a ação executada (U – UPDATE, D – DELETE, I – INSERT), possibilitando também identificar qual coluna foi atualizada no caso de um UPDATE.

O Change Tracking é comumente utilizado em combinação com o Sync Framework (Sync Service for ADO.NET), visto que com o Sync Framework se torna bastante prático criar um modelo para sincronizar bancos de dados (SQL Server + outra solução como o SQL Server CE). Mas é possível criar uma solução sem o Sync Framework para sincronizar bancos de dados com outras plataformas ou Data Warehouse, visto que o Change Tracking possui funções que facilitam bastante este processo.

Ao se configurar esta feature, o primeiro passo é habilitar o Change Tracking no banco de dados, informando quanto tempo os dados históricos ficarão armazenados (CHANGE_RETENTION) e se estes dados serão removidos automaticamente do banco de dados (AUTO_CLEANUP).

--Habilitando o Change Tracking no banco de dados CT_Demo

ALTER DATABASE CT_Demo
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
GO

Em seguida, habilitar o Change Tracking nas tabelas a serem monitoradas. Nesta passo existe a possibilidade de configurar que sejam armazenadas informações de quais colunas que foram atualizadas ou não (TRACK_COLUMNS_UPDATED).

--Habilitando o Change Tracking na tabela Salarios

ALTER TABLE Salarios
ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO

A função CHANGE_TRACKING_CURRENT_VERSION() poder ser utilizada para identificar a atual versão do Change Tracking no banco de dados, iniciando de 0 e sendo incrementada a cada INSERT/UPDATE/DELETE nas tabelas monitoradas.

--Identfiicando a versão atual do Change Tracking no banco de dados

SELECT CHANGE_TRACKING_CURRENT_VERSION();
GO

A função CHANGETABLE(CHANGES NomeTabela, Versão), permite relacionar todas as alterações realizadas na tabela informada a partir de uma determinada versão.

--Identificando as alterações realizadas na tabela Salarios a partir da versão 0

DECLARE @SINC BIGINT
SET @SINC = 0

SELECT *
FROM CHANGETABLE(CHANGES Salarios, @SINC) AS CT
GO

A função CHANGE_TRACKING_IS_COLUMN_IN_MASK, permite identificar qual coluna foi atualizada numa operação de UPDATE.

--Identificando se a coluna Salario ou coluna Empregado foi atualizada numa operação de UPDATE na tabela Salarios

DECLARE @SINC BIGINT
SET @SINC = 0

DECLARE @SalarioColumnId INT
SET @SalarioColumnId = COLUMNPROPERTY(
		OBJECT_ID('Salarios'),
		'Salario',
		'ColumnId'
	)

DECLARE @EmpregadoColumnId INT
SET @EmpregadoColumnId = COLUMNPROPERTY(
		OBJECT_ID('Salarios'),
		'Empregado',
		'ColumnId'
	)

SELECT
SalarioAlterado = CHANGE_TRACKING_IS_COLUMN_IN_MASK(@SalarioColumnId, CT.SYS_CHANGE_COLUMNS),
EmpregadoAlterado = CHANGE_TRACKING_IS_COLUMN_IN_MASK(@EmpregadoColumnId, CT.SYS_CHANGE_COLUMNS),
CT.*
FROM CHANGETABLE(CHANGES Salarios, @SINC) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'
GO

Podem aguardar que em um próximo artigo apresentarei um cenário completo com o Change Tracking.

Change Data Capture – CDC

Olá pessoas!

Uma feature muito interessante para quem deseja fazer “log” de dados históricos do banco de dados, de forma a não prejudicar o desempenho do banco ou ter que criar um monte de triggers e tabelas de log, é o Change Data Capture, ou simplesmente CDC. Esta feature presente no SQL Server 2008 nas edições Developer e Enterprise.

O CDC permite de forma simplificada monitorar e manter histórico das alterações de determinadas tabelas no banco de dados. Isso quer dizer que, não será necessário desenvolver toda uma estratégia complexas com triggers para salvar a execução de INSERT, DELETE e UPDATE, pois com esta feature permite isso de forma descomplicada e performática.

Ok, para que preciso manter histórico das alterar das minhas tabelas no banco e dados? A motivação mais comum é a possibilidade de utilizar estes históricos para sincronizar informações de aplicações OFF-LINE ou soluções de Data Warehouse, que receberam dados de um determinado período e em algum momento terá que ser atualizado com os novos dados.

Outra motivação comum é manter histórico das alterações de determinadas tabelas para uma auditoria periódica, como auditorias de alterações salariais e movimentações logísticas e financeiras.

O primeiro passo para configurar o CDC, é habilitar esta feature em nível de banco de dados, a partir da stored procedure sys.sp_cdc_enable_db:

Em seguida, utilizar a stored procedure sys.sp_cdc_enable_table para configurar a tabela a ser monitorada:

Diferente das triggers, que são disparadas na mesma transação que é executado INSERTs, DELETEs e/ou UPDATEs (e que até mesmo por algum erro de programação podem prejudicar toda a transação), o CDC utiliza um Job para capturar os registros alterados, excluídos ou inseridos diretamente de arquivos de logs do banco de dados a cada 5 segundos:

Obs.: O recovery model do banco de dados não intefere no CDC.

E um segundo Job executado todos os dias às 2h00 AM, elimina os registros armazenados a mais de 3 dias dos repositórios de registros históricos do CDC, considerando que estes registros históricos ficam armazenados no próprio banco de dados.

Após a captura dos dados pelo CDC, a forma mais simples de recuperar estes dados históricos é por meio da tabela que armazena estes dados no CDC, que normalmente segue a seguinte nomenclatura:

cdc.(schema)_(tabela)_CT

Exemplo:

Existem outras formas mais otimizadas para recuperar estes dados, mas isso será tratado em um próximo artigo.

Para aqueles que estão familiarizados com Transactional Replication, podem estar se questionando pelo fato desta forma de replicação também fazer leitura diretamente do arquivo de logs do banco de dados, geraria alguma forma concorrência com o CDC. Nesta situação, podem ficar tranquilos, pois o Transactional Replication e o CDC são compatíveis, e quando o Log Reader do Transactional Replication estiver varrendo o log do banco de dados para replicar as alterações, o CDC esperará este processo ser concluído para ser executado.

E no caso de Mirroring, algum problema em relação ao CDC? Não, nenhum, pois até mesmo os dados armazenados pelo CDC serão espelhados para o banco de dados espelho.

Mais informações:
http://msdn.microsoft.com/en-us/library/bb522489

Restaurando dados de um horário específico no SQL Server

E ai pessoas! Hoje, vou tratar novamente de backups!

Mas o que ainda tem de importante em backups, para eu retornar a este assunto? Simples! Já pensou em recuperar “dados pedidos em um ‘DELETE sem WHERE’ qualquer” realizados as 20h05min do dia 27/10/2010? Sim! Isso é possível! Mas vamos primeiramente preparar o ambiente para nos proporcionar isso!

O primeiro passo é ajustar o “Recovery model” do banco de dados para “Full”:

O segundo passo é realizar um backup “Full”:

E pronto! A partir de agora você pode destruir tranquilamente seus dados (mas, não se esqueça de lembrar a que horas você começou a destruir seus dados):

Dados foram mandados para o LIMBO? E agora… Bem agora, passe no RH… Mas você pode tentar fazer um Backup “Transaction Log”:

Se prepare para restaurar seu backup “Full” e os “Transaction Log” em um novo banco de dados, mas antes de executar a restauração clique no botão de seleção na frente do “To a point in time: Most recent possible”:

Informe um horário anterior à destruição dos dados e inicie a restauração:

Veja que seus dados foram restaurados corretamente no ponto de restauração que você informou:

Agora é só iniciar um processo de restauração dos seus dados manualmente ou utilizar alguma ferramenta de Data Compare, como as da RedGate ou do Visual Studio, ou até mesmo o Import\Export do SQL Server Management Studio, para ajustar seus dados.

Obs.: SQL Server não restaura dados anteriores ao horário do backup Full que você possui (No-Miracles, please).

Plano de Manutenção: Automatizando Backups no SQL Server

Será que o SQL Server não pode fazer backups por você? Há alguns artigos atrás, expliquei como criar backups nos bancos de dados, mas será que este processo não poderia ser automatizado?

Para responder estas perguntas, vou demonstrar como criar de forma simples um plano de manutenção do SQL Server pelo Management Studio de forma a permitir backups diários, ou seja, automatizar o processo de backups.

O primeiro passo é verificar se o SQL Server Agent esta executando, pois ele é o responsável pela automatização do seu plano de manutenção que você (e outras funcionalidades que não veremos por enquanto):

Em seguida, se aventure pela pasta “Management” para encontrar a pasta “Maintenance Plan”:

Agora adicione um novo plano de manutenção pelo Wizard (por favor, não fale mal de Wizards, pois são boas ferramentas para definir os cenários comuns de forma simples e rápida):

Na primeira tela, nomeie o seu plano de manutenção:

E no botão “Change”, defina quando este plano de manutenção será executado, exemplo:

Diariamente à meia-noite… ficará:

Frequência: diária
Frequência Diária: Ocorrente sempre à meia-noite

Concluídas as alterações, vamos ao próximo passo, no qual você escolherá o que será realizado por este plano de manutenção (no caso, backup full):

Ignore o passo seguinte, visto que somente estamos tratando uma única task e não precisamos definir a ordem de execução de tasks neste cenário:

No próximo passo, configure quais os bancos que serão afetados pelo backup e onde estes backups serão armazenados:

Ao escolher os bancos que serão afetados pelo backup, você pode optar por realizar backup somente dos bancos específicos, somente dos bancos de sistema, ou como no caso apresentado, somente bancos de dados que não são de sistema:

Você pode ignorar as opções do próximo passo, relacionadas a alertas e log:

E finalizar a criação do plano de manutenção:

Pronto, agora você possui seu plano de backups:

Caso seja necessário executar este plano de manutenção fora do horário previsto, você pode executá-lo manualmente pela opção “Execute”:

Após a execução manual ou agendada, você poderá encontrar seus backups:

Então, este foi um breve passo-a-passo de como criar planos de manutenção. Após criar seu primeiro plano, estude como funcionam os outros tipos de backup para criar planos mais complexos e que permitam uma estratégia recuperação dos dados adequada à sua necessidade.

Até o próximo post!

Arquivo de log grande no SQL Server

Em ambientes de desenvolvimento, testes e implantação de sistemas é comum o banco de dados sofrer grandes alterações, importações de dados massivas, exclusões de grande porcentagem de dados e transações com milhares de operações que aumentam o arquivo de log do banco de dados consideravelmente.

Uma saída no SQL Server para estes ambientes que não precisam de backups, é utilizar bancos de dados no modo de recuperação simples (recovery model, simple), onde o arquivo de log do banco de dados (*.ldf) somente será utilizado para gerenciar as transações correntes (e operações de replicação), diminuindo bastante a necessidade de espaço no HD.

Então, nas propriedades do “bando” de dados, aba “Options”, temos a propriedade “Recovery Model”, onde alteramos para Simple e damos OK!

É só isso? Sim!

Mas se você possui um arquivo de log que já esta ocupando muito espaço em disco, não seria interessante diminuir um pouco ele? Então após aplicar o modo de recuperação simples, vá na aba “Files” altere o “Initial Size (MB)” para um valor que você considere adequado para seu banco de dados e dê OK!

Mas algumas vezes só alterar esta propriedade não ajuda muito…

Então, nas “Tasks”, temos a opção “Shrink”, “Files”:

Neste nova janela, escolha a propriedade “File type” “Log” e dê OK:

Pronto, agora você tem um banco de dados com log reduzido.

Obs.: Até mesmo em ambientes de desenvolvimento não recomendo realizar shrink de qualquer outro tipo de arquivo que não seja o de log, então somente utilize shrink em arquivos de dados se realmente for necessário (ex.: exclusão de tabelas gigantes), pois shrink em arquivos de dados pode afetar consideravelmente o desempenho do banco de dados, por fragmentar de índices e arquivos de dados, e também no caso do arquivo de log, gerar processamento desnecessário para alocar mais espaço em disco em futuras operações.