Reduce tco using policy based management and windows powershell
1 / 37

- PowerPoint PPT Presentation

  • Uploaded on

Required Slide. SESSION CODE : DAT311. Reduce TCO Using Policy-Based Management and Windows PowerShell . Robert Hutchison Program Manager Microsoft Corporation. Agenda. Introduction Lap around PBM Lap around PowerShell PowerShell & PBM SQL Server R2 Control Point Wrap up.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '' - destiny

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Reduce tco using policy based management and windows powershell l.jpg

Required Slide


Reduce TCO Using Policy-Based Management and Windows PowerShell

Robert Hutchison

Program Manager

Microsoft Corporation

Agenda l.jpg

  • Introduction

  • Lap around PBM

  • Lap around PowerShell

  • PowerShell & PBM

  • SQL Server R2 Control Point

  • Wrap up

Policy based management l.jpg
Policy Based Management

  • Availability in SQL 2008+

  • Enables database administrators to manage SQL Server by intent

  • DBAs declare the desired state of the SQL Server environment

    • Automatic or manual checking/enforcement of compliance

    • Automation of configuration checklists

  • The desired state and the rules by which it is enforced is known as Policy

How does policy help with tco l.jpg
How does Policy help with TCO?

  • Contributors to high TCO

    • Availability issues

      • Downtime, outage & failure expenses

    • Performance issues

      • diminish money making ability

    • Security issues

      • breach, reputation, recovery & prevention

    • Capacity issues

      • Under/Overutilized resources

  • PBM can help with this

    • PBM enforces this (declarative)

    • PowerShell automates this (scale)

Policy categories l.jpg
Policy Categories

  • Policy Categories have 2 purposes

    • Organization

    • Scope

  • Policy belongs to one and only one category

  • Examples

    • Audit

    • Configuration

    • Maintenance

    • Security

    • Performance

Policy examples l.jpg
Policy Examples

  • Security

    • Restrict authentication mode for a SQL Server Instance to Windows Authentication

    • Checks whether password policy enforcement on SQL Server logins is enabled

  • Maintenance

    • Check if database and the backups are on separate backup devices

    • Check whether the database has suspect database pages

  • Performance

    • Check an instance of SQL Server having processors that are assigned with both the affinity mask and the affinity I/O mask options

  • Extensible

Creating evaluating policies l.jpg
Creating & Evaluating Policies

  • Policies are created and managed using Management Studio

  • Steps to creating a Policy

    • Select a ManagementFacet that contains the properties of interest

    • Define a Condition that specifies the state of the Facet

    • Define a Policy that contains the Condition

    • Specify the Target Set for the Policy

    • Specify the Evaluation Mode

  • Evaluating a Policy

    • This can be done manually or on a schedule

Slide10 l.jpg


Lap around PBM


Windows powershell l.jpg
Windows PowerShell

  • A revolutionary interactive shell and scripting language built on top of .NET

  • Powerful Windows Administration

  • Uses real objects in a “pipeline”

  • Designed for automation of the Windows environment

  • Standard component of Windows 2008 R2+ and Windows 7+

Cmdlets l.jpg

  • Name is always verb-noun, to enable users to “figure out” functionality from name

  • 150+ default cmdlets ship with PowerShell

  • You can also write your own

  • UseGet-Commandto list all cmdlets

  • Common Syntax:verb-noun –parameter1 argument1 –parameter2 argument2 -switchParameter

  • Can be “piped”

Providers l.jpg

  • The file system is just another “provider”

  • Other providers include:

    • The Registry

    • Certificates

    • Active Directory

    • SQL Server

    • You can write your own

  • Cmdlets provide consistent behavior, regardless of provider

  • A provider has “drives”

Sql powershell integration l.jpg
SQL PowerShell Integration

  • SQL 2008+ provide PowerShell integration

  • SQL Provider

    • Exposes the hierarchy of SQL Server objects

    • Similar to file system hierarchy

  • SQL Cmdlets

  • SQLPS.exe

    • PowerShell console that loads SQL snapins

    • Launch from standalone or from Management Studio

    • Integrated with SQL Agent

Sqlps exe l.jpg

  • SQLPS.exe is included in SQL 2008+

  • Starts a PowerShell session with the SQL Server PowerShell provider

  • PowerShell can be invoked 4 ways on a SQL Server installation

    • Launching SQLPS.exe directly

    • Launching from Management Studio

    • Launching from SQL Server Agent job

    • Launching PowerShell directly

Sql provider l.jpg
SQL Provider

  • SQL Provider has the following sub-folders


    • SQLSERVER:\SQLPolicy (more on this later)

    • SQLSERVER:\SQLRegistration

    • SQLSERVER:\DataCollection

    • SQLSERVER:\Utility

  • Included via the SqlServerProviderSnapin100 snapin

Sql cmdlets l.jpg
SQL Cmdlets

  • SQL Server 2008+ includes the following cmdlets

    • Invoke-SqlCmd

    • Invoke-PolicyEvaluation (more on this later)

    • Encode-SqlName

    • Decode-SqlName

    • Convert-UrnToPath

Slide18 l.jpg


Lap around SQL PowerShell


Powershell pbm l.jpg
PowerShell & PBM

  • Capabilities exposed to PowerShell

    • Creation/Modification

      • SMO Classes

    • Navigation

      • SQLSERVER:\SQLPolicy

    • Evaluation

      • Invoke-PolicyEvaluation

    • Results

      • EvaluationHistories

      • EnumPolicyExecutionHistoryDetail

      • EnumPolicyExecutionHistoryDetailResults

Sqlserver sqlpolicy l.jpg

  • All PBM objects are available in this folder

  • Navigate to PBM objects under each instance

    • Set-Location SQLSERVER:\SQLPolicy\<ComputerName>\<InstanceName>

  • Available objects

    • Conditions

    • ObjectSets

    • Policies

    • PolicyCategories

    • PolicyCategorySubscriptions

Invoke policyevaluation l.jpg

  • Cmdlet for invoking a Policy Evaluation 

  • Can be used to invoke Policies

    • Stored in SQL Server

    • Stored in XML files

  • Can specify evaluation modes

    • Check

    • CheckSqlScriptAsProxy

    • Configure

  • Targets

    • TargetServerName

    • TargetExpression

Slide22 l.jpg


PBM and PowerShell


Facets l.jpg

  • PBM relies heavily on facets

  • Facets enable the following:

    • Create a single view of all physical properties for an object

    • Create specific views of object properties (i.e. security, performance etc.)

    • Create logical properties derived from physical properties

  • Facets can be on one or more objects (target types)

  • Objects (target types) can have one or more facets

Facet examples l.jpg
Facet examples

  • Database

    • Maintenance

    • Options

    • Performance

    • Security

  • Server

    • Configuration

    • Performance

    • Security

    • Settings

    • Setup

    • Surface Area

Slide25 l.jpg




Slide28 l.jpg


PBM In Depth


Sql server r2 sql server control point l.jpg
SQL Server R2 – SQL Server Control Point

  • SQL Server Control Point is a new feature in R2

  • Provides insight into resource utilization through policy evaluation

  • Extends Policy-Based Management to the SQL Server Control Point

  • Set policies to define desired utilization thresholds across target servers or applications

Resource optimization l.jpg
Resource Optimization

  • Dashboard viewpoints provide insight

    • Resource Utilization

    • Policy Violations

  • Identify consolidation opportunities & at risk resources

  • Policies

    • Set desired utilization thresholds

    • Instances and applications

Dat track scratch 2 win l.jpg

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

DAT Track Scratch 2 Win

  • Find the DAT Track Surface Table in the Yellow Section of the TLC

  • Try your luck to win a Zune HD

  • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

Resources l.jpg

Required Slide



  • Sessions On-Demand & Community

  • Microsoft Certification & Training Resources

  • Resources for IT Professionals

  • Resources for Developers



Slide34 l.jpg

Required Slide

Complete an evaluation on CommNet and enter to win!

Slide35 l.jpg

Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

Slide36 l.jpg

© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.