Best Practices to Move SQL Server to Amazon RDS

Yatendra Singh    Yatendra Singh     Updated On - 29 May 2019

As a cloud-based web service, Amazon RDS – Relation Database Service, offers the user ease in various matters related to databases, such as simple & smooth database set up, easy management, database scaling, cost-efficiency, and more.

The CPU, memory, storage, IOPS, comes all packed into one with independent scalability, automatically managed backups, automatic failure detection, software patching, and more perks related to Amazon RDS. And these perks are enough to make the move to the relational database service.

Setting up a database in Amazon RDS and using backup & restore can be a tricky and hectic task, as the whole procedure of either database setup is complex and long. Thus, the chances of something going wrong are positive.

In this write-up, we’ll discuss all on Amazon RDS focusing on the best practices to move SQL Server database to Amazon RDS, followed by why you should consider migrating to Amazon RDS, associated limitations and recommendations, and the procedure of native backup & restore. At the end of this write-up, we’ve provided you with a demonstration of the easiest SQL server database to Amazon RDS migration.

Why Should You Consider Migrating SQL Server Database to Amazon RDS?

Let’s first discuss why should you consider migrating to Amazon RDS. We’ve given some benefits above that comes with Amazon RDS but let’s dive a little deeper in a simple manner.

As Amazon RDS is cloud-based, it offers the simplicity to set up and eliminates the complexity involved for database management and maintenance; thus, the firm can put more focus on its core business.

On July 27, 2016, announced in an official blog, Amazon RDS received support for Native Backup & Restore to Amazon S3, which earlier wasn’t available, therefore now you can backup and restore the SQL Server data from Amazon platform.

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:

  1. Copy backups of the on-premises database to Amazon S3.
  2. Restore SQL Server database backup to the RDS SQL Server instance.
  3. Encrypt database backup using AWS Key Management Service (KMS).
  4. 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.

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:

  1. Connect to SQL Server Database.
    Note: Click here to know how to connect to your database.

  2. 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.
    2. Click Browse to select the database file.

    3. 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.

    4. After you’ve selected the database file, click Recover.
    5. Wait while the file is being read; the time taken to read the database file is proportional to the file size.
    6. After the file is loaded successfully, all database objects will be available to preview.
    7. Click on the + icon to expand orto collapse the object.
      To generate live data preview, select the parent or child object by clicking on it, as shown below.
      To start the database migration, click on the Migrate button.

    8. Select the Amazon RDS option, and input Instance Name, Server Authentication credentials, as below.
    9. Now, select the database name from the drop-down, and click Ok.
    10. 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.