best practices for running microsoft sql server & windows

© 2020, Amazon Web Services, Inc. or its Affiliates.

Baris Furtinalar & David Donkers

Best Practices for Running

Microsoft SQL Server & Windows

File Server on AWS


Speakers

Baris FurtinalarSpecialized Solutions Architect

David DonkersSr. Business Development Manager


Agenda


Agenda

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


Why Choose AWS for SQL Server Workloads


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

https://zkresearch.com/blog/2018/11/comparing-sql-server-deployments-on-microsoft-azure-and-amazon-web-services

https://zkresearch.com/


Where can I run SQL Server on AWS


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


Amazon RDS for SQL Server SQL Server on Amazon EC2

Power, HVAC, net

OS install/maintenance

OS patching

DBMS install/maintenance

DBMS patching

Database backups

High availability

Scaling

AWS managed Customer managed

Power, HVAC, net

OS install/maintenance

OS patching

DBMS install/maintenance

DBMS patching

Database backups

High availability

Scaling

• Consider RDS first

• Focus on business

value tasks

• High-level tuning

• Schema

optimization

• No in-house

database expertise

• Automatic Host

Replacement

• Need full control over

DB instance

• 3rd party server agents

• Clustering

• Options that are not

currently available in

Amazon RDS

Options for deploying SQL Server on AWS


SQL Server features at a glance

Amazon RDS for SQL Server SQL Server on Amazon EC2

Versions supported 2012, 2014, 2016, 2017 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

• t3 instances for lower cost

• m5 instances

• r5/z1d/x1e 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 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



Synchronous commit

Automatic failover

Amazon

RDS

primary

Amazon

RDS

secondary

Managed service


Amazon RDS Read Replica

Region A

VPC



Asynchronous commit

Amazon

RDS

primary

Amazon

RDS

Read Replica

•Read replicas are only available on the SQL Server Enterprise Edition (EE) engine.

•Read replicas are available for SQL Server versions 2016 and later

•RDS must be a Multi-AZ deployment with Always On AGs


Amazon EC2 with SQL Server

SQL Server


Microsoft licensing on Amazon EC2

Dedicated host for

maximum virtualizationDefault tenancy

AWS licensing

Buy license-included

instances from AWSBring 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, Z1dMemory

optimized

P2, G3, F1 Accelerated computing

I3, I3enStorage optimized

(I/O)

H1, D2Storage 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%


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)


High Availability

Asynchronous commit

Manual failover

and Disaster Recovery

VPC

Peering

Region A

VPC



Synchronous

commit

Automatic failover

Region B

VPC



Synchronous

commit

Automatic failover


Amazon EC2

Multi-AZ Always On Availability Group

Region A

VPC



Synchronous commit

Automatic failover


Asynchronous commit

Manual failover

VPC

Peering

Region A

VPC



Synchronous

commit

Automatic failover

Region B

VPC



Amazon EC2SQL Availability Groups for HA and DR


AWS Launch Wizard

Simple

Based on Best Practices

AutomatedTailoredEnd-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 deploymentWorkload 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 deploymentsWith Amazon FSx there is no need to deploy, manage, and pay license fees for storage replication software solutions

Availability Zone 2Availability Zone 1

AWS Region

SQL Server

FCI Primary

SQL Server

FCI

Secondary

Automatic Failover


How do I migrate my SQL Server databases to AWS?


Simple backup/restore migrations

Amazon RDS

Amazon EC2


Hybrid distributed availability group

Region A

VPC



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


Securing, monitoring, and managing SQL Server on AWS


Amazon CloudWatch


SQL Server on Amazon RDS


Performance insights


Flexibility to enforce

license usage

Learn more at aws.amazon.com/license-manager

AWS License Manager

Leverage native

AWS Services

Manage licenses across hybrid

environments


Moving to Managed File Systems for WindowsCloud File Share


Files are everywhere!

1

2

3


Are you faced with these on-premises storage

challenges?

Growing infrastructure costs

Storage and compute capacity limits

Ongoing management overhead


Using the right tool

For Your Workload

Amazon FSx

for Lustre

Amazon FSx

for Windows File Server

Amazon Elastic File

Service (Amazon EFS)

Windows-Based

Workloads

Linux-Based

WorkloadsHigh-Performance

Workloads


How can we help in windows space?

Easy Lift and ShiftFully managed file

shares built on

Windows

Amazon FSx for Windows File Server


Delivering a better file

system for Windows, built on

WindowsJoins to

customer AD with full

Windows ACLs

`

Amazon FSx for Windows

File Server


Designed for a wide spectrum of use cases…

Home

directoriesLine-of-business

applications

Web serving and

content management

Software

development

environments

HA SQL Server

databasesBackup and

disaster recovery

NAS lift-and-shift


Global availability

and scalability

Multiple File

Protocols

Fully ManagedReliability and

durability

Cost EffectiveStorage and

archival options

Seamlessly transition to cloud-backed file shares on AWS

Migrate your on-premises file shares without disruption to applications and end users


How easy it to lift and shift?

• DataSync establishes and manages a secure connection to your FSx-Windows systems

• SMB to SMB, same protocol as on-premises, no need to refactor or rearchitect

• It just works!

Shared

file systemNetApp, Isilon, IBM, Windows File Servers

SMB

AWS DataSyncAmazon FSx for

Windows

SMB

AWS RegionOn-Premises


What fully-managed means for our customers

“We no longer need to worry about maintenance of the file system

environment or patching of the underlying servers. This is allowing

the team to focus on more business-critical parts of our

application structure.” -John Waller, Senior Engineer, DocXellent

"We were able move our Windows file-based application to AWS

quickly and solved a critical performance issue. Based on our

successful lift-and-shift experience, we’re looking to move more

of our legacy applications requiring Windows native

compatibility, to Amazon FSx.” -Hemanth Jayaraman, Director,

Cloud Center of Excellence

“With Amazon FSx we have a managed solution that eliminates the need for us

to manage the tasks associated with operating file storage, including

performing backups and applying software patches." -Greg Bell, Senior

Systems Administrator


FSx pricing(per GB-month)

HDD-based storage

SSD-based storage

Single-AZ Multi-AZ

New!

SSD

HDD

LOWESTcost file storage in the cloud

Note: Based on Amazon FSx for Windows File Server pricing in US East (N. Virginia)

Typical savings from deduplication for general file shares is additional 50-60%


Q&A

best practices for running microsoft sql server & windows

Documents