Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to build RDS Environment in AWS for SQL Server


By:   |   Last Updated: 2017-10-16   |   Comments   |   Related Tips: More > Amazon AWS

Problem

We are planning to migrate an on-premises SQL Server database to Amazon Relational Database Service (Amazon RDS), what are the options we have for migrating databases to RDS.

Solution

This tip will help all technology professionals, DBA Managers and IT Directors build an enterprise level environment in Amazon RDS. Amazon RDS is a cloud-based, managed, hosted solution for relational databases.  This tip is intended for anyone who doesn’t want to build or manage an on-premises data center or infrastructure.

Migrating to Amazon RDS

Amazon Web Service Relational Data Service (RDS) is a cloud-based, managed, hosted solution for relational databases. Amazon RDS provides many RDS instance options to migrate any database to Amazon RDS. With RDS you can automate the software patching, backups, multi-AZ deployment and its cost effective pay as you use model. You have two options to migrate databases to RDS:

1) Homogeneous – In the case of homogeneous migration, migrating from SQL Server to RDS SQL server, we will use SQL Server native backup and restore.

2) Heterogeneous – For a heterogeneous migration, use the Amazon database migration service (DMS) when you migrate from different SQL Server versions or from Oracle to SQL Server without having down time. Whenever you have an issue with downtime or minimal downtime is available then the heterogeneous migration is the best option to migrate on-premises database to Amazon RDS.

Edition and Version

Amazon RDS does provide express, web, standard and enterprise editions.  In standard and enterprise editions you will get options to select production, Dev/Test purposes and some features will automatically be added to your instance, but if you want to you can change the configuration. Amazon offers an option to use your own license, but you need to complete the license mobility agreement within 10 days of migrating.

Amazon RDS Limitations

Amazon RDS has some limitations compared to on-premises or EC2 server options – if your requirements are met with RDS you can migrate the database to RDS, but here are some limitations.

  • You can create up to 30 databases per RDS instance; if you have more than 30 databases then you need to create another instance.
  • You cannot rename the database.
  • You cannot restore the same backup file with a different database name on any given instance.
  • SQL Server native backup and restore for SQL Server is not supported on the db.t1.micro DB instance family.
  • Server roles are not supported.
  • High availability options like replication, Always On, log shipping, etc. is not supported in RDS however there is multi-AZ provisioning in RDS for production databases.
  • Amazon Relational Database Service (Amazon RDS) supports native backup and restore for Microsoft SQL Server databases using full backup files. You can create a full on-premises database backup, store it on Amazon Simple Storage Service (Amazon S3) and then restore the backup file onto an Amazon RDS instance or you can do vice versa to an EC2 instance.

The following scenarios support a database move to and from RDS.

The following scenarios supported to move the database to and from RDS. - Description: The following scenarios supported to move the database to and from RDS.

First you need to create a VPC, subnet, etc. in Amazon AWS.  In my last tip I explained how to build an enterprise environment in AWS, click here for more information.

Step # 1 – How to create RDS subnets groups

When you create a VPC it spans all availability zones within the region.  After creating a VPC you can add one or multiple subnets in one or more availability zones.  When you create an RDS SQL Server instance in a VPC you need to add the subnet group along with availability zone which will use an IP address within the subnet.  If your primary RDS instance of a multiple-AZ deployment fails then the Amazon RDS instance will failover to a secondary RDS instance in a different subnet group within the availability zone. You will get an option to select availability zones or you can leave as no preference and AWS will assign it.

On the subnet groups page; enter the name, description and select your VPC – if there is more than one VPC then select the correct one from the drop-down menu.  Select an Availability Zone from the drop-down list and the subnet id or click on “add all the subnets” which will add all subnets along with the CIDR block and then click the Create button.

On subnet groups page; give name, description and select your VPC – if there are more than one VPC then select from drop down menu, Select Availability Zone from drop down list and the subnet id or click on “add all the subnets” which will add all subnets along with CIDR block and create. - Description: On subnet groups page; give name, description and select your VPC – if there are more than one VPC then select from drop down menu, Select Availability Zone from drop down list and the subnet id or click on “add all the subnets” which will add all subnets along with CIDR block and create.

On the Subnet Groups page, the newly created subnet groups will appear and the status is shown as complete.

On Subnet Groups page newly created subnet groups will appear and status shown as complete. - Description: On Subnet Groups page newly created subnet groups will appear and status shown as complete.

Step # 2 – How to create RDS Option Group

Some database engines such as MS SQL, Oracle, MySQL, etc. offer additional features that will help to manage the data and database, using these additional features you can migrate a SQL Server database with native backup and restore. To enable the additional feature you need to follow some additional steps.

On the Create Option Group page, enter the option group name, discretion and select your database engine along with edition from the engine drop down menu.  Here I am configuring the SQL Server Express edition so we have selected sqlserver-ex.

On Create Option Group page, give option group name, discretion and select your database engine along with edition from engine drop down menu, Here I am configuring SQL server express edition so we have selected sqlserver-ex. - Description: On Create Option Group page, give option group name, discretion and select your database engine along with edition from engine drop down menu, Here I am configuring SQL server express edition so we have selected sqlserver-ex.

On the Create Option Group page, select the major engine version, here I am configuring SQL Server 2016 so we have selected 13.00.

On Create Option Group page, select major engine version, here I am configuring SQL server 2016 so we have selected 13.00. - Description: On Create Option Group page, select major engine version, here I am configuring SQL server 2016 so we have selected 13.00.

On the Option Groups page, the newly created option groups will appear as shown below.

On option Groups page newly created option groups will appear shown below. - Description: On option Groups page newly created option groups will appear shown below.

On the Create Option Group page, select the option group and click on Add Option.

On Create Option Group page, select option group and click on Add Option. - Description: On Create Option Group page, select option group and click on Add Option.

On the Add option dialog box, select SQLServer_Backup_Restore from the drop-down menu and select the IAM role and check Yes for apply immediately, to apply the changes immediately and click on the Add option.

Note – If you check the No option then the option changes will not take effect until the next maintenance window, during the maintenance window the changes will be implemented.  Until that time, the backup and restore option won’t be enabled.

On Add option dialog box, select SQLServer_Backup_Restore from drop down menu and select the IAM role and check apply immediately Yes, to apply the changes immediately and click Add option. - Description: On Add option dialog box, select SQLServer_Backup_Restore from drop down menu and select the IAM role and check apply immediately Yes, to apply the changes immediately and click Add option.

Note – if you check No option then the option changes will not take effect till next maintenance windows, during the maintenance the changes will take in place till that time backup and restore option won’t be enable. 


On the option Groups page, the newly added option feature will appear as shown below.

On option Groups page newly added option feature will appear shown below. - Description: On option Groups page newly added option feature will appear shown below.

If you didn’t check Yes for apply immediately, then you can modify the instance and apply immediately by selecting the RDS instance, go to the Instance Actions and click on Modify as shown below in the maintenance window.

If you didn’t check apply immediately as yes, then you can modify the instance and apply immediately by selecting RDS instance go to the Instance Actions and click on Modify. As shown below the maintenance window. - Description: If you didn’t check apply immediately as yes, then you can modify the instance and apply immediately by selecting RDS instance go to the Instance Actions and click on Modify. As shown below the maintenance window.

Step # 3 – How to create RDS Parameter Groups

All database engine configurations are managed using the parameters in the parameter group. The database parameter groups act as a container for the engine configuration values. If you create an RDS instance without specifying the custom created parameter group then the default parameter group will get created automatically using default values of the Amazon RDS system, database engine and compute class. If you want to tune the RDS instance database configuration then create your own parameter group and change the values in the group. The most important part is if you do not want to change the values on all of the RDS instances then you need to create separate parameter groups for each instance or else you will use the same parameter group for all RDS instances and all instances will get updated.

Below are some points you should be aware of when working with parameter groups:

  • The dynamic parameter values apply immediately once you save the database parameter group.
  • The static parameter values apply when you save the parameter group followed by a manual RDS instance reboot.
  • When you change the RDS instance parameter group you need to reboot the instance before it will take effect to use the newly changed parameter group.

On the Parameter Group page, select the parameter group family from the drop down menu, enter the group name and description and click Create.

On the Parameter Group page, select parameter group family from drop down menu, give the group name and description and click Create. - Description: On the Parameter Group page, select parameter group family from drop down menu, give the group name and description and click Create.

On the Parameter Group page, I have selected sqlserver-ex-13 (SQL server 2016 express edition).

On the Parameter Group page, I have selected sqlserver-ex-13 (SQL server 2016 express edition) - Description: On the Parameter Group page, I have selected sqlserver-ex-13 (SQL server 2016 express edition)

On the Parameter Groups page, the newly created parameter group will appear as shown below.

On Parameter Groups page newly created parameter group will appear shown below. - Description: On Parameter Groups page newly created parameter group will appear shown below.

Step # 4 – How to create an IAM Policy

Using an IAM you can create and manage users, groups and permissions to allow access to the AWS resources and create policies to manage the access and create and manage the encryption key.

Click on Services on the top left and under Security, Identity & compliance select IAM

Using IAM you can create and manage user, groups and permission to allow access on the AWS resources and create policies to manage the access and create and manage encryption key. - Description: Using IAM you can create and manage user, groups and permission to allow access on the AWS resources and create policies to manage the access and create and manage encryption key.

Click on Services top left side -> under Security, Identity & compliance --> select IAM


On the Policies page, click Create Policy.

On Policies page, click Create Policy. - Description: On Policies page, click Create Policy.

On the Create Policy dialog box, click on Select to create your own policy.

On Create Policy dialog box, Select create your own policy. - Description: On Create Policy dialog box, Select create your own policy.

On the Review Policy dialog box, enter the policy name, description and in the policy document use the policy code or you can copy the items below and change the S3 bucket name in yellow.

On Review Policy dialog box, give the policy name, description and in the policy document use policy code

On the Review Policy dialog box, validate the policy and click on Create Policy.

On Review Policy dialog box, validate policy and Create Policy

Code

{ 
    "Version": "2012-10-17", 
    "Statement": [ 
        { 
            "Effect": "Allow", 
            "Action": [ 
                "s3:ListBucket", 
                "s3:GetBucketLocation" 
            ], 
            "Resource": "xxx:xxx:xx:::xxxxxxx-rdsbackup" 
        }, 
        { 
            "Effect": "Allow", 
            "Action": [ 
                "s3:GetObjectMetaData", 
                "s3:GetObject", 
                "s3:PutObject", 
                "s3:ListMultipartUploadParts", 
                "s3:AbortMultipartUpload" 
            ], 
            "Resource": "xxx:xxx:xx:::xxxxxxx -rdsbackup/*" 
        } 
    ] 
}

On the Policies page, the new custom managed policy will appear as shown below.

On Policies page newly customer managed policy will appear shown below. - Description: On Policies page newly customer managed policy will appear shown below.

Step # 5 – How to create IAM Role

IAM roles contain sets of users and policies; you can grant access thru policies to users, applications or services that do not have access to your AWS resources.

On the Roles page, click on Create role.

On Roles page, click on Create role. - Description: On Roles page, click on Create role.

On the Create Role dialog box, select the service that uses the role and select your use case and click on Next: Permission.

On Create Role dialog box, select the service that use the role and select your use case and click Permission. - Description: On Create Role dialog box, select the service that use the role and select your use case and click Permission.

On the Attached permission policy dialog box, click Next: Review.

Note – Since a custom created policy is not available, we will attach the policy after creating the role.

On Attached permission policy dialog box, click Review Note – as customer created policy is not available so we will attach the policy after creating role. - Description: On Attached permission policy dialog box, click Review 
Note – as customer created policy is not available so we will attach the policy after creating role.


On the Review dialog box, enter the role name, description and Create role.

On Review dialog box, give the role name, description and Create role. - Description: On Review dialog box, give the role name, description and Create role.

On the Role page the new created custom role will appear as shown below.

On Role page newly created custom role will appear shown below. - Description: On Role page newly created custom role will appear shown below.

On the Polices page attach the role with the policy by selecting the policy and going to the Policy Actions and click on Attach.

On Polices page to attach role with policy select the policy and go to the Policy Actions and click on Attach. - Description: On Polices page to attach role with policy select the policy and go to the Policy Actions and click on Attach.

On the Polices dialog box, go to the Attached entities tab and click on Attach.

On Polices dialog box, go to the Attached entities tab and click on Attach. - Description: On Polices dialog box, go to the Attached entities tab and click on Attach.

On the Attach Policy dialog box, select your custom role and click Attach policy.

On Attach Policy dialog box, select your custom role and click Attach policy.  - Description: On Attach Policy dialog box, select your custom role and click Attach policy.

On the Polices page, the newly attached custom role will appear as shown below.

On Polices page newly attached custom role will appear shown below. - Description: On Polices page newly attached custom role will appear shown below.

Step # 6 - How to create Amazon RDS Instance

Amazon Web Service Relational Data Service (RDS) is a cloud-based, managed, hosted solution for relational databases. Amazon RDS provides different options to migrate any database to Amazon RDS. With RDS you can automate the software patching, backups, multi-AZ deployment and use a cost-effective pay as you go use model. In RDS you can create Web edition RDS instance (free tier) or you can pay as you use on hourly basis.

You can build your RDS instance with few clicks as per your requirement. Amazon offers a wide selection of RDS instance types to best fit your requirement. Amazon RDS has two types of instances; on-demand instance and reserved instance, you can compare various types of instance combinations of memory, storage capacity, etc.

To create a RDS instance, click on Services (top left side) and then under Database select RDS.

To create RDS Instance click on services (top left side) and under the compute select RDS. - Description: To create RDS Instance click on services (top left side) and under the compute select RDS.

On the Instances dialog box, click on Launch DB Instance.

On Instances dialog box, click on Lunch Instance. - Description: On Instances dialog box, click on Lunch Instance.

On the Select Engine dialog box, select the appropriate platform along with the SQL Server edition.

Note - You can change the instance type any time when you run the on-demand instance, but you need to migrate all the databases.  If you select standard or enterprise edition you will get an option to select a Production or Development instance.

On the Select Engine dialog box, select appropriate platform along with SQL server edition. - Description: On the Select Engine dialog box, select appropriate platform along with SQL server edition.
Note – all SQL server edition will have same feature but the power of instance are different like CPU and memory configuration; you can change the instance type any time when you run on-demand instance, but you need to migrate all the databases; if you select standard or enterprise edition you will get option to select prod or Dev instance.


On the Select Engine dialog box, select license model, database engine version and database instance class.

Note – If you need to add more memory or CPU to a RDS instance you need to select the DB Instance Class.  Some DB Instance Classes include IOPS, but if you need more IOPS you can get it based on the provisioned IOPS.

On the Select Engine dialog box, select license model, DB engine version, DB instance class. - Description: On the Select Engine dialog box, select license model, DB engine version, DB instance class.
Note – if you need more memory or/and CPU to RDS Instance you need to select on DB instance class; some DB instance class does have IOPS, but if you need more IOPS you can get it.


On the Select Engine dialog box, enter the DB Instance Identifier, master username and password and click Next Step.

On the Select Engine dialog box, put the DB instance identifier, master username and password and click Next.  - Description: On the Select Engine dialog box, put the DB instance identifier, master username and password and click Next.

On the Configure Advance setting section, select the VPC, Security group and availability zone.

Note – If you set Publicly Accessible to Yes; then your database will be accessible using a public IP address outside your VPC and security group.

On Configure Advance setting section, select the VPC, Security group, availability zone. - Description: On Configure Advance setting section, select the VPC, Security group, availability zone.
Note – if you select publicly accessible yes; then your database will be accessible using public IP address outside from VPC and security group.


On the Configure Advance setting dialog box, under database option section, enter the RDS database instance port number.

On Configure Advance setting dialog box, under database option section, put the RDS database instance port no. - Description: On Configure Advance setting dialog box, under database option section, put the RDS database instance port no.

On the Configure Advance setting dialog box, under the backup section, select the number of days for backup retention time, select backup window if you want to perform database backups at a specific time; select the monitoring option if you want to monitor an RDS instance performance.  Select the maintenance window as well and click Launch DB Instance.

Here we have a 3-day backup retention on an S3 bucket and if you want more than 3 days you can move to Glacier, if you want to copy a backup from Glacier it will take around 3-5 hours approximately for my test databases. By enabling maintenance, you allow RDS to make minor changes like reboot and patching.

On Configure Advance setting dialog box, under backup section, select the number of days for backup retention time, select backup window if you want to perform database backup on time; select monitoring option if you want to monitory RDS instance performance; select maintenance window and click launch DB Instance. - Description: On Configure Advance setting dialog box, under backup section, select the number of days for backup retention time, select backup window if you want to perform database backup on time; select monitoring option if you want to monitory RDS instance performance; select maintenance window and click launch DB Instance.
Here we have 3 days backup retention on S3 bucket and if you want more than 3 days you can move the glacier, if you want backup copy from glacier it will take around 3-5 hours approximately. By enabling maintenance you do allow RDS to make minor changes like reboot and patching.


On the Instance page, we will see a message indicating the instance is in a creating state as shown below.

On Instance page, we will see message appearing as Instance creating state. - Description: On Instance page, we will see message appearing as Instance creating state.

On the Instances page, the status will indicate the instance is available and the AWS instance is up and running.

On Instances page, the Instance is available the status and AWS Instance is up and running. - Description: On Instances page, the Instance is available the status and AWS Instance is up and running.

Conclusion

In this tip we built an enterprise environment and a created an Amazon RDS instance in AWS.

Next Steps


Last Updated: 2017-10-16


next webcast button


next tip button



About the author
MSSQLTips author Pinakin Patel Pinakin Patel is a SQL Server Admin / AWS Solutions Architect with experience in SQL Server 2000 to 2016.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools