Transcript In this session, learn how to use tools like Utility Control Point, Data Collector, Policy-Based Management, and PowerShell to detect and prevent.

In this session, learn how to use tools like Utility Control Point,
Data Collector, Policy-Based Management, and PowerShell
to detect and prevent problems in
multi-instance SQL Server 2008 R2 environments.
Today’s Talk
Utility Control Point and Managed Instance (Ent. & D.C. Ed. Only)
SQL Management Pack (update available H2CY10)
Today’s Talk
Data Collector, Management Data Warehouse, Performance Reports
Today’s Talk
Policy-Based Management
Activity Monitor
Graphical Showplan
SQL Profiler
Dynamic Management Views
XEvents
Database Engine Tuning Advisor (Ent. Ed. Only)
New in R2
Update in R2
•
•
•
How can you proactively detect troubled instances?
What tools can help prevent trouble from occurring?
How can you automate management of so many SQL instances?
•
•
•
How can you proactively detect troubled databases?
What tools can help prevent trouble from occurring?
How can you automate management of so many databases?
Farm
Instance
Database
Detect
Prevent
Automate
SQL Farm
Detect
SQL Instance
SQL Databases

PBM

PBM

PBM


UCP
DC


UCP
DC


UCP
DC

PBM

PBM

PBM


PBM
PowerShell


PBM
PowerShell


PBM
PowerShell
Prevent
Automate
DBA
SQL01
UCP
SQL05
SQL04
SQL03
• Central collection point for
resource utilization metrics
• Central point for policy
evaluation
• Central view over managed
instances
SQL02
UCP
SQL Server
Management
Studio
Managed Instances
Dimensions
Measures
Computer
Volume
DAC
CPU Utilization
Server
Database
Filegroup
DataFile
LogFile
Disk Space
Utilization
3
data-tier app
2
Filegroup1
Filegroup2
1
1
File1
File2
File3
File4
Dashboard
List View
Detail View
CPU Utilization
Storage
Utilization
Instance CPU overutilization threshold = 70%
SQL01..SQL100
CPU over-utilization
threshold = 70%
SQL03 CPU over-utilization
threshold = 90%
All except SQL03
CPU over-utilization
threshold = 80%
Managed instances
SQL01
SQL02
SQL03
SQL100
Instance
Threshold = 70%
Window = 6 hrs
Frequency = 50%
DAC
Threshold = 10%
Window = 24 hrs
Frequency = 90%
UCP
• Dashboard
• Policy Health State Visualization
• Resource Utilization Metrics Visualization
Polices
• Global Level
• Instance Level
• Database (“Data-Tier Application”) Level
Insight
• See trends
• See outliers
• Drill down from high-level to low-level
Data Collection UI
(Object Explorer)
Data Collection
Configuration
Collection Set
Reports
Target
Data
Collector
Collection
Collection
Sets
Sets
Target
Data
Collector
Collection
Collection
Sets
Sets
Management
Data
Warehouse
(sysutility_mdw)
Time line
Navigation control
Resource usage
Drill down
Detailed diagnostics
DC
MDW
Insight
• System & User-Defined Collections
• Collect query activity, perf counters, SQL Trace, query output
• DC working alongside UCP
• Central Repository
• History and baselining
•
•
•
•
Centralized Reporting
See history and trends
See outliers
Starting from overview, drill down to deep details
Defines the evaluation mode, target filters, and schedule of the conditions
Policy
Related
policies
Category
Boolean expression over
Objects to which
facet properties or scalar-valued queries
policy is applied
Condition
Target Set Filter
Set of related
logical properties
Scalar-valued
query
Scalar-valued
query
Facet
TSQL
WQL
On Demand
On Schedule
• Evaluate a policy when specified by user
• Available through SSMS & Windows PowerShell™
• Option to force certain conditions to comply with policy
• Supports down-level evaluation (depends on properties
exposed)
• SQL Server Agent job periodically evaluates a policy
Evaluation modes
On Change: Prevent
On Change: Log Only
• DDL triggers prevent policy violations
• Event notification evaluates a policy when a relevant
change is made
Central Management Server
EPM Framework
PBM
Capabilities
• System Policies
• User-defined Policies
• Policy Categories / Groups
• Central Management Servers together with UCP
• Insight - see outliers
• Prevent, Detect, Automate
PS, SQLPS
Capabilities
• Launch from Object Explorer
• Script-based access to object models
• Run a query across all databases
• Ad-hoc or on schedule
• Much more …
Resource
UCP
Managed Instance
Disk space
~ 2 GB per year per managed
instance.
Negligible
Disk IO
8x 10k RPM spindles RAID-10.*
Negligible
CPU
4x 2.5 GHz Intel Xeon or equivalent
CPU capacity.*
~ 4% utilization overhead
on 2x 2.5GHz Intel Xeon
41
DAT311 -
SQL Server 2008 R2 Manageability
SQL Server 2008 R2 - Application and Multi-Server Management
Data-tier Applications in SQL Server 2008 R2
SQL Server 2008 Policy-Based Management
Enterprise Policy Management Framework with SQL Server 2008
Monitoring and Enforcing Best Practices by Using Policy-Based
Management
How To
SQL Server Manageability Team Blog
SQL Server Policy-Based Management Blog
Lara Rubbelke on SQLBlog.com
Bill Ramos on SQL Server
Michiel Wories' Weblog
Dan's Blog
Bart Duncan’s SQL Weblog
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500
starting June 8 – June 31st
http://northamerica.msteched.com/registration
You can also register at the
North America 2011 kiosk located at registration
Join us in Atlanta next year