Introduvtion to Management data ware house - PowerPoint PPT Presentation
Title:
Introduvtion to Management data ware house
Description:
SQL SERVER DBA ONLINE TRAINING COURSE Duration: 5 – 6 Weeks Database development Basic Database Concepts Normalization Introduction to SQL Server Creating Database and Tables DDL, DML, DCL Statements DQL Statements Working with aggregate functions Working with Joins Working with Sub Queries Implementing Views Implementing Data Integrity and Constraints Writing Scripts using Transact-SQL(T-SQL) Indexing Dynamic SQL Implementing Stored Procedures Implementing User Defined Functions Implementing Triggers Working with Cursors Coantct us : info@quontrasolutions.com SQL Server Transactional logs, Recovery Models. – PowerPoint PPT presentation
Number of Views:38
Title: Introduvtion to Management data ware house
1
Management Data Warehouse
www.quontrasolutions.com
2
What is a Management Data Warehouse?
- System consisting of a one or more collectors
(Performance Data Collector), a management server
(MDW), and reports.
www.quontrasolutions.com
3
What is a Management Data Warehouse?
Physical Warehouse
Data Warehouse
- Database consisting of tables of data and access
functions - Database
- Tables
- Data records
- SSIS
- stored procedures/views
- Flat files/Agent job, Sproc/SSIS
- Reports
- Building that consists of racks or aisles of
products - Warehouse
- Racks
- Lots of products
- Forklifts for racking products
- And picking
- Trucks for transporting To
- And from warehouse
www.quontrasolutions.com
4
What is it used for?
- Baselining
- Performance Monitoring
- Performance troubleshooting
- I/O
- CPU
- database growth
www.quontrasolutions.com
5
MDW as part of a system
- Factories make products (Other servers)
- Factories have small internal warehouses,
shipping dock (temp cache) - Products are shipped to warehouses (SSIS)
- Products are stocked on shelves in the warehouse
(MDW) - Consumers request products (MDW reports)
- Warehouses pick products from the shelves (DMV)
- Warehouses ship products to the consumer
www.quontrasolutions.com
6
MDW components
- Schemas
- Core
- Snapshots
- Custom Snapshots
- Sysutility (Utility Control Point)
- Security Roles
- mdw_admin
- mdw_reader (read-only view of reports msdb
exception) - mdw_writer (collection set upload to MDW)
www.quontrasolutions.com
7
Data Collection Sets
- Disk Usage (collect and upload every 6 hours)
- Server Activity (every 60 seconds, upload every
15 min) - Query Statistics (every 10 seconds, upload every
15 min) - Custom
www.quontrasolutions.com
8
Data Providers
Performance counters DMVs SQL Trace T-SQL
www.quontrasolutions.com
9
Miscellaneous
- How many servers? 100
- How much space? 400Mb/day/server
- Editions? Enterprise, Data Center, Standard
- Backward compatibility? No
www.quontrasolutions.com
10
How does the data get into the warehouse?
SSIS
www.quontrasolutions.com
11
Reports
- Management Data Warehouse Overview
- Disk Usage
- Query Statistics
- Server Activity
- Reports offer drill-down to additional
reports/information
www.quontrasolutions.com
12
Demos
- Configure MDW
- Configure collectors
- MDW schema
- msdb
- SQL Agent - Jobs
- Integration Services - SSIS packages
- Task Manager
- Cache files
- Collector reports
- MDW Overview report
www.quontrasolutions.com
13
Thank you!
- Website
- www.systemental.com
- www.leanprojectmanager.com
- Blogs
- dean-o.blogspot.com
- practicalhoshin.blogspot.com
- twitter.com/deanwillson
- dean_at_systemental.com
- linkedin.com/in/deanwillson
www.quontrasolutions.com
14
www.quontrasolutions.com
15
Task Manager
www.quontrasolutions.com
16
Temporary Data Collector Cache
www.quontrasolutions.com
17
Disk Usage report
www.quontrasolutions.com
18
SSIS Packages
www.quontrasolutions.com
19
Hyper-V Server 2008
www.quontrasolutions.com
20
info_at_quontrasolutions.com
www.quontrasolutions.com
For More Details Contact us
Quontra Solutions
Visit http//Www.quontrasolutions.com Email
info_at_quontrasoluitons.com Call us (404) 900 -
9988
