February 21, 2017 at 9:32 am
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
February 22, 2017 at 9:25 am
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.
February 23, 2017 at 1:04 am
Hi Steve,
Thanks for replying, I will give your suggestion a go today and let you know how I get on.
Adam
February 23, 2017 at 3:58 am
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.
February 23, 2017 at 11:33 am
You have a logical fallacy. "ReportServer" is not like "ReportServerTempdb". Same for the reverse. What you need is not like "ReportServer%" as one entry.
February 23, 2017 at 12:02 pm
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
February 23, 2017 at 2:47 pm
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