Policy-Based Management és DBCC CHECKDB

English version is available at Technet Gallery: http://gallery.technet.microsoft.com/How-to-check-Last-known-0f93b4fa

Az utóbbi időben igen sokszor van szerencsém a DBCC parancsokkal dolgozni. Most egy érdekes kérdést próbálok megválaszolni: hogyan tudjuk Policy-Based Management segítségével az adatbázisaink utolsó jó DBCC CHECKDB futását ellenőrizni? Ezt a kérdést ma egy fórumon tette fel valaki, majd az MCM tanulócsoport is rákapott SmileAz alábbi megoldás született.

A probléma

Az SQL Server 2008+ verzióban található PBM segítségével meg akarjuk állapítani, hogy az adatbázison 30 napon belül a DBCC CHECKDB lefutott e hiba nélkül. A PBM jelenleg nem engedi a saját Facet létrehozását.

A megoldás

Létre kellett hozni egy olyan Condition-t, ami azt vizsgálja, hogy az adott időszakon belül lefutott e a DBCC parancs hiba nélkül. Majd ebből hoztam létre a Policy-t. A Condition az ExecuteSql() függvényt használja az alábbi lekérdezésekkel:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = 'upDbccLastKnownGood')
BEGIN
--Stored procedure létrehozása
EXEC ('CREATE PROCEDURE dbo.upDbccLastKnownGood
@Days int,
@IsLastKnownOk bit OUTPUT
AS

DECLARE @LastKnownDate datetime;
CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));
INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''DBCC DBINFO() WITH TABLERESULTS;'');
SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''dbi_dbccLastKnownGood'';

IF (DATEADD(DAY, -@Days, GETDATE()) <= @LastKnownDate)
	SET @IsLastKnownOk = 1
ELSE
	SET @IsLastKnownOk = 0
')
END
-- keresett adat lekérdezése
DECLARE @RetVal bit;
EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT
SELECT @RetVal;
--cleanup
DROP PROCEDURE upDbccLastKnownGood;

Na ezt a fenti kódot ha beillesztem, már meg is vagyok: ellenőrzi, hogy az adott adatbázisban 30 napnál nem régebbi a keresett információ.

A Condition az alábbi script segítségével hozható létre:

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'DBCCLastRun', @description=N'', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>NE</OpType>
  <Count>2</Count>
  <Function>
    <TypeClass>Numeric</TypeClass>
    <FunctionType>ExecuteSql</FunctionType>
    <ReturnType>Numeric</ReturnType>
    <Count>2</Count>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>Numeric</Value>
    </Constant>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>&lt;?char 13?&gt;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = ''''upDbccLastKnownGood'''')&lt;?char 13?&gt;
BEGIN&lt;?char 13?&gt;
EXEC (''''CREATE PROCEDURE dbo.upDbccLastKnownGood&lt;?char 13?&gt;
@Days int,&lt;?char 13?&gt;
@IsLastKnownOk bit OUTPUT&lt;?char 13?&gt;
AS&lt;?char 13?&gt;
&lt;?char 13?&gt;
DECLARE @LastKnownDate datetime;&lt;?char 13?&gt;
CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));&lt;?char 13?&gt;
INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''''''''DBCC DBINFO() WITH TABLERESULTS;'''''''');&lt;?char 13?&gt;
SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''''''''dbi_dbccLastKnownGood'''''''';&lt;?char 13?&gt;
&lt;?char 13?&gt;
IF (DATEADD(DAY, -@Days, GETDATE()) &lt;= @LastKnownDate)&lt;?char 13?&gt;
	SET @IsLastKnownOk = 1&lt;?char 13?&gt;
ELSE&lt;?char 13?&gt;
	SET @IsLastKnownOk = 0&lt;?char 13?&gt;
'''')&lt;?char 13?&gt;
END&lt;?char 13?&gt;
&lt;?char 13?&gt;
DECLARE @RetVal bit;&lt;?char 13?&gt;
EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT&lt;?char 13?&gt;
SELECT @RetVal;&lt;?char 13?&gt;
DROP PROCEDURE upDbccLastKnownGood;</Value>
    </Constant>
  </Function>
  <Constant>
    <TypeClass>Numeric</TypeClass>
    <ObjType>System.Double</ObjType>
    <Value>0</Value>
  </Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

Majd a Policy:

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'DBCC_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'DBCC_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

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'DBCC', @condition_name=N'DBCCLastRun', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'DBCC_ObjectSet'
Select @policy_id


GO

Mivel a Condition-nál a Facet Database-re lett állítva, így a lekérdezésünk minden adatbázison le fog futni, amelyek meg vannak adva az Against Targets beállításnál

image

Jogosultság

Az így létrehozott Policy futtatásához az alábbi jogosultságokra van szükség:

  • Az ellenőrzött adatbázisban CREATE/DROP PROCEDURE, EXEC a dbo schema-n,
  • a DBCC DBINFO nem dokumentált parancs futtatásának lehetősége (ez szerintem db_owner jogot szeretne, de nem ellenőriztem)

Eredmény

Amikor lefuttatom az ellenőrzést, a teszt szerveremen az alábbi ereményt kapom: a zöld a jó, piros a rossz.

image

Add comment