Reduce tco using policy based management and windows powershell l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 37

Reduce TCO Using Policy-Based Management and Windows PowerShell PowerPoint PPT Presentation


  • 177 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

Reduce TCO Using Policy-Based Management and Windows PowerShell

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

SESSION CODE: DAT311

Reduce TCO Using Policy-Based Management and Windows PowerShell

Robert Hutchison

Program Manager

Microsoft Corporation


Agenda l.jpg

Agenda

  • 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)


Terms and concepts l.jpg

Terms and Concepts


Policy example l.jpg

Policy Example


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

Demo

Lap around PBM

DEMO


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

Cmdlets

  • 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

Providers

  • 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

  • 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:\SQL

    • 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

Demo

Lap around SQL PowerShell

DEMO


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

SQLSERVER:\SQLPolicy

  • 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

Invoke-PolicyEvaluation

  • 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

Demo

PBM and PowerShell

DEMO


Facets l.jpg

Facets

  • 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

Demo

Facets

DEMO


Evaluation modes l.jpg

Evaluation Modes


Slide28 l.jpg

Demo

PBM In Depth

DEMO


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


Sql server control point l.jpg

SQL Server Control Point


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

Resources

Learning

  • Sessions On-Demand & Community

  • Microsoft Certification & Training Resources

www.microsoft.com/teched

www.microsoft.com/learning

  • Resources for IT Professionals

  • Resources for Developers

  • http://microsoft.com/technet

  • http://microsoft.com/msdn


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

http://northamerica.msteched.com/registration

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.


Slide37 l.jpg

Required Slide


  • Login