Three ways to migrate SQL Server to AWS
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 –
- Tasks with Migration Validation
- Tasks can pick up where they left off in case of network outages etc.
- Can go to Multi AZs as well – if one AZ DMS service is down, the other one picks up the replication
- Can also go to Aurora or Redshift directly instead of RDS
Steps – DMS Service
- Create a Replication Instance
- Connect a SRC and TARGET database
- Create Tasks
Steps – Source SQL Server
- Enable Replication for the DB using sp_replicationdboption
- Create a publication artefact with a 1 = 0 filter for the database (for logging all transactions to txaction log)
- 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 –
- Tasks with Migration Validation
- Tasks can pick up where they left off in case of network outages etc.
- Can go to Multi AZs as well – if one AZ DMS service is down, the other one picks up the replication
- Can also go to Aurora or Redshift directly instead of RDS
Steps – DMS Service
- Create a Replication Instance
- Connect a SRC and TARGET database
- Create Tasks
Steps – Source SQL Server
- Enable Replication for the DB using sp_replicationdboption
- Create a publication artefact with a 1 = 0 filter for the database (for logging all transactions to txaction log)
- Use fn_dblog() and fn_dump_dblog() functions to read changes from the transaction log to the LSN
Leave a Reply