Best practices to move SQL server to amazon RDS

Read time 9 minutes

Benefits of Amazon RDS for a business

Amazon Web Services brings a lot of benefits like Amazon RDS. It reduces the activities required for setting up, operating, and maintaining on-premises relational databases. It can run all the tasks to manage a large-sized database, and so you need to spend minimum time for server maintenance.

Amazon RDS lets you create multiple types of relational databases like PostgreSQL, SQL Server, Maria DB, Oracle, MySQL, etc. Amazon RDS will take care of all the maintenance of the database, take a routine backup, and allows the use of the latest version of the selected database application.

Cost-effective :- AWS lets you pay only for the service you use and the time spent in the service. There is on-demand pricing in which you can pay for a fixed time without any long-term commitment. If you use a reserved instance, then the overall expenditure will also reduce substantially.
Scalable :- Scalability in Amazon RDS’s database instance is just a click away. To lower the downtime, you can use a dedicated API for quick results. If you want to only access the database items, then you can choose the read replicas to get the information.
Quick :- By choosing the faster storage drive like an SSD, you can increase the throughput of the whole RDS database instance. There are multiple storage options for high OLTP applications as well as general-purpose instances.
Secure :- The highlight of Amazon RDS is its security features. The user can create the database instance in a separate VPC (Virtual Private Cloud) and allow others to access the database using an IPsec VPN. It is encrypted in all the conditions, whether the database is in transit or at rest.

Amazon RDS native backup and restore support

With the Native Backup/Restore support for SQL Server database, the target users can now create native database backups from the RDS instance and store it in Amazon S3 bucket. As per the need, you can restore the database backup either to on-premises SQL Server or another RDS instance.

With native backup/restore for SQL Server database, the user (DBA) can do the following:

Copy backups of the on-premises database to Amazon S3.
Restore SQL Server database backup to the RDS SQL Server instance.
Encrypt database backup using AWS Key Management Service (KMS).
With Amazon S3, the user gets the disaster recovery option for the SQL Server database.

Import and export SQL server database in Amazon RDS

With Amazon RDS, the approach is quite different, in place of using the local file system on the SQL database server, the user (DBA) accesses files stored in Amazon S3.

The migration method to RDS differs from what it is usually for normal SQL Server migration – the user creates a database backup from the local server, saves it on Amazon S3 and later restores it either to an existing or new Amazon RDS DB instance. Below illustration demonstrates the same.
Import and Export SQ

Source: AWS Documentation

Native backup/restore is made available to all regions of AWS including both Single-AZ & Multi-AZ DB instances and supports all available versions of Microsoft SQL Server.

Limitations and recommendations of backup and support

With all the usefulness and the benefits that Amazon RDS offers, there are some limitations and recommendations that users should be aware of.

The DBA can’t backup to or restore from an S3 bucket to a different AWS region than that of the users’ Amazon RDS DB instance.
SQL Server database backups exceeding the maximum size of 1TB aren’t supported.
Database backup restoration from one time zone to different isn’t recommended.
While the maintenance is ongoing, or an Amazon RDS database snapshot is in process, creating native backups aren’t allowed.
Within a transaction, calling RDS procedures for native backup/restore isn’t allowed.
Native backup files are encrypted with the AWS KMS using the Encryption-Only crypto mode.

Backup and restore in Amazon RDS

To start using Amazon RDS service, first you need to set up the native backup and restore which includes an Amazon S3 bucket to store the database backup (BAK) files, the AWS Identity and Identity Access Management role to access the S3 bucket, and the SQLSERVER_BACKUP_RESTORE option added to the option group on users’ DB instance. Considering that you are ready with them, you can proceed with the database migration process to Amazon RDS using native backup and restore.

Database migration using native backup and restore

Before proceeding with the migration process, make sure that native backup and restore is enabled & configured, follow the steps given below:

Connect to SQL Server Database.
Note: Click here to know how to connect to your database.
Call an Amazon RDS stored procedure to initiate.
Following are RDS stored procedure you can call:

Backup SQL Server Database

Use the rds_backup_database stored procedure to back up the SQL Server database.
To call RDS database backup procedure following parameters are necessary:
@source_db_name – Name of the database you want to backup.
@s3_arn_to_backup_to – Amazon S3 bucket you want to use for database backup.

Note: For the second parameter, it’s the combination of two, S3 bucket name + key ARN.

ARN stands for “Amazon Resource Name.”
Following are Optional Parameters
@kms_master_key_arn
@overwrite_S3_backup_file
@type_

Example
Differential Backup Without Encryption

exec msdb.dbo.rds_backup_database
@source_db_name=’database_name’,
@s3_arn_to_backup_to=’arn:aws:s3:::bucket_name/file_name_and_extension’,
@overwrite_S3_backup_file=1,
@type=’differential’;

Full Database Backup with Encryption

exec msdb.dbo.rds_backup_database
@source_db_name=’database_name’,
@s3_arn_to_backup_to=’arn:aws:s3:::bucket_name/file_name_and_extension’,
@overwrite_S3_backup_file=1,
@type=’FULL’;

Check Last Backup or Snapshot Using the given SQL Query

select top 1
database_name
, backup_start_date
, backup_finish_date
from msdb.dbo.backupset
where database_name=’name_of_db’
and type = ‘D’
order by backup_start_date desc;

Use the rds_restore_database stored procedure to restore the database.
For restoration, the following parameters are necessary:
@restore_db_name – Database name you want to restore.
@s3_Arn_to_restore_from – ARN prefix of the backup files from which you want to restore the database.

Following are Optional Parameters
@kms_master_key_arn

Database Restore Without Encryption

exec msdb.dbo.rds_restore_database
@restore_db_name=’database_name’;
@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/file_name_and_extension’;

Database Restore with Encryption

exec msdb.dbo.rds_restore_database
@restore_db_name=’database_name’;
@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/file_name_and_extension’;
@kms_master_key_arn=’arn:aws:kms:region:account-id:key/key-id’;

Single Database File Restore

exec msdb.dbo.rds_restore_database
@restore_db_name=’database_name’;
@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/backup_file’;

Multiple Database File Restore
Note: Make sure all database backup files have the same prefix, and no other files use it to avoid errors.

exec msdb.dbo.rds_restore_database
@restore_db_name=’database_name’;
@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/backup_file_*’;

Track the Task Status
Call the rds_task_status stored procedure to track the task status.
Note: On calling the stored procedure will return the status of all tasks if no parameter is provided.

Following are Optional Parameters

@db_name
@task_id

Example Query

exec msdb.dbo.rds_task_status @db_name=’database_name’;

Note: The task status procedure can return different columns as per the parameter specified.

Using the call procedures given above, you can backup database, restore the database, and track the task status.
But to simplify things to the next level and for the making the database migration to Amazon RDS more easy and fun, we’d suggest using a SQL migration tool.

Kernel migrator for SQL server for simple migration

It’s an advanced tool that assists you with SQL Server database migration to both Microsoft Azure SQL & Amazon RDS with ease irrespective of the database or backup file size. Not all users run the latest version of Microsoft SQL Server; thus, to make the software available for everyone, we’ve made it compatible with a wide range of SQL Server versions you can find on the internet.

Follow below steps to migrate SQL Server Database to Amazon RDS using the easiest method:
1. Launch Kernel Migrator for SQL Server.
  Free Download 100% Secure
2. Click Browse to select the database file.
  
  Note: You can select both corrupt/damaged or healthy database file; upon successful load, the tool will fix the issue of file corruption, once you proceed to migration.
3. After you’ve selected the database file, click Recover.
4. Wait while the file is being read; the time taken to read the database file is proportional to the file size.
5. After the file is loaded successfully, all database objects will be available to preview.
6. Select the Amazon RDS option, and input Instance Name, Server Authentication credentials, as below.
7. Now, select the database name from the drop-down, and click Ok.
8. Once the migration is complete you’ll be notified, click Ok.
You’ve migrated the selected SQL Server database to Amazon RDS successfully. To confirm login to RDS using sign-in credentials and check.

Wrap up

Kernel Migrator for SQL enables the users to open both corrupt/damaged or healthy MDF/NDF/LDF files, and as a third-party tool, it offers the easiest database migration approach whether to Microsoft Azure SQL or Amazon RDS.

Free Download 100% Secure