Deep Dive on Amazon Relational Database Service

50 %
50 %
Information about Deep Dive on Amazon Relational Database Service

Published on July 14, 2016

Author: AmazonWebServices


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

8. Security

9. Amazon  Virtual  Private  Cloud  (Amazon  VPC) Securely  control  network  configuration Availability  Zone AWS   Region 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 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

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 Amazon  Elastic  Block  Store   Volume

56. High  availability—Multi-­AZ Availability  Zone  A AWS   Region Availability  Zone  B Replicated  storage Same  instance   type  as  master

57. High  availability—Multi-­AZ  to  DNS­west-­

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 Paul  Burne Technical  Account  Manager Amazon  Web  Services Toby  Knight Manager,  Solutions  Architecture Amazon  Web  Services @martinminnock

63. Please  remember  to  rate  this   session  under  My  Agenda  on

Add a comment

Related pages

AWS Summit Series 2016 | Chicago – Deep Dive on Amazon ...

... 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.
Read more

AWS re:Invent 2015 | (DAT405) Amazon Aurora Deep Dive ...

Amazon Aurora is a MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with ...
Read more

Deep Dive on Amazon Aurora - CorpInfo

Deep Dive on Amazon ... Partner CorpInfo held a Deep Dive on Amazon Aurora event at ... managed database service that provides ...
Read more

AWS Summits 2016 | Stockholm | Sessions

... from introductory presentations on new and existing AWS services to deep dive sessions ... Deep Dive on Amazon Relational Database Service ...
Read more

1 - Deep Dive AWS Services -

... Deep Dive AWS Services ... between database engines. and support for highavailability deployments through the Amazon Relational Database Service.
Read more


CorpInfo collaborated with Amazon Web Services ... dive discussion on Amazon Aurora: Database ... Deep Dive on Amazon Aurora” spotlighted the ...
Read more

PolyBase: Gaining insights from HDFS and relational data ...

PolyBase: Gaining insights from HDFS and relational data in SQL Server 2016. ... services, devices and ... Deep Dive on Amazon Relational Database Service.
Read more

Amazon Aurora Deep-Dive - Vancouver - January 14, 2016 ...

Technical Level: Medium Abstract: At Amazon Web Services (AWS) we are working on a number of innovative services covering different aspects of ...
Read more