How to Migrate SQL Server Databases to AWS RDS

Are you looking to migrate your SQL Server databases to AWS RDS with minimal downtime? In this article, we will explore the steps to migrate your on-premises SQL Server database or SQL Server on EC2 instance to AWS RDS SQL Server using the AWS Database Migration Service (AWS DMS).

Configuring SQL Server on EC2 Instance as a Source Database

Before we begin the migration process, we need to configure the SQL Server on EC2 instance as the source database. This involves creating an EC2 instance with SQL Server 2019, restoring a sample database, and taking a full database backup.

USE [master]
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N'C:\SQL\AdventureWorks2019.bak' WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf',
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf',
NOUNLOAD, STATS = 5

BACKUP DATABASE [AdventureWorks2019] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\AdventureWorks2019.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Restoring the Full Database Backup on AWS RDS SQL Server

Once we have the full database backup, we can proceed to restore it on the AWS RDS SQL Server. This involves uploading the backup file to an S3 bucket, creating an IAM policy and role, and restoring the backup on the RDS instance.

exec msdb.dbo.rds_restore_database @restore_db_name = 'AdventureWorks2019', @s3_arn_to_restore_from = 'arn:aws:s3:::sqlshackdemo17112020/AdventureWorks.bak';

Exec msdb.dbo.rds_task_status @task_id = 4

Configuring AWS DMS Replication Instance

Next, we need to configure an AWS DMS replication instance for the database migration. This involves creating a replication instance, configuring the source and target endpoints, and creating a database migration task.

Creating a Database Migration Task

Finally, we can create a database migration task in AWS DMS to replicate the data changes from the source to the target. This involves specifying the replication instance, source and target endpoints, migration type, and enabling data validation.

Once the database migration task is created, AWS DMS will continuously replicate the data from the source to the target AWS RDS SQL Server. You can monitor the progress and validate the data on both source and target.

Conclusion

In this article, we have covered the steps to migrate SQL Server databases to AWS RDS SQL Server using the AWS Database Migration Service. By following these steps, you can migrate your databases with minimal downtime and ensure a smooth transition to the cloud.

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on

Let's work together