SQL Server Consolidation and Virtualization: Myths and Realities

Required Slide SESSION CODE: DAT 205 SQL Server Consolidation and Virtualization:Myths and Realities Joe Yong Chief Architect Scalability Experts jyong@scalabilityexperts.com

Before we begin • Goals • Understand the goals and requirements of consolidation • Review common oversights, myths and best practices for consolidation • Relationship between consolidation and virtualization • Real problems to be addressed • Picking the right solution for the problem • Non-goals • Deep dive into consolidation or virtualization technologies • One size fits all scripted solution • Flashy SQL Server demo fest

Agenda • Consolidation review • Virtualization review • The real problems • Common ground • Right solution for the problem

Why consolidate • Short term benefits • Immediate cost savings –licenses, infrastructure, maintenance, HADR • Halt / reverse uncontrolled growth and/or allow growth • Regulatory compliance • Improve utilization levels • Long term benefits • Operational costs savings • Expand and extend • Simplify management • Standardization of SLAs / support

What to consolidate • Easy candidates • Small & medium sized • Read-only or mostly reads • Average candidates • Business critical but no special requirements • VLDB with low to moderate workload demands • Tough candidates • Very high availability requirements • Extremely busy workload (‘000s of transactions per second) • High / complex security settings • Hard coded paths

Additional considerations and caveats • Consolidate homogeneous/similar workloads • Generally good guideline but there are caveats • Likely to have similar resource utilization and/or demand characteristics • Peak utilization at same time periods – contention • Mixed workloads with different peak periods and resource utilization are viable • ISV applications • Case-by-case; check with the ISV • Always end in one (1) central location • Regional consolidation is viable; often practical • Get buy in from all parties/peers • Good to do but top-down mandate is critical for success

Email Database File Server Reduce Servers Interoperability Consolidation strategy Improve Management Backward Consolidation (current systems) Forward Consolidation (new deployments) Lower cost, better control

Enterprise IT – CIO/BDM point of view

Enterprise IT – DBA/SysAdmin POV

Consolidation options • Single instance, single database, multi-schema • Very difficult to achieve • Common in “Other” database platform • Significant opportunity for conflicts, contention, management complexity and security overlap • Generally not recommended • Single server, single instance , multi-database • Difficult to achieve, but gives best utilization • Some resource isolation with Resource Governor • Some security isolation with encryption and extensible key management • Some namespace conflicts (e.g. logins) • Shared resource/configuration conflicts (e.g.TempDB, collation)

Consolidation options • Single server, multi-Instance • Good resource controls with SQL Server settings (optionally with WSRM) • Good security isolation (if best practices are followed) • Minimal namespace conflicts • Flexible deployment and management model • Multi-version support • Single server, single/multi instances on virtual machines • Different virtualization technologies available – ensure only supported vendors/configurations are used (see SVVP) • Sprawl is “virtualized”, but management of instances is not reduced • Slightly higher minimum resource overhead per server • Limited scalability • Virtual machine farm can be implemented for VM portability

Consolidation options • Single server, multiple physical partitions • Reduces physical sprawl but limited reduction in licenses • Used in combination with previous models (multi-instance, multi-database) • Very good resource and security isolation • Limited platform support

Consolidation options Management Tools Management Tools • SQL Server 2008/2005 supports up to 50 instances • Actual number limited by platform, resources, availability, manageability and security considerations Management Tools Management Tools Virtual MachineManager SQL Server App n SQL Server App 3 SQL Server Test SQL Server Test SQL Server Dev SQL Server Prod SQL Server App 2 Child partition 2 Other OS Child partition 1Windows Server 2008 RootpartitionWindows Server 2008 VM VM Windows Windows Hypervisor Physical Server Partition N Physical Server

Virtual machines overview • Two or more (mostly) isolated operating systems running concurrently on the same physical machine • Mostly independent operations, access and controls • Commonly run on servers but also useful on desktops/workstations • E.g. Legacy application support, dev/test • Common architectures • Hosted virtualization • Virtual Server, Virtual PC, VM Ware Workstation • Hypervisor virtualization • Hyper-V, VMWare ESX, Xen

Guest OSApplication Guest OSApplication VM architecture: hosted Virtual Hardware Virtualization Service Windows Server 2003/2008 x86/x64 Server

VM architecture: hypervisor based Rootpartition Child partition Child partition Apps Apps Apps Servercore OS 1 OS 2 Windows hypervisor (hyper-V) Hardware

Hyper-V architecture Provided by: VM Worker Processes OS ISV / IHV / OEM Microsoft Hyper-V Child Partitions Parent/root partition Microsoft / XenSource Applications (user mode) Applications (user mode) Applications (user mode) Applications User Mode WMI Provider VM Service Windows Kernel Windows Kernel Windows Server 2008 Non-Hypervisor Aware OS Xen-Enabled Linux Kernel Windows Server 2003, 2008 VSP Linux VSC VSC IHV Drivers Kernel Mode VMBus Emulation VMBus VMBus Hypercall Adapter Windows hypervisor (scheduler, memory management) Ring -1 “Designed for Windows Server” hardware

Example 1: Hyper-V VMs optimizationEach VM on its own network switch VM Worker Processes Parent / root partition Child Partitions Applications Applications Applications User Mode WMI Provider VM 3 Windows Server 2008 VM 1 VM 2 VM Service Windows Kernel Linux Kernel Windows Kernel VSC VSC VSC Kernel Mode VSP VMBus VMBus VMBus VSP VMBus VSP Windows hypervisor Ring -1 “Designed for Windows” Server Hardware Mgmt NIC 1 VSwitch 1 NIC 2 VSwitch 2 NIC 3 VSwitch 3 NIC 4 Source: Jeff Woosley (Sr. PM) Microsoft Hyper-V, Scenarios and Networking presentation

Example 2: mixed approach OS Stacking Reduces server hardware, but not OS licenses – need same number of OS licenses + virtualization license e.g. VMware. One App-One Server OS + Application Stacking + Dynamic Workload Mgmt One App-One Server Note that resources between multiple separate systems cannot be shared, hence low utilization. Workload Mgmt & App Stacking One App-One Server Float CPU’s can move dynamically between separate OS’s depending on load, time of day or manual intervention. Reduces server hardware + OS licenses. Need workload management software or careful configuration to ensure workloads do not negatively impact each other. Source: HP Corp.

SQL Server 2008 VM considerations • Both Hyper-V and Virtual Server 2005 are fully supported • Hyper-V highly recommended • 3rd party virtualization vendors supported if running Windows Server 2008 – see important notes and supported vendors • Server Virtualization Validation Programhttp://windowsservercatalog.com/svvp.aspx?svvppage=svvp.htm • Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment http://support.microsoft.com/KB/956893 • Update/modify monitoring scripts and/or tools • Update templates to monitor both host and guest CPUs using new hypervisor specific PerfMon counters • Implement new tools like Microsoft System Center – Virtual Machine Manager

SQL Server 2008 VM considerations • Mid/large servers recommended • 4-sockets or higher for growth and high speed PCI slots; • Blades/compact server not recommended regardless of CPU/RAM capacity • Multiple VMs per machine supported • Tested up to 4 VMs with near linear txn/s increase • Single user DB per VM, moderate workloads • Note increase in host OS resource utilization (memory, CPU) • CPU and memory utilization comparable to host installations • Storage IO performance comparable to host installation • All SQL Server IO tuning principals and best practices apply • Use pass-through disks for data/log files • Use fixed sized, pre-allocated disks

SQL Server 2008 VM considerations • Network IO performance impact 10-30% • Can be addressed using multiple NIC • CPU load will also increase if network load is high • 3rd party virtualization vendors have specific optimization recommendations • Can be highly secure but be aware of new and different security considerations • Risks from VMs/guest OS (often by internal perpetrators) • Hypervisor vulnerability exposes entire system (all VMs) • Patch management for all VMs and hyper-visor (MS Hyper-V) • Monitor intra-VM chatter, activity patterns and event logs (including entries & dates)

The real problems:Server sprawl

Failover Cluster Database Mirroring The real problems:Cost of high availability and disaster recovery • Failover Clustering protects entire instance but 1-1 protection usually hard to justify • Can be setup to protect multiple databases in the same instance • Build 3-4 node clusters to reduce cost of protection per node • Increases complexity • Database mirroring only protects user databases and requires individual effort • Scripting helps, a little • Methods and processes are kept in the senior DBA’s head • Up to 2x the administration overhead

100% -- 75% -- 50% -- 25% -- 0% -- Available Server Compute Capacity One App One Server Workload FY2005 The real problems:Inefficient resource allocation/utilization • Servers capacity designed for peak demands • Typical utilization is a fraction of peak demand • Peak utilization occurs at specific periods • Slow to respond to unexpected workload growth • Resource acquisition and provisioning can take hours or days 100% -- 75% -- 50% -- 25% -- 0% -- As server capacity continues to increase, the one app-one server model will have even lower utilization rates in the future. Available Server Compute Capacity One App One Server Workload FY 2002

The real problems:Do more with less • Administration • Too many servers to manage, monitor, patch • Deployment and re-hosting takes too much time and effort • Can I really use Management Studio? TSQL scripts? Powershell? • Security • Too many points to secure; too many keys to manage • Inconsistent standards and strictness • More than 1 SA owner • Infrastructure • Running out of power, HVAC and DR capacity • Blades and small servers are crowding, overloading data centers

More than just technology Technology: Tools & infrastructure 15% Process: Definition/design, governance, continuous improvement People: Roles & responsibilities, management, skills development & discipline Financial: TCO/ROI, business case Culture: Values, internal politics, reluctance to change External: Regulatory, legal compliance 85% Tough issues will not be technical!

Challenges • One-application, many servers culture • Funding of new projects owned by BU’s and internal politics • Software vendor support and licensing • Funding to plan, test, implement and support consolidation, virtualization & workload management technologies • Typically requires updated development, QA and release to production processes and management technologies • Incompatible hardware and OS versions with older applications • Security challenges • Application and system “time” - multiple time zone issues • Training – staff needs to be trained to support new technologies

Success factors • Ensure executive sponsor is in place • Does not guarantee success but lack of usually assures failure • Ensure current and accurate IT inventory is in place. • If inventory is not part of regular change management, the inventory will become stale very quickly and lose value; • Inventory tools gathering HW/SW info only gather about 70% of data required; non-tool related data includes • Server financials (e.g. purchase date/price, annual maintenance, depreciation) • Company Business Unit (BU), division and/or department owners • Application business criticality – typically depends on recovery time objectives (RTO) and recovery point objectives (RPO) • Growth expectations for next 18-24months • Application comments on dependencies, constraints and interfaces

Success factors • Design hardware and infrastructure for the long term • Support 24-36 months growth without major upgrade • Add components without re-hosting • Blades appear attractive but often the wrong answer – rack sprawl • Storage design is critical • Key considerations are IOPS, MB/s and latency, not GB/TB • Prioritize systems design based on • Business criticality • Security • Manageability • Scalability and performance • Knowledge and process upgrade • New technologies and operational structure

Hard problems • Business criticality • A lot more eggs in one basket • HA and DR implications • Security • User level separation • Role isolation • Security keys – electronic (e.g. encryption) and physical (access control) • Application/systems design • Clashes and conflicts • Tightly coupled systems • Hard dependencies – user or infrastructure induced • Operations • Server, instance, database level tasks – overlaps and oversights • SQL Agent jobs complexity • Resource allocations, monitoring and charge-backs • Hardware • Big iron servers vs. commodity server farm • Storage design for load, not size

Really hard problems • People • Who really owns the new systems? • Conflict resolution path? • Which (or whose) vendor to keep? • Who is SA? Can you trust SA? • How long for developers & DBAs to adopt new methods/practices?

Really, really hard problem • Consolidation or virtualization?

Right solution for the problem • Consolidation and virtualization both solve similar problems • Server sprawl • Improve resource utilization efficiency • Better control and improve manageability • Reduce overall costs • Despite significant overlap, consolidation does not require virtualization to be successful • Broad implementation of virtualization does not imply automatic consolidation

The right solution • Despite similarities, some key differences • Consolidation • Will always reduce and merge footprint – ratio varies depending on strategy and technology • Includes license, operations, processes, infrastructure and people • Moderately useful for legacy applications support • Requires organizational re-alignment to be successful • Virtualization • May or may not reduce or merge footprint • Requires very careful planning on storage to take advantage of transportability and HA features • Great for supporting legacy applications • Requires new skills and knowledge • Can be minimally intrusive to IT groups

Additional considerations for the right solution • Motivations for consolidation and/or virtualization (summarized) • Reduce • Server sprawl – known and unknown • Costs – hard and soft • Complexity • Regain control (at least some sense of it) • Improve resource utilization levels • Improve response time • Operations and support • Projects • SQL Azure…… • Private cloud • Doesn’t solve the problems by itself but makes the problems more manageable

Final thoughts • Just native SQL Server & Windows and/or 3rd party • Variants of application layer based virtualization • Variants of hyper-visor based virtualization • Resource management • Scale-out with middle tier data path multiplexing • Be wary of “solve-all-problems” claims/benefits • Dynamically move instances from one host server to another in seconds • Re-use existing hardware but with improved utilization levels • No application change • Free lunch…… with every bridge purchased • In our experience, SQL Server & Windows is adequate in most deployments • You may need some help in large scale management

Case study 1 – national retail chain • Organization overview • $1.4 billion revenue • 550 store locations • 20% annual business growth rate • Business issues • Poor service levels • Reduce business risk • Inconsistent server utilization • Needed architecture for growth

Case study 1 – national retail chain • Original environment • 40 IBM xSeries servers with 2 to 16 single core processors • Annual hardware support costs - $120,000 • Annual software support costs - $550,000 • IT server administration staff – 6 FTEs • Total annual server operating expenses - $1,250,000 • Business needs • Increase available computing power • Improve service levels • Reduce management burden • Establish architecture for growth

Case study 1 – national retail chain • “Low cost” option • 17 commodity servers – 4 dual core processors – 16 GB memory • 68 total processors, 272 total GB memory • Initial hardware purchase price - $395,138 • Initial software licenses - $1,917,464 • Enterprise servers option • 3 enterprise servers – 16 dual core processors – 64 GB memory • 48 total processors, 192 total GB memory • Initial hardware purchase price – $916,154 • Initial software licenses - $1,295,952

Sample three year TCO comparison • Additional significant business benefits: • Improved availability • Improved agility – time to deploy new systems • No “forklift upgrade” (aka re-host) for 24 months

Consolidation trends • Data from 4 cases – various industries • Typically start with dozens to hundreds of 32-bit servers • Commodity servers based design • 1:2 to 1:8 server reduction ratio • Enterprise servers based design • 1:10 to 1:64 server reduction ratio • Enterprise server base design does not fall back into “server sprawl” problem due to scalability limits

Case study 2: international petro-chemical company • Challenges with management, scalability, performance, DB growth (size & number), limited staff • Concerned with availability and recoverability • Initially decided on small, commodity x64 servers (1-step up from blades) • No requirements for >8cores in the next 3 years • No highly complex applications • No VLDB • Low entry cost

Case study 2: international petro-chemical company • New concerns 6-months after starting consolidation • System availability, manageability • Uncertainty over “newly discovered” SQL Server BI/OLAP • Limited options on consolidation • Minor reduction in data center footprint; reduced consolidation ROI • Minor reduction in number of servers; reduced estimated savings from operations

Case study 3: international services company • Challenges • Uncontrolled database deployments (including under desks) • DBA team cannot keep up with tracking, managing and patching systems • Security risks, business risks and customer data liabilities • Long evaluation cycle resulted in decision to deploy scale-out SQL Server farm using 3rd party virtualization technology • Features and benefits were very attractive on paper and during POC – moving systems around, HA benefits, rapid growth, reuse existing hardware

Case study 3: international services company • About halfway through deployment • Management efforts mostly the same but complexity increased • Marginal licensing savings • Limited hardware reuse • Instance portability not as easy as initially expected • Full rollback after about 50% deployment • Re-architected native consolidation based on enterprise servers

SQL Server Consolidation and Virtualization: Myths and Realities