Értesítsek PBM szabályszegésekről

5. February 2013 János Berke SQL Server Comments (0)

Mindig meglepem saját magam azzal, hogy újra és újra felfedezek egy olyan szolgáltatást/funkciót az SQL Server-ben, amit már használtam és bevállt, de már régóta nem volt rá szükségem. Mostanság a Policy-Based Management funkciót fedeztem fel ismét. Tavaly a DBCC CHECKDB parancsot “házasítottam” össze a PBM-el, most egy nagyon egyszerű dolgot fogok: szabad hely monitorozása egy adatbázisban. Ráadásul szeretnék erről értesítést is kapni, amikor elér egy bizonyos szintet.

Kezdjünk neki: az első dolog, hogy létrehozok egy adatbázist, fix, előre lefoglalt lemezterülettel, ami legyen 5 MB most és nem is engedem tovább növekedni.

USE [master];
GO

IF DATABASEPROPERTY('foo','Version') > 0
	DROP DATABASE [foo];
GO

CREATE DATABASE [foo]
ON PRIMARY
(
	NAME = 'foo_data', 
	FILENAME = 'C:\temp\foo_data.mdf',
	SIZE = 5120 KB,
	MAXSIZE = 5120 KB
)
LOG ON
(
	NAME = 'foo_log',
	FILENAME = 'C:\temp\foo_log.ldf'
)
GO
EXEC sp_cycle_errorlog;
GO

 

Következő lépésben, létre fogok hozni egy Condition-t, ami azt fogja vizsgálni, hogy az adott adat file teljes méretéhez képest, hány százaléknyi terület van használatban. Ezt SSMS-ben is meg lehet csinálni így:

  • Menjen a Server/Management/Policy Management/Conditions menüpontra, majd jelölje ki.

  • Nyomja meg a jobb egérgombot, majd válssza New Condtion menüpontot.
  • Adjon meg egy nevet, pl.: PCT FREE SPACE <= 40
  • A Facet melletti legördülő menüből válassza ki a Data File elemet.
  • Az Expression részen a Field oszlopnál írja be a következőt: Divide(@UsedSpace, @AvailableSpace)
  • Az Operator értéke legyen <=
  • A Value legyen 0.6

  • a végén nyomja meg az Ok gombot.

vagy az alábbi script segítségével is:

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'PCT FREE SPACE <= 40', @description=N'', @facet=N'DataFile', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>LE</OpType>
  <Count>2</Count>
  <Function>
    <TypeClass>Numeric</TypeClass>
    <FunctionType>Divide</FunctionType>
    <ReturnType>Numeric</ReturnType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>Numeric</TypeClass>
      <Name>UsedSpace</Name>
    </Attribute>
    <Attribute>
      <TypeClass>Numeric</TypeClass>
      <Name>AvailableSpace</Name>
    </Attribute>
  </Function>
  <Constant>
    <TypeClass>Numeric</TypeClass>
    <ObjType>System.Double</ObjType>
    <Value>0.6</Value>
  </Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

Ha ezzel megvagyunk, akkor jöhet a Policy:

  • Menjen a Server/Management/Policy Management/Policies menüpontra, majd jelölje ki.

  • Nyomja meg a jobb egérgombot, majd válssza New Policy menüpontot.
  • Adjon meg egy nevet, pl.: DB FILE FREE SPACE
  • A Check Condition menüpontban válassza ki a fenti Condition-t.
  • Az Against targetsmenüpontnál
    • jelölje ki, hogy minden File, minden Filegroup esetén legyen ellenőrizve,
    • legyen beállítva minden adatbázis, vagy hozzon létre egy Condition-t a megfelelő adatbázisra (lásd képen)
  • Az Evaluation Mode menüpontban állítsa be, hogy időzítve – On schedule –  legyen az ellenőrzése, majd a Schedule menüpontnál válassza ki/hozzon létre egy ütemezést.

A fentieket természetesen létre lehet hozni script segítségével is.

A lényegi résszel tulajdonképpen meg is vagyunk: van egy PBM elemünk, ami azt fogja vizsgálni, hogy az adatbázsiban a szabad terület 40% vagy attól kevesebb. De ezt csak negyedóránként fogja megtenni: ehhez az SQL Server Agent-tel ütemezett feladat fogja végrehajtani, ami a syspolicy_check_schedule_{schedule_uid} nevet fogja kapni. A schedule_uid egy GUID, ami az msdb adatbázisban a [dbo].[sysschedules] táblában található schedule_uid értéke. Az ütemezett feladat egy PowerShell script-et fog meghívni, amivel ellenőrzi a Policy által meghatározott paramétereket.

Értesítéseket az alábbi módon kaphatunk: hozzunk létre értesítéseket – Alert – a 34052 esemény kódra. Ez akkor van, amikor egy ütemezett Policy ellenőrzés során mért paraméterek nem felelnek meg a leírásban megadottakkal.

Itt tennék egy kis kitérő: négy típusa van a Policy ellenőrzéseknek:

  1. On Change: prevent: ez azt jelenti, hogy a Policy-ban meghatározott értékeket kell teljesíteni a célobjektumoknak, nem engedi azt megváltoztani. Igen hasznos tud lenni, ha ki akarok kényszeríteni pl. névkonvenciót – a táblák neve mindig kezdődjön tbl_-al egy bizonyos adatbázisban.
  2. On Change: log only: ebben az esetben “csak” beírja a változást az SQL Server Error log-ba, de nem akadályozza meg, mint az 1. pont esetén.
  3. On demand: manuális ellenőrzés, vagy PowerShell vagy SSMS segítségével lehet a Policy  ellenőrzést elindítani.
  4. On Schedule: időzített ellenőrzés.

Minden PBM szabálysértés esetén bekerül egy bejegyzés az SQL Server Error Log-ba, az alábbiak szerint:

  • Error 34050 On Change: prevent esetén
  • Error 34051 On Change: log only esetén
  • Error 34052 On Schedule esetén
  • Error 34053 On Demand esetén.

Most, hogy minden információnk adott, hozzuk létre az értesítéseket az alábbi kód segítségével:

/*============================================================================
  File:     PostInstall-SetUpPBMAlerts.sql

  Summary:  This script sets up alerts for PBM violations

  SQL Server Version: 10.50.2500.0 (SQL Server 2008 R2 SP1)
------------------------------------------------------------------------------
  Written by Janos Berke, IamBerke.com

  For more scripts and sample code, check out http://www.IamBerke.com
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
--switch to SQLCMD mode by using ALT + Q + M
:setvar operator "DBA_GROUP"
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_34050', 
		@message_id=34050, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34050', @operator_name=N'$(operator)', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error_34051', 
		@message_id=34051, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34051', @operator_name=N'$(operator)', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error_34052', 
		@message_id=34052, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34052', @operator_name=N'$(operator)', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error_34053', 
		@message_id=34053, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34053', @operator_name=N'$(operator)', @notification_method = 1
GO

 

Ha ezekkel megvagyunk, térjünk vissza a foo nevü adatbázishoz. Először is hozzunk létre egy teszt táblát:

USE [foo];
GO

CREATE TABLE Table1
(
	Col1 char(8000) DEFAULT('a')
)
GO

 

Majd nézzük meg, hogy mekkora helyet foglal az adatbázisunk:

USE [foo];
GO
SELECT
	SUM(used_pages) * 8  AS [space_usage_kb]
FROM
	sys.allocation_units A 
JOIN 
	sys.partitions P ON (A.[type] = 2 AND P.[partition_id] = A.[container_id]) OR (A.[type] IN (1,3) AND P.[hobt_id] = A.[container_id])
GO

 

Itt nekem 1128KB-ot mutat. Ennek alapján könnyű kiszámolni, hogy akkor fog értesítést küldeni a fenti kód, ha elérjük a 3072KB-ot, amihez 1944KB hiányzik. A tábla adattípusa alapján 243 sor létrehozása elegendő lesz, de biztosra megyek és 245 sort adok hozzá:

INSERT INTO Table1 DEFAULT VALUES;

GO 245

Majd ismét lekérdezem az adatbázis méretét, mely során 3096KB-ot kapok. Ez már magasabb érték annál, mint amit engedni szeretnék. Ha időközben nem futott volna le az időzített PBM ellenőrzés, indítsuk el manuálisan: jobb gomb a Policy-n majd kattintson az Evaluate menüpontra.

Ezek után, ha a Database Mail be van állítva, már küldi is az email értesítőt, illetve az alábbi kód segítségével az SQL Server Error Log-ból is ki lehet olvasni:

EXEC sp_readerrorlog 0,1,'Policy';
EXEC sp_readerrorlog 0,1,'34052';

Az alábbi két file tartalmazza a demó kódot és az exportált Policy definiciót:

Tags: , , , , , , , ,

Add comment