The Magic that is DMS Who is this Tim guy... Fox! 20 years in IT - PowerPoint PPT Presentation

Database Migration Service Postgres to Aurora... in 372 steps Timothy R. Fox Not a DBA The Magic that is DMS

Who is this Tim guy... • Fox! • 20 years in IT ‒ Worked at Companies from 14 to 2,400 employees ‒ Unix/Linux Admin by Trade ‒ Started with SunOS (not Solaris) • Now Head of Cloud Managed Services for CorpInfo ‒ Prior VP, Tech Ops for a $500M Public Company • PostgreSQL DBA ( not Extraordinaire ) ‒ I’m the reason you don’t raise your hand when someone asks if you know something

Hiked the JMT this Past Summer • Tuolumne Meadows in Yosemite, to Mount Whitney • 200+ Miles • 20 Days … • But enough about me

What were we trying to do? Fix stuff … • 2 TB Postgres DB • Running on RDS • Doing 20-25,000 Transactions a second • Multiple outages lasting 2-3 minutes each.

What stuff we were a fix’n • RDS runs on EBS • Reduce IO Dependency on EBS • Known issues (now corrected) with IO “hiccups” on snapshots on Postgres • Recovery/Build time for Read Replicas really high

Looked at Aurora

How to Get there • Run AWS Schema Conversion Tool ‒ Define Schema changes and optionally apply those changes to a new Empty Aurora DB • Use DMS to move the data • Apply the remaining schema objects • Flip

As provided by AWS Sales What the heck is this DMS thing anyway?!?

Start your first migration in 10 minutes or less AWS Keep your apps running during the migration Database Replicate within, to or from Amazon EC2 or RDS Migration Service Move data to the same or different database engine

AWS DATABASE MIGRATION SERVICE AWS Database Migration Service Customer AWS VPN Premises Internet Let the AWS Database Migration • Start a replication instance Service create tables, load data and keep them in sync • Connect to source and target databases Application Users • Select tables, schemas or databases Switch applications over to the target at your convenience

HOW DOES DMS WORK? • Provisions a replication server • Define source and target database endpoints • Create a task to migrate data from source to target • Existing data is copied from source tables to tables on the target • Changes on source are captured while the tables are loaded • Once load is complete, buffered changes are applied to the target • Additional changes captured on the source are applied to the target until the task stopped or terminated

START FULL LOAD Source Target Replication Instance

LOAD IS TABLE BY TABLE Source Target Replication Instance • Creates tables at target database • Sets up metadata required at target • Populates data from source • Each process loads one entire table • Can use multiple processes • Can be paused ‒ When restarted will continue from where it was stopped ‒ Will reload any tables that were currently in progress

DMS – CHANGE DATA CAPTURE (CDC) • “No Touch” design ‒ Reads recovery log of source database ‒ Using the engine’s native change data capture API ‒ No agent required on the source • Some requirements ‒ Oracle : Supplemental logging required ‒ MySQL : Full image row level bin logging required ‒ SQL Server : Recovery model bulk logged or full ‒ Postgres : wal_level = logical; max_replication_slots >= 1; max_wal_Senders >=1; wal_sender_timeout = 0 • Changes captured and applied as units of single committed transactions • Activated when load starts • No changes are applied until load completes, then applied as soon as possible in near real-time

CHANGES ARE TRANSACTIONAL & COME FROM THE LOGS Update Migration Server t1 t2 t1 t2 Source Target

CUTOVER – SHUT DOWN APPS & APPLY REMAINING CHANGES Update Target Replication Instance Source

FLIP! Update Target Replication Instance Source

AWS SCHEMA CONVERSION TOOL Assessment Report • SCT analyses the source database and provides a report with a recommended target engine and information on automatic and manual conversions Code Browser and recommendation engine • Highlights places that require manual edits and provides architectural and design guidelines.

OTHER DMS MAGIC NOT PART OF THE DEMO …

MULTIPLE TARGETS Target Source Target Replication Instance Target

MULTIPLE SOURCES Source Target Source Migration Server Source

MULTIPLE SOURCES & TARGETS Source Target Source Migration Server Target Source

HOMOGENOUS OR HETEROGENEOUS Oracle Oracle Replication Instance MySQL SQL Server Replication Instance Oracle Aurora Replication Instance

Lets See it in Action