Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to migrate your SQL Server database to Amazon RDS

09 March,2022 by Tom Collins

How to migrate your SQL Server database to Amazon RDS

Main Points - These are summarized notes, for a full details I've added the video at the bottom of these notes

- RDS fully managed service and provides as a managed experience. 

 The aim is to free up DBAs time involved on common DBA tasks , & move away from focusing on infrastructure & redeploy IT resources that deliver value .

 RDS supports Aurora ,PostgreSQL,MySQL,MariaDB,SQL Server , Oracle

  Automated Backups - Point in Recovery for your DB Instance

                                - Scheduled daily volume snapshots of entire instance - EBS block level snapshot , t-log backups every 5 mins. Different from native backups. Difference between EBS & SQL Server native backups is that EBS utilises 

                              -Difference between application consistence & crash consistency . Snapshots are application consistent . Important point!

                             - For larger installs will separate TempDB onto instance store

                            -  Manual Snapshot - can trigger at your own schedule , storage will be charges

Native Backups are supported

         - backup & restore directly to & from Amazon S3 bucket. For example , if you want to move databases into \ or out of AWS 

         - supports compression

         - Only full  & diff backups

        - Multi-file backup \restore   - performance enhnacement

High Availability (multi-AZ)

         - probably the most important feature

         - part of the managed service experience

        - under the hood relies on database mirroring (for older versions)& always on AG

       - Backup retention > 0 

       - Full Recovery mode

       - Synchronous replication

        -Automatic & manual failover - application requires retry logic 

       -To support a fast(er) failover - the application must use a modern driver   on the client side that can support multi-subnet failover = true   & be connected to the listener endpoint. This way you can short-circuit the DNS propagation.

       - There is no ability to send over the read traffic to the hot standby 

        - There is a separate feature to create read replicas , they are promotable 


 Cross-region Automated Backups - enable and select a second region of you choice to stream your backups\t-logs to a second region.     

Integrated Windows Authentication  - AWS managed AD is required

                                                      - can cross VPC and accont boundary

                                                      - it requires launching AWS AD Managed service

                                                       - Kerberos & NTLM

                                                       - When   you join an RDS instance to the AWS AD managed   service - it will only join as a FQDN. 

Features supported - Integration Services

                             - Reporting Services

                             - Analysis Services

                             - SQL Server Agent Jobs

                           - DB Mail

                           -MS DTC

                          -Extended Events   

The main point is that unless there is an exotic feature used than most SQL Server features are supported.         

Building blocks RDS is built on 

                           -Memory optimized Instances  - multiple t-shirt sizes --> wide variety 

                           - map your existing workload  to the different t-shirt sizes

                          - Amazon Elastic Block Store 

                           - solid state device attached to compute instance over the network 

                           - gp2 v  io1   - carefully assess your requirements , e.g max throughput/volume , Max IOS


Migrating to AWS

                        - Native & simple   - covers large proportion of migrations but must accept downtime 

                        - If can't tolerate downtime consider AWS Database Migration Service 

                                                 - logical decoding of physical changes on AWS DMS Instance

                                                - built with heterogeneous migrations

                                                 - Limited support for DDL , if schema in flux DMS may not be the best choice

                                                - Transactional replication 

                                                           - near0zero downtime


Post Migration Steps

             - TempDB on local instance

             - Parameter groups

            - DBCC CHECKDB

            -Configure backups

            -CloudWatch, performance Insights



Monitoring on RDS

                   - Most scripts on-prem work unless they require "sa" access

                   -Performance Insights - boiled down to average active sessions  - breakdown by waits , users , hosts 

                   -CloudWatch metrics - collected at hypervisor level

                    - Third Party Tools ==> SentryOne,Redgate,SolarWinds ,Quest 

                   -Enhanced metrics   - up to 1 second granularity 


Watch the full video of Migrate your SQL Server database to Amazon RDS


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to migrate your SQL Server database to Amazon RDS | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer