SQL Server in the AWS Cloud

Who Are We Leading provider of SQL Server services to Corporate and Government clients. We have a team of experienced, Microsoft certified SQL Server professionals that are passionate about the SQL Server technology suite. Rob Risetto – Technical Director 0417 322 000 rob@dbinsight.com.au www.dbinsight.com.au

Agenda • • • • • • • • Application candidates and use cases AWS building blocks for SQL Performance considerations SQL Server 2014 and AWS Security High Availability SQL Backups Pricing and SQL Licensing

Application Candidates Web applications with minimal links

Application Candidates Applications with varying workloads

Application Candidates Applications that require Global Scale

Use Cases Disaster Recovery Site for On-Premise

Use Cases Replacement of end of life server hardware

Use Cases Offsite Backups to AWS S3 storage.

Use Cases • Deployment of new “greenfield” solution – Not sure of resources required • New Online service/product • – Uncertain if it will succeed in the market • Fast provisioning for a proof of concept • Require development/test servers to be provisioned at short notice or for limited periods of time. • User training – run up a temporary training environment

AWS Terms & Components • EC2 instance – AWS virtual machine • EBS – Elastic Block Store – persistent disk storage • Standard vs Provisioned IOPS • S3 – simple storage service– durable object storage • Glacier – long term archive storage

AWS Terms & Components • VPC – Virtual Private Cloud – isolate your VMs into your own network • RDS – Relational Database Service – kind of a managed service for database server • Availability zone (AZ) – data centre in a geographical region • Region – AWS location that houses 1 or more AZs

AWS Terms & Components Lets have a look at the AWS Management Console and review the components

AWS Performance Considerations Noisy Neighbours – Physical host shared by multiple VMs

AWS Performance Considerations • EBS Volumes – network attached disks • Standard EC2 instances - allows from 32 to 128 MB/sec - Aligns with 1 Gbit network card • Higher End EC2 instance allows up to 800 MB/sec - Aligns with 10 Gbit network card • Consider Placement Groups – group EC2 instances to maximise node to node communications. High end instances only

AWS Performance Considerations Use EBS Optimised instances - dedicated capacity for EBS I/O

AWS Performance Considerations • EBS Volume Types • Provisioned IOPS - delivers within 10 % of the provisioned IOPS performance 99.9 % of the time over a given year - up to 4000/sec 16 KB, or 2000/sec x 32 KB or 1000/sec x 64 KB IO. • Standard – 100/sec with burst into the 100s/sec • First touch penalty – 5 to 50 % - write to each block or full format

AWS Performance Considerations • Provisioned IOPS • Needs EBS optimised instance to ensure rate • Drive enough IOs/sec – keep queue length around 5 • Good for high IO needs of Data or Transaction log files • Standard EBS • Use if IO workload is not high – < 100 or so/sec • Use for local backup

AWS Performance Considerations • Software Raid EBS for bigger volumes and performance – Raid 0 or Raid 1 • Windows instances – use Red Hat or Citrix paravirtual (PV) drivers – max 25 EBS volumes if using Citrix driver – Otherwise 16 EBS volumes • EBS is block replicated in the AZ to avoid downtime • Use local EC2 instance storage for TempDB

AWS Terms & Components SQLIO Tests on m1.xLarge instance 4 x CPUs, 15 GB RAM, EBS Optimised - 128 MB/sec 1 EBS, Raid 2xEBS, 4xEBS, 8xEBS Graphs test data courtesy of David Tan!

AWS Performance Considerations EBS Configuration Performance Summary

SQL 2014 Performance on AWS In Memory OLTP challenge – xLarge vs Large EC2 instance • xLarge - 4 CPUs, 15 GB RAM, data/tlog disk -> 3000 PIOPS EBS volume • Large – 2 CPUs, 7 GB RAM, data/tlog disk -> Standard EBS volume Used Codeplex sample to run Disk Based and In Memory OLTP tests http://msftdbprodsamples.codeplex.com/releases/view/114491 ostress.exe -S.sql2014 -E -dAdventureWorks2012 -Q"EXEC Demo.usp_DemoInsertSalesOrders @use_inmem = 1, @order_count=10000" –n100

AWS Performance Considerations SQL Server 2014 – Performance Features on AWS

SQL 2014 Performance on AWS Buffer Pool Extension Challenge – OLTP and DW workload Improvement?? Test environment :• C3.Large instance, 2 CPUs, 3.75 GB RAM, 2 x 16 GB SSD local storage • HammerDB www.hammerora.sourceforge.net to generate OLTP and DW workload • Ran tests with and without BPE • Ran DW test with 1 Query set of 22 queries – compared Elapsed time • Ran OLTP test for 5 minutes – compared Transaction Rate/sec

SQL 2014 Performance on AWS Buffer Pool Extension Feature

SQL 2014 Performance on AWS Buffer Pool Extension Feature -- Enable BPE ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'Z:SQLBPESQLSSDCache.BPE', SIZE = 10 GB) -- Check BPE status SELECT * FROM sys.dm_os_buffer_pool_extension_configuration -- Check if any pages in BPE SELECT * FROM sys.dm_os_buffer_descriptors -- Disable BPE ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION Off

SQL 2014 Performance on AWS Buffer Pool Challenge – DW workload Improvement

SQL 2014 Performance on AWS Buffer Pool Challenge – OLTP workload – no benefit

AWS Security for SQL Server Virtual Private Cloud (VPC) – logically isolated network, private/public subnets

AWS Security for SQL Server Security Groups – inbound network firewalls for EC2 instances Specify protocols, ports and source IPs or other Security groups to reach a EC2 Instance

AWS Security for SQL Server AWS Security Layers

SQL Server High Availability Options SQL Server High Availability Options • Log Shipping • Replication • Database Mirroring • Availability Groups • Native Windows/SQL Cluster NOT POSSIBLE • 3rd Party Windows Clustering software using - WSFC - Block level replication

SQL Server High Availability Options SteelEye DataKeeper Clustered Edition - WSFC, No shared disk, works on SQL Standard Edition - Block level replication, compression, SQL Cluster Failover http://us.sios.com/windows-replication-availability-software-smb Youtube video http://www.youtube.com/watch?v=giEg68Ori9M

SQL Server High Availability Options • Separate Primary and Secondary in separate Availability Zones • Also useful to failover for maintenance or noisy neighbour issues

SQL Server High Availability Options Challenge – On Premise to AWS Availability Group • • • • • Two SQL Servers in separate Availability Zones Third SQL Server located at DBInsight Office Use AWS VPN endpoint Create Customer VPN Gateway Use on premise Router/ADSL equipment – cost less than $200 - Microtik RB951 routerboard - TP Link 8840 T ADSL Modem Router

SQL Server High Availability Options Challenge – On Premise to AWS Availability Group AWS Whitepaper http://aws.amazon.com/whitepapers/microsoft-wsfc-sql-alwayson/ Cloudformation template used for based configuration installation

SQL Server Backups Standard SQL Backup Configuration Use SQL Server 2014 backup encryption for extra protection on the EBS volume. Create Snapshot of system EBS volume, stored on S3 automatically.

SQL Server Licensing • SPLA type licensing – license built into EC2 hourly cost • Express edition (free) • Web edition • Standard edition • BYO – Need SA for Mobility Rights • BizSpark Program for start ups

AWS Pricing • AWS has pricing on their web site – refer to the correct region • EC2 Instance pricing - On – Demand instance - Reserved Instance • AWS Online calculator http://calculator.s3.amazonaws.com/calc5.html

DBInsight Contact Details Rob Risetto 0417 322 000 rob@dbinsight.com.au www.dbinsight.com.au Take Cloud Readiness Survey – Get 1 hour free consultation http://dbinsight.com.au/dbinsight/consulting/sql-in-thecloud/cloud-readiness-assessment Free 14 day trial SQL Server Cloud Monitoring http://dbinsight.com.au/dbinsight/dbinsight-monitor

SQL Server in the AWS Cloud

More Related Content

Similar to SQL Server in the AWS Cloud

Recently uploaded

SQL Server in the AWS Cloud