Reduce TCO Using Policy-Based Management and Windows PowerShell

1. Required Slide SESSION CODE: DAT311 Reduce TCO Using Policy-Based Management and Windows PowerShell Robert Hutchison Program Manager Microsoft Corporation

2. Agenda • Introduction • Lap around PBM • Lap around PowerShell • PowerShell & PBM • SQL Server R2 Control Point • Wrap up

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

4. 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)

5. Terms and Concepts

6. Policy Example

7. Policy Categories • Policy Categories have 2 purposes • Organization • Scope • Policy belongs to one and only one category • Examples • Audit • Configuration • Maintenance • Security • Performance

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

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

10. Demo Lap around PBM DEMO

11. 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+

12. 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”

13. 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”

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

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

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

17. SQL Cmdlets • SQL Server 2008+ includes the following cmdlets • Invoke-SqlCmd • Invoke-PolicyEvaluation (more on this later) • Encode-SqlName • Decode-SqlName • Convert-UrnToPath

18. Demo Lap around SQL PowerShell DEMO

19. PowerShell & PBM • Capabilities exposed to PowerShell • Creation/Modification • SMO Classes • Navigation • SQLSERVER:\SQLPolicy • Evaluation • Invoke-PolicyEvaluation • Results • EvaluationHistories • EnumPolicyExecutionHistoryDetail • EnumPolicyExecutionHistoryDetailResults

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

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

22. Demo PBM and PowerShell DEMO

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

24. Facet examples • Database • Maintenance • Options • Performance • Security • Server • Configuration • Performance • Security • Settings • Setup • Surface Area

25. Demo Facets DEMO

26. Evaluation Modes

28. Demo PBM In Depth DEMO

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

30. SQL Server Control Point

31. Resource Optimization • Dashboard viewpoints provide insight • Resource Utilization • Policy Violations • Identify consolidation opportunities & at risk resources • Policies • Set desired utilization thresholds • Instances and applications

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

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

34. Required Slide Complete an evaluation on CommNet and enter to win!

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

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

37. Required Slide