Evaluate your Daily Checklist against
100+ instances of SQL Server while
you get a cup of coffee
John Sterrett (@JohnSterrett)
http://johnsterrett.com/go/PBM
• I am not an expert but I stayed at a Holiday Inn once….
• I reside in Wheeling, WV (Pittsburgh, PA) • Work for Orrick Herrington & Sutcliffe LLP
– Responsible 100+ Instances of SQL Server – Responsible for PeopleSoft
• Do you have a daily checklist? • What is your daily checklist?
– Full backups
– Transactional Log backups – Free space
– SQL Agents are enabled – SQL Agent jobs succeeded
• How do you evaluate your daily checklist?
– Central Management Server (CMS) – Policy Based Management (PBM)
Daily Checklist
70% 30%
Do you have a Daily Checklist?
Yes No
Daily Checklist?
20% 19% 17% 19% 12% 8% 5%What do you check in your daily checklist?
Full Backups succeeded Transactional log backups succeeded
SQL Services are running SQL Agent jobs
succeeded
Disks have free space Data and Log files have free space
Daily Checklist?
9% 28% 21% 21% 9% 9% 3%How do you evaluate your daily checklist? Policy-Based Management
and/or Central
Management Server 3rd Party Tool
It's a manual process
We don't have a process to evaluate daily checklists I don't know how to
evaluate a daily checklist SQL Agent Notifications and/or Alerts
Trust but verify…..
• Is the SQL Agent enabled for all servers? • Does email operator exist for all jobs?
• Is the operator the same across all servers? • Is Database Mail enabled for all servers?
• Similar to local server group except it’s a repository for your team.
• Group database servers
– Environment – Version
– Location
• Apply scripts against all servers in a group
• Not all checklist items can be validated with PBM
• Can use SQL Server 2008 R2 Express
• MSDB is used to store servers and groups • Servers – msdb.dbo.sysmanagement_shared_registered_servers_internal • Groups – msdb.dbo.sysmanagement_shared_server_groups_internal
CMS Internals
CMS Security
• Security –
– ServerGroupAdministratorRole role can manage the central management server.
– ServerGroupReaderRole role is required to connect to a central management server.
– Windows Authentication of the individual servers is used to execute t-sql and policies.
• CMS itself cannot automate query execution. • CMS only supports windows authentication • Query results are ordered by server name • Cannot include host server (Unless you use
127.0.0.1 or add port number)
• Query executes for all listed servers. (Includes duplicates)
• Create Configuration Management Server • Create Groups
• Register Servers
• Execute scripts against a group
– Find missing backups
– Find failed SQL Agent Jobs
– Check SQL Agent Service is enabled
Leverage PowerShell to automate CMS
DEMO!
• Policy-Based Management is a system for
managing one or more instances of SQL
Server 2008 (* BOL).
• Some facets allow you to enforce standards with “On-Change – Prevent”
• Can import and export policies
• Apply changes when evaluation fails
• Policies can run against SQL 2000 & 2005
• Prevent things from happening Apply changes • Automate validation of checklists
– Checklists are prone to human error.
– Server configurations can change over time.
• Daily checklist items we will validate
– SQL Agent is enabled – SQL Agent Jobs
– Full backups
– Transactional Log backups – Free Space
• Targets • facets • conditions • policy • categories • server restrictions
Page Verify Policy
Database Target
Database Performance Facet @PageVerify = true Condition
SQL Server 2005 or newer Server Restriction On Schedule Evaluation Mode
PBM Example – Page Verify
• On Demand
– Executed by user or powershell scripts
• On Schedule
– Uses SQL Agent • On Change – Prevent
– Uses DDL Triggers
– * Nested Triggers should be enabled • On Change – Log Only
– uses event notification
• For More: http://msdn.microsoft.com/en-us/library/bb510667%28SQL.100%29.aspx
• Not all facets support On-change. – On Change – Log (17) – On Change – Prevent (12) • Application Role • Asymmetric Key • Database Role • Endpoint • Login Options • Multipart Name
On-Change Evaluation….
• Resource Pool • Schema • Stored Procedure • Table Options• User Defined Function • User Options
• View Options • Workload Group
• Best Practices
– SQL Server 2008 Feature Packs – C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 • Interwebs – http://www.mssqltips.com/category.asp?catid=89 – Blogs – Bing or Google
Message Number Evaluation Mode
34050 On change – prevent when policies are enabled for automatic evaluation.
34051 On change – prevent when the policy is on demand
30452 On Schedule
34053 On change – Log
Policy Alerts
• Views in MSDB database and dbo schema – syspolicy_policies – syspolicy_conditions – syspolicy_system_health_state – syspolicy_categories – syspolicy_policy_category_subscriptions – syspolicy_policy_execution_history – syspolicy_policy_execution_history_details – syspolicy_object_sets – syspolicy_target_sets
For more: http://msdn.microsoft.com/en-us/library/bb510742.aspx
PBM Views
PBM - ExecuteSQL
• The ExecuteSql() function can be used against any facet and it'll be executed once for each
target in the target set.
• The proxy account is called
##MS_PolicyTsqlExecutionLogin## used for scheduled jobs
• http://blogs.msdn.com/b/sqlpbm/archive/2008/07 /03/executesql.aspx
• Cannot modify or create facets
• Policies fail to read mount points as separate locations
• ExecuteSQL only supports @ObjectName and @SchemaName
• Reporting is very limited out of the box • On Change-Prevent is limited
• Best Practice Policies may not be best practice for your shop.
• Create a Category • Import Policies • Export Policies • Create Policy • Evaluate Policy – On-Demand – Scheduled – On Change – Prevent – On Change – Log – Apply Policy
• Evaluate Policy on Multiple Servers using CMS
• View Policy History
• http://epmframework.codeplex.com/ • EPMF leverages
– Central Management Server – Policy-Based Management – PowerShell
– XML
– SQL Server Reporting Services
• Show EMPF…
• All reference material can be found at http://johnsterrett.com/go/PBM
– Books On Line
• Administering Servers by Using Policy-Based Management – Book
• Pro SQL Server 2008 Policy-Based Management – White Papers
• Enterprise Policy Management Framework with SQL Server 2008 • SQL Server 2008 Policy-Based Management
– Web
• www.mssqltips.com
Notes (hidden)
• Some speakers may use this slide for hidden notes
• Please delete if you prefer not to use
• Please note you are also able to use notes section for each slide
Please Complete the Evaluation Form
Pick up your evaluation form:
• In each presentation room
Drop off your completed form
• Near the exit of each presentation room • At the registration area
Session Title 35
THANK YOU!
For attending this session and PASS SQLRally Orlando, Florida
Session Code | Session Title36
