Criando políticas com Policy-Based Management (Parte 1)

Um recurso do SQL Server pouco implementado nas empresas é o gerenciamento baseado em diretivas (“Policy-Based Management“). Permite gerenciar uma ou mais instâncias do SQL Server baseando-se, por exemplo, na própria política da empresa. Entre os objetos que podemos gerenciar encontra-se os databases, tabelas, stored procedures, views, auditoria, logins, entre outros. As diretivas são armazenadas no banco de dados msdb. Atenção! Após a alteração de uma diretiva ou condição, recomenda-se realizar o backup do msdb.

Se você fizer parte do grupo sysadmin (que, por padrão, tem total controle sobre a instância) e deseja que algum usuário realize as tarefas no Policy-Based Management, será preciso inclui-lo no grupo PolicyAdministratorRole no banco de dados msdb. Esse grupo tem controle total de todas as diretivas existentes no sistema. Portanto, cuidado ao incluir usuários nesse grupo.

O Policy-Based Management é útil, por exemplo, no seguinte cenário: Verificar quais os databases que se encontram com auto-close configurado para o valor True (habilitado – como boa prática é recomendado que ele fique desabilitado).

O Policy-Based Management disponibiliza 4 modos de avaliação, 3 dos quais podem ser automatizados. Vamos falar sobre cada um deles agora:

  • On demand. Este modo avalia a política quando especificado diretamente pelo usuário.
  • On Change: Prevent. É um modo automatizado que usa gatilhos DDL para impedir violações de política. Aplica a restrição no momento da criação do objeto.
  • On Change: log Only. Este modo automatizado usa a notificação de eventos para avaliar uma política quando uma alteração relevante é feita.
  • On Schedule. Este modo automatizado usa um job do SQL Server Agent para avaliar, periodicamente, uma ou várias políticas.

Uma facet do Policy-Based Management é um conjunto de propriedades lógicas relacionadas a uma área de interesse de gerenciamento. O SQL Server 2008 disponibilizava 74 facets pré-definidas. No SQL Server 2017 esse número aumentou para 96. Não podemos criar facets, apenas utilizar as existentes.

Qual seria a grande vantagem das facets? Quando você gerencia diversas instâncias e, ambientes semelhantes, é possível configurar uma facet em uma instância do SQL Server, em seguida copiar a facet em um arquivo e importar esse arquivo para outra instância como uma diretiva. Para
obter uma lista das facets disponíveis, podemos utilizar o seguinte código T-SQL:

 

Agora, vamos confirmar que existe, pelo menos, um database com auto-shrink habilitado. Neste exemplo, o database WideWorldImporters apresenta a configuração habilitada (TRUE):


Próximo passo: criar primeiro uma condição e, em seguida, a política.

Object Explorer à Management à Policy Management à Condition e selecione New Condition:


Configurando a condição…


Vamos criar a nossa politica: Policies à New Policy.


Executando a Politica: Object Explorer à Management à Policy Management à Policies e selecione Evaluate:


Em “Target details” marque a caixa referente ao banco de dados que não atendeu a politica… e para aplicar basta clicar em Apply:


O SQL Server questiona se deseja realmente aplicar a politica… clique em Yes…


Pronto! Politica aplicada com sucesso. Verificando se realmente Auto-Shrink está, agora, com o valor de FALSE:


No próximo post, iremos padronizar a nomenclatura de stored procedures que serão criadas. Não será permitido a criação de stored procedures que comecem por sys, xp_ e sp_.