Use CMS and PBM to Manage your SQL

Use CMS and PBM to Manage your SQL Servers Central Management Server Policy Based Management

About me Mitchell Bottel Ø Sr SQL Server DBA – DHCS Ø Owner Innovative IT Consulting, Inc. Ø Idera ACE ØAdvisors and Community Educators Ø 10+ years in IT Ø Member Placer County Search and Rescue

Let’s Make this Simple Ø Ø What is CMS? What is PBM? Demo Questions

How do you Manage your Current Environments? � Daily Monitoring � Auditing � Reporting � Daily Checklists

What is CMS? Ø Central Repository for connection information for all your SQL servers Ø Data is housed in MSDB Ø Must be on a SQL 2008 Instance ØNot Express Edition Ø You can register SQL 2000 and 2005 instances ØAll editions are supported including Express

Advantages Ø Repository that all your admins can use Ø Allows you to run a query against multiple servers simultaneously Ø Allows you to group servers logically. Ø Allows you to evaluate PBM policies against groups of servers (we’ll get to that)

Disadvantages Ø The CMS server cannot be a part of its own group. Workaround is to use 127. 0. 0. 1 (localhost) or ‘Instance, Port’ Ø Registered servers can only use Windows authentication. ØWhy is this bad you ask? ØCross domains?

Let’s take a look!

What is PBM? Ø A system for managing one or more instances of SQL Server 2008 (BOL) Ø You can evaluate policies against SQL 2000 and 2005 instances, but SQL 2008 is required for the policy server

PBM Terms � Policy – the container � Facet – collection of related properties for an object � Condition – question you are asking � Target – entity you are checking for compliance � Category – logical grouping of policies

What’s the Purpose of PBM? Ø Ø Ø Consistency Across Instances Apply policies across multiple servers Administrator Control Compliance Prevent and Notify Changes NO COST!

DEMO

EPM Framework Ø Code. Plex Ø Uses (www. codeplex. com) Power. Shell, SQLAgent & SSRS

New in SQL 2012 Ø Not Much! ØMore facets – 86

Resources Jorge Segarra - Expert ◦ http: //sqlchicken. com/ PBM on MSSQLTips ◦ http: //www. mssqltips. com/category. asp? catid=89 SQLPBM Blog ◦ http: //blogs. msdn. com/sqlpbm/default. aspx MSDN ◦ http: //msdn. microsoft. com/enus/library/bb 510667. aspx

Great Reference

� July 28 th in Sacramento! � Looking � Come � Help for Speakers and Sponsors! Attend!! us spread the word!

How to Contact Me? ØEmail: mitch@iitconsulting. com ØTwitter: @Sac. SQLDude ØBlog: www. mitchespitch. com ØEval Forms

Questions?