Published on July 14, 2016
1. © 2016, Amazon Web Services, Inc. or its Affiliates. All rights reserved. 7 July 2016 Deep Dive on Amazon Relational Database Service Martin Minnock, Centre for Innovation & Analytics, Aon Paul Burne - Technical Account Manager, AWS Toby Knight - Manager, Solutions Architecture, AWS
2. What to expect • Amazon RDS overview (super quick) • Security • Customer story • Migrating to RDS • Metrics and monitoring • Scaling on RDS • Backups and snapshots • High availability
3. No infrastructure management Scale up/down Cost-effective Instant provisioning Application compatibility Amazon Relational Database Service (Amazon RDS)
4. Amazon RDS engines Commercial Open source Amazon Aurora
5. Amazon Aurora vs. MySQL Feature RDS Aurora RDS MySQL Number of replicas Up to 15 Up to 5 Replication type Asynchronous (milliseconds) Asynchronous (seconds) Replication performance impact on primary Low High Replica can act as failover target Yes (no data loss) Yes (potentially minutes of loss) Storage Up to 64 TB, auto growth Up to 6 TB, specify storage limit Automated failover Yes, to replica Yes, to standby User-‐defined replication delay No Yes Replica support for different data or schema vs. primary No Yes Cross-‐region replication No Yes Data cache survives Yes No
6. Trade-offs with a managed service Fully managed host and OS • No access to the database host operating system • Limited ability to modify configuration that is managed on the host operating system • No functions that rely on configuration from the host OS Fully managed storage • Max storage limits • SQL Server—4 TB • MySQL, MariaDB, PostgreSQL, Oracle—6 TB • Aurora—64 TB • Growing your database is a process
7. Selected Amazon RDS customers
9. Amazon Virtual Private Cloud (Amazon VPC) Securely control network configuration Availability Zone AWS Region 10.1.0.0/16 10.1.1.0/24 Manage connectivity AWS Direct Connect VPN Connection VPC Peering Internet Gateway Routing Rules
10. Security groups Database IP firewall protection Protocol Port Range Source TCP 3306 172.31.0.0/16 TCP 3306 “Application security group” Corporate address admins Application tier
11. Compliance Singapore MTCS 27001/9001 27017/27018
12. MySQL and Oracle • SOC 1, 2, and 3 • ISO 27001/9001 • ISO 27017/27018 • PCI DSS • FedRamp • HIPAA BAA • UK government programs • Singapore MTCS Compliance SQL Server and PostgreSQL • SOC 1, 2, and 3 • ISO 27001/9001 • ISO 27017/27018 • PCI DSS • UK government programs • Singapore MTCS
13. SSL Available for all six engines Using SSL to encrypt a connection to a DB instance mysql -h myinstance.c9akciq32.rds-eu-west-1.amazonaws --ssl-ca=rds-combined-ca-bundle.pem --ssl-verify-server-cert.com
14. At-rest encryption • DB instance storage • Automated backups • Read Replicas • Snapshots • Available for all six engines • No additional cost • Support compliance requirements
15. AWS KMS — RDS standard encryption Two-tiered key hierarchy using envelope encryption • Unique data key encrypts customer data • AWS KMS master keys encrypt data keys Benefits: • Limits risk of compromised data key • Better performance for encrypting large data • Easier to manage small number of master keys than millions of data keys • Centralized access and audit of key activity Data Key 1 Amazon S3 Object Amazon EBS Volume Amazon Redshift Cluster Data Key 2 Data Key 3 Data Key 4 Custom Application Customer Master Key(s)
16. Enabling encryption AWS Command Line Interface (AWS CLI) aws rds create-db-instance --region us-west-2 --db-instance-identifier sg-cli-test --allocated-storage 20 --storage-encrypted --db-instance-class db.m4.large --engine mysql --master-username myawsuser --master-user-password myawsuser aws rds create-db-instance --region us-west-2 --db-instance-identifier sg-cli-test1 --allocated-storage 20 --storage-encrypted --kms-key-id xxxxxxxxxxxxxxxxxx --db-instance-class db.m4.large --engine mysql --master-username myawsuser --master-user-password myawsuser
17. Amazon RDS + AWS KMS useful hints • You can only encrypt on new database creation • Encryption cannot be removed • Master and Read Replica must be encrypted • Unencrypted snapshots cannot be restored to encrypted DB • Cannot restore MySQL to Aurora or Aurora to MySQL • Cannot copy snapshots or replicate DB across regions
18. IAM governed access You can use AWS Identity and Access Management (IAM) to control who can perform actions on RDS Users and DBAApplications DBA and Ops Your database RDS Controlled with IAMControlled with database grants
19. IAM governed access Policies "Action": [ "rds:Describe*", "rds:ListTagsForResource", "ec2:DescribeAccountAttributes", "ec2:DescribeAvailabilityZones", "ec2:DescribeSecurityGroups", "ec2:DescribeVpcs”, "cloudwatch:GetMetricStatistics", "logs:DescribeLogStreams", "logs:GetLogEvents" ], "Effect": "Allow", "Resource": "*" "Action": [ "rds:*", "cloudwatch:DescribeAlarms", "cloudwatch:GetMetricStatistics", "ec2:DescribeAccountAttributes", "ec2:DescribeAvailabilityZones", "ec2:DescribeSecurityGroups", "ec2:DescribeSubnets", "ec2:DescribeVpcs", "sns:ListSubscriptions", "sns:ListTopics", "logs:DescribeLogStreams", "logs:GetLogEvents" ], "Effect": "Allow", "Resource": "*" Read Only Full Access
20. Prepared by Aon Inpoint | July 2016 RDS Deep Dive Martin Minnock - Aon Centre for Innovation & Analytics
21. 130+ staff Data Analysts | Data Scientists | Business Analysts | IT Development, Database & Infrastructure Specialists Platforms, Projects & Services multi-channel web portals | ad-hoc reporting | statistical analysis | machine learning initiatives Dublin Centre for Innovation and Analytics at the heart of Aon Inpoint Agile Scrum 16 cross-functional teams Agile Scrum & Kanban 2 weekly sprints | Incremental releases Aon Inpoint & ACIA (Dublin)
22. ACIA Reference Architecture for Analytics Data Transformation & AnalysisData Lake Ingestion Database File/Object Storage Message Channel consume Data Warehouses Advanced Analysis Mart Marts Mart Mart Analytics Distribution Bespoke Analysis Reports APIs Web Portal Dashboards Application Middleware OrchestrationData Sources Transactional Systems Documents Public Sources Reference Data Logs SQL APIs JSON/ XML SFTP/ PUT Metadata Workflow & BatchMessaging Technology Management MonitoringSecurity Backup & Recovery ITIL Service Management integrate Logging & Audit.
23. Drivers for AWS Cloud Adoption Performance and Productivity Poor server performance Re-purposing/refreshing hardware Capacity planning fails Cumbersome work practices Engagement Focus on business differentiation Promote experimentation & fail- fast Drive innovation Develop careers Costs and Risks Poor utilisation Responsiveness to change Emerging security standards Ageing hardware / EoL Separation of duties Platform for Growth Global user base Data increase across 4V’s Auto-scaling analytics Democratisation of data Relentless business appetite
24. Backend Databases for: Analytics Delivery Analytics Engine New Products Lift & Shift Targets Short-Life POC systems Precedent for native AWS services How ACIA uses RDS
25. Risk/View – Analytics Platform for Market & Risk Insights Rapid Updates, Agile delivery Customisable Future- Proofed, Flexible Focused on Self-Service & Automation Highly Available Resource Intensive
26. Challenges (and Solutions) 3rd Party ToolsDatabase Refreshes Missing Functionality EC2 (& BA) RDS in the Ecosystem AWS DMS
27. Complete Lift & Shift – 100% AWS Data Lake – feat. S3, EMR, and ECS New Product Development RDS for PostgreSQL, AWS Lambda for Python Innovation! Data Science & Machine Learning Intentions for the Future – RDS and Beyond
28. © Aon plc or its affiliates ("Aon"). All rights reserved. NOTE: Aon does not provide or express an opinion or recommendation regarding any matter mentioned in this presentation.The recipient understands that neither Aon nor its employees makes or shall make any representation or warranty as to the accuracy or completeness of any information contained in this presentation. Aon shall not have any liability to the recipient or any other party resulting from the use of such information by the recipient or any other party. The information contained in this presentation may not be reproduced in any way or disseminated to any other party without the prior written consent of Aon. Aon has endeavoured to ensure that this presentation is free of any virus or any other thing that would affect the recipient’s computer system. However, Aon cannot guarantee the security status of this presentation when accessed by the reader and shall not have any liability to the reader, recipient or any other party resulting from access to or use of the information contained herein. Disclaimer
29. Migrating onto RDS
30. Historically, Migration = Cost, Time Commercial data migration and replication software Complex to setup and manage Legacy schema objects, PL/SQL or T-SQL code Application downtime
31. Database Migration – 2 Steps
32. Step 1: Schema Conversion Overview
33. ü Move data to the same or different database engine ü Keep your apps running during the migration ü Start your first migration in 10 minutes or less ü Replicate within, to, or from Amazon EC2 or RDS AWS Database Migration Service
34. Customer premises Application Users AWS Internet VPN Start a replication instance Connect to source and target database Select tables, schemas, or databases Let the AWS Database Migration Service create tables, load data, and keep them in sync Switch applications over to the target at your convenience Keep your apps running during the migration
35. Flexible Migration Approach Replication instance Source Target Target Target Multiple targets Replication instance Source Target Source Source Multiple sources Source L Target Replication instance instance Selective
36. Metrics and monitoring
37. Summary of Metrics and Monitoring • Amazon RDS Metrics • Event Notifications • Log Files • Cloudtrail
38. Accessing Amazon RDS Metrics
39. Amazon RDS Standard Metrics 45 MetricsChange Time Period Dive Deeper Create Alarms
40. Amazon RDS Enhanced Monitoring Access to over 50 metrics in 7 categories: • Memory, • I/O, • CPU, • File system, • Load, • Swap • Processes
41. Amazon RDS Event Notifications • Get Notified when events occur on your database instances • 17 different event categories (availability, backup, configuration change, and so on) • Uses Amazon Simple Notification Service (Amazon SNS)
42. Scaling on RDS
43. Scale out with Read Replicas Relieve pressure on your master node for supporting reads and writes. Bring data close to your customer’s applications in different regions Promote a Read Replica to a master for faster recovery in the event of disaster Replicas within and cross- region • MySQL, MariaDB, PostgreSQL • Aurora Engines Needing Other Tools • Oracle • Microsoft SQL Server
44. Creating and Prompting Read Replicas Read Replica creation and promotion are accessed from the Instance Actions button in the RDS console
45. Creating and Promoting Read Replicas
46. Creating and Promoting Read Replicas With CLI
47. Creating and Promoting Read Replicas With CLI
48. Scaling Up and Down • Handle higher load or lower usage • Control costs
49. Scaling Up and Down Console
50. Backups and snapshots
51. RDS Backups MySQL, PostgreSQL, MariaDB, Oracle, SQL Server • Scheduled daily backup of entire instance • Archive database change logs • Up to 35 day retention for backups • I/O suspension as backup is initiated (but not with multi-AZ deployment) • Multiple copies in each AZ where you have instances for a deployment Aurora • Automatic, continuous, incremental backups • Point-in-time restore • No impact on database performance • 35 day retention
52. RDS Restore • Restoring creates an entire new database instance • You define all the instance configuration just like a new instance
53. Snapshots • Full copies of your Amazon RDS database that are different from your scheduled backups • Backed by Amazon S3 • Typical use cases • Resolve production issues • Nonproduction environments • Point-in-time restore • Final copy before terminating a database • Disaster recovery • Cross-region copy • Copy between accounts
54. High availability
55. Minimal deployment—single AZ Availability Zone AWS Region 10.1.0.0/16 10.1.1.0/24 Amazon Elastic Block Store Volume
56. High availability—Multi-AZ Availability Zone A AWS Region 10.1.0.0/16 10.1.1.0/24 Availability Zone B 10.1.2.0/24 Replicated storage Same instance type as master
57. High availability—Multi-AZ to DNS dbinstancename.1234567890.us-west-2.rds.amazonaws.com:3006
58. High availability—Amazon Aurora storage • Storage volume automatically grows up to 64 TB • Quorum system for read/write;; latency tolerant • Peer-to-peer gossip replication to fill in holes • Continuous backup to Amazon S3 (built for 11 9s durability) • Continuous monitoring of nodes and disks for repair • 10 GB segments as unit of repair or hotspot rebalance • Quorum membership changes do not stall writes AZ 1 AZ 2 AZ 3 Amazon S3
59. High availability—Aurora nodes • Aurora cluster contains primary node and up to 15 secondary nodes • Failing database nodes are automatically detected and replaced • Failing database processes are automatically detected and recycled • Secondary nodes automatically promoted on persistent outage, no single point of failure • Customer application can scale out read traffic across secondary nodes AZ 1 AZ 3AZ 2 Primary Node Primary Node Primary Node Primary Node Primary Node Secondary Node Primary Node Primary Node Secondary Node
60. Aurora-DNS Failover App RunningFailure Detection DNS Propagation Recovery Recovery DB Failure MYSQL App Running Failure Detection DNS Propagation Recovery DB Failure AURORA WITH MARIADB DRIVER 1 5 - 3 0 s e c 5 - 2 0 s e c 1 5 - 3 0 s e c Driver benefits
61. Thank You!
62. Contacts Martin Minnock Cloud Product Owner & Database Manager Aon Centre for Innovation & Analytics email@example.com Paul Burne Technical Account Manager Amazon Web Services firstname.lastname@example.org Toby Knight Manager, Solutions Architecture Amazon Web Services email@example.com @martinminnock
63. Please remember to rate this session under My Agenda on awssummit.london
... Deep Dive on Amazon Relational Database ... deep dive into how RDS works and how Aurora differs from the rest of the engines. via Amazon Web Services.
Amazon Aurora is a MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with ...
Deep Dive on Amazon ... Partner CorpInfo held a Deep Dive on Amazon Aurora event at ... managed database service that provides ...
... from introductory presentations on new and existing AWS services to deep dive sessions ... Deep Dive on Amazon Relational Database Service ...
... Deep Dive AWS Services ... between database engines. and support for highavailability deployments through the Amazon Relational Database Service.
CorpInfo collaborated with Amazon Web Services ... dive discussion on Amazon Aurora: Database ... Deep Dive on Amazon Aurora” spotlighted the ...
PolyBase: Gaining insights from HDFS and relational data in SQL Server 2016. ... services, devices and ... Deep Dive on Amazon Relational Database Service.
Technical Level: Medium Abstract: At Amazon Web Services (AWS) we are working on a number of innovative services covering different aspects of ...