Starbucks Data Movement Technology Capacity Plan

ARCHITECT GROUP
Starbucks Data Movement
Technology Capacity Plan
REVISION HISTORY Date Version Description Author
SIGNED OFF BY Date Version Description Author
Technology Capacity Plan Version 1.0
1. INTRODUCTION (3)
1.1 PURPOSE (3)
1.2 SCOPE (3)
1.3 AUDIENCE (3)
1.4 TERMINOLOGY (3)
2. TECHNICAL OVERVIEW (4)
2.1 ENABLING TECHNOLOGIES (4)
2.2 DATA MIGRATION APPROACH (4)
2.3 SQL SERVER UPGRADE STRATEGY (5)
3. CAPACITY PLANNING (6)
3.1 FACTORS (6)
3.2 ENVIRONMENT PROVISIONING (6)
3.3 CAPACITY REQUIREMENT ESTIMATES (7)
3.4 CONFIGURATION MANAGEMENT (7)
3.5 MONITORING AND MAINTAINING (7)
©2015 Bleum. All Rights Reserved. 2 Last modified by: deacon.yin@Deacon Yin
Xxxxxxxxxxxx.
1.1 PURPOSE
Xxxxxxxxxxxxx
1.2 SCOPE
In Scope
•Capacity planning for production environment
•Capacity planning for development environment •Capacity planning for staging environment
Out of Scope
•Execution of capacity plan
•Capacity planning for network components
1.3 AUDIENCE
This document is intended for the following audience:
•IT Planners
•SQL Server DBAs
•Architects and IT Professionals
•Environment Delivery Teams
1.4 TERMINOLOGY
Terms Definition
IOPS
DSS Workloads
2.1 ENABLING TECHNOLOGIES
The solution uses the following key hardware and software components:
•EMC VNX5500 series storage system with high bandwidth
•EMC storage integrator (ESI) for windows suite
•VMware vSphere ESXi
•Microsoft SQL Server 2014 Enterprise Edition
Since Microsoft SQL Server 2014 offering the memory-resident technologies as major feature, the solution will also leverage the relevant technologies.
In general, In-Memory OLTP is a good fit for:
•Performance-Critical OLTP
•High Data-Input Rate
•ETL Staging Table
•Relational Cache
2.2 DATA MIGRATION APPROACH
The enterprise data warehousing solution offers a hybrid approach in order to facilitate the legacy BI infrastructure and architecture moderation.
Re-Hosting
The existing data model and schema is moved directly to the Staging DB with little or no change to the model.
Redesign
The data model is re-architected following the SQL Server data warehousing best practices.
Evolution
It is a combination of above two, allowing for the new target platform to be up and running quickly and over time deliver increasing benefits and capabilities by redesigning.
Figure – EDW Migration Approaches
2.3 SQL SERVER UPGRADE STRATEGY
There have two upgrade strategies.
•In-place Upgrade
•Side-by-side Migration
In-place Upgrade
Pros
-Additional hardware is no required
-Less risk and upgrading fast
-Still point to the old server and instance names
Cons
-Full database components upgrade, less granular control
- A planned outage is required
-If failed, the rollback strategy can be complex and time-consuming affair
-Supports upgrade only from,
-SQL Server 2008 SP3 or later
-SQL Server 2008 R2 SP2 or later
-SQL Server 2012 SP1 or later
Side-by-side Migration
Pros
-Build out an entire new database infrastructure
-More granular control, migrate databases and components independently
-The legacy instance remains online during the migration process
- A rollback is easy if a problem arises during the migration
Cons
-Additional hardware/virtual machine may be required
-Application need to be re-directed to new server and instance
In order to maximize the reliability and online availability, the side-by-side migration is an ideal option. The overall processes will be considered as below,
1)Check hardware and software requirements
2)Review the deprecated and discontinued features
3)Install the Windows Server 2012 as recommended
4)Install the SQL Server 2014 prerequisites and components
5)Migrate objects from legacy to new
6)Redirect to new, and decommission legacy
3.1 FACTORS
It is critical that the infrastructure supporting the data warehousing – server, network, storage, and applications –provides a robust, powerful, and flexible solution.
To achieve proportional and predictive bandwidth, consider the following factors,
•Design for the bandwidth, not for database capacity
•Check disk performance
•Calculate the building block storage requirement
To fulfill DW/DSS-like application required efficiency and reliability, consider the following factors,
•Balanced disk utilization
•Sufficient memory utilization
•Sufficient processor utilization
•Sufficient Tempdb design
To fulfill high performance workloads, consider the following factors,
•Disk bytes/sec
•Average CPU utilization (%)
Figure – Baseline of Data Warehousing Workload Capacity Unit 3.2 ENVIRONMENT PROVISIONING
•Development/Sandbox
-Development should be a standalone box. It is not required to interact with external systems.
However, if the different software needs to be installed in different boxes in the sandbox
environment, we need to ensure they are able to connect (DB, application)
-Connection to workstation should also be enabled (via citrix)
•Staging/Testing
-Database port should be open for EBI staging environment and application server to connect to.
-Ensure connectivity with Control-M application is open for staging
-Web port should be open to the internet
-Database should be accessible to user workstation via citrix
-Application server should be accessible (RDP) via citrix
•Production
-Database port should be open for EBI environment and application server to connect to.
-Ensure connectivity with Control-M application is open for production
-Web port should be open to the internet
-Database should be accessible to user workstation via citrix
-Application server should be accessible (RDP) via citrix
3.3 CAPACITY REQUIREMENT ESTIMATES
Following is the proposed environment configuration:
Environment500GB
Capacity Unit
1TB
Capacity Unit
2TB
Capacity Unit
Database LUN
(RAID 5 (4+1))
1 data LUN
2 data LUNs 4 data LUNs
Transaction Log 1 log LUN 1 log LUN 1 log LUN Tempdb 1 data LUN 1 data LUN 1 data LUN Disk Allocation 5 SAS Disks 10 SAS Disks 20 SAS Disks Memory 8GB 16GB 32GB
CPU 2.4GHzx2 2.4GHzx4 2.4GHzx8 3.4 CONFIGURATION MANAGEMENT
3.5 MONITORING AND MAINTAINING。