CMS PBM EPM Central Management Server Policy Based

CMS, PBM & EPM Central Management Server Policy Based Management Enterprise Policy Management Framework Presented by: Nancy Hidy Wilson

Agenda � Objective � Central Management Server � Policy Based Management � Enterprise Policy Management Framework � Q&A � Additional Resources � About Me / Contact Info Nancy Hidy Wilson 12/3/2020 2

Objective �Learn how to leverage CMS, PBM and EPM Framework to secure your servers and your job! Nancy Hidy Wilson 12/3/2020 3

Central Management Server What is it? Implementation Security Caveats Nancy Hidy Wilson 12/3/2020 4

CMS – What is it? �SQL Server 2008+ �Purpose: ◦ A repository of registered servers organized by server groups contained in the msdb database of the specified SQL Server instance designated as the CMS. �Enables sharing of registered serversgroups �Enables multi-instance T-SQL queries in SSMS �Enables multi-instance evaluation of policies Nancy Hidy Wilson 12/3/2020 5

CMS – Implementation – Step 1 � SSMS – View Registered Servers Database Engine Central Management Servers (Right-Click and select Register…) Where to find it What it looks like Nancy Hidy Wilson 12/3/2020 6

CMS – Implementation – Step 2 �Define manually via SSMS or programmatically: ◦ Tables: �dbo. sysmanagement_shared_server_groups_internal �dbo. sysmanagement_shared_registered_servers_internal ◦ Stored Procedures: �dbo. sp_sysmanagement_add_shared_server_group �dbo. sp_sysmanagement_add_shared_registered_server Nancy Hidy Wilson 12/3/2020 7

CMS – Security �Security – msdb Database Roles ◦ Server. Group. Administrator. Role ◦ Server. Group. Reader. Role Nancy Hidy Wilson 12/3/2020 8

CMS - Caveats �Windows Authentication Only �Can’t sort results in SSMS client ◦ Results are evaluated and returned per instance �Version sensitive ◦ If a different number of columns may be returned by different versions, then the first instance which is returned dictates the expected columns from additional instances. Additional columns may be discarded an error occurs. Nancy Hidy Wilson 12/3/2020 9

CMS Demo Nancy Hidy Wilson 12/3/2020 10

Policy Based Management What is it? Terminology Implementation Security Caveats Nancy Hidy Wilson 12/3/2020 11

PBM – What is it? �SQL Server 2008+ �Purpose: ◦ Allows management of properties of instances, databases and other SQL Server entities. ◦ Replaces Best Practices Analyzer tool Nancy Hidy Wilson 12/3/2020 12

PBM Terminology �Managed �Facet Target �Condition �Policy Category Nancy Hidy Wilson 12/3/2020 13

PBM Implementation �Policies default* are not installed by ◦ Import from C: Program Files (x 86)Microsoft SQL Server100ToolsPoliciesDatabase. Engine1033 ◦ Subfolders for SSRS and SSAS under the Policies folder �Extensible policies – create your own *SQL Server 2012 does install default policies for HADR Nancy Hidy Wilson 12/3/2020 14

PBM Security �Security – msdb Database Role ◦ Policy. Administrator. Role Nancy Hidy Wilson 12/3/2020 15

PBM Caveats � Only manual evaluation of policies for SQL 2000/2005 Nancy Hidy Wilson 12/3/2020 16

PBM - Demo Nancy Hidy Wilson 12/3/2020 17

Enterprise Policy Management Framework What is it? Implementation Reports Nancy Hidy Wilson 12/3/2020 18

EPM – What is it? �Available from http: //epmframework. codeplex. com �Purpose ◦ Reporting solution for policy evaluation ◦ Enables automated evaluation of policies for all versions of SQL Server (2000/2005/2008 R 2…) Nancy Hidy Wilson 12/3/2020 19

EPM - Implementation �Requires SQL Server 2008* Database Engine instance for: ◦ Central Management Server (CMS) ◦ PBM Policies ◦ Executing Power. Shell script (1. 0 minimum) via SQLAgent job ◦ Management Database to store results �Requires SSRS 2008 *Minimum SP 1 CU 3 (but Microsoft support ended 10/11/2011) Nancy Hidy Wilson 12/3/2020 20

EPM - Configuration � Set variables for your environment in EPM_Create_Database_Objects. sql ◦ : SETVAR Server. Name “WIN 2008" ◦ : SETVAR Management. Database "PDW" � Set variables for your environment in EPM_Enterprise. Evaluation_3. 0. 0. ps 1 ◦ $Central. Management. Server = "WIN 2008" ◦ $History. Database = “PDW" ◦ $Result. Dir = “C: Results" � Be sure to create your $Result. Dir! Nancy Hidy Wilson 12/3/2020 21

EPM - Scheduling � In SQLAgent Job Power. Shell step: ◦ SL "Insert script folder location” ◦. EPM_Enterprise. Evaluation_3. 0. 0. ps 1 Configuration. Group "Insert Central Management Server Group" -Policy. Category. Filter "Insert Policy Category" –Eval. Mode “Check” � You will likely need to setup a Proxy account to run these steps which has access to all the target servers in the CMS Group. Nancy Hidy Wilson 12/3/2020 22

EPM - Reports �Provides default SSRS reports �Build your own Nancy Hidy Wilson 12/3/2020 23

EPM - Demo Nancy Hidy Wilson 12/3/2020 24

Q&A What else do you want to know? Nancy Hidy Wilson 12/3/2020 25

Additional Resources �Microsoft SQL Server BOL (2008/2008 R 2/2012) �Microsoft Certified Master Readiness Videos http: //technet. microsoft. com/en-us/sqlserver/ff 977043 ◦ Multi-Server Management Lecture & Demo ◦ Policy-Based Management Lecture & Demo Nancy Hidy Wilson 12/3/2020 26

About Me � Database Technologist in large enterprise environments � Working with SQL Server since 1994 � PASS Volunteer since 1999 � Houston Area SQL Server User Group – Chapter Leader since 2001 � PASS Community Summit Speaker – 20002007 � SQLSaturday Organizer and Speaker Nancy Hidy Wilson 12/3/2020 27

My Info � Email: Nancy. Hidy. Wilson@gmail. com � Twitter: @Nancy. Hidy. Wilson � Blog: http: //Nancy. Hidy. Wilson. wordpress. com � Linked. In: http: //www. linkedin. com/in/Nancy. Hidy. Wilson � Houston Area SQL Server User Group: http: //houston. sqlpass. org Nancy Hidy Wilson 12/3/2020 28