Transcription
Running MS SQL Serverworkload on Amazon RDSEugene StepanovSr. Database Specialist Solutions Architect 2021, Amazon Web Services, Inc. or its Affiliates.
Who am IEugene Stepanov 20 years of development experience(web, desktop, middle tier and back-end) Focusing on RDBMS since 2005 Worked with SQL Server since version 7.0 Worked at number of companies big andsmall Joined AWS one and half years ago Prior to AWS worked at Microsoft Based in Seattle, WA 2021, Amazon Web Services, Inc. or its Affiliates.
Agenda Amazon RDS EC2 vs. RDS Managed experience of RDS Automated Backup, Manual snapshot, Native BackupsHigh AvailabilityRead Scale-outCompute & Storage scaling Performance Insights Compute & Storage subsystemActive Directory IntegrationPerformance MonitoringMoving data to and from RDS SQL ServerNew Features of RDS SQL ServerHow to deploy & manage RDS SQL Server 2021, Amazon Web Services, Inc. or its Affiliates.
Amazon RDSChoice of open source and commercial databasesCloud Native EngineOpen Source EnginesCommercial EnginesRDS PlatformAutomatic fail-overBackup & recoveryX-region replication 2021, Amazon Web Services, Inc. or its Affiliates.Isolation & securityIndustry complianceAutomated patchingAdvanced monitoringRoutine maintenancePush-button scaling
Options for Deploying SQL Server on AWSAmazon RDS for SQL ServerSQL Server on Amazon EC2ScalingScaling Consider RDS firstFocus on businessvalue tasksHigh-level tuning asksSchema optimizationNo in-house databaseexpertiseHigh AvailabilityDatabase BackupsDBMS PatchingDBMS Install/MaintenanceOS Patching Need full control overDB instanceBackupsReplicationClusteringOptions that are notavailable in RDSHigh AvailabilityDatabase BackupsDBMS PatchingDBMS Install/MaintenanceOS PatchingOS Install/MaintenanceOS Install/MaintenancePower, HVAC, netPower, HVAC, netAWS managed 2021, Amazon Web Services, Inc. or its Affiliates.Customer managed
SQL Server Features at a GlanceAmazon RDSAmazon EC2Versions Supported:2012 – 2019AllEditions Supported:Express, Web, Standard, EnterpriseAllAWS-managedSelf-managed; AlwaysOn, Mirroring High Availability:Encryption:Authentication:TDE, Column-level, Always Encrypted, TLS, EBS encryptionWindows & SQL AuthenticationBI Stack:SSIS, SSRS, SSAS (tabular)AllBackups:AWS- managedSelf-managedMaintenance:AWS- managedSelf-managed 2021, Amazon Web Services, Inc. or its Affiliates.
SQL Server EC2 vs. RDS: Which should I use?License includedBYOLFull control over the instanceAutomated backupsAWS-managed Multi-AZ deploymentAWS-managed Read scale-outAWS-automated ability to scale Compute &Storage 2021, Amazon Web Services, Inc. or its Affiliates.EC2RDS
Managed Experience 2021, Amazon Web Services, Inc. or its Affiliates.
Automated BackupsPoint-in-time recovery for your DB instance Scheduled daily volume backup ofentire instance Archive database change logs 35–day maximum retention Minimal impact on databaseperformance In-region and x-region PiTREvery day during your backupwindow, RDS creates a storagevolume snapshot of your instanceEvery five minutes, RDS backs up thetransaction logs of your database 2021, Amazon Web Services, Inc. or its Affiliates.
Manual Snapshot Triggered by customer Instance level Keep as long as needed 2021, Amazon Web Services, Inc. or its Affiliates.
Native Backups Backup and restore directly to andfrom S3 bucket Supports Compression Only full & diff backups (no t-log) Full, diff & t-log restores Multi-file backup/restoreAmazonRDS SQL Server*.bakIAMAmazonS3SQL Server 2021, Amazon Web Services, Inc. or its Affiliates.
Multi-AZ SQL Server on Amazon RDS AlwaysOn AG (Basic AG for Standard) for 2016, 2017 & 2019DB Mirroring for 2012 and 2014Synchronous Secondary hot StandByAutomatic & Manual FailoverNo read traffic 2021, Amazon Web Services, Inc. or its Affiliates.
Availability: Failover TimesTime to reconnect* (seconds)MirroringPrimary DNSFailover reasonAlways OnPrimary DNSAlways OnListenerManual failover60-7030-405-7Primary SQL Servercrash30-4030-406-9Primary servernetwork disconnect80-11080-11030-35*Crash recovery times not included**MultiSubnetFailover True 2021, Amazon Web Services, Inc. or its Affiliates.
Read scale-out 2016 Enterprise Edition featureUp to 5 asynchronous read replicasIn-regionPromotionSeparate end-point 2021, Amazon Web Services, Inc. or its Affiliates.
Compute and Storage ScalingScale Compute toHandle Increased LoadUp to 96 vCPUs (R5.24XL)976 GiB of RAM (x1e.8XL) 2021, Amazon Web Services, Inc. or its Affiliates.Scale Storage forLarger Data SetsScalable EBS storage up to 16TiBScale Down toControl CostsAs little as 1 vCPU and1 GiB of RAM
Performance Insights for RDS SQL Server 2021, Amazon Web Services, Inc. or its Affiliates.
Available Instance TypesR5 Family Memory OptimizedR5.Xlarge (4 vCPU/32 GiB )R5.24Xlarge (96 vCPU/768 GiB )High performance networkingM5 Family General Purpose InstancesM5.Xlarge (4 vCPU/16 GiB )M5.24Xlarge (96 vCPU/384 GiB )High performance networking 2021, Amazon Web Services, Inc. or its Affiliates.R5b Family (New!)R5d Family (New!) Memory OptimizedLocal NVMe for TempDBR5.Xlarge (4 vCPU/32 GiB )R5.24Xlarge (96 vCPU/768 GiB )High performance networking X1E Optimized for large-scale,enterprise-class and in-memoryapplications, and offer one of thelowest price per GiB of RAM amongAmazon EC2 instance type x1e.Xlarge (4 vCPU/122 GiB ) x1e.8Xlarge (32 vCPU/976 GiB )Memory Optimized3x EBS PerformanceR5.Xlarge (4 vCPU/32 GiB )R5.24Xlarge (96 vCPU/768 GiB )High performance networkingz1d Family CPU Optimized, 4GHzMemory Optimizedz1d.Xlarge (4 vCPU/32 GiB )z1d.12Xlarge (48 vCPU/384 GiB)
Amazon Elastic Block StorageWhat is Amazon Elastic Block Storage (EBS)? Network-attached block storage Available for all instance types Many instance types support EBS optimization– dedicated channel for network storage I/O,eliminating contention with regular I/O Some instance types are EBS optimized, othersoffer it as an option 2021, Amazon Web Services, Inc. or its Affiliates.
General Purpose (GP2) Cost-effective storageWide range of workloadsSingle-digit ms latenciesAbility to burst to 3,000 IOPS for extended periods of timeMin of 100 IOPS (at 33.33 GiB and below)Max of 16,000 IOPS (at 5,334 GiB and above)Baseline performance scales linearly at 3 IOPS per GiB of volume sizeProvisioned performance 99% of the timeSize from 1 GiB to 16 TiB 2021, Amazon Web Services, Inc. or its Affiliates.
Provisioned IOPS (IO1) I/O-intensive workloadsAllows you to specify a consistent IOPS rateDelivers provisioned performance 99.9 percent of the timeSize ranges from 4 GiB to 16 TiBIOPS range from 100 IOPS to 64,000 IOPS (Nitro instances)Up to 32,000 (prior to Nitro instances)The maximum ratio of provisioned IOPS to requested volume size (in GiB) is 50:1. 2021, Amazon Web Services, Inc. or its Affiliates.
Windows Authentication Using On-Prem AD AWS CloudRegionAccount BVPCAccount AVPCTrusting Setup SQL Server RDSSetup Managed ADEnable Windows IntegratedAuthentication to use Managed ADdirectoryCreate a Trust with On-Premises DomainAssign privileges to On-Premises foraccess to RDSAccount CCorporate data centerVPCVPC peering1-way trust 2021, Amazon Web Services, Inc. or its Affiliates.Trusted domain
Monitoring RDS SQL Server performance1Amazon CloudWatch2Enhanced Monitoring3Performance Insights4SQL Server Native (DMVs/DMFs, Profiler, etc)53rd Party (Ola Hallengren, SentryOne, etc) 2021, Amazon Web Services, Inc. or its Affiliates.
Cloud Watch metricsAmazon CloudWatch metrics CPU UtilizationDB ConnectionsFree Storage SpaceFreeable memoryWrite / Read IOPSQueue DepthWrite / Read ThroughputSwap usageWrite / Read LatencyNetwork Receive ThroughputNetwork Transmit Throughput 2021, Amazon Web Services, Inc. or its Affiliates.
Amazon RDS Enhanced MonitoringOverview: OS Level Monitoring Metrics – 26 system and per process metrics Metrics delivered to CloudWatch Logs Up to 1 second granularityCompared to CloudWatch Metrics: Agent based metrics collections There can be differences with CloudWatch metrics due to collectionsource (hypervisor vs. agent) – eg. CPU 2021, Amazon Web Services, Inc. or its Affiliates.
Migrating Data to & from Amazon RDS12345.BAK File Save & RestoreLeverages SQL Server’s native backup functionalityAWS Database Migration ServiceMinimize downtime during migrations, migratebetween different DB platforms, Schema ConversionToolSQL Server ReplicationPush subscriptions to transactionalreplicationMicrosoft SQL Server Database Publishing Wizard,Import/ExportExport to T-SQL files, load using sqlcmdAWS MarketplaceThird-party data import and export tools andsolutions 2021, Amazon Web Services, Inc. or its Affiliates.
Amazon RDS hybrid use casesLatencyApplications andprocesses sensitive tonetwork and disk latencyFinancial servicesapplications such astrading and brokerageSecurity and fraudapplications that requirea quick response time 2021, Amazon Web Services, Inc. or its Affiliates.ResidencyRegulations dictate thatdata and infrastructurereside locallyContracts specify whereapplications are deployedInformation security orother reasons preventadoption of AWS regions
Amazon RDS on VMwareOverview Database managed services forthe hybrid cloud (AWS regionsand on-premises datacenters)FeatureDB engines SQL Server 2016 SP2Enterprise edition MySQL 5.7,PostgreSQL 10.9Performance andscalability Instances from Mto 24XL Read replica (MySQL,PostgreSQL)Availability anddurability Event integration Amazon Cloudwatchevents and metrics On-premises storagefor data residencyInfrastructure VMware vSphere 6.5 VMFS, NFS, or vSAN Available on VMware vSphereBenefits Unified Amazon RDS interfaceto manage databases onpremises and AWS regions Comprehensive databasemanagement services for ahybrid cloud environment 2021, Amazon Web Services, Inc. or its Affiliates.RDS on VMware
New features of RDS SQL Server1.Enterprise Edition Multi-AZ Price or-high-availability-dbinstances/2.Cross-Account & cross-VPC Domain ngle-shared-domain/3.Disable older versions of TLS and erver/4.SQL Server Integration Serviceshttps://www.youtube.com/watch?v 4-6Jo7RDLoc5.SQL Server Reporting Serviceshttps://www.youtube.com/watch?v 2JyiHnjdRiQ 2021, Amazon Web Services, Inc. or its Affiliates.
New features of RDS SQL Server (Continued)6.SQL Server Analysis Services (tabular)https://www.youtube.com/watch?v G2kaz G7vNA7.In-Region Read Replicashttps://www.youtube.com/watch?v 9.Replicating the Service Master -master-keyretention/10.Multi-file ti-file-nativerestores/ 2021, Amazon Web Services, Inc. or its Affiliates.
How to deploy and manage RDS SQL ServerMultiple ways to start and manage your SQL Server resources using AWSAWS Console 2021, Amazon Web Services, Inc. or its Affiliates.AWS CLIAWS SDKsCloudFormation
Questions Answers 2021, Amazon Web Services, Inc. or its Affiliates.
Amazon RDS for SQL Server SQL Server on Amazon EC2 AWS managed Customer managed Power, HVAC, net OS Install/Maintenance OS Patching DBMS Install/Maintenance DBMS Patching Database Backups High Availability Scaling Power, HVAC, net OS Install/Maintenance OS Patching DBMS Install/Maintenance DBMS Patching Database Backups High Availability Scaling
