DBI 208 Get More out of SQL Server

DBI 208 Get More out of SQL Server 2012 in the Microsoft Private Cloud environment Guy Bowerman guybo@microsoft. com Madhan Arumugam madhana@microsoft. com

Shared Lower cost Hybrid Cloud On premises Dedicated Higher cost Private cloud. The cloud infrastructure is operated solely for an organization. It may be managed by the organization or a third party and may exist on premise or off premise. Off premises High Control Low Control

Shared Lower cost Managed database service Focus on business logic WA SQL Database - Paa. S 100% Compatibility Rapid self-service provisioning SQL Server in WA VM - Iaa. S Elastic/Self-Service capabilities Full h/w control Dedicated Higher cost SQL Server Private Cloud - Iaa. S Full h/w control Roll-your-own HA/DR/scale SQL Server High Control Low Control

Self-Build Appliance Reference Architecture Fastest Time to Solution Performance at Scale Component Flexibility Good Better Best Good Better Private Cloud Reference Architecture http: //social. technet. microsoft. com/wiki/contents/articles/4346. private-cloud-principles-patterns-and-concepts. aspx Best

Optimize for Uptime Optimize for Time to Solution

What scalable & elastic Why Scale to meet variable workload demands shared Resources Reduce CAPEX Pay as you go Map resources to priorities Implement Green IT Improve utilization always up, always on Extreme high availability Low cost disaster recovery Make IT strategic automated & self-Service Reduce OPEX Improve time to solution Simplify management

What scalable & elastic Why Scale to meet variable workload demands Pay what you consume Implement Green IT • Support for Dynamic Memory • RDBMS Memory management • Scale up

scalable & elastic Great for Self Service pattern SKU Dynamic Memory SKU Express - Express Workgroup - Web (SPLA)/VM Standard - Standard/VM Enterprise Dynamic Memory -

demo SQL Server 2012 with Hyper-V Dynamic Memory

scalable & elastic Hyper-V / VMM DM Params Recommendations Startup RAM 1 GB + SQL Min Server Memory (if set) In unplanned failover scenarios the total amount of Startup Memory combined (both local to the host and virtual machines to be failed over) should be lower than the total physical memory minus the host reserve memory Maximum RAM >SQL Max Server Memory (if set) Hyper-V Dynamic Memory Balancer – Available Memory, Hyper-V Dynamic Memory VM – Physical Memory (for each VM) Running SQL Server with Hyper-V Dynamic Memory - http: //msdn. microsoft. com/en-us/library/hh 372970. aspx

scalable & elastic OS OS CLR, DWA, TS, >8 K allocations DWA, TS Bufferpool (8 K allocations) SQL 2008 R 2 and before All size page allocations + CLR SQL 2012

scalable & elastic

scalable & elastic > 1 million IOPS from a single VM Great for centralized IT services pattern

Resource Governor: 64 Resource Pools (up from 20) What • Multi-tenant Isolation • Increase application density shared resources Resource Governor: Max cap on CPU usage • Predictable CPU usage for chargeback • Isolation from system tasks Why Reduce CAPEX Map resources to priorities Improve utilization Resource Governor resource pool affinity • Simple partitioning of physical resources • Predictable resource allocation Great for centralized IT services pattern

shared resources Requests Attributes UDF Background tasks, DAC connections Internal pool Classification Group 1 Pool 1 Group 2 N … Pool 62 Default Group Default pool

demo Resource Governor in SQL Server 2012

shared resources Most often investing in more physical memory and giving more RAM per VM gives you the best ROI Windows Server 2012 Hyper-V VHDX Format support larger capacity (64 TB), better perf and data corruption protection

What Always On always up, always on Why Built-in high availability Built-in disaster recovery Make IT strategic • Multiple Active Secondaries Improves utilization by offloading read/maintenance work (backup) • Much more performant than DBM • Availability Groups : Application centric multi. DB HADR over non shared storage with faster/reliable application recovery Improvement HW/SW Recoverability • Geo Cluster Multi-subnet Support enables DR • sp_server diagnostics : comprehensive SQL health status • Hardware Memory scrubber support Great for centralized IT services pattern

SQL 2012 Always. On HA/DR Care. Group Healthcare Systems Ext Windows 2008 R 2 Hosts Cluster rem igh Av Primary Site e. H Hyper-V Node A DR Site always up, always on aila bil ity Sync Availability Group: Billing. Sys Hyper-V Node B Windows 2008 R 2 Guest Cluster ASync HW & OS Failure Protection OS & SQL Failure Protection Disk & DB Failure Protection Node C

Hyper-V Live Migration always up, always on Us wi er s th til ou l c t in on ter nec ru ted pt ion Sync AG: Billing. Sys Configuration Memory Data Memory. Content Sync Hyper-V Node A Sync Availability Group: Billing. Sys Denali_A VHD (in Cluster Shared Volume) CSV Hyper-V Node B Denali_B VHD (in Cluster Shared Volume) Windows 2008 R 2 Host Cluster

always up, always on

always up, always on 5 – Failover or restart on any qualified failure conditions 4 – Failover or restart on moderate SQL Server errors 3 – Failover or restart on critical SQL Server errors 2 – Failover or restart on server unresponsive 1 – Failover or restart on server down 0 – No Automatic Failover or restart Query errors Resource errors System errors No response from SP Service is down

What § § automated & self-Service Why Reduce OPEX Improve time to solution Simplify management §

demo SQL Server Automation with Power. Shell cmdlets

SQL Server 2012 licensing guide http: //www. microsoft. com/sqlserver/en/us/editions. aspx

Half Rack Full Rack Multi-Rack Pod 100 -200 VMs 200 -400 VMs Hundreds of VMs Thousands of VMs REDUCE COST 35% Drive Green IT: in the first year ENERGY 90% COOLING 95% FLOOR SPACE 89% ADMINISTRATION 75%

http: //www. microsoft. com/sqlserver/en/us/solutionstechnologies/cloud-computing/private-cloud. aspx https: //connect. microsoft. com/site 297/Downloads/Download. Det ails. aspx? Download. ID=23188 Memory error recovery support in SQL 2012 Always. On Powershell automation http: //blogs. msdn. com/b/sqlosteam/

Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //europe. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn

Evaluations Submit your evals online http: //europe. msteched. com/sessions