Prev: MS ACCESS & SQL 2005 Security
Next: from money to currency
From: Chris M on 6 May 2010 16:11
Hi all,

I'm currently studying SQL server with a view to take exam 70-432 soon.
I'm looking at the chapter on Policy-Based Management and I came across
something which I hope someone here can explain.

While I was reading around the subject of policies, I found a couple of
places gave an example of implementing a policy to ensure that table
names follow a naming convention and prevent any tables from being
created that don't adhere to the policy:

http://www.mssqltips.com/tip.asp?tip=1492

and a video here covering the same thing:

http://www.youtube.com/watch?v=krwdJ-huLsk


After creating the condition that applies to the Table facet, I then
created a new policy and found that I didn't have the option of either
of the 'On Change' evaluation modes. I was able to reproduce the same
thing on a couple of SQL 2008 boxes at work too.

If I create a similar condition that applies to the names of Stored
Procedures instead of tables, I get the 'On Change' options as expected.

One of the DBAs at work suspects that this capability was perhaps
present in early versions of SQL 2008 (the YouTube video is certainly
using a CTP version) and has since been removed - he speculated that it
would perhaps cause problems with the creation of temporary tables.

Anyway, can anyone shed any light on this?

Thanks,

--
Chris M.
From: Erland Sommarskog on 7 May 2010 18:04
Chris M (nobody(a)nowhere.special) writes:
> After creating the condition that applies to the Table facet, I then
> created a new policy and found that I didn't have the option of either
> of the 'On Change' evaluation modes. I was able to reproduce the same
> thing on a couple of SQL 2008 boxes at work too.
>
> If I create a similar condition that applies to the names of Stored
> Procedures instead of tables, I get the 'On Change' options as expected.
>
> One of the DBAs at work suspects that this capability was perhaps
> present in early versions of SQL 2008 (the YouTube video is certainly
> using a CTP version) and has since been removed - he speculated that it
> would perhaps cause problems with the creation of temporary tables.

There were indeed quite a few changes forth and back in PMB during the
beta of SQL 2008, not the least for which type of objects you could do
On Change - Prevent.

Personally, I would like to raise a word of warning for that mode. First
of all, the true story is "On Change - Rollback"; there is no way in
SQL Server to prevent a DDL action before hand. Also, sometimes PMB will
decide to ignore an On Change action if the rules are too complex. If
you edit the Policy itself, you will get an error message, but if you
change a Condition, it can happen silently.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Chris M on 7 May 2010 18:28
On 07/05/2010 23:04, Erland Sommarskog wrote:
> Chris M (nobody(a)nowhere.special) writes:
>> After creating the condition that applies to the Table facet, I then
>> created a new policy and found that I didn't have the option of either
>> of the 'On Change' evaluation modes. I was able to reproduce the same
>> thing on a couple of SQL 2008 boxes at work too.
>>
>> If I create a similar condition that applies to the names of Stored
>> Procedures instead of tables, I get the 'On Change' options as expected.
>>
>> One of the DBAs at work suspects that this capability was perhaps
>> present in early versions of SQL 2008 (the YouTube video is certainly
>> using a CTP version) and has since been removed - he speculated that it
>> would perhaps cause problems with the creation of temporary tables.
>
> There were indeed quite a few changes forth and back in PMB during the
> beta of SQL 2008, not the least for which type of objects you could do
> On Change - Prevent.
>
> Personally, I would like to raise a word of warning for that mode. First
> of all, the true story is "On Change - Rollback"; there is no way in
> SQL Server to prevent a DDL action before hand. Also, sometimes PMB will
> decide to ignore an On Change action if the rules are too complex. If
> you edit the Policy itself, you will get an error message, but if you
> change a Condition, it can happen silently.

Thank you Erlang, both for the explanation and the insight - it's very
much appreciated.

--
Chris M.
 | 
Pages: 1
Prev: MS ACCESS & SQL 2005 Security
Next: from money to currency