This article will review on how to launch an AWS RDS SQL Server instance and how to connect the instance and modify the properties of database instance.
Overview
AWS RDS SQL Server comes with license included and there is no need to purchase license from Microsoft separately.
You can deploy multiple editions of SQL Server (2008 R2, 2012, 2014, 2016, and 2017) including Express, Web, Standard and Enterprise, in minutes with cost-efficient and re-sizable compute capacity.
Creating a database instance on AWS RDS SQL Server
Here is step by step for creating a database instance on AWS RDS SQL Server.
Login in to AWS management console using your email and password. Navigate to Services and type RDS in search box. Click on RDS managed relational database services as show in the below image.
Click on Create Database and select Microsoft SQL Server as database engine.
Select the type of edition as per your need. If your looking for free tier check Only enable options eligible for RDS Free Usage Tier.
Only SQL Server expression edition is available in free tier and all other editions will be graded out if you opt for free tier.
Click Next once you select the edition and choose the use case as per your need. Please refer to the below image. Click Next. AWS RDS recommends deploying production database instances in multiple available zones for high availability.
The are various SQL server versions available. Choose the SQL Server version of your choice and select the DB instance class. You will be charged based on the type of DB engine version and DB instance class you choose. The default time zone is UTC and changing the time zone is optional. You can choose the time zone of your choice. The time zone can not be changed once the instance is created.
Select Multi AZ deployment to have replica of your database instance in a different availability zone. Amazon relational database services uses mirroring/ always on in background to support multi AZ deployment.
AWS RDS SQL Server will automatically fail over to the standby in the case of a planned or unplanned outage of the primary.
Select the type of storage. There are different storage types available.
- General Purpose SSD: Performance is determined by the size of the volume. Baseline I/O performance for General Purpose SSD storage is 3 IOPS for each GB. For example, 100GB IOPS will 300 and for 1000 GB IPOS will be 3000
- Provisioned IOPS: Ranges from 1000-32000 IOPS. Recommended for OLTP database instances
When we select the provisioned IPOS we must input the IOPS number and the IOPS will be used until we change the number again. You can see the estimated monthly cost of your instance based on the configuration you selected.
The IPOS is based on the storage capacity. Example, for a 20 GB allocated storage the maximum IPOS is 1000. If you try to increase IPOS beyond 1000 for a 20 GB storage. It will show waring as in below image.
If you increase the allocated storage, you can increase IPOS. You will be charged based on your IPOS and storage you choose.
In settings section, specify the name of the DB instance identifier and this should be unique among all the instances in your AWS account in the region. Input the master username and password of your choice and click Next.
In advanced configuration settings, Select the VPC and subnet. Select the public accessibility as per your choice. If you select Yes, you can connect to DB instance from SQL Server management studio or any other tools which are outside of the VPC network.
If you select No, you can connect to the instance from devices or ec2 which are within VPC network only.
Select the security group. This security group will have inbound and outbound rules defined. If you need to open any specific port or limit the connectivity from specific IP address, all these rules can be defined in these security groups.
Input the port number, Select the DB parameter group and the option group. Parameter group is the database engine configuration. If you want to use your own parameter group, you create a new parameter group and modify the parameters that you want to. Select the newly created parameter group while creating database instance. You can also associate the custom parameter group after creating the database instance.
Select the backup retention period. select the backup window if you want take backups at specific time.
Input the start time. These timings are in UTC.
If you want your database instance to be auto upgraded select Enable auto minor version upgrade. These automatic upgrades occur during the maintenance window. You can specify the maintenance window by selecting the Select Window option and input start day and start time.
Enable deletion protection. If you enable this option, you cannot delete the database instance. Click on Create Database. Your database instance will be created in few minutes.
Navigate to the Databases tab and click on database instance name to view more details of the instance like endpoint which is used to connect database instance, logs, monitoring, configuration etc.
Once the database instance is available, it will display the endpoint which will be used to connect the database instance.
Connecting to a database instance on AWS RDS SQL Server
Once the instance is available on AWS RDS SQL Server, you can SQL Server management studio to connect the instance. Copy the endpoint and open SQL Server management studio from windows machine and input the endpoint in server name and use the master account username and password you created previously.
To connect using SQLCMD, open command prompt pass endpoint as server parameter and master login credentials to SQLCMD.
If you have connection issues, please look at your public access settings of your database instance and your security groups associated with instance.
If the database instance is not enabled for public access, make sure you are connecting using tools with in VPC network.
To access the log files and default trace files, Navigate to Databases -> click on the database instance -> in database instance details page, Click on Events and Logs. You can also read the error log using rds_read_error_log procedure. Login to database instance using SQL Server management studio and execute the procedure in rdsadmin database.
|
1 |
EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 1; |
To stop the database instance, select the instance and click on Actions. Click Stop.
If you need the snapshot before stopping the instance, Click Yes and give the snapshot name.
The status of the DB instance changes to stopping and then stopped. Even though you stop the database instance you will be charged for the provisioned storage.
If the instance is not started again with in the seven days, it will be automatically started.
To start the database instance, select the instance and click on Actions. In the drop down click on Start.
At any point of time if you forgot the master password or wish to change the master password, navigate to the Databases, select the database instance and click on Modify.
Enter new master password and click Continue at bottom
In modifications summary page, you will be able to see the all modification done. If you plan to apply these changes in maintenance window, select Apply during the next scheduled maintenance window.
If you want these changes to reflect immediately, select Apply Immediately.
Similarly, you can also modify other properties of the database instance like increasing the allocated storage, change the security group, public accessibility, port number and backup settings etc.
Please note that modifying some properties and applying them immediately may reboot the AWS RDS SQL Server database instance.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019