Create a Microsoft SQL Server Database in an Amazon RDS Instance
SQL Server is a relational database management system (or RDBMS) developed and marketed by the Microsoft corporation. It has a client-server database architecture, and its primary function is to store and retrieve data used by other applications. SQL Server relies on SQL query language for interacting with relational databases.
AWS, which stands for Amazon Web Services, is a popular, fast-growing cloud platform that provides cost-efficient computing services. AWS offers more than 100 products and services, among which Amazon RDS is one of the most demanded ones. Amazon Relational Database Service comprises a collection of managed services that make it easy to set up, manage, and maintain a relational database in the cloud.
Amazon RDS for SQL Server operates under the "License Included" model, which means that you do not have to buy Microsoft SQL Server licenses separately. To access databases on an Amazon RDS DB instance, you can use any standard SQL client application such as dbForge Studio for SQL Server.
Amazon RDS features
- Replication
- Scalability
- Manageability
- Reliability
- Security
Replicas comprise read-only copies of database instances that applications can use without making changes to the original production database. Replication significantly improves the availability and reliability of production workloads.
For read-heavy database workloads, you can use read replicas to solve the capacity issues with your database instances. Creating multiple replicas of your database instance will allow you to significantly increase overall read throughput. Storage scaling is quite easy too. If your databases grow, you can always provision additional storage. You can also scale compute and memory resources up to a maximum of 32 vCPUs and 244 GiB of RAM.
The Amazon CloudWatch service provides key operational metrics for your DB instances, including memory and storage capacity utilization, I/O activity, and instance connections. You can also be notified of database events via email or SMS.
The Amazon RDS automated backup feature allows for point-in-time recovery for your DB instances. Database backups and transaction logs are stored for the period specified by the user (up to 35 days). One can also benefit greatly from database snapshots that can be used to create a new database instance. Amazon RDS has no storage period limitations for snapshots; however you will have to pay for incremental storage use. In the context of reliability of Amazon RDS, Multi-AZ deployments should also be mentioned as they ensure enhanced availability, durability and automatic failover for database instances.
Such Amazon RDS features as encryption at rest and in transit, network isolation, and resource-level permissions ensure that your data is securely stored and accessed.
SQL Server versions supported in AWS RDS
- Microsoft SQL Server 2019
- Microsoft SQL Server 2017
- Microsoft SQL Server 2016
- Microsoft SQL Server 2014
- Microsoft SQL Server 2012
Create an Amazon RDS SQL Server database instance
Step 1. Open the Amazon RDS console
To start with, you need to sign in to AWS Management Console, which is a browser-based GUI for Amazon Web Services. Then, in the Database section of All Services, click RDS to open the Amazon RDS Console.
Step 2. Create an Amazon SQL Server instance
To create a Microsoft SQL Server database instance on Amazon RDS, go to Databases in the side menu and click Create database. Then, select a database creation method, engine and database management types.
Next, select the SQL Server edition and version. Amazon RDS supports Express, Web, Standard and Enterprise editions.
Step 3. Configure settings for a new DB instance
In the Settings section, provide a name for your DB instance, then specify a login ID and password for the master user.
Step 4. Set up instance configuration and storage options
Instance configuration options are limited to those supported by the engine that you have selected in Step 2. RDS provides three types of storage: General-purpose solid-state drive (SSD), Provisioned input-output operations per second (IOPS), and Magnetic. They have different prices and performance characteristics, hence you can select the option that best suits your project needs.
Also in the Settings section, you can select to enable storage autoscaling and specify maximum storage threshold.
Step 5. Configure connectivity options
In the Connectivity section, select a network type and configure its settings. Then select whether to make your DB instance publicly accessible or not. If you select "No", your instance won't be exposed to the Internet, and Amazon RDS will not assign a public IP address to it. And finally, choose a VPC security group and provide its settings.
Step 6. Enable Microsoft SQL Server Windows authentication and provide additional options
In the last step, you need to select whether to enable Microsoft SQL Server Windows authentication or not. Next, you can get acquainted with the estimated costs. Once done, click Create database.
To configure additional options, such as backup, monitoring, logging, and maintenance configurations, click to expand the Additional configuration section.
It can take up to 20 minutes before your SQL Server DB instance will become available on Amazon RDS. The wait period depends on the instance class and the chosen amount of storage. After the instance is created, you can start working with it. To view the details of your SQL Server instance on Amazon RDS, click the database instance name.
Connect to the Amazon RDS SQL Server DB instance with dbForge Studio for SQL Server
dbForge Studio for SQL Server is an integrated development environment (IDE) produced by Devart. It is a powerful all-in-one solution that provides an intuitive and well-designed graphical interface for connecting and working with MS SQL Server databases.
To configure remote access to your Amazon RDS instance via dbForge Studio for SQL Server:
1. In Database Explorer, click the New Connection button. Optionally, go to Database in the main menu and select New Connection.
2. In the Database Connection Properties window, specify the server name, select the authentication method, enter the Login and Password.
3. Optional: Click Test Connection to verify the specified settings.
4. Once all the settings are configured, click OK.
After you have successfully connected to AWS RDS, you can start developing, administering, and maintaining RDS SQL Server databases directly from dbForge Studio. You don't need to switch between different tools to perform different tasks — dbForge Studio for SQL Server is designed to cover all your needs. And it's worth mentioning that the Studio provides broad compatibility and connectivity options that are not limited just to Amazon RDS support.
By the way, if you need to deploy a MySQL database instance in Amazon RDS, you can refer to Connecting to Amazon RDS MySQL Database artice.