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

calendar 2013-02-05 · 6 perc olvasás · SQL SQL Server Database Maintenance  ·
Megosztás: linkedin copy

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.

 1USE [master];
 2GO
 3 
 4IF DATABASEPROPERTY('foo','Version') > 0
 5    DROP DATABASE [foo];
 6GO
 7 
 8CREATE DATABASE [foo]
 9ON PRIMARY
10(
11    NAME = 'foo_data',
12    FILENAME = 'C:\temp\foo_data.mdf',
13    SIZE = 5120 KB,
14    MAXSIZE = 5120 KB
15)
16LOG ON
17(
18    NAME = 'foo_log',
19    FILENAME = 'C:\temp\foo_log.ldf'
20)
21GO
22EXEC sp_cycle_errorlog;
23GO

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:

 1Declare @condition_id int
 2EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'PCT FREE SPACE <= 40', @description=N'', @facet=N'DataFile', @expression=N'<Operator>
 3 <TypeClass>Bool</TypeClass>
 4 <OpType>LE</OpType>
 5 <Count>2</Count>
 6 <Function>
 7   <TypeClass>Numeric</TypeClass>
 8   <FunctionType>Divide</FunctionType>
 9   <ReturnType>Numeric</ReturnType>
10   <Count>2</Count>
11   <Attribute>
12     <TypeClass>Numeric</TypeClass>
13     <Name>UsedSpace</Name>
14   </Attribute>
15   <Attribute>
16     <TypeClass>Numeric</TypeClass>
17     <Name>AvailableSpace</Name>
18   </Attribute>
19 </Function>
20 <Constant>
21   <TypeClass>Numeric</TypeClass>
22   <ObjType>System.Double</ObjType>
23   <Value>0.6</Value>
24 </Constant>
25</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
26Select @condition_id
27
28GO

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:

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. 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. On demand: manuális ellenőrzés, vagy PowerShell vagy SSMS segítségével lehet a Policy ellenőrzést elindítani. 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:

 1/*============================================================================
 2  File:     PostInstall-SetUpPBMAlerts.sql
 3 
 4  Summary:  This script sets up alerts for PBM violations
 5 
 6  SQL Server Version: 10.50.2500.0 (SQL Server 2008 R2 SP1)
 7------------------------------------------------------------------------------
 8  Written by Janos Berke, IamBerke.com
 9 
10  For more scripts and sample code, check out http://www.IamBerke.com
11   
12  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
13  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
14  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
15  PARTICULAR PURPOSE.
16============================================================================*/
17--switch to SQLCMD mode by using ALT + Q + M
18:setvar operator "DBA_GROUP"
19USE [msdb]
20GO
21EXEC msdb.dbo.sp_add_alert @name=N'Error_34050',
22        @message_id=34050,
23        @severity=0,
24        @enabled=1,
25        @delay_between_responses=0,
26        @include_event_description_in=1,
27        @job_id=N'00000000-0000-0000-0000-000000000000';
28GO
29EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34050', @operator_name=N'$(operator)', @notification_method = 1
30GO
31 
32EXEC msdb.dbo.sp_add_alert @name=N'Error_34051',
33        @message_id=34051,
34        @severity=0,
35        @enabled=1,
36        @delay_between_responses=0,
37        @include_event_description_in=1,
38        @job_id=N'00000000-0000-0000-0000-000000000000';
39GO
40EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34051', @operator_name=N'$(operator)', @notification_method = 1
41GO
42 
43EXEC msdb.dbo.sp_add_alert @name=N'Error_34052',
44        @message_id=34052,
45        @severity=0,
46        @enabled=1,
47        @delay_between_responses=0,
48        @include_event_description_in=1,
49        @job_id=N'00000000-0000-0000-0000-000000000000';
50GO
51EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34052', @operator_name=N'$(operator)', @notification_method = 1
52GO
53 
54EXEC msdb.dbo.sp_add_alert @name=N'Error_34053',
55        @message_id=34053,
56        @severity=0,
57        @enabled=1,
58        @delay_between_responses=0,
59        @include_event_description_in=1,
60        @job_id=N'00000000-0000-0000-0000-000000000000';
61GO
62EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_34053', @operator_name=N'$(operator)', @notification_method = 1
63GO

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

1USE [foo];
2GO
3 
4CREATE TABLE Table1
5(
6    Col1 char(8000) DEFAULT('a')
7)
8GO

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

1USE [foo];
2GO
3SELECT
4    SUM(used_pages) * 8  AS [space_usage_kb]
5FROM
6    sys.allocation_units A
7JOIN
8    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])
9GO

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

1INSERT INTO Table1 DEFAULT VALUES;
2 
3GO 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:

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