Policy based maangement - Limit target databses.

  • Hi all,

    I am trying to set up some new policies for checking database configurable options e.g. auto_shrink, recovery_model etc... but I would like to be able to limit the databases that are included in the policy to exclude system databases.
    So far I have followed this guide: https://www.simple-talk.com/sql/database-administration/policy-based-management-and-central-management-servers/

    I thought I would be able to create another condition under the target option and limit the available databases by name e.g. New condition: database name not in  Array('master', msdb', 'model','tempdb', 'ReportServer', 'ReportServerTempDB'). Although the new condition is created I am not able to select it for use under the "Target" drop down. I only see "every" or "new condition".

    Any assistance is appreciated.

    Kind regards,

    Adam

  • The array issue seems to be the problem. I can create a condition that uses @name (for the database facet) = 'a' and then include an OR condition for @name = 'b'. This works for target filtering.

  • Hi Steve,
    Thanks for replying, I will give your suggestion a go today and let you know how I get on.
    Adam

  • Steve,

    I gave your suggestion a go this morning, I found the following behaviour to be a bit strange and condusing:.

    The requirement is to be able to create a policy to evaluate certain database properties i.e. if auto_shrink enabled against all databases execpt for the following: Master, Model, MSDB, TempDB, ReportServer and ReportServerTempDB.

     It appears by default that the system databases are not evaluated anyway, but the ReportServer, ReportServerTempDB and any user databases are. So I need to exclude ReportServer and ReportServerTempDB from being evaluated.
     I created a new target condition and specified the following:

    When the policy is evaluated 3 users databases are returned along with the 'ReportServer' and 'ReportServerTempDB', which should have been excluded. 

    To test if the operators take any effect I changed them from "not like" to "like".
    .
    After re-evaluating the policy again only results for the "ReportServer" and "ReportServerTempDB" are returned.

    So it appears as though the operators are working, but only where "Like" is specified..... unless I am doing something wrong.

  • You have a logical fallacy. "ReportServer" is not like "ReportServerTempdb". Same for the reverse. What you need is not like "ReportServer%" as one entry.

  • Hi Steve,

    Yep spot on with your logic... So obvious now! ?? How embarrassing!

    I thought I had got round the issue using database ID to exclude the system databases e.g ID > 6 (where 1-6 are sys db's and report server db's)

    ... Until I needed to add another which wasn't a sys db and the id was 14. But your logic works perfectly so many thanks for your guidance.

    Asam

  • No worries. I've done that a lot of times, wondering why SQL Server (or the compiler) doesn't just understand what I mean.

    I didn't test this, preferring to go with = a number of db names, but you should be able to find some way to limit the checks to just the SSRS dbs.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply