Alertas

Uma vez que as políticas estejam configuradas e implementadas, não é necessário verificar constantemente os servidores para termos certeza de que estão de acordo com as condições definidas nas políticas. Ao invés disso, podemos utilizar os alertas do SQL Server Agent para recebermos automaticamente notificações quando uma política for violada.

[CHECKPOINT]
Criação de uma política

No SQL Server 2014 é possível criar uma manualmente através do T-SQL, exportando políticas já existentes em uma instância de banco de dados SQL Server ou importando políticas criadas pela própria Microsoft. Com a utilização de condições avançadas quase não existem limites para os tipos de políticas que podem ser criadas. Assim que iniciarmos a criação de políticas, ficará clara a vantagem em agruparmos políticas similares em uma mesma categoria.

Para facilitar o entendimento de todos os componentes utilizados na criação de uma política e como os mesmos interagem entre si, criaremos uma política do zero. Para criarmos manualmente uma política é necessário primeiro definirmos uma condição e então poderemos criar uma política que utilizará esta condição. Uma vez que a política estiver criada, será possível categorizá-la e definir em quais targets deverá ser avaliada. Nesta parte do artigo realizaremos a criação de uma política que avaliará se todos os bancos de dados de uma determinada instância SQL Server estão com a propriedade AutoShrink desabilitada.

Criando uma condição

Vamos iniciar criando uma condição que será utilizada em uma política. Neste exemplo, criaremos uma condição que avaliará se um banco de dados está com a propriedade AutoShrink desabilitada.

A criação de uma condição pode ser realizada de duas maneiras. A primeira opção é através do SQL Server Management Studio (SSMS):

1 – Utilizando o Object Explorer, abra a pasta Management e depois a pasta Policy Management (Figura 8);

2 – Dentro dessa pasta, clique com o botão direito na pasta Conditions e selecione a opção New Condition;

3 – Feito isso, será aberta a janela para criação da nova condição (Figura 9);

Conforme ilustrado na Figura 9, durante a criação da condição foram definidas as seguintes opções:

• Name: A condição foi criada com o nome de AutoShrink False;
• Facet: A facet utilizada foi a Database;
• Expression: A propriedade avaliada foi a @AutoShrink e o seu valor deve ser igual a false;

Opcionalmente, podemos incluir uma descrição detalhada sobre a condição através da opção Description, conforme mostra a Figura 10.

A segunda opção para a criação da condição é através da stored procedure de sistema SP_SYSPOLICY_ADD_CONDITION. A Listagem 1 exemplifica a criação da condição AutoShrink False, com as mesmas opções definidas no SSMS.

Listagem 1. Criação da condição com T-SQL

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'AutoShrink False', @description=N'', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Bool</TypeClass>
    <Name>AutoShrink</Name>
  </Attribute>
  <Function>
    <TypeClass>Bool</TypeClass>
    <FunctionType>False</FunctionType>
    <ReturnType>Bool</ReturnType>
    <Count>0</Count>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO

Criando uma política

Agora que temos uma condição já criada, podemos criar uma política que utilizará esta condição.

Como na criação de uma condição, a política pode ser criada tanto através do SQL Server Management Studio (SSMS), como através de T-SQL.

Primeiramente criaremos a política utilizando o SSMS:

1 – Utilizando o Object Explorer, abra a pasta Management e depois a pasta Policy Management (Figura 8);

2 – Dentro dessa pasta, clique com o botão direito sobre a pasta Policies e selecione a opção New Policy;

3 – Feito isso, será aberta a janela para a criação da nova política (Figura 11);

• Evaluation Mode: O modo de avaliação selecionado foi o OnDemand, ou seja, esta política deverá ser executada manualmente;

Nota: Os modos de avaliação disponíveis na lista dependem das facets utilizadas na condição. Todas as facets suportam o OnChange e o OnSchedule, mas o OnChange: Prevent depende da facet que pode utilizar as triggers de DDL para realizar o procedimento de rollback da transação. O modo de avaliação OnChange: Log Only baseia-se na capacidade das alterações realizadas na facet serem capturadas por um evento.

• Server Restriction: Para esta política não haverá condições que restrinjam os servidores de banco de dados avaliados;

Podemos adicionar mais detalhes referentes à política através da opção Description, conforme mostra a Figura 12

Figura 12. Janela de descrição da política

Também é possível realizar a criação da política através das stored procedure de sistema SP_SYSPOLICY_ADD_OBJECT_SET, SP_SYSPOLICY_ADD_TARGET_SET, SP_SYSPOLICY_ADD_TARGET_SET_LEVEL e SP_SYSPOLICY_ADD_POLICY. A Listagem 2, exemplifica a criação da política Verifica AutoShrink, com as mesmas opções definidas no SSMS.

Listagem 2. Criação da política com T-SQL

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Verifica AutoShrink_ObjectSet_1', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
GO
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Verifica AutoShrink_ObjectSet_1', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
GO
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Verifica AutoShrink', @condition_name=N'AutoShrink False', @execution_mode=0, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Verifica AutoShrink_ObjectSet_1'
GO

Todas as políticas criadas são armazenadas no banco de dados de sistema msdb. Depois de criarmos nossas políticas, temos de nos certificar que o banco de dados de sistema msdb faça parte da estratégia de backup utilizada no ambiente.

Importando políticas

Podemos importar políticas disponibilizadas pela Microsoft durante o processo de instalação do SQL Server. Estas políticas são armazenadas em arquivos no formato XML e que ficam localizados no diretório Tools onde o SQL Server foi instalado.

A importação de políticas predefinidas tem algumas vantagens, pois além de criadas as políticas também são criadas todas as condições necessárias para o funcionamento correto da política. No entanto, não podemos importar apenas as políticas disponibilizadas pela Microsoft, mas também qualquer arquivo XML que possua as informações necessárias para a criação e uma política.

Para executar a importação de uma política temos os seguintes passos:

• Utilizando o Object Explorer, abra a pasta Management (Figura 8);
• Dentro dessa pasta, clique com o botão direito na pasta Policies e selecione a opção Import Policy;
• Feito isso, será aberta a janela para importação do arquivo XML com as definições da política (Figura 13);

Selecione a opção Replace duplicates with Items Imported para sobrescrever qualquer política e condição que tenha o mesmo nome da política que está sendo importada. Ao sobrescrever uma política já existente, não serão perdidas as informações das validações já realizadas com a política sobrescrita.

Também podemos preservar o status da política que está sendo importada, habilitar a política após a importação ou desabilitar a política após a importação, conforme mostra a Figura 14.

Após a importação do arquivo XML contendo as definições da política, podemos visualizá-la na pasta Policies. A nova política foi criada com o nome de Data and Log File Location, conforme mostra a Figura 15.

É interessante observar que diferentemente da política criada anteriormente, a política criada através do processo de importação possui uma restrição de servidor que limita a validação das condições existentes na política apenas nas instâncias de SQL Server que utilizem as edições Enterprise ou Standard.

A Figura 16 mostra as informações gerais que foram automaticamente inseridas quando importamos a política através do arquivo XML. A categoria, descrição e hyperlinks foram populadas, facilitando o processo de documentação que informa o motivo desta política ter sido implementada.