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

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


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


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


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?

  • 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


Policy Example


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

  • 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

  • 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


Demo

Lap around PBM

DEMO


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

  • 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

  • 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

  • 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

  • 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

  • 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

  • SQL Server 2008+ includes the following cmdlets

    • Invoke-SqlCmd

    • Invoke-PolicyEvaluation (more on this later)

    • Encode-SqlName

    • Decode-SqlName

    • Convert-UrnToPath


Demo

Lap around SQL PowerShell

DEMO


PowerShell & PBM

  • Capabilities exposed to PowerShell

    • Creation/Modification

      • SMO Classes

    • Navigation

      • SQLSERVER:\SQLPolicy

    • Evaluation

      • Invoke-PolicyEvaluation

    • Results

      • EvaluationHistories

      • EnumPolicyExecutionHistoryDetail

      • EnumPolicyExecutionHistoryDetailResults


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

  • 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


Demo

PBM and PowerShell

DEMO


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

  • Database

    • Maintenance

    • Options

    • Performance

    • Security

  • Server

    • Configuration

    • Performance

    • Security

    • Settings

    • Setup

    • Surface Area


Demo

Facets

DEMO


Evaluation Modes


Demo

PBM In Depth

DEMO


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


Resource Optimization

  • Dashboard viewpoints provide insight

    • Resource Utilization

    • Policy Violations

  • Identify consolidation opportunities & at risk resources

  • Policies

    • Set desired utilization thresholds

    • Instances and applications


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


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


Required Slide

Complete an evaluation on CommNet and enter to win!


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


© 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.


Required Slide


  • Login