SQL Server 2008 new features-Policy management

As you can see, policy Management contains three nodes: policies, conditions, and aspects.

Aspect is the object that the policy applies to, including: Server, table, trigger, view, stored procedure ... These aspects are all system-defined, and are only for viewing and cannot be changed. Double-click a specific facet to view the properties of that facet, which can be judged when a condition is defined, as a property of the stored procedure.

The condition is that a Boolean expression determines whether the policy is true.

A policy is an action to be performed if the condition is false, that is, the evaluation mode. There are 4 evaluation modes in the policy: On demand, on schedule, on change, and when changed. For these 4 modes, the official definition is as follows:

• On-demand. When the user specifies this mode directly, it can evaluate the policy.
• When changing: prohibited. This automatic mode uses DDL triggers to prevent policy violations.
• When changing: Record only. This automatic mode uses event notifications to evaluate policies when relevant changes occur and the log policy is violated.
• as planned. This automatic mode uses the SQL Server agent job to periodically evaluate the policy. This mode records a violation of the policy.
It is manually operated on demand, while the other three can be done automatically. The schedule is to use SQL Server Agent to check the policy periodically, and the other two are triggered by a DDL trigger when the change occurs.

Other concepts I do not need to say, you can read Books Online, here is an example to illustrate the use of policy management.

Suppose now that we are going to develop a business system whose database is TestDB1, using the ADO call stored procedure to implement data manipulation, now the project specifies the naming specification of the stored procedure: "Usp_" begins. Here we can use policy management to implement the inspection or enforcement of the specification. The following procedures are described:

(1) Since we are targeting a stored procedure, right-click stored procedure under the "Facets" node and select the "new condition" option, the system will pop up the new condition window.

(2) Enter the name of the "condition": "Stored procedure naming specification", then select @name in the field list, the operator is like, and the value is ' usp[_]% '. That is, the name of the stored procedure like ' usp[_]% ', the SQL expression that begins with "Usp_".

You can use variables and functions for both fields and values, and if you allow stored procedures beginning with "Usp_", "usp_", you can use a lowercase function, rewrite the field to "Lower (@Name)", and then click the OK button to create the "condition" finish.

(3) Right-click on the "Policies" node, select "New policy" option in the right-click menu, the system will open the new Policy window, enter the policy name "Check stored procedure naming specification", in the check conditions drop-down list to choose the condition "stored procedure naming specification", the system will be listed according to the selected check criteria for target By default, every stored procedure for each database is checked, because here we only want to check the TestDB1 database, so we need to create a new database condition.

(4) Click "New Condition" will appear with the new conditions in step (2) the same window, but here we newly created condition is the database, new condition TestDB1,:

(5) Click the OK button to return to the new policy window, which becomes the target for each stored procedure on the TESTDB1 database. To enforce this policy, select the Evaluate mode as "change: Forbidden" and select the "Enabled" check box to enable the policy.

(6) Click the Description Selection page, where you can select a category for the policy, a friendly description that is given when the policy is violated. Finally, click the OK button to complete the creation of your policy.

(7) The next step is to test if the policy is valid, and run the following SQL statement to create a stored procedure usp_getdate:

Use TestDB1
GO
CREATE PROC Usp_getdate
As
SELECT GETDATE ()
GO

Everything is OK, the stored procedure was created successfully. Then create a stored procedure db1_getdate:

Use TestDB1
GO
CREATE PROC Db1_getdate
As
SELECT GETDATE ()
GO

Haha, the system throws an exception:

"Sqlserver:\sql\ms-zy\default\databases\testdb1\storedprocedures\dbo.db1_getdate" has violated policy "Check stored procedure naming specification".
This transaction will be rolled back.
Policy condition: "Lower (@Name) like ' usp[_]% '"
Policy description: "The naming convention for stored procedures in the TESTDB1 database is consolidated in the project, and all stored procedures must start with USP_"
Additional help: "The stored procedure must start with usp_": ""
Statement: "CREATE PROC db1_getdate
As
SELECT GETDATE ()
”。
Msg 3609, Level 16, State 1, procedure sp_syspolicy_dispatch_event, line 65th
The transaction ends in the trigger. The batch has been aborted.

Look again in Object Explorer, the stored procedure is not actually created. You can also create these two stored procedures in a different database, but because the policy is for the TESTDB1 database, the two stored procedures will be created successfully in the other database.

Now with this strategy, you must follow the specifications when you name the stored procedure.

If the policy is defined as "On demand" evaluation mode, then the user can create a stored procedure that violates the policy. To check whether an existing database object conforms to a policy, simply right-click the database Object node in Object Explorer and choose the Evaluate option under policies in the right-click menu, or select the Facet option in the context menu if you want to check the Facet property value for a specific database object.

After selecting the Evaluate option, the system pops up the evaluation policy window, which lists all stored procedure-related policies, selects the policy that needs to be validated, and then clicks the Evaluate button to see if the current database object conforms to the policy.

Here is just a simple example, the function of policy management is much more than the check of the naming specification, and through policy management you can also check whether the database shrinks automatically, the password policy of SQL Server, the size of the network data table, the maximum degree of parallelism ...

Microsoft has provided us with an installation package that includes a number of policy examples to help you learn and use policy-based management:

http://go.microsoft.com/fwlink/?LinkId=123720&clcid=0x804

SQL Server 2008 new features-Policy management