© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Design, migrate, and optimize SQL Server on AWS
W I N 3 1 3 - R 1
Tom Staab
Partner Solutions Architect Amazon Web Services
Pallavi Sharma
Sr. Product Manager Amazon EC2
Amazon Web Services
Stephen Wright Sr. Director, VP Liberty Mutual
Sean Creagan
Director, Analytics &
Emerging Technology Liberty Mutual
AWS hosts nearly two times as many Windows Server
instances in the cloud as Microsoft
SQL Server on AWS
Why?
Where?
How?
What’s next?
Why choose AWS for SQL Server workloads?
Where can I run SQL Server on AWS?
How do I migrate SQL Server databases to AWS?
Securing, monitoring, and managing SQL Server on AWS
SQL Server on AWS exhibited 2X+ better price/performance than Azure (ZK Research)
SQL Server on Amazon EC2 consistently outperforms Azure across a variety of machine types
https://zkresearch.com/blog/2018/11/comparing-sql-server-deployments-on-microsoft-azure-and-amazon-web-services on https://zkresearch.com,
0 500 1000 1500
3 5 8 13 21 34 55 89 144 233
AWS versus Azure Large*
configuration (TPM)
AWS Azure
$0
$200
$400
$600
Small Medium Large
Costs per 1 billion transactions per month
AWS Azure
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Where can I run my SQL Server workloads on AWS?
Amazon Relational Database Service (Amazon RDS)
Managed service with up to 96 vCPU, 488 GB RAM, and 16 TB storage
Amazon Elastic Compute Cloud (Amazon EC2)
Self-managed virtual machine with up to 128 vCPU, 4TB RAM, and 400-TB storage
SQL Server features at a glance
Amazon RDS for SQL Server SQL Server on Amazon EC2
Versions supported 2012, 2014, 2016, 2017, 2019 All**
Editions supported Express, Web, Standard, Enterprise All**
High availability AWS-managed Self-managed; AlwaysOn, Mirror, Log Ship Encryption Encrypted Storage using AWS KMS (all editions); TDE support
Authentication Windows & SQL authentication
Backups Managed automated backups Maintenance plans & third-party tools
Maintenance Automatic software patching Self-managed
Amazon RDS with SQL Server
Amazon RDS: Hassle-free flexibility
Instance type selection:
M5/M4 instances for lower cost R5/R4 instances for more RAM EBS volume type selection:
GP2 volumes for lower cost
IO1 volumes for I/O intensive, critical workloads
Scale compute and storage with ease
Scale compute to handle increased load
Up to 128 vCPUs or 3096 GiB of RAM
Scale storage for larger datasets
Scalable EBS
storage up to 16 TiB*
Scale down to control costs
As little as 1 vCPU and 1 GiB of RAM
Amazon EC2 with SQL Server
SQL Server
Microsoft licensing on Amazon EC2
Dedicated host for
maximum virtualization Default tenancy AWS licensing
Buy license-included
instances from AWS Bring licenses to AWS
Requires Software Assurance with License Mobility
Amazon EC2: Purpose-built computing
Current instance families and generation Family/usage
M5, M4, A1 General purpose compute
T2, T3 Burstable performance
C5, C5n, C4 Compute optimized
X1, X1E, R5, R5d, Z1d Memory
optimized
P2, G3, F1 Accelerated computing
I3, I3en Storage optimized
(I/O)
H1, D2 Storage optimized
(Density)
Amazon EC2: Scaling to meet your needs
R5n networking performance up to 100 Gbps i3en local NVMe storage up to 60 TB
X1e up to 4 TB RAM
High memory instances with up to 24 TB RAM
License optimization with Optimize CPUs for BYOL
• Control active vCPUs and hyperthreading status when launching new EC2 instances
• Reduce the number of SQL Server licenses
Sample licensing example only
Instance type Total vCPUs Active vCPUs with Optimize CPUs
SQL Server license savings
r5.4xlarge 16 8 50%
r5.12xlarge 48 8 83%
AWS Cloud storage options
Amazon EC2 Instance Store (Ephemeral Volumes) Amazon Elastic Block Store (Amazon EBS)
Amazon Simple Storage Service (Amazon S3) Amazon S3 Glacier
Amazon FSx for Windows File Server AWS Storage Gateway: File Gateway
Amazon Elastic File System (Amazon EFS)
AWS Snowball, AWS Snowball Edge, and AWS Snowmobile
What is Amazon EC2 Instance Store ?
• Local to instance
• Nonpersistent data store
• SSD or HDD
• Data not replicated (by default)
• No snapshot support
• Great option for tempdb
• Very fast NVMe on R5d, z1d, and i3
EC2 instances
Physical host Instance store
or
Not all instance types have local, instance storage
Amazon EBS
• Persistent block-level storage for EC2
• Pay only for what you provision
• Native redundancy and write cache
• Consistent and low-latency performance
• Optimized for random I/O
• Native support for encryption at rest (data volumes)
Amazon EBS volume types
SSD HDD
Provisioned IOPS
$0.125 per GiB
$0.065 per PIOPS
io1
General purpose
$0.10 per GiB
gp2
Throughput optimized
$0.045 per GiB
st1 sc1
Cold
$0.025 per GiB
Snapshot storage for all volume types is $0.05 per GiB per month
All prices are per month, prorated to the second, and from the US-East-1 Region as of September 2019
• EBS performance varies by EC2 instance type
Up to 80k IOPS and 1,750 MB/s with some families; scales with size
• gp2 volumes
Performance scales with size; 3 IOPS/GB; max 16k IOPS; 250 MB/s Designed to deliver within 10% of provisioned IOPS 99% of the time
• io1 volumes
Configurable performance; up to 50 IOPS/GB; max 64k IOPS; 1k MB/s Designed to deliver within 10% of provisioned IOPS 99.9% of the time
Amazon EC2 and EBS performance
High Availability
Asynchronous commit Manual failover
and Disaster Recovery
VPC Peering
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit
Automatic failover
Region B VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit
Automatic failover
Amazon EC2
Multi-AZ Always On Availability Group
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit Automatic failover
AWS Launch Wizard
Simple
Based on Best Practices
Automated Tailored
End-to-end
AWS Launch Wizard for SQL Server
Guided experience to size, configure, and deploy SQL Server Always on solutions on Amazon EC2.
Size, deploy and configure your workload
Easy GUI based interface
for deployment Workload is customized
to your needs
Deployment is
automated through a few easy steps
Follows Well-Architected framework
AWS Launch Wizard for SQL Server
AWS Launch Wizard
Amazon FSx simplifies SQL Server HA deployme nts
With Amazon FSx there is no need to deploy, manage, and pay license fees for storage replication software solutions
Availability Zone 2 Availability Zone 1
AWS Region
SQL Server
FCI Primary SQL Server
FCI Secondary Automatic Failover
Amazon EC2
Multi-AZ failover cluster instances with SIOS DataKeeper
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit Automatic failover
Amazon EC2
Multi-Region disaster recovery with SIOS DataKeeper
Asynchronous commit Manual failover
WSFC Standalone instance
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit
Automatic failover
VPC Peering
Region B VPC
Availability Zone 1
Private subnet
Amazon RDS
Multi-AZ SQL Server
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit Automatic failover Amazon
RDS primary
Amazon RDS secondary
Amazon RDS
Multi-AZ SQL Server
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit Automatic failover Amazon
RDS primary
Amazon RDS secondary
Managed service
Private subnet
CloudBasic Instance A
Multi-AR failover
Amazon RDS multi-Region SQL Server availability with CloudBasic
Region A VPC
Availability Zone
Private subnet
CloudBasic Instance B
Region B VPC
Availability Zone
VPC Peering
Amazon RDS multi-Region SQL Server read replicas with CloudBasic
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
CloudBasic instance
Asynchronous replication
Read replica 1
Read replica 2
Read replica N Amazon
RDS primary
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Simple backup/restore migrations
Amazon RDS
Amazon EC2
Hybrid distributed availability group
Region A VPC
Availability Zone 1 Availability Zone 2
Private subnet Private subnet
Synchronous commit Automatic failover Synchronous commit
Automatic failover
Primary
replica Secondary
replica Corporate
data center
Asynchronous commit Manual failover
What are AWS DMS and AWS SCT?
AWS Database Migration Service (AWS DMS) easily and securely migrates and/or replicates your
databases and data warehouses to AWS
AWS Schema Conversion Tool (AWS SCT) converts your commercial database and data warehouse schemas to open-source engines or AWS-native services, such as Amazon Aurora and Amazon Redshift
Over 200,000 databases migrated and counting…
When to use AWS DMS and AWS SCT?
Migrate
• Migrate business-critical applications
• Migrate from Classic to VPC
• Upgrade to a minor version
Modernize
Modernize your database tier
• Commercial to open source
• Commercial to Amazon Aurora
Modernize your data warehouse
• Commercial to Amazon Redshift
Replicate
• Create cross-region read replicas
• Run your analytics in the cloud
• Keep your dev/test and
production environment in sync
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Addressing the gaps
NI current state
18 ADWs Teradata, DB2, Informatica, Cognos, MicroStrategy, Tableau
Ironshore
1 GDW
SQL Server, SSIS, Informatica, BO NAS, LSM, and surety
7 EDWs
MS SQL Server, SSIS, SSRS, SSAS, Power BI
Active directory for row-level security
Support global Power BI users
Reduce support and run rate costs
Leverage Azure DevOps for CICD
Phase 1: Lift Microsoft BI stack into AWS
Power BI Gateway SSAS query
Landing ODS
EDW
Report framework Data mart
SSAS processing Source system
Source system
Source system
Source system
Dev sources: SQL Servers
On-prem dev source replication Transformations and warehousing Analytical layer Reporting
Ongoing DMS replication SSIS ODBC
CLI/Bastion host
Source system Source system
Source system
Dev source: IBM DB2
Triangles framework Triangles mart Triangles staging
Amazon EC2 AWS Database Migration
Service
Amazon RDS
Amazon EC2
Amazon EC2 Amazon EC2
Amazon EC2 Amazon EC2
Amazon EC2
multidimensionalSSAS
MS SQL Server
MS SQL Server
SSIS Microsoft Excel
SSRS
Power BI
Summary
Migration from costly on prem
Maintained architecture
Leveraged AWS MSBI capabilities
Cost effective — minimal refactoring
Scalable
Playbook for remaining solutions
Migrate remaining solutions in 2020
Move EC2 objects to RDS (subject to availability)
Integration of data lake
Refactor elements to native AWS tools
Maintain MSBI toolset
Limit need for refactoring
Active directory for row-level security
Support global Power BI users
Reduce support and run rate costs
Leverage AWS
Requirements Outcomes Next steps
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Amazon CloudWatch
SQL Server on Amazon RDS
Performance insights
Additional services to manage SQL Server workloads on AWS
AWS CloudTrail – log account activity
AWS CloudFormation – infrastructure as code AWS Auto Scaling
AWS Systems Manager – patch and compliance management and more AWS License Manager
AWS Directory Service – Windows authentication AWS Key Management Service (KMS) – encryption Amazon GuardDuty – intelligent threat detection
SQL Server competency partners
Thank you!
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
For further information, email
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
