Policy-Based Management in SQL Server 2008

Some time ago I promissed publishing a post on Policy-Based Management feature in SQL Server 2008. Well, it's time to fulfil my promise! :) This is a rather large post, I hope you find the information it contains useful!

Policy-Based Management is indeed one of the greatest administration features in SQL Server 2008. With this feature you can easily manage one or more instances of SQL Server!

The main concept in Policy-Based Management, is that Policy Administrators can set up policies that are evaluated upon certain events within the scope of the monitored SQL Server instance(s). After setting up the policies, based on certain scenarios, the policies are evaluated and specific actions are automatically performed as set up in the active Policies. 

To this end, there are four evaluation modes:

• On demand - This mode evaluates the policies when explicitly requested by the user.
• On change: prevent - This mode uses DDL triggers in order to prevent Policy violations.
• On change: log only - This mode allows Policy violations but it logs each violation using event notification. It logs the violations to the SQL Server logs and Windows Application logs.
• On schedule - This mode allows the user to schedule (using a SQL Agent job) when the policies will be evaluated.

Let’s get a first look and feel at the Policy-Based Management set up dialogs in SSMS. In the following screenshot you can see the Policy Management module in the Object Explorer in SSMS:

• When right-clicking on Policies you can create a new Policy.
• When right-clicking on Conditions you can create a new condition and then assign it to an existing or a new Policy.
• The Facets is a collection of SQL Server objects that allow you to set up conditions and policies against their properties. By right-clicking on a Facet you can create a condition or a Policy.

Note that the Policy Management module falls under Management as its scope is for the entire SQL Server instance. Of course, via the Policies you create, you can evaluate multiple SQL Server instances, certain databases, even certain database objects!

OK! Now let's proceed with an example, showing how we can set up policies based on the abovementioned evaluation modes. For this post, I will be using the "AdventureWorks2008" sample database.

On demand
We right-click on Policies and we select New Policy. We are presented with the following dialog where we provide a name for the Policy and then we either select the check condition or we create a new one. In this case we will be creating a new condition:
 

The next screenshot shows the newly created condition:

For creating a new condition, you must provide a name, select the Facet (in this example) the Stored Procedure, and set the expressions for evaluating the properties you like. In this case we want the condition to track all the stored procedures that their names start with 'sp_'.

We click on the OK button and we are returned to the Policy:

You can see that the Policy now contains the "SPNames" condition we created in the previous step. Also, I set up the Evaluation Mode to "On Demand". Though, this type of condition allows you to select any Evaluation Mode.

The available Evaluation Modes are dependent on the type of the condition. For example there are conditions for which a DDL trigger cannot be executed so the “On change: prevent” mode cannot be selected in such case, etc.

Also, in the “Against targets” list you can target specific databases against which the condition will be evaluated. Additionally, in the“Server restriction” you can select against which SQL Server instances the condition will be executed. Remember: Policy-Based Management allows managing multiple instances of SQL Server!

Now, let’s set the Policy to only evaluate the “AdventureWorks2008” database by clicking on the “In Every - Database” list item. Here’s the new condition for restricting the Policy’s evaluation on the specific database:

We click on OK and we are returned back to the Policy properties dialog:

I will not add a condition for the Server restriction as I am only using a single SQL Server instance for this post.

That’s it! We click on OK and the Policy is ready! As this Policy’s Evaluation Mode has been set to “On demand” we cannot enable it. It wouldn’t make any sense after all. We can only use it by right-clicking on it and selecting “Evaluate”. This is the purpose of the On Demandevaluation mode after all! :)

After running the Policy we get its evaluation results:

From the above screenshot we can conclude that at the time being all stored procedures within the “AdventureWorks2008” database comply with the condition set up in the Policy.

Now, what about adding a new procedure on the “AdventureWorks2008” database having the name “sp_SampleSP”? Let’s do so, re-run the Policy and check the evaluation results!

Here are the results:

Oops, we got an error message by the Policy. Let’s click on [Details] View… to see what’s wrong:

We can see that the Policy reported that the stored procedure “sp_SampleSP” does not comply with its condition of not having stored procedures with names starting with 'sp_'.

The above is a very simple example of what a Policy can do. Imagine what else you can do with Policy-Based Management thus making your life easier! :)

Let’s delete now the sample stored procedure we earlier created. We need to comply after all! :)

On change: prevent
If we want to change the Evaluation Mode of the above Policy to “On change: prevent”, we can double-click on the Policy, change it and then enable the Policy:

Now let’s try to create again the same stored procedure:

As you can see from the above screenshot, the Policy prevented the Database Engine from creating the stored procedure as it does not comply with the naming condition specified by the Policy!

On change: log only
Similarly, we can change the Evaluation Mode of the Policy to “On change: log only”. This will allow the stored procedure to be created but it will also add a log entry in SQL Server Logs and Windows Application Log reporting the violation of the Policy:

On schedule

Last but not least, you can set up a schedule for when the Policy should be executed. This will create a SQL Server Agent job that will execute the Policy at the selected time.

Summary

This post was a very basic example on how you can use Policy-Based Management in SQL Server 2008. You can build really advanced Policy scenarios where you can have multiple Policies and conditions, evaluating a large set of actions in more than one SQL Server instances.

The Policy-Based Management feature in SQL Server 2008 is extremely powerful and allows setting up strict Compliance scenarios ensuring the health and desired structure of the monitored SQL Server instances and the databases that exist within them.

The best way to learn more about Policy-Based Management in SQL Server is to get experimented with it in order to fully realize its potentials.

You can also check out this link for more information on Policy-Based Management in SQL Server.

Until next time!


Source: 
http://aartemiou.blogspot.com/2010/04/policy-based-management-in-sql-server.html

Using Policy Based Management in SQL Server 2008

ProblemAs we are reviewing the new features in SQL Server 2008, we found one that looks really interesting - Policy-Based Management.  Could you help us to understand how this works and provide some examples?  Can you please explain each of the components and how to manage them in the interface and with commands?

SolutionPolicy-Based Management is indeed a new feature in SQL Server 2008.  It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.  Originally this feature was called the Declarative Management Framework but has since been renamed.  There are a number of terms that we need to define in order to begin to understand Policy-Based Management:

• Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
• Facet - a predefined set of properties that can be managed
• Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
• Policy - a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS).  Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes:

Expand the Facet node to see the list of facets:

As you can see there is a rather comprehensive collection of facets predefined in SQL Server 2008, allowing you to manage just about every aspect of SQL Server.  Double click on a facet to see the actual list of properties in the facet; e.g. double click the Database facet:

These facet properties are used to specify a condition; e.g. AutoShrink = False means that you do not want to automatically shrink database files.  A policy specifies an expression that evaluates to True or False.  The expression can be made up of one or more conditions logically joined by And / Or.

In this tip we are going to gain an understanding of Policy-Based Management by walking through the following demonstration:

• Create a Condition
• Create a Policy
• Evaluate a Policy

The demo steps below were only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Create a Condition

The starting point in Policy-Based Management is to create a Condition.  Right click on Conditions in the SSMS Object Explorer (under the Management | Policy Management node) then select New Condition from the menu.  Fill in the dialog as follows:

You select a single Facet for a Condition, then enter an Expression.  The Expression evaluates to either True or False.  This is the essence of Policy-Based Management which will test whether the Condition is True.

Create a Policy

Right click Policies in the SSMS Object Explorer (under the Management | Policy Management node) then select New Policy from the menu.  Fill in the dialog as follows:

The Check Condition drop down will include the list of conditions that you have defined.  You can check Every Database in the Against targets list, or you can click the glyph (between Every and Database) and define a condition.   Execution Mode can have one of the following values:

• On Demand (this is the default)
• On Schedule
• On Change - Log Only
• On Change - Prevent

The On Demand option only evaluates the policy when a user right clicks on the policy in the SSMS Object Explorer and selects Evaluate from the menu. 

The On Schedule option takes advantage of SQL Agent to execute a job on a particular schedule to check the policy.  After selecting On Schedule from the Execution Mode drop down list, you can click either the Pick or New button.

To pick an existing schedule, make a selection from the available options:

To create a new schedule, fill in the familiar schedule dialog:

When policy evaluation is scheduled, any violations are logged to the Windows Event Log.

The On Change - Log Only option evaluates the policy whenever the property in the facet is changed and any violation is logged to the Windows Event Log.  The On Change - Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this option uses DDL triggers to enforce the policy.  Not all changes can be detected and rolled back by DDL triggers; the Execution Mode drop down list will include the On Change - Prevent option only when it is available.

One final note on the policy setup concerns the Enabled check box.  When the Execution Mode is On Demand, the Enabled check box must be unchecked; for all other options you must check the Enabled check box in order for the policy to be evaluated.

Evaluate a Policy

To evaluate a policy on demand, right click on the policy in the SSMS Object Explorer and select Evaluate from the menu.  The following is a partial screen shot of the output from evaluating a policy on demand:

 

The green check icon signifies that the policy evaluated to True for the databases shown.  Not shown above is a Configure button that allows the user to automatically fix a target where the policy evaluates to False.

Right click on a database in the SSMS Object Explorer and select Properties from the menu.  Click the Options page and change the AutoShrink property to True.  Evaluate the policy again and you will see the following output:

Note the red icon with the X indicating that policy evaluation failed for a particular database.  Not shown above is the Configure button which you can click to automatically change the AutoShrink property to comply with the policy.

Edit the policy and change the Execution Mode to On Change - Log Only.  Select a database and change the AutoShrink property to True.  Open the Windows Event Viewer, click on Application and you will see an event that was written when the policy evaluation detected the violation:

To test the On Change - Prevent Execution Mode for a policy, create a new condition and a new policy.  Create a new condition as follows:

Now create a new policy as follows:

This policy will prevent a table from being created if the table name does not begin with 'tbl_'.  Open a New Query window in SSMS and enter a create table script.  When you execute the CREATE TABLE script you will get the following error message and the table will not be created:

CREATE TABLE sample ( message varchar(256) ) Policy 'Table Prefix Must Be tbl_' has been violated by '/Server/(local)/Database/demo/Table/dbo.sample'.This transaction will be rolled back. Policy description: ''Additional help: '' : ''. Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50 The transaction ended in the trigger. The batch has been aborted.

Source:

http://www.mssqltips.com/sqlservertip/1492/using-policy-based-management-in-sql-server-2008/

Managing and Connecting to AlwaysOn Availability Groups

From the previous AlwaysOn Availability Group article, we provided a name for the availability group listener which is simply a unique DNS name as a Virtual Network Name (VNN) to direct read-write requests to the primary replica and read-only requests to the read-only secondary replica. In this way the client doesn’t need to know which physical server is hosting the primary replica and which is hosting the secondary replica for the read-only load.

A client connects to the current primary replica using the availability group listener for a read-write operation, for read-only it connects using the availability group listener but this time it also needs to provideApplicationIntent=ReadOnly in the connection string. In this way the read-only requests are routed to the readable secondary replica.

Connecting to The Primary Replica Using The Physical Server Name

Not only you can use an availability group listener to connect the replica/instance but if you know the physical server name, you can directly connect to it. If you expand Availability Groups node under AlwaysOn High Availability node, you will be shown the current instance is the primary replica (ARSHAD-PC in this example) whereas another instance (ARSHAD-LP) is the secondary replica as shown below:

Connecting To The Secondary Replica Using Physical Server Name

The way you can connect to primary replica using physical server name, you can also connect to secondary replica using the physical server name. If you expand Availability Groups node under AlwaysOn High Availability node, you can notice the current instance is the secondary replica (ARSHAD-LP) whereas another instance (ARSHAD-PC) is the primary replica as shown below:

Connecting To The Availability Group Using The Availability Group Listener

As shown below, you can see that I connected to availability group using the listener which directs connections to appropriate replica based on the type of connection I am making.

Managing And Monitoring Availability Groups

There are several ways to effectively manage and monitor availability groups, replicas and databases in SQL Server 2012 but the most efficient is the inbuilt dashboard which displays information in very a intuitive way for high-level monitoring and management. In addition, you can also use the newly introduced catalog views and dynamic management views, extended events as well as system performance counters.

Viewing The Availability Group information Dashboard

To the view availability group dashboard, connect to the server/replica (using physical server name or availability group listener), expand the nodes and then right-click on availability group node and click on Show Dashboard as shown below:

Clicking on the Show Dashboard as above will open the selected availability group dashboard. The dashboard displays primary replica and all the secondary replicas and its synchronization status along with overall health indicators as shown below. If there is any failure or any exception, the dashboard displays the messages or links for details message and what can be done to resolve the issues:

Monitoring Health Events

Every events on the the availability groups are tracked by SQL Server which can be very helpful for troubleshooting. On the top right of the availability group dashboard you can see these two links, click View AlwaysOn Health Events link to display health events:

Clicking on View AlwaysOn Health Events will open a grid-view list for all the events tracked to date, you can sort these events by name or by timestamp by clicking on the column heading. Clicking/selecting on each event row will display the details on that particular event in the bottom section of page as shown below:

SQL Server 2012 has now two more SQL Server performance objects for performance monitoring:

• SQLServer:Availability Replica – contains performance counters for reporting information about the availability replicas in availability groups.
• SQLServer:Database Replica – contains performance counters for reporting information on the secondary databases on secondary replica of the availability group.

For those who prefer T-SQL, SQL Server 2012 also introduces several new catalog views and dynamic management views and extended events for managing and monitoring availability groups, replicas and databases, to learn more on these please check out msdn.

Manual Failover

You can configure your availability group for automatic failover or for manual failover. In case of automatic failover if the primary replica/instance does down the AlwaysOn availability group switches the role of primary replica/instance to another synchronous secondary replica.

You can also elect for manual failover. The failover will occur without data loss if both primary and secondary replicas are in synchronous commit mode and are in a healthy state, data loss will occur if the secondary replica is in asynchronous commit availability mode. To do a manual failover using the wizard, click on the Start Failover Wizard link on the top right corner of the availability group dashboard as shown below:

This will launch the failover wizard, the first screen of the wizard is the welcome screen so click Next to proceed:

On the Select New Primary Replica page of the Failover Availability Group wizard, you need to specify the secondary replica which will be the new primary replica after the failover, the screen will also display the failover readiness and availability mode (synchronous or asynchronous). Click next continue:

On the Connect to Replica page of the wizard, you need to connect to the secondary replica which you want to make the primary replica after failover, click on the Connect button to get connected to the secondary replica:

The Summary page of the Failover Availability Group wizard will display a summary on your selection and actions that wizard is going to perform. Click on Next to failover. You can also generate the failover T-SQL script by clicking on Script button at the bottom:

Clicking on Finish will start switching the role or performing the failover, you can monitor the progress and overall of status of operation as shown in screen below:

If I open the availability group dashboard again or refresh it, I can see role has now, my earlier primary replica (ARSHAD-PC) is now the secondary replica whereas my earlier secondary replica (ARSHAD-LP) is now the primary replica:

Please note, this example and demonstration is based on SQL Server 2012 RC0 and there might be slight changes in the RTM release; refer to BOL (Books online) or msdn for updated information.

Summary

In this article I discussed the AlwaysOn availability group feature of SQL Server 2012. AlwaysOn is a new High Availability (HA) and Disaster Recovery (DR) solution in SQL Server 2012 which improves high availability and protects data of your mission critical applications. AlwaysOn availability group is recommended over database mirroring since it combines the best of failover clustering and database mirroring and overcomes the several limitations imposed in failover clustering or database mirroring setup.

Source:

http://www.sql-server-performance.com/2012/managing-and-connecting-to-alwayson-availability-groups/

http://www.sql-server-performance.com/2012/managing-and-connecting-to-alwayson-availability-groups/2/