How to migrate your SQL Server database to Amazon RDS

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