There are many ways to get your SQL server database onto AWS.

Backup and Restore

  • Disadvantages – Manual Process, The databases don’t sync up automatically, and you have to manually ensure that they are in sync.
  • Advantages – Simple and Free. Can go directly to EC2 or to S3 bucket first. From S3, can go to an RDS instance

Native SQL Server Replication (Always On Replication)

No real downsides – unless going from an unencrypted to an encrypted (or vice versa) database. In that case, this will not work

AWS DMS – Bulk Load Data with Ongoing Replication

Advantages  –

  1. Tasks with Migration Validation
  2. Tasks can pick up where they left off in case of network outages etc.
  3. Can go to Multi AZs as well  – if one AZ DMS service is down, the other one picks up the replication
  4. Can also go to Aurora or Redshift directly instead of RDS

Steps –  DMS Service

  1. Create a Replication Instance
  2. Connect a SRC and TARGET database
  3. Create Tasks

Steps – Source SQL Server

  1. Enable Replication for the DB using sp_replicationdboption
  2. Create a publication artefact with a 1 = 0 filter for the database (for logging all transactions to txaction log)
  3. Use fn_dblog() and fn_dump_dblog() functions to read changes from the transaction log to the LSN

DMS Pricing

https://aws.amazon.com/dms/pricing/

There are many ways to get your SQL server onto AWS.

Backup and Restore

  • Disadvantages – Manual Process, The databases don’t sync up automatically, and you have to manually ensure that they are in sync.
  • Advantages – Simple and Free. Can go directly to EC2 or to S3 bucket first. From S3, can go to an RDS instance

Native SQL Server Replication (Always On Replication)

No real downsides – unless going from an unencrypted to an encrypted (or vice versa) database. In that case, this will not work

AWS DMS – Bulk Load Data with Ongoing Replication

Advantages  –

  1. Tasks with Migration Validation
  2. Tasks can pick up where they left off in case of network outages etc.
  3. Can go to Multi AZs as well  – if one AZ DMS service is down, the other one picks up the replication
  4. Can also go to Aurora or Redshift directly instead of RDS

Steps –  DMS Service

  1. Create a Replication Instance
  2. Connect a SRC and TARGET database
  3. Create Tasks

Steps – Source SQL Server

  1. Enable Replication for the DB using sp_replicationdboption
  2. Create a publication artefact with a 1 = 0 filter for the database (for logging all transactions to txaction log)
  3. Use fn_dblog() and fn_dump_dblog() functions to read changes from the transaction log to the LSN

DMS Pricing

https://aws.amazon.com/dms/pricing/

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.