Transcript Put Your feet upSimplified Management using the Enterprise

1
PUT YOUR FEET UP
SIMPLIFIED MANAGEMENT USING THE ENTERPRISE
POLICY MANAGEMENT FRAMEWORK
[email protected]
Simplified Management using the Enterprise Policy Management Framework
SQLBits 5, Celtic Manor – 22nd November 2009
2
Components of the Enterprise Policy
Management Framework
•
•
•
•
SQL Server 2008 Policy-Based
Management
Central Management Servers
Windows PowerShell
SQL Server 2008 Reporting Services
Introduction
3
Policy-Based Management Review
•
•
evaluating policies
categorising policies
Central Management Servers
•
•
configuring
managing policies with CMS
Extending with the Enterprise Policy
Management Framework
•
•
•
components and deployment
evaluation on down-level instances
large scale environments
Introduction
4
SQL Server Policy-Based Management
5
Policy Evaluation Modes
On Demand
On Schedule
On Change
– Log Only
On Change –
Prevent
SQL Server Policy-Based Management
6
SQL Server Policy-Based Management
7
Effect of Database Subscriptions
Security Category
Security Category
RTM
Target A: AdventureWorks evaluated
NO Security Category
Target B: AdventureWorks and pubs evaluated
SP1 (+CU3)
Target A: PayrollDB evaluated
Target B: All databases checked
SQL Server Policy-Based Management
8
•
•
policies replace the Best Practices Analyser in
previous releases
best practice policies installed when you install an
instance:


•
<SQL_Directory>\100\Tools\Policies\DatabaseEngine\1033
Sub-directories for Database Engine, SSRS and SSAS
note that all best practices policies have a Category
SQL Server Policy-Based Management
9
DEMO
Categorise and Evaluate Policies
10
Central Management Server
•
•
•
•
•
extends Registered Server concept in SSMS
shared registrations for every user that connects to
the same CMS
only works for instances in the same or a trusted
domains
can register the same instance in multiple groups
registered servers stored in msdb in :
dbo.sysmanagement_shared_server_groups_internal
dbo.sysmanagement_shared_registered_servers_internal
Central Management Servers
11
DEMO
Central Management Servers
12
Windows PowerShell
13
Components of the EPM Framework
•
•
•
•
•
SQL Server 2008 instance to store policies
SQL Server 2008 instance to act as the Central
Management Server
SQL Server 2008 instance to execute the
PowerShell script
SQL Server management database and policy
history table to archive policy evaluation results
SQL Server 2008 Reporting Services to render
and deliver policy history reports
Extend to the Enterprise
EPM Framework In Action
policy
results
CENTRAL
MANAGEMENT SERVER
policy
results
policy
results
policy
results
policy
results
policy
results
Extend to the Enterprise
15
Extend to the Enterprise
16
DEMO
Extend to the Enterprise
17
Scaling to the Enterprise
•
•
•
Add Intelligence to Policies by placing each policy in
a category and defining server restrictions for
versions and editions where appropriate
Define Concurrent Jobs with parameters for each
Policy Category and/or Central Management
Server group
simple table structure in repository database so you
can roll your own reports
Extend to the Enterprise
18
Enterprise Policy Management Framework
•
•
•
•
extends SQL Server 2008 Policy-Based
Management to all versions of SQL Server in an
enterprise
leverage CMS and Powershell script to capture the
policy evaluation output and insert it into a SQL
Server database
automate using SQL Agent job(s)
Reporting Services reports will deliver information
from the centralised database
Summary
19
SQL Server Manageability Team's blog on Policy Based Management
http://blogs.msdn.com/sqlpbm
Enterprise Policy Management Framework Download
http://epmframework.codeplex.com
Lara Rubbelke’s Blog
http://sqlblog.com/blogs/lara_rubbelke/default.aspx
Enterprise Policy Management Framework with SQL Server Whitepaper
http://sqlcat.com/whitepapers/archive/2009/02/25/enterprise-policymanagement-framework-with-sql-server-2008.aspx
Windows Powershell Blog
http://blogs.msdn.com/powershell
Right Join Consultants Web Site
http://www.rightjoin.net
References