Brian Beach1, Steven Armentrout2, Rodney Bozo3 and Emmanuel Tsouris4
(1)
Raleigh, NC, USA
(2)
Mountlake Terrace, WA, USA
(3)
Sterling, VA, USA
(4)
North Bend, WA, USA
Relational Database Service (RDS) is a service that makes it easy to create and manage a database in the cloud. RDS supports MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server. While you could install and run any of these on an EC2 instance, RDS greatly simplifies the effort. RDS instances are managed by AWS, eliminating time-consuming activities, such as patching and backups, and allowing you to focus on your application.
In this chapter, we discuss the RDS architecture and learn to launch an SQL Server RDS instance. Next, we will learn to configure an RDS instance using parameters and options. Then, we will learn to manage backups and restores using both snapshots and point-in-time restores. We will also briefly cover Amazon Aurora in this chapter. Aurora is a MySQL- and PostgreSQL-compatible database optimized for the cloud.
In the exercises we will focus on securing an RDS instance running SQL Server. In the first exercise, we will enable SSL to encrypt the connection to SQL Server. In the second exercise, we will enable Transparent Database Encryption (TDE) to encrypt data and back up files stored on disk.
RDS Architecture
RDS is designed to be deployed in multiple availability zones for high availability. Therefore, your VPC must have subnets in at least two availability zones. Even if you choose to launch only a single stand-alone instance, you must have two subnets in different availability zones to use RDS.
AWS uses a DB Subnet Group to identify which subnets are reserved for RDS. You simply create two or more subnets in multiple availability zones and add them to the Subnet Group. In addition, we use VPC ACL and security groups to control access to the RDS instances.
Figure 10-1 shows the basic configuration of a single instance RDS configuration. Later on we will deploy a highly available Multi-AZ configuration. Let’s assume we have two web servers running on EC2 instances, and they will use an RDS SQL Server instance to store data. The RDS instance will be launched into one of the two subnets that make up the DB Subnet Group. Let’s first configure the VPC.
Figure 10-1
Single instance deployment
Creating a VPC
Before we can create a database instance, we need to configure a VPC for it to live in. Let’s begin by creating a new VPC. If you prefer, you can add two new subnets to an existing VPC (e.g., the VPC created in Chapter 8). First, I create a new VPC using the 192.168.0.0 private IP range:
$VPC = New-EC2Vpc -CidrBlock '192.168.0.0/16'
Next, I create two subnets in our VPC. These are the subnets that the database instance will live in. Because we want to be able to support a multi-AZ deployment, I am using two different availability zones (this should all be familiar by now, but if you need to review, go back to Chapter 5); therefore
Now that we have our VPC configured, we need to describe how we plan to use it. We need to tell RDS which subnets to use for database instances. We do this using a subnet group. To create a subnet group, use the New-RDSSubnetGroup command. New-RDSSubnetGroup requires a name and description, along with a list of subnets to use. You will use the name rather than an ID to refer to this subnet group later. For example:
New-RDSDBSubnetGroup -DBSubnetGroupName 'MySubnetGroup' -DBSubnetGroupDescription 'Pair of subnets for RDS' -SubnetIds $PrimarySubnet.SubnetId, $StandbySubnet.SubnetId
Even if you do not plan to deploy a multi-AZ RDS instance, you must specify at least two subnets when creating a subnet group. In addition, the subnets must be in different availability zones.
Configuring Security Groups
The last thing we need is a security group. This security group is used to define which EC2 instances can connect to the RDS database instance. Let’s create a new security group for the database and then allow traffic from EC2 instances in the default security group. First, we create a new security group for the RDS instance:
Next, we get a reference to the default group. In this example I am going to allow any instance in the default group to access to our database instance. I am using filters to find the default group (if you need to review, see Chapter 6):
Now that we have our VPC configured, we are ready to launch a database instance. In the next section, we will create an SQL Server instance. We will start my launching a single-AZ database. Later in the chapter, we will examine a multi-AZ configuration for high availability.
Managing RDS Instances
Now that we have our VPC configured, we can begin working with RDS instances. Let’s get started by launching a new SQL Server database on RDS.
Launching an Instance
To launch a new instance, we use the New-RDSDBInstance command. It takes a few minutes for a new instance to launch – especially using the micro instances – so let’s jump right in and launch one. This is another one of those commands with a ton of options. While the new instance is launching, we can examine all of the optional parameters available.
Since you’re reading a book on PowerShell, I assume you are most interested in SQL Server. Let’s start with a stand-alone, single-AZ instance. This will not be highly available, but it will launch relatively quickly. To create a new stand-alone SQL Server instance, enter the following command:
Never use “password” as a password. Please choose something more complex and novel.
The previous command includes the minimum set of the parameters required to launch a database instance into a VPC, which are
DBInstanceIdentifier is simply a unique name you will use to refer to the database instance later. Unlike the EC2 and VPC commands we have been using, RDS uses a name, called an identifier, rather than an ID.
Engine defines which type of database you want to use. RDS supports multiple versions of MySQL, Oracle, and SQL Server. If you are not familiar with the various versions of each database, see the vendor’s web site for details. The specific engine types are
mysql – There is only one version of MySQL that includes all options.
mariadb – There is only one version of MariaDB that includes all options.
oracle-se1 – Oracle Standard Edition One.
oracle-se2 – Oracle Standard Edition Two.
oracle-se – Oracle Standard Edition.
oracle-ee – Oracle Enterprise Edition.
postgres – There is only one version of PostgreSQL that includes all options.
sqlserver-ex – SQL Server Express.
sqlserver-web – SQL Server Web Express.
sqlserver-se – SQL Server Standard Edition.
sqlserver-ee – SQL Server Enterprise Edition.
AllocatedStorage describes how much storage to allocate to the database. The maximum storage is 16TiB, and each engine type has a different minimum. See Table 10-1 for details of each database engine.
Table 10-1
Storage by Engine Type
Engine
Min Storage
Max Storage
mysql
5GB
16TiB
mariadb
5GB
16TiB
oracle-se1
10GB
16TiB
oracle-se2
10GB
16TiB
oracle-se
10GB
16TiB
oracle-ee
10GB
16TiB
postgres
5GB
16TiB
sqlserver-ee
200GB
16TiB
sqlserver-se
200GB
16TiB
sqlserver-ex
30GB
16TiB
sqlserver-web
30GB
16TiB
Note The default storage type is magnetic disk which has a 1TiB storage limit and cannot be resized. I strongly suggest you override the default and use gp2 (SSD). See details under the Storage Type attribute later in this section.
DBInstanceClass describes the hardware your database instance will use. This is similar to the EC2 instance types. SQL Server licensing limits which engines support instance types.
MasterUsername and MasterUserPassword are used to log into the database. Note that the master user does not have system administrator rights to the database. Remember that you do not have access to the underlying operating system when using RDS. Therefore, the master user has limited access. In addition, note that SQL Server only supports database accounts. Of course you can create additional database accounts after logging in.
DBSubnetGroupName is the name of the subnet group we created earlier. RDS will launch the instance into one of the subnets in this group. If you want to specify which subnet to use, see the optional AvailabilityGroup parameter described later.
VpcSecurityGroupIds is a list of security groups the RDS instance should be placed into.
In addition to the required parameters, New-RDSDBInstance also supports a bunch of optional parameters, which include
LicenseModel allows you to choose from multiple software licensing models. Depending on the engine you are using, you can choose to bring your own license or have the cost of license included in with the hourly cost of the instance.
EngineVersion defines the specific version of each database type. For example, RDS supports SQL Servers 2008, 2012, 2014, 2016, and 2017. If you omit this parameter, RDS will use the latest version. At the time I am writing this, the latest version of SQL Server is SQL Server 2017. If you want to list all of the supported engine versions, use the command Get-RDSDBEngineVersion | Format-Table.
AutoMinorVersionUpgrade tells RDS to automatically apply minor updates. Updates are applied during the maintenance windows defined later. Major upgrades (e.g., SQL 2008 R2 to SQL 2012) are not supported. This option is enabled by default.
MultiAZ specifies that you want to create both a primary and standby instance. The primary and standby will be launched into subnets in different availability zones as defined in the subnet group. (See the section on multi-AZ configuration later in this chapter.)
AvailabilityZone specifies which availability zone to launch the instance into. In a VPC, RDS will use the subnet in the specified availability zone. You cannot specify availability zone if you are using the MultiAZ option.
StorageType specifies the disk type to use. The options are standard, gp2, and io1. Note that standard (e.g., magnetic) is the default. I strongly suggest you use gp2.
IOPS specifies the IO operations per second (IOPS) desired from the disk. This is similar to provisioned IOPS in EC2, and you pay a premium for this option just like EC2. RDS uses striping and can support 1000–30,000 IOPS.
StorageEncrypted is a Boolean indicating the disk should be encrypted.
KmsKeyId specifies which key to use for encryption.
PreferredMaintenanceWindow defines a weekly outage window when Amazon can apply patches to the RDS instance. For example, you might specify sat:22:00-sat:23:00. If you omit this option, AWS will choose a random 30-minute window from an 8-hour block defined for each region. AWS will choose a time that is generally considered “off hours” for the region, but it is best to specify your own window.
PreferredBackupWindow defines when the daily full backup is taken. For example, you might specify 23:00-24:00. The backup windows cannot overlap the maintenance window and must be a minimum of 30 minutes. (There is more detail on backup and recovery later in this chapter.)
BackupRetentionPeriod defines how long to save backups. You can specify 0–8 days. The default is 1 day and specifying 0 days disables backup.
PubliclyAccessible specifies that the instance will be assigned a public IP address and can be accessed from the Internet. In general this is a bad idea; I prefer to have a micro instance on the VPC that I can use for administration.
Port allows you to change the default port for your database. Table 10-2 lists the default ports for each engine.
Table 10-2
Default Port by Engine Type
Engine
Default Port
MySQL/MariaDB
3306
Oracle
1521
PostgreSQL
5432
SQL Server
1433
DBParameterGroupName allows you to alter engine parameters. For example, I will show you how to enable the Common Language Runtime (CLR) in the next section.
DBOptionGroupName allows you to alter engine options. For example, I will show you how to enable Transparent Data Encryption (TDE) in the next section.
Wow, that was a lot of options to discuss. By now our instance should be running. You can use the Get-RDSDBInstance command to check on it. Check the DBInstanceStatus attribute. For example:
You can now enter the address into SQL Server Management Studio to connect. Figure 10-2 shows an example.
Figure 10-2
Logging into an RDS with SQL Server Authentication
Joining a Domain
You can optionally join your RDS instance to an Active Directory Domain using Directory Service. I’m not going to assume you already have Directory Service configured.
Note
RDS only supports the Microsoft Active Directory version of Directory Service. You cannot use Simple Active Directory or Active Directory Connector.
RDS is going to need permission to join the domain. There is a managed policy defined for RDS Directory Service Access, but you need to create an IAM role. Go back to Chapter 9 to review IAM roles.
Now we can launch an RDS instance just like we did earlier with the addition of two new parameters. Domain is the ID of your Directory Service domain, and DomainIAMRoleName is the IAM role we just created.
Now you can log in with directory credentials, for example (Figure 10-3).
Figure 10-3
Logging into an RDS with Windows Authentication
All of the database we have launched so far have been single instance. Let’s launch a highly available multi-AZ database next.
Multi-AZ Instances
RDS supports multi-AZ instances for high availability and durability. When you deploy a multi-AZ database, AWS deploys a primary instance in one AZ and a synchronous replica in another AZ (see Figure 10-4). All of the complexity is hidden from you, and the database appears to be one logical instance. If the primary database were to fail, RDS automatically fails over and updates the DNS entry so your application begins using the secondary without manual intervention.
Figure 10-4
Multi-AZ deployment
Launching a multi-AZ instance is just like launching a stand-alone instance, except that we add the MultiAZ option. I am also going to override the default storage type to specify SSD disk. For example:
That is all there is to it! RDS takes care of the heavy lifting. All of the preceding options we discussed are supported, except for the AvailabilityZone parameter, on any of the commands that have it. You cannot choose which AZ the primary database runs in. RDS manages that behind the scenes.
Of course, we hope that we never need the standby instance, but we should plan for failure. It’s also good to test your application and ensure if the failover works as expected. You can test the failover by running Start-RDSDBClusterFailover. For example:
In addition to replicating to a standby instance, RDS can also replicate to additional read replicas. Let’s take a look in the next section.
Modifying an Instance
No sooner do you launch a new instance than you realize you need to change something. Many of the options we discussed in the last section can be modified after the RDS instance has been launched by using Edit-RDSDBInstance.
For example, let’s assume we are running out of disk space and need to increase the volume size.
Notice that I have included the ApplyImmediately attribute. If I did not, the change would be applied during the next maintenance window. The following options can be altered using Edit-RDSDBInstance.
AllocatedStorage
AllowMajorVersionUpgrade
ApplyImmediately
AutoMinorVersionUpgrade
BackupRetentionPeriod
DBInstanceClass
DBParameterGroupName
DBSecurityGroups
EngineVersion
IOPS
MasterUserPassword
MultiAZ
NewDBInstanceIdentifier
OptionGroupName
PreferredBackupWindow
PreferredMaintenanceWindow
VpcSecurityGroupIds
Notice that I can change the DBInstanceClass either scaling up or down. This allows me to resize my database when I need to. If you have a multi-AZ instance, RDS will perform this change without taking an outage. It will first resize the standby instance, then perform a failover, and finally resize the primary. Imagine we are approaching a busy period and want to scale up our db.r4.large to db.r4.xlarge.
Even with the graceful failover, active connections when the failover occurs will fail. Therefore, this is a modification best done without applying immediately.
As you can see, modifications are easy to make. You can also change database engine options. Will get to that shortly, but first let’s look at how we delete a database.
Deleting an Instance
When you no longer need an instance, you can delete it using the Remove-RDSDBInstance command. If you want to take a snapshot of the database before deleting it, you can simply specify the identifier when you call remove. (I will explain RDS snapshots later in the chapter.) The following command will delete the database we created:
As you can see, RDS makes launching and managing a database instance really easy. In the next section, we will discuss how to configure options specific to SQL Server.
Configuring a Database Engine
So far, all of the parameters we have configured are common to all of the database engines. Obviously there are also engine-specific configuration options to choose from. RDS breaks these into two categories: parameters and options. Let’s spend a minute looking at parameters and options specific to SQL Server.
Modifying Parameters
Parameters allow you to configure your database engine. RDS organizes parameters into parameter groups for each engine type. For example, the default parameter group for SQL Server Express is default.sqlserver-ex-14.0. You can get a list of parameter groups using the Get-RDSDBParameterGroup command.
There are numerous parameters available for SQL Server, and not all parameters are available on all SQL Server editions. For example, some options are only available on the Enterprise Edition. To list the parameters available, use the Get-RDSDBParameter command. For example, the following code will list the parameters specific to SQL Server Express:
If you want to customize the parameters, you can create your own parameter group using the New-RDSDBParameterGroup command. For example, let’s assume you want to enable the Common Language Runtime (CLR) to support stored procedures written in .Net. Start by creating a new parameter group.
New-RDSDBParameterGroup -DBParameterGroupName 'SQL2017' -DBParameterGroupFamily 'sqlserver-ex-14.0' -Description "SQL2017 with CLR enabled"
Now you can configure the individual parameters in the group. Once again, we use a .Net object to describe the change and pass it to the EditRDSDBParameterGroup command. For example:
Note the ApplyMethod parameter. Some parameter changes can be applied immediately, while others require a reboot. You can check if a reboot is required by checking the apply type returned by Get-RDSDBParameter. If the apply type is static, then a reboot is required. If the apply type is dynamic, you can choose to apply the change immediately or after a reboot. To apply the change immediately, set the ApplyMethod parameter to immediate. To wait for the next reboot, set the ApplyMethod parameter to pending-reboot. You can force the reboot using the Restart-RDSDBInstance method.
Use the DBParameterGroupName of the New-RDSDBInstance or Edit-RDSDBInstance command to associate the new parameter group with an instance.
Modifying Options
Some database engines offer optional features that you can choose to enable. For example, SQL Server Enterprise Edition offers Transparent Data Encryption (TDE) or enabling backup and recovery to S3.
Option groups work a lot like parameter groups. First, you create a custom option group, and then you associate your instance with the custom group. Let’s get started by creating a custom option group to enable S3 Backup on SQL 2017 Express Edition. This option allows RDS to read and write SQL backups (∗.bac files) to S3. This is a great way to import data from an on-prem database to SQL Server. Let’s begin by creating a new S3 bucket for our role.
New-S3Bucket -BucketName pwsh-book-rds-backup
RDS is going to need permission to S3 to read and write ∗.bac files. Therefore, we are going to need to create a new IAM role for RDS. If you need to review how IAM roles work go back to Chapter 9. Let’s start by creating a new role and specifying the RDS service as the principal.
Similar to parameter groups, we use a .Net object to define the settings. In this case we enable the SQLSERVER_BACKUP_RESTORE option and specify the Arn of the new role we just created.
Check out the exercises at the end of this chapter for an example of enabling Transparent Database Encryption (TDE). Next, let’s look at native RDS backups using snapshots.
Working with Snapshots
RDS supports two types of backup: snapshots and point-in-time recovery. The backup windows and retention period we discussed earlier are related to point-in-time recovery and will be discussed in the next section. This section is about RDS snapshots, which are similar to EC2 snapshots.
A RDS snapshot creates a copy of the database just like an EC2 snapshot creates a copy of a volume. They are created manually using either the AWS Management Console or the API. You can create as many snapshots as you want, any time you want. Snapshots are retained until you manually delete them and are not affected by the retention period specified when you create the instance.
When you restore a RDS snapshot, AWS always creates a new instance. You cannot overwrite an existing database using a snapshot. This is just like restoring an EC2 snapshot, which, we already know, always creates a new volume rather than overwriting an existing one.
You can create a new snapshot using the New-RDSDBSnapshot command. This command simply takes the name of the instance you want to back up and a name to identify the snapshot.
It will take a few minutes to create the snapshot. You can check on the status of the snapshot using the Get-RDSDBSnapshot command. For example, to check on the snapshot we just created, use the following command:
The Get-RDSDBSnapshot command can also be used to list all the snapshots taken for a given database instance. The following command will list all snapshots taken of the SQLServer01 instance:
You can restore a snapshot using the Restore-RDSDBInstanceFromDBSnapshot command. Remember that restoring a snapshot always creates a new instance. Therefore, we need to include a new identifier. In addition, we can change many of the parameters we specified when we created the database instance.
The following command will restore a RDS snapshot creating a new RDS instance called SQLServer03. The new instance will have a new DNS name, and you must update your application to use the new name.
Note that I had to specify the subnet group in the preceding command. In addition, I could have changed any of the following options. If you leave these options blank, RDS will use the settings that were present on the original instance rather than the defaults defined for New-RDSDBInstance:
DBInstanceClass
Port
AvailabilityZone
MultiAZ
PubliclyAccessible
AutoMinorVersionUpgrade
LicenseModel
Engine – Note that the engine must be compatible. You cannot restore an SQL Server snapshot to an Oracle database, but you can move from Standard Edition to Enterprise Edition.
IOPS
Just like EC2, RDS snapshots can be copied to another region for an additional level of redundancy. You can copy a snapshot using Copy-RDSDBSnapshot. The copy is always initiated from the target region. Rather than specifying the source region as we did with EC2 snapshots, you must use the fully qualified Amazon Resource Name (ARN) for the source snapshot. The ARN uses the format
Obviously you are charged for the storage required to keep the snapshot. When you no longer need a snapshot, you can delete it using the Remove-RDSDBSnapshot command.
Snapshots are a great way to back up a database when you can plan for a specific risk. For example, you might take a snapshot before upgrading the application code. But, snapshots are not well suited for unexpected issues. For example, if a disk failed, you might not have taken a snapshot recently. For unexpected issues, we need to take regularly scheduled database backups. In the next section, we will examine how to do this.
Using Point-in-Time Restores
In addition to snapshots, RDS also supports database and transaction log backups. Using these backups, we can restore a database within a second of any point in time within the retention period. The best part is that AWS takes care of all the work required to create and maintain the backups.
When we launched the RDS instance at the beginning of this chapter, we accepted the default backup windows and retention period. Remember that the default retention period is 1 day. As long as the retention period is greater than zero, database backups are enabled. If backups are enabled, RDS will take a full backup of the database once a day during the backup window. In addition, it will back up the transaction log every 5 minutes.
These backups can be used to create a point-in-time restore. Point-in-time restores allow you to specify a specific time you want to restore, and since transaction log backups are taken every 5 minutes, you will never lose more than 5 minutes.
Now, I want to mention a few details specific to SQL Server. First, if your SQL Server has multiple databases, the individual databases will be restored to within 1 second of one another. Second, RDS does not support multi-AZ SQL Server instances. As a result, you should expect a momentary outage when the full backup is taken. This does not occur with multi-AZ databases because the backup is taken on the secondary instance.
Similar to snapshots, RDS point-in-time restores always create a new RDS instance. You cannot overwrite an existing instance. Before restoring an instance, you should check when the last transaction log backup was taken and how many days the backups are retained. You can restore to any point within this period. For example, to check the time of the last transaction log backup and retention period of our SQL database, use the following code:
The output of this command, shown as follows, indicates that you can restore to any point within a 1-day window between November 4 at 5:22 p.m. and November 5 at 5:22 p.m.
Tuesday, November 5, 2013 5:22:42 PM
1
We can use the Restore-RDSDBInstanceToPointInTime command to create a new RDS instance restored to any point within this range. For example, to restore to November 5, 2013, at 11:15 a.m., use the following command. This is almost identical to the Restore-RDSDBInstanceFromDBSnapshot command except that I am specifying a time and day to restore to. Note that RDS expects the time in UTC.
Just like when restoring a snapshot , you are creating a new instance, and you can specify many of the options that were available when we created the original instance, including
DBInstanceClass
Port
DBSubnetGroupName
AvailabilityZone
MultiAZ
PubliclyAccessible
AutoMinorVersionUpgrade
LicenseModel
Engine
IOPS
Unlike snapshots, there is no need to delete database backup files. They are automatically deleted after the retention period. This is the benefit of the RDS platform. AWS takes care of the maintenance for you. In addition, you cannot copy backups to another region.
In the next section, we discuss how to keep track of our RDS instances using tags and how to monitor our instances using events and logs.
Working with Tags, Events, and Logs
As your inventory of servers grows, it will become more and more difficult to keep track of everything. It is really important that you have a strategy for organizing and monitoring your resources. RDS offers tags to help categorize everything and events and logs for monitoring. Let’s look at each.
Tags
We saw the power of tags with EC2. The same holds true for RDS. You can use tags to include metadata describing your RDS resources. For example, you might want to tag an instance with the department that owns it so you can create a chargeback report and know whom to contact if something goes wrong.
Creating a tag is similar to EC2. You begin by creating a .Net object used to describe the tag. Then you add a key and value. For example, the following code will create a tag specifying the department=marketing.
$Tag = New-Object('Amazon.RDS.Model.Tag')
$Tag.Key = 'Department'
$Tag.Value = 'Marketing'
To add the tag to a RDS resource, you use the Add-RDSTagsToResource command. Remember that RDS uses names rather than ids to identify resources. Different resource types can have the same name. For example, I can name both an instance and snapshot “database1.” As a result, we have to use the fully qualified Amazon Resource Name (ARN) to uniquely identify a resource. Remember that ARNs follow the format
Tags are a great way to organize RDS resources. In the next section, we will look at using RDS events to monitor our instances.
Events
It is important that you always know what is going on in the cloud. Events allow us to monitor our RDS instances and receive notifications from SNS when specific events occur. For example, you might want to be notified when the disk is filling up.
To get a list of all events, we use the Get-RDSEvent command. For example:
Get-RDSEvent
You can control how many events are returned using the Duration and MaxRecords parameters. For example, the following command will return the first 25 events that occurred in the last 15 minutes:
Get-RDSEvent -Duration 15 -MaxRecords 25
You can also specify a specific range using StartTime and EndTime, but events are only stored for 15 days. For example:
RDS captures many event types. Events are organized into source types that correspond to the RDS resource types and include db-instance, db-security-group, db-parameter-group, and db-snapshot. Events are further organized into categories. To get a list of categories, use the Get-RDSEventCategories command. For example, to get the categories available for an RDS instance
You can use the parameters of the Get-RDSEvent command to limit the events returned. For example, to only retrieve events for the SQL instances we created earlier, use the following command:
Similarly you can filter for specific event categories. For example, the following command will return all information about the backup of any RDS instance:
Of course, you can combine these in various combinations to return the events you want. The following command will return all of the backup events for a specific instance:
Being able to query events is great, but we cannot expect someone to sit in front of PowerShell all day looking for issues. We really want a more proactive solution. Luckily RDS allows us to subscribe to events using Simple Notification Service (SNS) with the New-RDSEventSubscription command.
For example, let’s assume we want to know whenever a failure occurs or the disk space is getting low. More specifically, we want to receive a notification via e-mail so we can respond quickly. First we need to create an SNS topic and e-mail notification. This is exactly what we did in Chapter 8, for example:
Now we can create a RDS subscription. The RDS subscription will publish a notification to the SNS topic we just created whenever a new RDS event occurs that matches the criteria we specify. To create the subscription, we use the New-RDSEventSubscription command. For example, the following command will subscribe to all failure and low-storage events and send a notification to our SNS topic:
We can also subscribe to events from specific sources. For example, you might have both development and production RDS instances in the same account. You don’t want to get a notification in the middle of the night if a development instance fails, so you only set up notifications for the production instances. The following example creates a subscription for a specific instance, SQLServer01:
The source ID in the previous example is all lowercase. Your source ID must be lowercase or you will get an error.
As our application changes over time, you may want to add or remove instances from the subscription. You can do this using the Add-RDSSourceIdentifierToSubscription and Remove-RDSSourceIdentifierFromSubscription commands. The following two examples add and then remove an instance from the subscription:
Finally, you may want to delete a subscription altogether and stop receiving notifications. You can do so using the Remove-RDSEventSubscription command. For example:
Events are a great way to monitor your RDS instances, but you will likely need more detail to debug a failure when it occurs. In the next section, we discuss how to retrieve logs from the database engine.
Logs
With RDS you do not have access to the operating system and therefore cannot access the file system. This means that you cannot see the detailed logs produced by the database engine. In order to access the logs, you need to use an API call.
To list the log files available on the instance, you use the Get-RDSDBLogFiles command. This command will list the log files available on the server. For example:
You can also use the FilenameContains parameter to find specific files. For example, to find the error log on an SQL Server, use the following command. Note that the file name is case sensitive.
Once you know which file you are looking for, you can download the contents using the Get-RDSDBLogFilePortion command. For example, to read the error log on our SQL instance, use the following command:
As you can see, RDS gives us all the tools we need to manage and monitor our database instance. In the next section, we will briefly discuss Amazon Aurora.
Amazon Aurora
Amazon Aurora is a cloud native database engine that supports both MySQL and PostgreSQL. According to Amazon, Aurora is up to five times faster than MySQL and three times faster than PostgreSQL. To achieve this, Aurora using a different from the RDS databases we have been discussing so far (Figure 10-5).
Figure 10-5
Amazon Aurora Architecture
Aurora is built on a cloud native shared storage solution that is replicated across three availability zones. This storage solution can scale up to 64TB per database. That’s four times the size of traditional RDS databases. In addition, you only pay for storage you are using rather than the storage you provision.
Rather than replicate data between a primary and secondary instance like other RDS databases, Aurora allows you to add up to 15 read replicas to a cluster that share the same storage. If the writer node fails, any of the read replicas can be promoted.
Let’s start by creating a new cluster. This is very similar to launching a new RDS database. However, you may notice that I am not specifying an instance size. That is because the cluster only creates the underlying storage, and you add database instances separately.
Next, we can add read replicas to the cluster. You can spread read operations over many read replicas. In addition, this instance will be promoted if the writer fails.
If you want to control which instance is promoted on failure of the writer, you can specify a PromotionTier. By default, instances are added as tier 1.
If we describe the cluster, you will notice that we have three instances. aurora01a is the writer instance. In addition, aurora01b has a higher tier and will be promoted before aurora01c if the writer fails.
The Endpoint attribute will always return the IP address for the writer instance. This is always the same IP, assuming the writer has not failed. If you run the following command a few times, you will keep getting the same IP address which is the IP address of the aurora01a instance:
ReaderEnpoint, on the other hand, will load balance across the two reader nodes. If you run this command a few times, you notice that you randomly get one of the two reader IP addresses. Of course you can still send read requests to the writer node; however, it’s better to distribute your load across all the nodes in the cluster.
As you can see, RDS and Aurora offer everything you need to build a robust database platform without having to worry about the day-to-day details of system administration and backup. Let’s wrap up this chapter with two exercises focused on securing SQL Server. The first will enable SSL to protect your connection and the second will enable Transparent Data Encryption.
Exercise 10.1: SQL Server and SSL Encryption
It is always a good practice to encrypt the connection between your client and server. It is common to do so between the user and a web server, but less common between the web server and database. SQL Server supports encrypting the connection using SSL.
You can enable SSL when using an SQL Server RDS instance. All RDS instances include a self-signed certificate. Of course, your client machine will not trust the self-signed certificate until we import the public key into the trusted store. Let’s build a script to do so.
You can download the public key from https://rds.amazonaws.com/doc/rds-ssl-ca-cert.pem. Let’s use PowerShell to save a copy of the key on our client machine. This command must be run on the client machine.
Next, we can use PowerShell to import the certificate into our trusted store. Note that you must run PowerShell as an administrator on our client machine to complete this step.
Finally, we should clean up the temporary copy of the certificate.
Remove-Item "$env:TEMP
ds-ssl-ca-cert.pem"
That’s all there is to it. All you have to do to enable encryption is add two parameters to the connection string: encrypt=true and TrustServerCertificate=true. For example:
User Id=sa;Password=password;encrypt=true;TrustServerCertificate=true"
Encrypting your database connection is a simple way to add an extra layer of security. In the next example, I will show you how to encrypt the data that is stored on disk using TDE.
Exercise 10.2: SQL Server Total Database Encryption
Earlier in this chapter, we talked about option groups, and I showed you how to create an option group that enables SQL Server Transparent Data Encryption (TDE). In this exercise, we build on that example to fully configure SQL TDE in a new instance. We will create an option group that enables TDE, launch a RDS instance that uses the new option group, create a new database on the RDS instance, and encrypt the new database.
First, we need to accept a few parameters as input to our script. These should all look familiar; they are all the parameters that will be passed to New-RDSDBInstance. Notice that the default engine is SQL Server Enterprise Edition. Remember that TDE is only supported on the Enterprise Edition of SQL Server. In addition, notice that the default instance class is small and I have allocated 200GB of disk. These are the minimum values for SQL Server Enterprise Edition.
Next, we create the new option group just like I did earlier in this chapter. In the following example, I first check if the option group already exists and, if not, create a new group.
It will take a while for the instance to start. Let’s add a while loop that will wait for it.
While ($Instance.DBInstanceStatus -ne 'available') {$Instance = Get-RDSDBInstance $DBInstanceIdentifier; Write-Host "Waiting for RDS instance to launch.";
Start-Sleep -s 60}
Once it’s done, we can get the address and report it back to the user so he or she can log into SQL Server and finish the configuration.
Write-Host "The RDS instance $DBInstanceIdentifier is ready. The address is $Address."
At this point TDE is enabled on the instance, but the individual databases are not encrypted. TDE allows you to selectively encrypt individual databases on an instance. Each database has its own encryption keys, and the individual encryption keys are protected by the server’s certificate, which was created by Amazon when we enabled TDE.
We can use SQL scripts to create and encrypt a database. The remaining scripts in this exercise are SQL scripts that should be run in SQL Management Studio against the RDS instance.
Let’s begin getting the name of the server certificate. Make reference of the name that is returned; you will need it later.
USE [master]
SELECT TOP 1 Name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%'
Next, we create a new database that we will encrypt. If you already have a database on the instance, you can just skip this step.
USE [master]
CREATE DATABASE MyDatabase
Then, we create a new encryption key for our database. Replace <<PUT_NAME_HERE>> with the name of the certificate you found earlier.
Use [MyDatabase]
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE
<<PUT_NAME_HERE>>
Finally, you can alter the database to enable encryption.
ALTER DATABASE MyDatabase SET ENCRYPTION ON
That’s all there is to it. With TDE enabled, everything SQL writes to disk is encrypted including data files and backups.
Summary
RDS provides a developer everything that he or she needs to launch a database server without the burden of managing it. AWS will take care of the maintenance, backups, replication, and monitoring, so you can concentrate on your application.
We have seen how to launch and configure SQL Server instances. We learned how to restore instances from snapshots and perform point-in-time recovery from database backups. We also learned to create scalable, highly available architectures using multi-AZ instances and read replicas. Finally, we learned how to secure SQL Server using SSL to encrypt the connection and TDE to encrypt files on disk.
In the next chapter, we will focus on Simple Storage Service (S3). S3 is a highly resilient data solution for storing files. This is the data store AWS uses to keep snapshots and RDS backups, but you can use it to store anything you want.
