Forecast Cloudy – SQL Server on Amazon Web Services RDS

aws_logo

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks.

The basic building block of Amazon RDS is the DB instance. A DB instance is an isolated database environment in the cloud.  A DB instance can contain multiple user-created databases, and you can access it by using the same tools and applications that you use with a stand-alone database instance. You can create and modify a DB instance by using the Amazon RDS command line interface, the Amazon RDS API, or the AWS Management Console.

Each DB instance runs a DB engine. Amazon RDS currently supports the MySQL, PostgreSQL, Oracle, and Microsoft SQL Server DB engines. As per my background I will illustrate running DB instance of SQL Server in RDS here, but in the future I may venture further touching MySQL and especially its specialized Amazon cousin known as Aurora. The computation and memory capacity of a DB instance is determined by its DB instance class. You can select the DB instance that best meets your needs. If your needs change over time, you can change DB instances. For each DB instance, you can select from 5 GB to 3 TB of associated storage capacity, instance storage comes in three types: Magnetic, General Purpose (SSD), and Provisioned IOPS (SSD). They differ in performance characteristics and price, allowing you to tailor your storage performance and cost to the needs of your database.

Amazon cloud computing resources are housed in highly available data center facilities in different areas of the world (for example, North America, Europe, or Asia). Each data center location is called a region.Each region contains multiple distinct locations called Availability Zones, or AZs. Each Availability Zone is engineered to be isolated from failures in other Availability Zones, and to provide inexpensive, low-latency network connectivity to other Availability Zones in the same region. By launching instances in separate Availability Zones, you can protect your applications from the failure of a single location. You can run your DB instance in several Availability Zones, an option called a Multi-AZ deployment. When you select this option, Amazon automatically provisions and maintains a synchronous standby replica of your DB instance in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to the standby replica to provide data redundancy, failover support, eliminate I/O freezes, and minimize latency spikes during system backups.

Amazon RDS supports DB instances running several editions of Microsoft SQL Server 2008 R2 and SQL Server 2012.  Amazon RDS currently supports Multi-AZ deployments for SQL Server using SQL Server Mirroring as a high-availability, failover solution. Amazon also supports TDE  (Transparent Database Encryption) feature in SQL Server , as well as allows SSL connections to your SQL Server instance as necessary. In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges. Amazon RDS supports access to databases on a DB instance using any standard SQL client application such as Microsoft SQL Server Management Studio. Amazon RDS does not allow direct host access to a DB instance via Telnet, Secure Shell (SSH), or Windows Remote Desktop Connection. When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those that are used for backups (Amazon RDS manages backups for you).

Obviously before you proceed you need to sign up for AWS account. You can do so here – https://aws.amazon.com/. Next you should create IAM (Identity and Access Management) user.

image

Once you log into AWS Console and go to IAM, as above.

  • In the navigation pane, choose Groups, and then choose Create New Group
  • For Group Name, type a name for your group, such as RDS_Admios, and then choose Next Step.
  • In the list of policies, select the check box next to the AdministratorAccess policy. You can use the Filter menu and the Search box to filter the list of policies.
  • Choose Next Step, and then choose Create Group.

Your new group is listed under Group Name after you are done. Next create a user and add user to the group. I covered creating IAM users in my previous blogs, but here it is again:

  • In the navigation pane, choose Users, and then choose Create New Users
  • In box 1, type a user name. Clear the check box next to Generate an access key for each user. Then choose Create.
  • In the Groups section, choose Add User to Groups
  • Select the check box next to your newly created admins group. Then choose Add to Groups.
  • Scroll down to the Security Credentials section. Under Sign-In Credentials, choose Manage Password. Select Assign a custom password. Then type a password in the Password and Confirm Password boxes. When you are finished, choose Apply.

Next in AWS Console I will select RDS from Data Section

image

I will pick SQL Server from available DB Instance types.

image

Picking SQL Server Standard for sake of example I am presented with choice of Multi-Availability Zone deployment for HADR and consistent throughput storage, For the sake of this quick post I will do what you will not do for production and decline that option: After all doing things properly does cost money:

image

Next screen opens a more interesting Pandora’s box here – licensing. I can license my SQL Server here via “bring your own license” or use Amazon license, Customers using License Mobility through Software Assurance Plan to launch bring-your-own-license Amazon RDS for SQL Server instances must complete a license verification process with Microsoft . That option opens following checkbox

image

Here also I can pick my DB Instance Class. Instance classes are detailed in Amazon docs here – http://docs.amazonaws.cn/en_us/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html. I am picking a very small testing instance , what Amazon calls micro instance size – an instance sufficient for testing that should not be used for production applications.

image

After adding some more info including Availability Group, Backup preferences and retention I can hit Create Instance button.

image

Once Amazon RDS provisions your DB instance, you can use any standard SQL client application to connect to the instance. In order for you to connect, the DB instance must be associated with a security group containing the IP addresses and network configuration that you will use to access the DB instance. So lets connect to my new DB instance using SSMS:

  • On the Instances page of the AWS Management Console, select the arrow next to the DB instance to show the instance details. Note the server name and port of the DB instance, which are displayed in the Endpoint field at the top of the panel, and the master user name, which is displayed in the Username field in the Configuration Details section
  • Use that instance string and your user name to connect.

image

I did run into issue with VPC Security groups and Inbound rules. See this post for proper way to setup rules – https://msayem.wordpress.com/2014/10/23/unable-to-connect-to-aws-rds-from-sql-server-management-studio/

For more see – https://www.mssqltips.com/sqlservertip/3251/running-sql-server-databases-in-the-amazon-cloud-part-1/, http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

Advertisements

One thought on “Forecast Cloudy – SQL Server on Amazon Web Services RDS

  1. Pingback: Forecast Cloudy – SQL Server on Amazon Web Services RDS LIMITATIONS, BACKUPS, HA AND DR | A posteriori

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s