Live Migration Runbook — All Engines

What Is Live Migration?

Live migration keeps the target continuously in sync with the source so application downtime during cutover is measured in seconds. Two sequential phases apply to all engines:

Engine Support Matrix

Retention Policy — Minimum 30 Days

Cutover Flow (All Engines)

Downtime = time between "Quiesce Source" and "Flip Apps" — typically 10–60 seconds. Sequence is identical across all engines; only the change mechanism differs.

SQL Server — Prerequisites

USE EndriasBridge;
GO
-- Agent status
SELECT servicename, status_desc FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server Agent%';
-- Recovery model
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME();
-- CDC status
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();
-- Login check
SELECT IS_MEMBER('db_owner') AS is_db_owner, SUSER_SNAME() AS login;
-- Tables without PK
SELECT s.name+'.'+t.name AS table_no_pk
FROM   sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id
LEFT JOIN sys.indexes i ON i.object_id=t.object_id AND i.is_primary_key=1
WHERE  s.name NOT IN('cdc','sys') AND i.object_id IS NULL;

Grant permissions

USE EndriasBridge;
GO
ALTER ROLE db_owner ADD MEMBER [Migration];              -- source
ALTER ROLE db_owner ADD MEMBER [MigrationUser];       -- target (Azure SQL / RDS)

SQL Server — Enable CDC on Database

USE EndriasBridge;
GO
EXEC sys.sp_cdc_enable_db;
GO
-- Grant cdc schema after it is created
GRANT SELECT ON SCHEMA::cdc TO [Migration];
GO
-- Verify
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();
SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.EndriasBridge%';

-- Azure SQL Database ONLY — run immediately after sp_cdc_enable_db
-- (on-prem / EC2 / RDS: SQL Agent creates the job automatically — skip this)
EXEC sys.sp_cdc_add_job @job_type = N'capture';
GO

SQL Server — Enable CDC per Table

USE EndriasBridge;
GO
-- Generate enable statements for all tables not yet CDC-enabled
SELECT
    'EXEC sys.sp_cdc_enable_table @source_schema=N'''+s.name+''','
    +'@source_name=N'''+t.name+''','
    +'@role_name=NULL,@supports_net_changes=1;' AS stmt
FROM   sys.tables t
JOIN   sys.schemas s        ON t.schema_id=s.schema_id
LEFT JOIN cdc.change_tables ct ON ct.source_object_id=t.object_id
WHERE  s.name NOT IN('cdc','sys') AND ct.capture_instance IS NULL
ORDER BY s.name, t.name;
-- Paste and execute the generated statements, then verify:
SELECT s.name AS schema_name, t.name AS table_name,
       ct.capture_instance, ct.supports_net_changes
FROM   cdc.change_tables ct
JOIN   sys.tables  t ON ct.source_object_id=t.object_id
JOIN   sys.schemas s ON t.schema_id=s.schema_id
ORDER BY s.name, t.name;
-- supports_net_changes must = 1 on all rows

SQL Server — Set 30-Day Retention

USE EndriasBridge;
GO

-- Cleanup: 30 days retention, higher threshold for large tables
EXEC sys.sp_cdc_change_job
    @job_type  = 'cleanup',
    @retention = 43200,   -- 30 days x 1440 min/day
    @threshold = 50000;  -- rows per cleanup cycle (default 5000)

-- Capture: reduce polling to 1 second for lower latency
EXEC sys.sp_cdc_change_job
    @job_type        = 'capture',
    @pollinginterval = 1;

-- Restart jobs to apply new settings
EXEC msdb.dbo.sp_stop_job  N'cdc.EndriasBridge_cleanup';
EXEC msdb.dbo.sp_start_job N'cdc.EndriasBridge_cleanup';
EXEC msdb.dbo.sp_stop_job  N'cdc.EndriasBridge_capture';
EXEC msdb.dbo.sp_start_job N'cdc.EndriasBridge_capture';
GO

-- Verify
SELECT job_type, retention, threshold, pollinginterval FROM cdc.cdc_jobs;
-- cleanup.retention must show 43200

SQL Server on AWS RDS — Notes

# Set backup retention to 35 days (RDS max)
aws rds modify-db-instance \
  --db-instance-identifier mydb \
  --backup-retention-period 35 \
  --apply-immediately

-- Also set CDC job retention:
EXEC sys.sp_cdc_change_job
  @job_type='cleanup', @retention=43200;

Azure SQL Database — CT Sync (Recommended)

Enable Change Tracking (CT Sync)

-- Database level: 30-day retention
ALTER DATABASE EndriasBridge
SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 30 DAYS, AUTO_CLEANUP = ON);
GO

-- Generate per-table enable statements
SELECT 'ALTER TABLE '+s.name+'.'+t.name
      +' ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON);'
FROM   sys.tables t
JOIN   sys.schemas s ON t.schema_id=s.schema_id
WHERE  s.name NOT IN('cdc','sys')
ORDER BY s.name, t.name;

Or: CDC on Azure SQL DB (if Agent-managed CDC is acceptable)

EXEC sys.sp_cdc_enable_db;
GO
-- Required on Azure SQL: manually add the capture job (not auto-created unlike on-prem)
EXEC sys.sp_cdc_add_job @job_type = N'capture';
GO
EXEC sys.sp_cdc_change_job @job_type='cleanup', @retention=43200;
GO
-- Note: Cannot restart Agent jobs manually on Azure SQL DB

SQL Server — Verify and Start Stream

Pre-stream verification checklist

1. Confirm is_cdc_enabled = 1 and all tables show supports_net_changes = 1
2. Confirm capture Agent job is Running in msdb.dbo.sysjobactivity
3. Confirm cdc.cdc_jobs.retention = 43200
4. Run full load: Source DBA → Migrate tab → Sync Mode = CDC Stream → click Start Migration
   Tool saves LSN anchor at this point. Full load runs. Source stays live.
5. Wait for full load to complete (watch Migration Log — all tables show row counts)
6. Start CDC Stream — tool replays changes from the saved LSN, then enters tail mode
7. Validate: run a test UPDATE on source, confirm it appears on target within 1 second

USE EndriasBridge;
-- Quick validation after streaming starts: check lag
SELECT
    sys.fn_cdc_get_max_lsn()   AS source_head_lsn,
    sys.fn_cdc_get_min_lsn(N'Person_Address') AS capture_from_lsn;
-- source_head_lsn should equal (or trail by milliseconds) the last LSN the tool processed

-- Test change to confirm replication
UPDATE Person.Address SET City = 'StreamTest' WHERE AddressID = 1;
-- Verify on PostgreSQL target: SELECT city FROM person.address WHERE addressid = 1;
-- Expected: 'StreamTest' within ~1 second

MySQL — Prerequisites

-- Check current settings
SHOW VARIABLES LIKE 'binlog_format';             -- Must be ROW
SHOW VARIABLES LIKE 'log_bin';                   -- Must be ON
SHOW VARIABLES LIKE 'server_id';                 -- Must be non-zero
SHOW VARIABLES LIKE 'binlog_row_image';          -- Recommend FULL
SHOW VARIABLES LIKE 'expire_logs_days';          -- 5.7: must be 30
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- 8.0: must be 2592000
SHOW MASTER STATUS;                               -- Current binlog file + position

MySQL On-Prem / VM / EC2 — Setup

Step 1 — Edit my.cnf

# /etc/mysql/mysql.conf.d/mysqld.cnf  (Ubuntu/Debian)
# /etc/my.cnf                         (RHEL/CentOS/Amazon Linux)

[mysqld]
server-id                    = 1
log_bin                      = /var/lib/mysql/mysql-bin
binlog_format                = ROW
binlog_row_image             = FULL
max_binlog_size              = 512M

# MySQL 5.7:
expire_logs_days             = 30

# MySQL 8.0+ (replaces expire_logs_days):
binlog_expire_logs_seconds   = 2592000   # 30 days x 86400

Step 2 — Restart and verify

sudo systemctl restart mysql    # Ubuntu/Debian
sudo systemctl restart mysqld   # RHEL/CentOS
mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog_format';"
mysql -u root -p -e "SHOW MASTER STATUS;"

Step 3 — Create replication user

CREATE USER 'migration'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'migration'@'%';
GRANT SELECT ON mysql.* TO 'migration'@'%';
FLUSH PRIVILEGES;

MySQL on AWS RDS / Aurora — Setup

Parameter Group

# AWS CLI
aws rds modify-db-parameter-group \
    --db-parameter-group-name my-mysql8-params \
    --parameters \
      "ParameterName=binlog_format,ParameterValue=ROW,ApplyMethod=immediate" \
      "ParameterName=binlog_row_image,ParameterValue=FULL,ApplyMethod=immediate" \
      "ParameterName=binlog_expire_logs_seconds,ParameterValue=2592000,ApplyMethod=immediate"

Retain binlogs for 30 days (RDS stored procedure)

-- This overrides the parameter group for log file retention
CALL mysql.rds_set_configuration('binlog retention hours', 720);  -- 720h = 30 days

-- Verify
CALL mysql.rds_show_configuration;
-- Look for: binlog retention hours = 720

Create replication user on RDS

CREATE USER 'migration'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'migration'@'%';
FLUSH PRIVILEGES;

Azure Database for MySQL Flexible Server — Setup

# Azure CLI
az mysql flexible-server parameter set \
    --resource-group myRG --server-name my-mysql-flex \
    --name binlog_format --value ROW

az mysql flexible-server parameter set \
    --resource-group myRG --server-name my-mysql-flex \
    --name binlog_expire_logs_seconds --value 2592000

# Verify in portal: Server parameters → search "binlog"

MySQL — Retention Summary

PostgreSQL — Prerequisites

SHOW wal_level;
SHOW max_replication_slots;
SELECT * FROM pg_replication_slots;

PostgreSQL On-Prem / VM / EC2 — Setup

Step 1 — postgresql.conf

# /etc/postgresql/15/main/postgresql.conf
wal_level               = logical
max_replication_slots   = 10
max_wal_senders         = 10
wal_keep_size           = 51200    # 50 GB backstop if slot dropped

Step 2 — pg_hba.conf

# Allow replication from migration host
host    replication     migration_user  10.0.0.0/24     scram-sha-256

Step 3 — Create replication user

CREATE USER migration_user WITH REPLICATION LOGIN PASSWORD 'StrongP@ssw0rd!';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_user;

Step 4 — Restart and create slot

sudo systemctl restart postgresql

-- Create logical replication slot (tool creates this automatically)
SELECT pg_create_logical_replication_slot('sourcedba_slot', 'pgoutput');

-- Monitor retained WAL
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots;

PostgreSQL on AWS RDS / Aurora — Setup

# Parameter Group (reboot required for static params)
aws rds modify-db-parameter-group \
    --db-parameter-group-name my-pg15-params \
    --parameters \
      "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
      "ParameterName=max_replication_slots,ParameterValue=10,ApplyMethod=pending-reboot" \
      "ParameterName=max_wal_senders,ParameterValue=10,ApplyMethod=pending-reboot"

aws rds reboot-db-instance --db-instance-identifier my-pg-db

-- After reboot: verify
SHOW wal_level;   -- Must show: logical

-- Create replication user (use rds_replication role, not SUPERUSER)
CREATE USER migration_user WITH PASSWORD 'StrongP@ssw0rd!';
GRANT rds_replication TO migration_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_user;

Azure DB for PostgreSQL Flexible Server — Setup

# Azure CLI
az postgres flexible-server parameter set \
    --resource-group myRG --server-name my-pg-flex \
    --name wal_level --value logical

az postgres flexible-server parameter set \
    --resource-group myRG --server-name my-pg-flex \
    --name max_replication_slots --value 10

az postgres flexible-server restart \
    --resource-group myRG --name my-pg-flex

CREATE USER migration_user WITH REPLICATION LOGIN PASSWORD 'StrongP@ssw0rd!';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_user;

PostgreSQL — WAL Retention (30-Day Equivalent)

-- Weekly check: alert if retained_wal > 20 GB or slot age > 25 days
SELECT
    slot_name, active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots;

MongoDB — Prerequisites (Replica Set Required)

Convert standalone to single-node replica set

# mongod.conf — add replication section
replication:
  replSetName: "rs0"

# Restart then initiate
mongosh --eval "rs.initiate()"

Create migration user

use admin
db.createUser({
  user: "migration",
  pwd:  "StrongP@ssw0rd!",
  roles: [
    { role: "readAnyDatabase", db: "admin" },
    { role: "clusterMonitor",  db: "admin" }
  ]
})

MongoDB — Oplog Retention (30-Day Window)

// MongoDB 4.4+ — set 30-day minimum window (720 hours)
use admin
db.adminCommand({
  replSetResizeOplog: 1,
  minRetentionHours: 720
})

// All versions — resize oplog (adjust size for your write volume)
db.adminCommand({ replSetResizeOplog: 1, size: 102400 })  // 100 GB in MB

# mongod.conf — persistent settings
replication:
  replSetName: "rs0"
  oplogSizeMB: 102400          # 100 GB
  oplogMinRetentionHours: 720  # 30 days (MongoDB 4.4+)

// Verify oplog window
rs.printReplicationInfo()
// Expected: "log length start to end: 2592000secs (720hrs)"

MongoDB Atlas — Change Streams

# Connection string for Source DBA (Atlas):
# mongodb+srv://migration:<pwd>@cluster0.abcde.mongodb.net/?retryWrites=true&w=majority

Cosmos DB — Change Feed

Deletion handling — soft-delete pattern

// Cosmos DB Change Feed does not emit deleted documents.
// Mark deletions with a flag; tool reads isDeleted=true and issues DELETE on target.
{
  "id": "doc-123",
  "isDeleted": true,
  "deletedAt": "2026-07-08T00:00:00Z"
}

DynamoDB Streams

Enable streams

# NEW_AND_OLD_IMAGES required for full UPDATE tracking
aws dynamodb update-table \
    --table-name MyTable \
    --stream-specification StreamEnabled=true,StreamViewType=NEW_AND_OLD_IMAGES

aws dynamodb describe-table --table-name MyTable \
    --query "Table.StreamSpecification"

IAM permissions

{
  "Effect": "Allow",
  "Action": [
    "dynamodb:GetRecords", "dynamodb:GetShardIterator",
    "dynamodb:DescribeStream", "dynamodb:ListStreams",
    "dynamodb:Scan", "dynamodb:Query"
  ],
  "Resource": [
    "arn:aws:dynamodb:us-east-1:123456789:table/MyTable",
    "arn:aws:dynamodb:us-east-1:123456789:table/MyTable/stream/*"
  ]
}

Pre-Cutover Checklist (All Engines)

• Full load complete — all tables / collections show match in Compare tab
• Change stream running — status shows Streaming
• Lag < 1 second for at least 5 minutes (relational) / oplog lag < 10s (MongoDB)
• Retention set to 30 days (or 12h max for DynamoDB)
• Smoke test passed — test DML propagated to target within expected latency
• Application maintenance window scheduled and team on standby
• Target row / document counts verified against source
• Rollback procedure reviewed (see Rollback Plan)
• Connection strings / DNS change tested in staging
• Monitoring alerts configured on target (CloudWatch / Azure Monitor / Atlas)

Execute Cutover (All Engines)

SELECT sys.fn_cdc_get_max_lsn() AS max_lsn;

SHOW MASTER STATUS;

SELECT pg_current_wal_lsn();

rs.printReplicationInfo()

Post-Cutover Validation

• Application login succeeds on target
• Core business transactions complete without errors
• Row / document counts match (Compare tab)
• No FK constraint violations (SQL Server: DBCC CHECKCONSTRAINTS)
• Query / request latency within baseline range
• Monitoring alerts active on target
• Source server kept online in read-only mode for 30-day rollback window

Troubleshooting

Rollback Plan

Cutover aborted before flip

1. Re-enable writes to source — no data lost
2. Stop change stream in tool
3. Investigate, fix, re-attempt

Rollback after flip

1. Stop application writes to target
2. Reverse migration in Source DBA: swap source ↔ target connections
3. Run Full or Incremental sync from target back to source
4. Flip applications back to source

Cleanup after successful migration (SQL Server)

USE EndriasBridge;
GO
EXEC sys.sp_cdc_disable_db;
GO
SELECT name, is_cdc_enabled FROM sys.databases WHERE name=DB_NAME();

Cleanup after successful migration (PostgreSQL)

-- CRITICAL: drop the slot or WAL accumulates indefinitely
SELECT pg_drop_replication_slot('sourcedba_slot');

Retention Quick-Reference — All Engines