DATABASE CONSOLIDATION WITH SQL SERVER 2008 Justin Langford Principal

DATABASE CONSOLIDATION WITH SQL SERVER 2008 Justin Langford Principal Consultant [email protected]

AGENDA Business and technical drivers Why consolidate? Type of consolidation Planning and considerations Key considerations Designing SQL instance and common configurations Guiding principles Consolidation process Migration Techniques Potential issues and solutions Shared components Using WSRM or Resource Governor

DATABASE SPRAWL - THE PROBLEM SQL Server is prolific in most mid-large organisations Wide selection of versions and editions Many bespoke/ non-standard configurations Servers deployed “per-application” Many servers under-utilised Licensing and maintenance costs

CONSOLIDATION – WHAT’S THAT? Data/ database/ instance consolidation Host same databases with fewer servers Share resources across multiple environments Better resource utilisation Reduced support costs

SQL CONSOLIDATION To y a d e n s a o b ta dati a D oli s n co e c n a n t o s n ati I L lid Q S so n o c SQL2 SQL1 SERVER1 SERVER2 BIZSQL BIZSQL BIZSQL BIZSRV SQL1 SERVER1 BIZSQL SQL1 SERVER1

SERVER VIRTUALISATION Virtual Machine Virtual Machine Virtual Machine Virtual Machine Manager Windows 2008 Server Virtual Machine

VIRTUALISATION VS CONSOLIDATION Same Virtualisation Goal reduce physical servers Savings on power, licensing, maintenance Performance concerns Same number of O/S Same databases/ instance Same number of instances No Windows/ SQL Upgrade “Black-box” approach Additional layer of technology Hardware abstraction Consolidation Different Consolidation Goal to reduce physical servers Savings on power, licensing, maintenance Performance concerns Fewer O/S More databases/ instance Fewer instances Can upgrade Windows and SQL Lots of engineering/ test effort More mixed workload Tight hardware integration Virtualisation

HOW NOW? Affordable performance Multi-core CPUs 64-bit Memory Disk I/O – Solid State Disks Core 1 Core 2 Core 3 Core 4

Wi nd ow s P Ma er ny Ser - V ver irtu alis ati on nces per a t s n I L SQ s Window Da ta/ Da tab Du pli cati ase on me o S One On e Som e er ce p s tan B D Ins L SQ s d e dr n Hu Multiple CONSOLIDATION DIMENSIONS si Phy era v e S ons ti a oc L l a c er Few l Management & Administrative L ot Processes Independent Standards Current & “To Be” position along each dimension

CONSOLIDATION CONSIDERDATIONS Resource requirements of databases Processor, memory, disk I/O and network TempDB usage Dependencies outside user database Third party support Instance-wide settings, security model Replication, Log Shipping, Database Mirroring Collation and sort order

BUSINESS DRIVERS Why consolidate? Reduced costs Standardisation Better server utilisation Space, electricity, cooling License costs Consolidation activities can reduce costs by 10 percent to 25 percent or more in some cases Gartner, March 2008 Better control of IT Processes Consistent operations, BaR, DR, Maintenance Improved Business Integration

TECHNICAL BENEFITS Multi-Instance Flexibility to based on Service Level Agreements (SLA) requirements Performance Backup / Recovery Security Change control Operational Upgrade Multiple development environments on single server Support larger workloads on a single server Several trade-offs/ considerations (more later)

TECHNICAL BENEFITS Single Instance Avoid of fixed overhead of multiple instance Fixed server memory configuration Single set of .EXEs, .DLLs etc. Some components are always shared anyway Dynamic memory for single instance server Less administrative work Several trade-offs and considerations (more later)

TECHNICAL BENEFITS Multi-Windows Instance (Virtualisation): More: Provides O/S Isolation Too many instances decrease value of high end servers Less: Reduces scale-up capability Can increase risk (single OS point of failure)

HIGH-LEVEL PLANNING

CONSOLIDATION SUCCESS FACTORS Tech nolo gy Cons olida tion Proc ess Stra tegy Peo ple

HIGH LEVEL PLANNING Strategy Motivation for consolidation and end goals Measure/ quantify benefits Establish Guiding Principles People Potential change in the ownership (DBA Custodianship) of data Technically Database Ownership (DBO) can be retained Ongoing support and change management System Administration role change

HIGH LEVEL PLANNING Process Administrative, Operational, Performance Monitoring/Tuning, Backup/Restore, Capacity Planning Migration and rollback planning Technology CPU, memory management, I/O subsystem Workload isolation Consolidation – name conflicts, objects, security, logins Management Focus Critical to success

CONSOLIDATION ROADMAP “To Be” Environment Test & Production Consolidated SQL 2008 Current Environment SQL 2000/ 2005 & older instances ore M ch Mu g g in n e l l Cha One at a time SQL 2008 (2005) Instances One at a time or en-masse? Staging Environment Monitor behavior Stabilise

GUIDING PRINCIPLES Evaluate, Customise, and Adopt: Start by consolidating non-mission critical workload Upgrade to SQL 2008 before consolidating Consolidate similar workloads into single SQL Server instance Avoid bug fixes during consolidation Maintain transparent user experience Drive standardisation (configuration and operations)

PLANNING, DESIGN & IMPLEMENTATION

PLAN, DESIGN, IMPLEMENT Stage 1 – Envision 1. Current Environment Assessment Discovery/ Audit Business Requirements: SLA, RPO, RTO Workload profile: transaction volumes, processor, disk, memory SQL Server feature usage: Linked servers, Extended SP 2. Identify Target Environment & Develop Consolidation Principles First cut “To Be” environment Discuss, gather feedback, revise target environment Review financial justification - RoI Develop Consolidation Guiding Principles

PLAN, DESIGN, IMPLEMENT Stage 2 – Plan & Design 3. Design ‘Future’ Consolidated Infrastructure Develop design Blueprint Design Review 4. Service, User & Data Migration Planning Migration approach Deployment planning Sequencing of Applications, Users, Data Design / develop migration scripts

PLAN, DESIGN, IMPLEMENT Stage 3 – Implement 5. Commission consolidated environment for test Test parallel to production Migrate/ test database Test Migration process Load/ capacity testing highly recommended Gain confidence in platform and workload profile Isolate, identify, fix issues

PLAN, DESIGN, IMPLEMENT Stage 4 – Deploy & Stabilise 6. Build & deploy Production Consolidated SQL Service Consolidate/migrate incrementally Capture baseline Add incrementally (ONE at a time) Stabilise Measure again Transparent User Experience 7. Measure new Service Compare with ‘old’ Retire old servers

TECHNICAL CONSIDERATIONS

MULTI-INSTANCE SERVERS Min/ Max server memory CPU affinity mask Windows System Resource Manager (WSRM) Failover cluster Possible owners Preferred owners AntiAffinityClassName BIZSQL SQL1 SERVER1

PLANNING MULTI DATABASES Resource Governor Min/ max CPU Min/ max Memory Un-managed Worker threads TempDB Plan cache SQL1 SERVER1

VIRTUALISATION DESIGN Recommendations Proper hardware sizing critical Good storage configuration is critical: Use fixed or pass-thru disks (avoid dynamic disks) Avoid thin-provisioning Tune storage configuration Limitations: 4 virtual processor per guest 64 cores in host 64 GB memory in host x64 benefits are significant Avoid over-committing CPU or memory Start with single VPU

VIRTUALISATION WITH HIGH AVAILABILITY Virtual machine failover High Availability regardless of Windows or SQL edition Live Migration with Hyper-V Uses Failover clustering Requires Win 2008 R2 Single host failover

VIRTUALISATION FOR SITE RESILIENCE Using virtualisation to provide site resilience Site-to-site disaster recovery

INTERESTING READS. SQL Server Consolidation Whitepaper http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx SQL Server Virtualisation Whitepaper http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf 076d9b9/SQL2008inHyperV2008.docx Microsoft Assessment and Planning Toolkit h ttp://www.microsoft.com/downloads/details.aspx?FamilyID 67240b76-3148-4e49943d-4d9ea7f77730&displaylang en Windows System Resource Manager (WSRM) http://technet.microsoft.com/en-us/library/cc771218(WS.10).aspx

SUMMARY Plan, D e si g n, Del Conso iver lidatio n Const ruct G uiding Princi ples Identi fy goo d can for co didate nsolid s ation Provid i isolat ng workloa io n d An a p p conso roach to lidatin g an e