Transcript SQL Server 2005 BI - Cost Effective Information

SQL Server 2005 BI
Cost Effective Information Lifecycle Management
for Data Warehouses
Adam Waddell
© 2005 EMC Corporation. All rights reserved.
Agenda
 How Information Drives Business
 Managing Growing Mountains of Data
 New Features for Storage in SQL Server 2005
 REAL Solutions to Real Problems
 Applying Data Lifecycle Management
© 2005 EMC Corporation. All rights reserved.
2
The Information Age
 Information management drives business efficiency
– Six Sigma
– Total Quality Management
– Total Customer Experience
 Wal-Mart sells products to consumers and
information to producers
 Managing inventory, logistics, and supply chains
requires information
© 2005 EMC Corporation. All rights reserved.
3
Information Overload
 Data we are REQUIRED to keep
– Regulatory compliance
– Legal obligations
– Archive requirements
 Data we DESIRE to keep
– Sales and inventory records
– Customer, supplier, and partner records
– More detail is generally better
© 2005 EMC Corporation. All rights reserved.
4
Information Overload
 Data Analysis becoming increasingly critical to daily
business operation
– Many business processes now depend on historical
data analysis
– Availability requirements of data and data warehouses
are increasing
 Better tools and processes are needed to manage
the growing stream of data
– Take advantage of changing access and usage patterns
over the data lifecycle
© 2005 EMC Corporation. All rights reserved.
5
Agenda
 Increasing Importance of Information
 Managing Growing Mountains of Data
 Partitioning in SQL Server 2005
 REAL Solutions to Real Problems
 Applying Data Lifecycle Management
© 2005 EMC Corporation. All rights reserved.
6
Managing Mountains of Data
 Data reduction
– Archiving unused data
– Pruning unused dimensions
– Replacing complete data with summary or aggregate
data
 Tiered storage solutions
– Consolidate back up and recovery operations
– Age data off most expensive storage
– Match business information needs with appropriate
storage capabilities
© 2005 EMC Corporation. All rights reserved.
7
Backing Up Mountains of Data on SQL 2000
 VDI, provided by Microsoft with MS SQL 2000
– Uses “WITH SNAPSHOT” functionality
 Creates a Meta-data file with locations to aid
recovery processing
 Checkpoint and write-hold to quiesce database
 The database remains online and accessible during
the replication process
 Minimal/no impact on the performance of the
database server
© 2005 EMC Corporation. All rights reserved.
8
Backing Up Mountains of Data on SQL 2005
with VSS
R
R
Requester
(Replication Manager)
Writer
(Exchange)
Writer
(SQL)
Volume Shadow
Copy Service
Writer
(Other Apps)
Writer
(Other Apps)
Provider
(Symmetrix)
Disk 1
© 2005 EMC Corporation. All rights reserved.
Disk 2
Disk 3
Provider
(CLARiiON)
Disk 4
Disk 5
9
Replication Manager Family Portfolio
 Replication Manager/SE (new)
creates local Exchange, SQL
Server, and filesystem
replications on a CLARiiON
 Replication Manager/Local
manages local (within an array)
application-focused replications
for repurposing and rapid
recovery
© 2005 EMC Corporation. All rights reserved.
Replication
Manager/SE
Replication
Manager/Local
10
Hot Split Backup with Clone
SQL Backup
SQL
Production Server
G:\DB
Truncate
logs
F:\Logs
1) Check Production database integrity
Completely automated
2) Synchronize clones (VSS/VDI)
3) Perform hot-split (VSS/VDI)
4) Mount replicas on backup server
5) Truncate logs (optional)
6) Post processing task (optional)
G:\DBClone
F:\LogsClone
DMX or
CLARiiON
© 2005 EMC Corporation. All rights reserved.
Backup
Server
11
Rapid Recovery from a Clone
Exchange Recovery
SQL
Production Server
G:\DB
Roll forward logs
F:\Logs
1) DB gets corrupted
Completely automated
2) Reverse sync from Clone
3) Mount DB and roll log forward
G:\DBClone
F:\LogsClone
DMX or
CLARiiON
© 2005 EMC Corporation. All rights reserved.
Backup
Server
12
Managing Mountains of Data
Partitioned Storage Savings
3 Tier $
2 Tier $
1 Tier $
0
1000 2000 3000 4000 5000 6000 7000 8000 9000
Capacity (GB)
 Three tier cost savings can be substantial
© 2005 EMC Corporation. All rights reserved.
13
Managing Mountains of Data
Cost Effective Tiered Storage Solutions
 Data ages from high performance
storage needs to higher density storage
Fast Fast Fast Fast
Medium
Medium
 Over time data ages to
highest density storage
Highest Density
© 2005 EMC Corporation. All rights reserved.
14
Agenda
 Increasing Importance of Information
 Managing Growing Mountains of Data
 Partitioning in SQL Server 2005
 REAL Solutions to Real Problems
 Applying Data Lifecycle Management
© 2005 EMC Corporation. All rights reserved.
15
SQL Server 2005
Partitioned Tables and Indexes
 What are they?
– Default unit of data organization for all tables and
indexes in SQL Server 2005
– Single object for the relational engine
– Multiple objects for the storage engine
 Why do I care?
– Increased database uptime
– Improved performance
– Foundation for leveraging tiered storage
© 2005 EMC Corporation. All rights reserved.
16
Partitioned Tables vs Views
 Partitioned Views
– Query optimizations (select) in 7.0 & 2000
– No efficiencies with data modification
– Tables managed separately
 Partitioned Tables
– Introduced in SQL Server 2005
– Lower complexity – managed as one table
– Higher performance – reduced contention
© 2005 EMC Corporation. All rights reserved.
17
Divide and Conquer
 Why partition databases?
– Scalability
• Easier support for very large databases
• See sessions DBA416 and DBA311
– Performance
• Improved query optimization
• Increases parallelization and reduced contention
– Availability & Manageability
• Backup and restore individual filegroups
• Finer control over data ranges
© 2005 EMC Corporation. All rights reserved.
18
Sliding Window Scenario
 Aging ranges across storage platforms
Q2
Q3
Q4
Q1
Q2
Q3
Q4
2004 2004 2004 2005 2005 2005 2005
 Partitions move from primary
storage to secondary storage as
usage decreases
Q2
Q2
Q3
Q4
Q1
2003 2003 2003 2004 2004
© 2005 EMC Corporation. All rights reserved.
19
Agenda
 Increasing Importance of Information
 Managing Growing Mountains of Data
 Partitioning in SQL Server 2005
 REAL Solutions to Real Problems
 Applying Data Lifecycle Management
© 2005 EMC Corporation. All rights reserved.
20
Project REAL Overview
 Joint effort to discover best practices for BI
applications on SQL Server 2005
 Project comprises
– Schema design including partitioning
– Data ETL processes
– Management and maintenance of systems
– Management of incremental data updates
 Visit project REAL for best practices
• www.microsoft.com/sql/bi/ProjectREAL
© 2005 EMC Corporation. All rights reserved.
21
Project REAL Overview
 Based on a real customer scenario:
Barnes & Noble Booksellers
– 800 stores in the United States
– 40,000 employees
 Data warehouse supports planners, buyers and
managers in headquarters, stores and distribution
centers
 Sales and inventory data
 Data is “masked” to protect privacy and business
value
© 2005 EMC Corporation. All rights reserved.
22
Project REAL Overview
 One year of inventory data comprises over 8 billion
rows
– Data loaded/processed weekly
– Weekly partitions < 12 million rows
 Partitions read only after 8 weeks
– Actually, filegroups are marked read only
– Another value in storage aligned partitions
 Two data aging methodologies
– Sliding window-style with data copy
– Storage array based (simplified ETL)
© 2005 EMC Corporation. All rights reserved.
23
REAL BI System Architecture
 ETL system integrates and moves data from sources to
relational data warehouse
– SQL Server Integration Services
Sales
data
source
Inventory
data
source
 Relational warehouse provides flexible
long-term storage
– SQL Server relational database
 OLAP cubes allow fast interactive
exploration of large amounts of data
SSIS manages
data movement
Relational
data
warehouse
– SQL Server Analysis Services
 Reports provide consistent, familiar outputs
from the system
AS cubes
and
data mining
– SQL Server Reporting Services
 Data mining helps to find patterns in large amounts of
data
– SQL Server Analysis Services
© 2005 EMC Corporation. All rights reserved.
Reports
generated
by RS
24
REAL Data Aging
Host-based file copy
–Leverage sliding window concept
–Data retained in table, moves on disk
Storage array-based LUN migration
–“Data in place” aging of existing partitions
–No change to partition function or scheme
Independent of loading new partitions
© 2005 EMC Corporation. All rights reserved.
25
REAL Tiered Storage
Older data is
aged to highest
density storage
after one year
Last quarter
data is aged to
higher density
storage
LUN Migration reduces ETL process complexity and
increases storage and database availability
© 2005 EMC Corporation. All rights reserved.
26
Agenda
 Increasing Importance of Information
 Managing Growing Mountains of Data
 Partitioning in SQL Server 2005
 REAL Solutions to Real Problems
 Applying Data Lifecycle Management
© 2005 EMC Corporation. All rights reserved.
27
Service Offerings
© 2005 EMC Corporation. All rights reserved.
28
International Network Services
Vendor-Independent, Global Provider of IT Infrastructure Services &
Software
– 12 years providing business-centric, technology solutions
• Professional services spanning IT infrastructure lifecycle
• Software to manage, secure & operate multiple IP infrastructures
– Privately held by West Coast Venture Capital and INS employees
– 30+ offices across North America, Europe, and the Pacific Rim
– Fortune 1000 focus
• Experience with >75% of Fortune 500
• Conducting business with >50% of the Fortune 100
© 2005 EMC Corporation. All rights reserved.
29
Consulting Portfolios
© 2005 EMC Corporation. All rights reserved.
30
Best-of-Breed
Partner Strategy
Cisco
IP
Networks
Converged
Infrastructure
OS &
Network
Directory
Storage
Services
MSFT
© 2005 EMC Corporation. All rights reserved.
IT Planning & Strategy
EMC2
31
EMC SQL Server Database Layout Accelerator Overview
 Accelerated implementation or migration
of your SQL Server database onto EMC
networked storage—Symmetrix or CLARiiON
– Storage design for database layout
 Best-practices implementation from EMC
– Reference architectures
• Symmetrix
• CLARiiON
– Best-practice configurations
© 2005 EMC Corporation. All rights reserved.
32
What Makes the SQL Server Database Layout Unique?
 Leverage EMC’s expertise
 Documented customer
deliverables
 The EMC / Microsoft / INS
alliance
– Technology collaboration
– Best-practices collaboration
– Joint Customer Support process
© 2005 EMC Corporation. All rights reserved.
33
How the Solution Works
Assessment / Planning
Phase 1: Assessment
and Planning
 Review current
infrastructure—server,
operating systems,
database
 Review current databaseI/O usage
Design /
Recommendations
Phase 2: Database
Layout and Design
Recommendations
 Provide recommendations
for optimal deployment of
SQL Server on EMC SAN
infrastructure, based on
EMC best practices
Implementation /
Testing / Validation
Phase 3: Joint Testing
and Validation (optional)
 Assist your staff with the
implementation, testing,
and validation of
recommendations
 Provide knowledge
transfer to your staff
 Define requirements for
new MS SQL environment
 Determine recommended
architecture to optimize
performance, scalability,
and availability
© 2005 EMC Corporation. All rights reserved.
34
Customer Deliverables—Ensuring Knowledge Transfer to
Your IT Staff
Deliverables
Phase 1
Assessment / Planning
•
•
•
•
•
Project Plan
Microsoft SQL Server review
Final Report detailing recommendations
Draft Configuration Guide
Proposed Test and Acceptance Plan
Phase 2
Design /
Recommendations
• Assistance with implementing server and / or
storage design recommendation
• Successful completion of Test and Acceptance
Plan (optional)
Phase 3
Implementation /
Testing / Validation
• Completed Configuration Guide
• Completed Test and Acceptance Plan (optional)
© 2005 EMC Corporation. All rights reserved.
35
SQL Design Validation Service
SQL Design
Validation
Service
 SQL experts review your
design against industry
best practices
 Final Report includes Gap
Analysis and
Recommendations
Back
bone
IP
IP
– Pre-Site Planning
• Review your IT infrastructure and business requirements.
• Verify that your IT infrastructure documentation aligns with your objectives
for the project
– On-Site Reviews and Gap Analysis
•
•
•
•
•
Uses a storage analyzer tool to gather info on servers & storage
Reviews existing Windows/SQL architecture and requirements definition.
Perform an SQL 200x design review and gap analysis.
Design an SQL 200x high-level architecture.
Provide a storage and server consolidation design review and gap
analysis.
– Final Report
• Present findings and gap analysis
© 2005 EMC Corporation. All rights reserved.
36
SQL Archiving Assessment Service
 1-2 Week
Engagement
SQL Archiving
Assessment  SQL 2000/2003
 Data Archive
Service
Assessment Report
Back
bone
IP
IP
– Workshop that details the benefits of Data
archiving
– Final report includes
•
•
•
•
Key business requirements
High-level design and requirements
TCO/ROI review of project
Roadmap of required projects to implement archiving
solution
© 2005 EMC Corporation. All rights reserved.
37
To Learn More…
Visit :
EMC.com
Visit:
www.microsoft.com/sql/bi/ProjectREAL
© 2005 EMC Corporation. All rights reserved.
38
Questions?
© 2005 EMC Corporation. All rights reserved.
39
Your Feedback is
Important!
We invite you to participate in our
online evaluation on CommNet,
accessible Friday only
If you choose to complete the evaluation online,
there is no need to complete the paper evaluation
© 2005 EMC Corporation. All rights reserved.
40
© 2005 EMC Corporation. All rights reserved.
41