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?
- Slides: 20