Evaluate your Daily Checklist against 100+ instances of SQL Server while you get a cup of coffee

36 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

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

(2)

• 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

(3)

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

(4)

Daily Checklist

70% 30%

Do you have a Daily Checklist?

Yes No

(5)

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

(6)

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

(7)
(8)

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?

(9)

• 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

(10)
(11)

• 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

(12)

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.

(13)
(14)

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

(15)

• 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

(16)

Leverage PowerShell to automate CMS

(17)

DEMO!

(18)

• 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

(19)

• 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

(20)

• Targets • facets • conditions • policy • categories • server restrictions

(21)

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

(22)

• 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

(23)

• 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

(24)

• 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

(25)

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

(26)

• 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

(27)

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

(28)

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

(29)

• 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

(30)

• http://epmframework.codeplex.com/ • EPMF leverages

– Central Management Server – Policy-Based Management – PowerShell

– XML

– SQL Server Reporting Services

(31)
(32)

• Show EMPF…

(33)

• 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

(34)

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

(35)

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

(36)

THANK YOU!

For attending this session and PASS SQLRally Orlando, Florida

Session Code | Session Title36

Figure

Updating...

References

Updating...

Related subjects :