Thanks to Lara Rubbelke for teaching me about PBM! See the attached PBM.zip for a full slide deck and demos of the "on change" functionality. 

Install

•       Get updated policies from the feature pack item “Microsoft SQL Server 2008 Policies”

•       In Management Studio expand your instance then Management.Policy Management.Policies.Import Policy

•       Import from C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

Policy-Based Management

•       Combines prior features such as

−     Agent for schedules and alerts

−     DDL triggers as event handlers

−     Best Practices Analyzer

−     Surface Area Configuration Tool

PBM Components

•       Facets

−     Groupings of properties that encapsulate a target such as Surface Area Configuration or Table

•       Conditions

−     States such as true/false or on/off for given settings

−     Almost anything that can be verified programmatically

•       Policies

−     Verification of a condition and the required state for a defined target(s)

PBM - Facets

•       Contains properties that can have conditions set

•       Example: Table facet has properties such as Name, HasClusteredIndex, IsPartitioned, CreateDate, etc.

•       Cannot be modified or added

•       Are not executed directly

PBM - Conditions

•       Are set for facet properties

•       Can be tested programmatically

•       Can be limited to targets such as certain databases or objects

•       Example: For the Table facet you can set @HasClusteredIndex = True

                AND @Owner = dbo

PBM - Policies

•       Applies a check condition against targets such as “every table”

•       Can be on demand or scheduled

−     Scheduling is through SQL Agent jobs

−     On demand has the option of "apply" for some conditions to correct exceptions

•       Can be exported then imported to other servers

•       Or execute against a group of servers in SSMS

PBM – "On Change " Enforcement

•       Not available for all conditions

•       On Change – Log Only

−     Allows a change away from the policy to occur but logs the change to SQL Server error log and Windows application log

•       On Change – Prevent

−     Prevents changes from occurring if they are against policy

−     Enforced through DDL triggers

Permissions

•       To set/change policies, add users to role PolicyAdministratorRole in msdb – note that this an attack vector as a possible elevation of privilege

•       On Schedule evaluation mode uses SQL Server agent jobs owned by SA

References

•       List of evaluation modes for each facet

•       Evaluating Policies On Demand Through PowerShell

•       Administering Servers by Using Policy-Based Management

•       Take Control of the Enterprise: Effective Solutions for Governing your Environment With Policy Based Management

Want to provide feedback on any Microsoft product? Want access to the latest CTPs (sometimes known as betas)?

Create an account on http://connect.microsoft.com/ and you can file bugs or wishes and vote on what other people have submitted. You can also find CTPs and evaluation editions of various products. For example, to get the CTP of SQL Server 2008 SP1 go to http://connect.microsoft.com/SQLServer.  From the same page you can access the SQL Server feedback section as well as the SQL Server discussion forums. Please vote on your favorite bugs/requests/wishes as this increases the chance they will change for a future version of SQL Server.

SPNs: What They Are and How SQL Server 2000/2005 Uses Them

Agenda

·         Overview

·         Vocabulary

·         When SQL Server Uses an SPN

·         What a valid SQL Server SPN Looks Like

·         Define a Valid SQL or OLAP SPN

·         How to Check an SPN

·         SPN Ownership

·         Prerequisites

·         How to Set an SPN

·         Troubleshooting

·         SQL 2008

·         References

With Windows 2008 you now have the option to use multiple DTCs. There is a lot of confusion over whether to do that for SQL Server and what the exact steps are. If you use multiple DTCs you must also make sure you map each SQL Server to a specific DTC.

When you choose to open a file in SQL Server Management Studio (SSMS) it opens a new query window. Sometimes you would prefer to open the file in an existing window to add it to other code or to avoid entering the connection information again.

This is the best description I’ve seen of how to do disk alignment. It’s not really specific to SQL Server, it applies to any Windows server.

SQL Server 2005 service pack 3 (SP3) is now available! There’s also a new version of the feature pack and of BOL.

·         SP3 itself contains all fixes from SP2 based CU1-CU9 but it does not include most CU10 or CU11 fixes due to the timing of when the CTP (beta) of SP3 started. This pure SP3 version is 9.00.4035. If you want to have al

Read More »

The first cumulative update is available for SQL Server 2008. I’ve compiled the link for the cumulative update plus some other SQL Server 2008 links that you may find useful.

Sometimes you need to change the collation of a database after you have created it and populated it with objects and data. So how do you change the collation?

This year SQL injection attacks are being stepped up and even automated against SQL Server. While SQL injection attacks can occur against any DBMS, my blog will only address SQL Server.

Many people have asked where the TSQL debugger is for SQL Server 2005.

SQL Server 2008 release candidate 0 (RC0) is now available for public download. Use the links below to download SQL Server, Books Online, the Feature Pack, and more.

Finding out which build is the latest or what fixes are included in a particular build can be difficult. These links should make that job easier.

Read More »

A Universal Data Link (UDL) can be very useful in troubleshooting SQL Server and Analysis Services connectivity issues.

Read More »

Microsoft has recently revised the documentation on the below message due to our recent history with troubleshooting the warning:
A significant part of sql server process memory has been paged out.

Read More »

Have you heard about the new Service Broker functionality in SQL Server but don't know where to start with installing, understanding, or troubleshooting it? These links will help you get started.