SQL Server Migration To RDS:Instance Sizing and Migration best practices.
Abstract: Moving SQL servers from om on-Prem to AWS could be challenging and overwhelming, in this blog I will shed some light on RDS instance sizing and migration best practices to SQL Server RDS.
Introduction: The journey to AWS starts first with picking the right RDS instance and second the right migration path. In this blog, we will not go into details on SQL server on Prem assessment and how to right-size your workload, this is for another blog coming soon so stay tuned. Picking your instance means finding the instance family that most closely matches the CPU and memory for your on Prem SQL Server instance. AWS RDS provides a wide selection of instances, which gives you lots of flexibility to right size your compute resources to match
capacity at the lowest cost. There are several families of RDS instances. These database instance families are optimized for memory, performance, and I/O:
- Standard performance (includes the M3 and M4 instance types) — Designed for general-purpose database workloads that don’t run many in-memory functions. This family has the most options for provisioning increased IOPS.
- Burstable performance (includes T2 instance types) — For workloads that require burstable performance capacity.
- Memory optimized (includes the R5, X1, and Z1 instance types) — Optimized for in-memory functions and big data analysis.
Full listing of all SQL server RDS instances can be found in this below link: https://aws.amazon.com/rds/sqlserver/instance-types/
AS previously discusses the first step is to pick up the right RDS instance to match you’re on Prem SQL Server, but this step could be overwhelming especially if you are dealing with mass server migration. To help with this task I am introducing “RDSInstanceTool”
What is RDSInstanceTool ? RDSInstanceTool v 1.00 is a free no impact tool that helps you with matching you’re on Prem SQL server with the right RDS Instance based on CPU and Memory allocation. The tool will read your SQL server Memory, CPU count and present you with the right instances on RDS for General purpose or Memory optimized.
Downloading and Installing RDSInstanceTool
The tool is available on Github
Installation Steps:
- Extract RDSSqlServerInstanceTool.zip on c:\RDSSQL as shown below
The tool has 2 files RdsInstanceTool.exe and RDSSQLInstances.xlsx
2- Create an input file that contains a list of all your SQL Server that you are trying to match with RDS Instance and place it in c:\RDSSQL .A sample file can be found in GitHub
You can use IP address or SQL Servername and if the port is different from the default port, enter the port as well.
3- Installation complete Running RDSInstanceTool
- To execute open a CMD prompt navigate to c:\RDSSQL and type:
C:\RDSSQL>RDSqlServerInstanceTool.exe login password file type
Login: Sql server login ( sa password or login with access to DMV )
Password: Sql server login password
File: the input file created in step 2
Type: M- for memory optimized instances G- for general purpose instances
Output: The output will look like the screenshot below:
A great effort is made to match your on Prem resources with the right RDS instance. Few things are considered while matching your resources with an RDS instance: 1- Not all instances are available for all SQL Server version and edition. A complete list of instances per Sql server edition and version can be found in this link
2- I try to match your CPU first with the RDS instance and this means that it is possible that
the instance that I am suggesting could be overprovisioned on RAM. For example, in the below screenshot server #2 has 36 CPU and 54 GB RAM allocated.
the closest that I can match with 12xlarge which is 48 CPU and 192 GB RAM. Downsizing to 8xlarge will end up well below the 36 CPU. So, review all suggestions and refer to the below link to adjust to the right instance that works for you at the end of the day you would know your need and your server requirements.
3-the tool as of today will only match standard and Enterprise Edition with a minimum of 4 CPU (Xlarge).
4-I give all possible matches as per CPU and memory.
What’s Next
RDSInstanceTool V 2.00 will be able to run a full assessment on your on Prem server (CPU an Memory) based on a defined collection time and give you an RDS instance type based on your load .
For bugs , Suggestions and ideas feel free to reach out to bobtherdsman@gmail.com
Migration: To migrate your SQL server to RDS you have multiple options:
1-Using Amazon S3 and SSMS https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-on-premises-microsoft-sql-server-database-to-amazon-rds-for-sql-server-using-amazon-s3-and-ssms.html
2-Using DMS https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html
Migration Workshop:
Related resources References
- Deploying Microsoft SQL Server on Amazon Web Services
- AWS DMS website
- Amazon RDS Pricing
- Microsoft Products on AWS
- Microsoft Licensing on AWS
- Microsoft SQL Server on AWS
- Using Windows Authentication with a Microsoft SQL Server DB Instance
- Amazon RDS Multi-AZ Deployments
Tutorials and videos