In previous versions of SQL Server, the biggest missing part was lack of a centralized management framework to create, deploy and manage a policies based infrastructure. One of the key management components in SQL Server 2008 is the ability to define policies and deploy across all servers to enforce configurations and making management less error-prone. This is achived via the new Policy-Based Management, which consists of three components, which are: policy management, explicit administration and execution mode.
Policies can be targeted at the following: database engine, a database, a table, an index, xp_cmdshell, which form the basic hierarchy. Policies allow a DBA to enforce basic standards across a set of SQL Server instances, such as naming standards, sp_configure options and feature availability as an example, which allows a SQL Server environment to have the same "look and feel" across an organization.
Policies are created using the SQL Server Management Studio Tool and stored in the MSDB database, thus any change to a policy or condition requires a backup of the MSDB database. To create a new policy, the syntax is as follows:
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy
@name=N'Check Window User',
@condition_name=N'Check if windows user policy',
@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'Check if windows user policy'_ObjectSet'
This is an awesome addition to an already brillant product and should make life easier to SQL Server DBA's!
