SQL Server 2008 : Policy-based management - Policies in action (part 2) - Creating a database properties policy

9/19/2013 7:32:54 PM

3. Creating a database properties policy

The first step in creating a new policy is to right-click Policies under Policy Management and choose New Policy. You then enter a policy name and either choose an existing condition or create a new condition.

In the example shown in figure 5, we'll create a policy called Database Properties Policy and create a new condition. We'll use the Database facet and specify that both AutoClose and AutoShrink should be false.

Figure 5. After selecting a condition's facet, we build the expression.

After clicking OK, we're returned to the policy definition screen shown in figure 6. Here we select Every Database for the condition's target. For the evaluation mode, we'll choose On Schedule and create a new schedule for Nightly 11PM.

Figure 6. When you're creating a policy, after choosing a condition, you select the condition target and evaluation mode.

Figure 7. SQL Server Management Studio includes visual aids to make policy violations stand out. In this case, the AdventureWorks2008 database has failed a policy.

At this point, the policy is created and will run according to the defined schedule. One of the really useful features of the new SQL Server Management Studio is that it's aware of policy failures. As shown in figure 7, any server and/or database that has failed a policy will be marked with a red cross icon. In this example, the AdventureWorks2008 database is set to AutoShrink and AutoClose, contrary to the policy we just created.

To correct the policy failure, rather than manually setting these two database properties we can simply right-click the database and choose Polices > Evaluate to view the policy failure, and then click Apply to force the server's properties to comply with the policy conditions.

Once created, policies can be easily exported, and doing so enables a number of important management functions.

4. Exporting policies

Policies can be exported in one of two ways. First, you can simply right-click an existing policy and choose Export Policy. The resulting dialog box allows you to select a location in which to save the XML-based policy file.

The other method is based on an instance facet. By right-clicking on a registered SQL Server instance, you can choose Facets. The View Facets window allows you to view the instance properties on a facet-by-facet basis, but more important, you can choose the option Export Current State as Policy.

Figure 8 shows the Server Performance facet of the BNE-SQL-PR-01\SALES instance. By clicking the Export Current State as Policy button, we're able to create a new policy file based on the Server Performance properties of this server.

The importance of this function can't be overstated; essentially, we're able to configure a single server to be exactly how we want all servers to be configured, and then create policies based on the individual facets. Doing so makes the configuration of multiple servers very simple, a process we'll cover next.

Figure 8. The Export Current State as Policy button allows us to create a policy file based on an instance's facet configuration.
Most View
Spring Is Here (Part 2)
Is 802.11ac Worth Adopting?
BlackBerry Z10 - A Touchscreen-Based Smartphone (Part 1)
LG Intuition Review - Skirts The Line Between Smartphone And Tablet (Part 5)
Fujifilm X-E1 - A Retro Camera That Inspires (Part 4)
My SQL : Replication for High Availability - Procedures (part 6) - Slave Promotion - A revised method for promoting a slave
10 Contenders For The 'Ultimate Protector' Crown (Part 3) : Eset Smart Security 6, Kaspersky Internet Security 2013, Zonealarm Internet Security 2013
HTC Desire C - Does It Have Anything Good?
Windows Phone 7 : Understanding Matrix Transformations (part 2) - Applying Multiple Transformations
How To Lock Windows By Image Password
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Top 10
OPEL MERIVA : Making a grand entrance
FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
BMW 650i COUPE : Sexy retooling of BMW's 6-series
BMW 120d; M135i - Finely tuned
PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
Java Tutorials : Nested For Loop (part 1)
C# Tutorial: Reading and Writing XML Files (part 2) - Reading XML Files
C# Tutorial: Reading and Writing XML Files (part 1) - Writing XML Files