Introduction to the power of policies Adelaide SSUG 25th January 2011 1 martincairneyrightjoinnet Introduction 2 SQL Server 2008 PolicyBased Management Central Management Servers Extending with Windows ID: 602539
Download Presentation The PPT/PDF document "Introduction to the power of policies" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Introduction to the power of policies
Introduction to the power of policiesAdelaide SSUG – 25th January 2011
1
martin.cairney@rightjoin.netSlide2
Introduction2
SQL Server 2008 Policy-Based ManagementCentral Management ServersExtending with Windows PowerShellSlide3
SQL Server Policy-Based Management3
Login Options
Enabled = True
Condition
Ensure Compliance
Policy
All SQL logins have password complexity rules enabled
All databases must be backed up every day
LastBackupDate
>=
dateadd
(‘day’, -1,
getdate
())
Ensure Compliance
Facet
Database MaintenanceSlide4
SQL Server Policy-Based Management4Policy Evaluation ModesSlide5
SQL Server Policy-Based Management5Slide6
SQL Server Policy-Based Management6Effect of Database Subscriptions
NO Security Category
AdventureWorks
Pubs
HRDB
Security Category
AdventureWorks
not
subscribed
PayrollDB
subscribed
(no pubs db)
Security Category
AdventureWorks
subscribed
pubs subscribed
Target B
Target A
RTM
Target A:
AdventureWorks
evaluated
Target B:
AdventureWorks
and pubs evaluated
SP1 (+CU3)
Target A:
PayrollDB
evaluated
Target B: All databases checkedSlide7
Central Management Servers7Central Management Server
extends Registered Server concept in SSMSshared registrations for every user that connects to the same CMSonly works for instances in the same or a trusted domainscan register the same instance in multiple groupsregistered servers stored in msdb in :
dbo.sysmanagement_shared_server_groups_internaldbo.sysmanagement_shared_registered_servers_internalSlide8
Policies and Central Management Servers8
DEMOSlide9
Windows PowerShell9Slide10
Extend to the Enterprise10Scaling to the Enterprise
Add Intelligence to Policies by placing each policy in a category and defining server restrictions for versions and editions where appropriateDefine Concurrent Jobs with parameters for each Policy Category and/or Central Management Server groupsimple table structure in repository database so you can roll your own reportsSlide11
Summary11Enterprise Policy Management Framework
extends SQL Server 2008 Policy-Based Management to all versions of SQL Server in an enterpriseleverage CMS and Powershell script to capture the policy evaluation output and insert it into a SQL Server databaseautomate using SQL Agent job(s)Reporting Services reports will deliver information from the
centralised databaseSlide12
Summary12
Issues Identified While Using Policy Managementsome operational issues identified while creating Policesdocumentation not always detailed – so you will experience a bit of trial and errorpossible bugs raised at Connect website – please vote for these:https://connect.microsoft.com/SQLServer/feedback/details/637291/policy-management-cannot-evaluate-policies-that-use-executesql-against-sql-server-2000
https://connect.microsoft.com/SQLServer/feedback/details/640369/executewql-in-policy-based-management-does-not-execute-against-some-facetsSlide13
References13QL Server Manageability Team's blog on Policy Based Management
http://blogs.msdn.com/sqlpbmEnterprise Policy Management Framework Downloadhttp://epmframework.codeplex.comLara Rubbelke’s Bloghttp://sqlblog.com/blogs/lara_rubbelke/default.aspx
Enterprise Policy Management Framework with SQL Server Whitepaperhttp://sqlcat.com/whitepapers/archive/2009/02/25/enterprise-policy-management-framework-with-sql-server-2008.aspx
Windows
Powershell
Blog
http://blogs.msdn.com/powershell