AWS Database Blog

Ilia Gilderman is software development manager in Amazon Web Services

Congratulations! You have convinced your boss or the CIO to move your database to the cloud. Or you are the boss, CIO, or both, and you finally decided to jump on the bandwagon. What you’re trying to do is move your application to the new environment, platform, or technology (aka application modernization), because usually people don’t move databases for fun.

Database migration is a complex, multiphase process, which usually includes assessment, database schema conversion (if you are changing engines), script conversion, data migration, functional testing, performance tuning, and many other steps.

Tools like the AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT), native engine tools, and others can help to automate some phases of the database migration process. The key to success, though, is to make your database migration project predictable. You don’t want any surprises when you are 10 terabytes deep in the data migration phase.

So you are eager to start. But do you have all the information required to finish the project successfully?

This blog post discusses common migration problems that can occur when you move your database to the cloud. In the last section, we will discuss some more detailed information related to an AWS migration.

Plan your talent
Application modernization can be challenging, because there are many moving parts and you need to understand all of them. Even if you are an expert with your source database engine, if you switch engines you need to be an expert in the target database engine also. You are going to move your data from one database to another, which will involve crossing the network, so somebody needs to know about servers, ports, and firewall rules. As previously mentioned, database migration is usually a part of a bigger application modernization project, which means the database migration (even for homogeneous migrations) will involve some changes to application code. A common scenario we’ve all heard of is a 15-year-old, 300,000 line COBOL application, developed by dozens (or even hundreds) of developers, and none of them works in the company anymore. But even if you are lucky and you have code written in a modern language by three developers still proudly working for you, somebody will need to assess what changes to the application code the database migration will require. Finally, depending on the cloud provider, you will need somebody on your team who is familiar with the particular provider ecosystem. To make a long story short, you will need your best people on this project.

Plan your time
Database migration projects usually include refactoring application and database code, and also the schema, which is a time-consuming, iterative process. The refactoring process can take anywhere from a few weeks to several months, depending on the complexity of your application and database. If you have hard dates (for example, if you need to vacate your data center), be realistic about what you can achieve. Even if everything goes well, data migration might be slower than anticipated. Be aware that planning can often take more time than the actual migration!

Understand your database
Understanding your source database is crucial to the success of the migration project.

Again, we can go to the extreme and think about a 15-year-old database that can still support the first version of your product, despite the fact that support for this version was discontinued years ago. We can think about the database that has thousands of tables and dozens of schemas and still has these stored procedures which you wrote when you just joined the company more than a decade ago…

But let’s say you are lucky and it’s not that bad. Can you answer this simple question: What is the size of the database you are trying to migrate? You might be surprised—most customers fail to answer this question. The size of the database is important when determining the correct approach to use for your migration project. It also helps to estimate how long the data copy will take. What is even more important is how many schemas and tables you are going to migrate. Knowing the layout of your database can help you define a migration project and allows parallel execution of your migration project. For extra credit, you can try to find out which tables participate in which transactions. If you know, you can execute the migration of multiple disjointed table sets in parallel.

Another important question to ask is: How many very large tables exist in your database? “Very large” is subjective, but for migration purposes, tables larger than 200 gigabytes and with hundreds of millions of rows might end up being a long tail for your data migration.

Okay, suppose you’ve done some initial digging and now you have data about the size, schema, and tables of the database to migrate. The next question you should ask is: Do we have some “fancy” engine-specific types in our schemas? Having those usually presents a challenge, especially if you want to switch database engines. Even if you are planning a homogenous migration, many tools won’t support all the data types involved. As usual, checking the documentation is a good start. Later, when you migrate your data, verify all your data looks correct on the other end before you shut down the power at your data center.

Now let’s talk about LOBs. If you don’t have them in you database, lucky you, but keep reading to understand how lucky you are. The bad news is migrating LOBs can be slow. Move time can be longer if you don’t know what the maximum size of the LOBs in your schema are. It can be very painful to move even small numbers of them. Another challenge you might face is that you have LOBs in a table without primary keys or any other unique constraints (perhaps your DBA read some cutting-edge blog 10 years ago that claimed this was a good idea). These tables will cause you some pain if you want changes to them to be part of your change data capture (CDC) stream.

Before we talk how not to overload your source database, it’s important to know that working with database roles, users, and permissions can be a huge time-waster during a migration. Most migration tools require some elevated access to the source database catalog to retrieve metadata, and to read logs and replicate changes. Knowing your database roles and permission model, or at least having the name of the person who knows it and can grant access, helps.

Finally, even if you get all the types, schemas, and LOBs right, it’s possible the migration will be slow. In some cases, you won’t be able to afford this slowness and it will cause your migration project to fail. A few factors can affect how long the wait will be for the last row to show up on the other end. Some of them are easy to tune and take into consideration; others can be harder.

The first is how hot or busy your source database is. This one is important to understand because most migration tools will put some additional load on your source database. If your traffic is very high, it might be unrealistic to plan a live migration and you should consider alternatives. Usually, compacted databases migrate faster and with fewer issues, so you should find out when your database was last vacuumed. If the answer is never, think about doing it.

Phew—there are a lot of questions to answer, and a lot of people to harass to get the answers. However, having all the answers helps you save a lot of time during your first migration and create some best practices for the following ones.

Understand your network
The sad truth is, a database migration requires extensive network knowledge from you or your people. It becomes worse if you are migrating a database that is part of your corporate fleet. Most likely, network access to these databases is managed by several teams (security, networking, and so on). You might need to talk to all of them to punch a hole in the firewall or change routing so the replication server can access the database. You’ll also need information about the network setup your organization has with its cloud provider.

Finally there is this question: Does the connection between your source and target database have enough bandwidth for the migration? Don’t forget, your organization can have an awesome 10 GB direct pipe to your cloud provider, but that doesn’t mean it’s all yours. Make sure that when you start to move data you know exactly how much of this pipe the migration is going to use, and that you are not taking down mission-critical workloads by kicking the tires migrating your test database.

Understand your requirements
I know you want to see that giant server that runs your database or databases go away for good, and sooner rather than later. But do you know what are you replacing it with? Do you know all the key parameters of your new environment? Setting some of these is a new decision to make. Some you’re inheriting from your current environment, or your app dictates them. The simplest questions are: Can your app afford downtime, and how long can this downtime be? In most fleets, some apps can take 20 to 30 minutes of downtime or even better have a maintenance window of a few hours. Hint: It is better to have these apps as your first project. You won’t be dealing with CDC, and you can use dump and restore or native tools to migrate your database. Another important question that will affect your approach is: Do you need your source database to remain available after the migration is done? If so, how do you switch back?

Even if you are doing lift-and-shift, you have decisions to make about your new platform. You have even more decisions if you are switching engines. For instance, do you know why you chose one engine over another? I hope that it’s not because your lead developer told you, “Dude, this is cutting-edge, everybody is using it” and that you had a more structured conversation why this specific engine is best for your app, your team, and your timeline.

Another important decision to make is whether all the data needs to migrate. For example, you might have an audit log table that is 97 percent of your database. Yes, you need the logs for compliance reasons, but could they be archived later instead of being part of the live migration? Another example: Do you have some dead tables in your database that supported a feature not offered on the new platform?

Even if all the data needs to move, this is a perfect time to examine alternatives for some of your data. For example, can you make use of noSQL stores or data warehouses on the other end? In general, it’s always good to keep it simple—that is, move the data first, then transform, restructure, or rearchitect it. That said, it might be that refactoring your database during the migration is the only opportunity to do so. In any case, it doesn’t hurt to plan and analyze your data model, even if you are going to change it after migration is complete.

Finally, after all your preparations, thousands things can go wrong during and after the migration. Having a contingency plan is always a good idea. Can you flip back? Can you run your app on the old and the new database simultaneously? Can you afford to have some data unavailable for a period of time? Answers to these and some other questions help you shape a better contingency plan.

Create your target schema
There are also questions about the target database schema. Even for homogeneous migrations, it’s better to apply the schema in parts. You need to have your tables and primary keys before you can move data. But for performance and other reasons, you might want to create secondary indexes and apply constraints later. You will also likely want to keep your triggers disabled for the initial load of the data. The point is, somebody needs to prepare your schema to be applied in parts. If you are switching engines, the AWS Schema Conversion Tool mentioned earlier can help with some of this. Also, unless there is a pressing need, it’s better to postpone all schema transformations until the end of the migration.

Migrate to AWS
Migration to every cloud has its own specific steps and requires an expertise with the specific cloud ecosystem. You need to understand details of networking, permissions, roles, accounts, and so on. Not only do you need to know how the cloud works in general, but also be very familiar with your specific corporate cloud implementation. This section of the post will cover some of the specifics of migrating to AWS and what help is available, and help you ask the right questions about the benefits and limitations of your new environment.

As I said before, AWS has created a number of tools and support materials to help you to be successful with your migration projects. The first and very important tool is the documentation. Please, please read the documentation for DMS and the documentation for SCT. Of course, you know how to set up supplemental logging and you remember most of the Oracle data types and how they map to rest of the database engines. But please spend some time reading the documentation. It will save you a lot of time and frustration during the migration. The DMS and SCT forums are another place to find useful information before you start the project.

For many use cases, using Amazon RDS for your target database is a natural choice; you get the benefits of a managed service. Make sure you are familiar with those—things like backups, OS and database patches, security, high availability, scalability, elasticity, integration with the AWS ecosystem, and so on. Using RDS frees you from spending time on these activities.

But there are also limitations. Make sure you are familiar with those—things like storage limits, lack of admin privileges on your databases, and so on. You need to evaluate carefully whether you really do require access to your database host. Of course, your DBA will tell you that the world is going to end the moment SSH access is lost to the database box, but will it really? Some apps need this access, but in most cases the sun will shine the day after your DBA loses database admin privileges, as it does for other customers.

RDS also offers a variety of managed high availability (HA) features. This is a good point to review your HA requirements. Yes, your current setup includes a few MySQL slaves, but why do you have them? Maybe you can do better and more importantly do with less pain, or maybe this is why your database guy wanted to have the host access? It’s important to understand that some of these decisions can be made after the migration. For example, it is perfectly fine to migrate to Single-AZ MySQL (actually faster) and then convert it to a Multi-AZ instance, or go further and use Amazon Aurora.

Finally, AWS offers a wide list of migration partners that can help you to make an assessment, scope, and even execute your migration projects.

Summary
Database migration projects can be hard, especially your first one, but the benefits of migrating your database to the cloud are significantly greater than the challenges migrations can present. You can make these challenges predictable and less painful through diligent preparation and collecting the necessary information before you start. Thousands of AWS customers have already migrated with or without our help. Some of them took some shortcuts and hit every possible problem during their first migration; others spent some time preparing and flew through their migrations with zero pain.

The following checklist will help you to ask the right questions before you start.

Thanks for reading! Have a successful database migration!

Database migration checklist
1. What is the size of your database?
2. How many schemas and tables do you have?
3. How many really big tables do you have (200 gigabytes or 200 million rows in size)?
4. What do the transaction boundaries look like?
5. Do you have engine-specific)data types that won’t be migrated by your migration tool?
6. Do you have LOBs in your tables, and how large are they?
7. Do all your tables with LOBs have primary keys?
8. How hot is your source database?
9. What kind of users, roles, and permissions do you have on the source database?
10. When was the last time you vacuumed or compacted your database?
11. How can your database be accessed (firewalls, tunnels, VPNs)?
12. Do you know what VPC you want to use in AWS?
13. Do you know what VPC security group you can use?
14. Do you have enough bandwidth to move all your data?
15. Can you afford downtime?
16. Do you need the source database to stay alive after migration? For how long?
17. Do you know why you preferred one target database engine over another?
18. What are your HA requirements?
19. Does all the data need to move?
20. Does it need to move to the same place?
21. Do you understand the benefits offered by RDS?
22. Do you understand the RDS limitations?
23. What happens to your application after the migration?
24. What is your contingency plan?

Satheesh Subramanian is a software development engineer at Amazon Web Services.

AWS Database Migration Service (DMS) lets you migrate databases to AWS easily and securely. The process consists of setting up a replication instance, source and target endpoints, and a replication task. The replication task runs on the replication instance and migrates data from the source endpoint to the target endpoint.

You can use the AWS DMS console or the AWS CLI or the AWS SDK to perform the database migration. In this blog post, I will focus on performing the migration with the AWS CLI.

If you are a first-time AWS DMS user, then follow these steps to set up your account.

• Create an IAM user: http://docs.aws.amazon.com/dms/latest/userguide/CHAP_SettingUp.html#CHAP_SettingUp.IAM
• Set up required permissions for the IAM user: http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.IAMPermissions.html
• Set up the roles required to use AWS DMS: http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.APIRole.html
• Set up the AWS CLI: http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html

After you have completed the setup, follow these steps to complete a simple database migration.

1. Create a replication instance.
   Use the following command to create a replication instance with the name dms-instance.
   aws dms create-replication-instance --replication-instance-identifier dms-instance --replication-instance-class dms.t2.medium --allocated-storage 50
   This command creates the replication instance on a t2.medium instance with 50 GB of allotted storage. It uses default values for other parameters. For more configuration options to use when creating a replication instance, see create-replication-instance in the AWS CLI Command Reference.
2. Describe the replication instance.
   Run the following command to describe the replication instance. The response of this command will include the status of create-replication-instance. This will help you understand the status of the instance creation.
   aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance
   Run the following command to save the ReplicationInstanceArn for use in later steps.
   rep_instance_arn=$(aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance --query 'ReplicationInstances[0].ReplicationInstanceArn')
   It will take a few minutes to create the replication instance. While that’s in progress, create the source and target endpoint objects.
3. Create the source and target endpoints.
   Run the following command to create the source and target endpoints. Provide source and target database details like the engine-name, the hostname and port and the username and password.
   aws dms create-endpoint --endpoint-identifier source-endpoint --endpoint-type source --engine-name --username --password --server-name --port
aws dms create-endpoint --endpoint-identifier target-endpoint --endpoint-type target --engine-name --username --password --server-name --port
   For more advanced options for endpoint creation, see create-endpoint in the AWS CLI Command Reference.
   Run the following commands to save the endpoint ARNs for use in later steps.
   source_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=source-endpoint " --query="Endpoints[0].EndpointArn")
target_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=target-endpoint" --query="Endpoints[0].EndpointArn")
4. Test source and target endpoints from the replication instance.
   After the replication instance is active and the endpoints have been successfully created, test connectivity from the replication instance to these endpoints. The following commands will invoke connectivity tests from the replication instance to the database endpoints:
   aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $source_endpoint_arn
aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $target_endpoint_arn
5. Describe connections to the source and target endpoints.
   The describe-connections response will contain the status of the test connection and, in the case of a failed connection, the failure message. After invoking the connectivity test, describe the connections to ensure the tests are successful. If the test connection fails for any reason, it must be fixed and retested.
   aws dms describe-connections --filter "Name=endpoint-arn,Values=$source_endpoint_arn,$target_endpoint_arn"
   Note: A failure message in the describe response will provide details for the test connection failure.
6. Create a replication task.
   If the test connections are successful, use the following command to create the task:
   aws dms create-replication-task --task-identifier replication-task-1 --source-endpoint-arn $source_endpoint_arn --target-endpoint-arn $target_endpoint_arn --replication-instan+ce-arn $rep_instance_arn --migration-type --table-mappings file:///tmp/table-mappings --task-settings file:///tmp/task-settings
   This command assumes you have table mappings and task settings files in the temp directory (in Linux/Mac OS). For information about how to specify the file input for various platforms, see Loading Parameters from a File in the AWS Command Line Interface User Guide.To decide which values to use for task settings, table mappings, and migration types, see the following:
   • Task Settings for AWS Database Migration Service Tasks
   • Migration Methods for AWS Database Migration Service
   • Using Table Mapping with an AWS Database Migration Service Task to Select and Filter Data

   For more advanced options for replication task creation, see create-replication-task in the AWS CLI Command Reference.

7. Describe the replication task.
   Task creation will take a few minutes. After the task is created, describe the task and make sure it is ready to be executed.
   aws dms describe-replication-tasks --filters "Name=replication-task-id,Values=replication-task-1"
   Run the following command to save the replication task ARN for use in later steps:
   replication_task_arn=$(aws dms describe-replication-tasks --filters "Name= replication-task-id,Values=replication-task-1" --query "ReplicationTasks[0].ReplicationTaskArn")
   Run the following command if you want to just retrieve the status of the task:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].Status"
8. Start the replication task.
   Run the following command to start the task after it is ready to be executed:
   aws dms start-replication-task --replication-task-arn $replication_task_arn --start-replication-task-type start-replication
   For all available options for the start-replication-task command, see start-replication-task in the AWS CLI Command Reference.
9. Monitor the progress of the replication task.
   After you start the task, it’s very important to monitor its progress. Run the following commands to keep track of the task progress.
   To monitor the overall task-level statistics, run the following command:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].ReplicationTaskStats"
   To monitor the table-level statistics, run the following command:
   aws dms describe-table-statistics --replication-task-arn $replication_task_arn
   To monitor the task status itself, run the following command:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].{Status:Status,StopReason:StopReason}"
10. Stop the replication task.
    You can stop the migration after data is completely migrated from source to target. Run the following command to stop the migration task:
    aws dms stop-replication-task --replication-task-arn $replication_task_arn
11. Delete the replication task.
    If you don’t want to keep the task, run the following command to delete it:
    aws dms delete-replication-task --replication-task-arn $replication_task_arn
12. Delete the source and target endpoints.
    If you no longer need the endpoints, run the following commands to delete them:
    aws dms delete-endpoint --endpoint-arn $source_endpoint_arn
aws dms delete-endpoint --endpoint-arn $target_endpoint_arn
13. Delete the replication instance.
    After your migration is complete, run the following command to delete the replication instance:
    aws dms delete-replication-instance --replication-instance-arn $rep_instance_arn

This was a simple migration. For more advanced database migrations, see the AWS Database Migration Service User Guide.

For migration best practices, see AWS Database Migration Service Best Practices.

And in case something goes wrong, see Troubleshooting AWS Database Migration Service Tasks.

Sign in to the AWS DMS console to easily create and manage database migration tasks here: https://console.aws.amazon.com/dms/home

Prudhvi Janga is a software development engineer in Amazon Web Services

SQL Server Native Backups and Restores Using RDS and Amazon S3

If you’re thinking about moving your on-premises Microsoft SQL Server databases to Amazon RDS, one option to migrate your databases is to use native SQL Server backups. Backups can be uploaded to AWS using Amazon Simple Storage Service (Amazon S3). These backups can also be encrypted using the AWS Key Management Service (AWS KMS). Once the backup has been uploaded to Amazon S3, you can then restore your databases onto an RDS SQL Server instance. We recommend this option for bulk migration if having only a minimum downtime is not a critical requirement for you. You can also use this feature to:

• Move databases between Amazon RDS SQL Server DB instances.
• Back up and restore individual databases, as opposed to entire RDS instances.
• Store and transfer backups in and out of RDS while preserving complete compatibility with native SQL Server installations.
• Move database backups to RDS to use as a disaster recovery solution for on-site SQL Server installations.

Setting Up
RDS offers option groups to enable certain features on an RDS instance. You can associate an option group with multiple options. Each option typically represents one feature. Depending on the feature, the option might have additional settings to be configured.

To enable native backups on your RDS instances, you create an option group, add the SQLSERVER_BACKUP_RESTORE option, and assign it to an RDS instance. The steps following show how.

1. Choose Option Groups on the navigation pane in the RDS dashboard in the AWS Management Console.
   
2. Choose Create Group to create a new option group.
   
   Note: You can also select one of your existing groups and add the option. However, the default option groups cannot be modified. The default groups start with the word default and can be easily identified. So, unless you already have an option group that you have manually created in the past, you will have to create a new one.
3. Type the option group name, description, and other details, and choose Create.
   
   Note: Option groups are specific to particular RDS engines, engine editions, and major engine versions. If you have multiple RDS instances running on different editions and versions of SQL Server, you’ll have to create an option group for each edition and version combination.
4. Select the option group that you created in the previous step and choose Add Option.
   
5. In the Add Option section, fill in the details.
   Typically in an on-premises setting, DBAs back up databases to disk and move the backup to external storage by using a file transfer mechanism. However, backups made by using this feature in RDS are directly uploaded to S3. So, RDS needs permissions to access your S3 bucket to upload and download database backups.To provide this access to RDS, you can do one of two things. You can provide your own AWS Identity and Access Management (IAM) role—this approach comes in handy when you already have an IAM role that contains a bucket where you want RDS to place backups or retrieve backups from. Or you can let RDS set up an IAM role for you in the background by providing the necessary information. If you let RDS set up the IAM role:
   • You can specify an existing S3 bucket or create a new one.
   • An S3 prefix is optional. We recommend it only in cases where you want to restrict access to a shared bucket to the files in folders with that specified prefix.
   • You can enable encryption, to encrypt and decrypt the backups using KMS. If you enable encryption, you’ll need to specify a KMS key to encrypt or decrypt backups. We will cover this process in a separate blog post.

   Once the option group creation succeeds, you can see the IAM role that was created in the option groups’ screen. You can also edit it by going to the IAM service dashboard.
   
   

6. You can assign the new option group to an existing or a new RDS instance.To assign the option group to an existing RDS instance, navigate to the RDS dashboard, choose Instances, and then select the instance that you want to associate the option group with. For Instance Actions, choose Modify. You can modify the option group associated with the instance under Database Options:Note: After you have associated the instance with the option group, it will take anywhere from 10 to 15 minutes for the modifications to complete. When the instance returns to available status, you can start using the feature related to that option group with your RDS instance.

Troubleshooting Errors during Setup
For possible errors during setup, some recommendations follow.

This error occurs when RDS can’t generate temporary credentials to access an S3 bucket using the IAM role specified. In this case, it’s likely that the IAM role provided doesn’t give RDS access to the S3 bucket. To fix this issue, you can modify the role and provide RDS access (rds.amazonaws.com) under the trust relationship tab of the IAM role.

This error occurs when the AWS console is unable to retrieve existing IAM roles, which happens if IAM service is unavailable temporarily. To address this issue, try again later.

Backing Up or Restoring a Database
To perform backups and restores, RDS provides four stored procedures in the MSDB system database. The master user can run or grant access to others to run these stored procedures. Following are the stored procedures and their details.

Backup Task Stored Procedure

Restore Task Stored Procedure

Task Status Stored Procedure

Task Cancellation Stored Procedure

After you issue a backup or restore task by using one of these stored procedures, RDS outputs task information as shown following. You can also retrieve this information later by running the rds_task_status stored procedure.

The following table gives some details about these columns.

Troubleshooting Backup and Restore Errors
When you run the stored procedures, you might encounter some validation errors. These errors are displayed immediately, and a task will not be created if you encounter these errors. You can find common validation errors and suggested fixes following—here, assume your database is named AdventureWorks.

Sometimes, the backup or restore task is created successfully but you encounter errors afterward and the task errors out. You can find the related error information in the task_info column that is displayed by running the rds_task_status stored procedure. Following, you can find some common errors and how you can correct them.

In addition to these errors, you might receive errors from SQL Server if there are problems performing a backup or restore. For example, you might receive errors when restoring from a backup file that is not backward compatible, restoring a database greater than 10 GB on a SQL Server express instance, or restoring when the backup is not correctly formed, to name a few.

Stay tuned for our next blog post on migrating enterprise workloads using AWS Data Migration Service (AWS DMS).

Shawn McCoy is a database engineer in Amazon Web Services.

As a PostgreSQL database owner, you know that critical maintenance operations are handled by the autovacuum process. The effectiveness of this background process is constantly being improved with each release. For most PostgreSQL users, the default settings will work just fine. However, some workloads and usage patterns require customized parameters. I’ll cover two areas in this post: First, what you can do to monitor your Amazon RDS for PostgreSQL database’s transaction ID health, and second, common things I’ve helped customers work through once a problem is identified.

What is “transaction ID” health?
A PostgreSQL database can have two billion “in-flight” unvacuumed transactions before PostgreSQL takes dramatic action to avoid data loss. If the number of unvacuumed transactions reaches (2^31 - 10,000,000), the log starts warning that vacuuming is needed. If the number of unvacuumed transactions reaches (2^31 - 1,000,000), PostgreSQL sets the database to read-only mode and requires an offline, single-user, standalone vacuum. This vacuum requires multiple hours or days of downtime (depending on database size). A very detailed explanation of transaction ID wraparound is found in the PostgreSQL documentation.

Early warning is the key
We now know that around 2.1 billion unvacuumed transactions is “where the world ends” to quote the source code comment. There are several reasons a transaction can become this old, but let’s first look at how you can be alerted of this pending problem. Although it can be monitored with manual queries, we have introduced an Amazon CloudWatch metric, MaximumUsedTransactionIDs, to make setting this kind of alert easier for you. To generate this metric, the Amazon RDS agent runs this query:

SELECT max(age(datfrozenxid)) FROM pg_database;

From my experience working with customers, I recommend an alarm when this metric reaches a value of 1 billion. For some situations, a low severity warning at 500 million might also be useful. Let’s take a look at configuring a CloudWatch alarm, as described in the CloudWatch documentation.

First, choose CloudWatch in AWS Management Console:

Then choose Alarms on the navigation pane:

Choose Create Alarm to create a new alarm:

Select the MaximumUsedTransactionIDs metric and press Return to bring up a list of your instances. For this example, I’m going to just select one of them. If you have several instances, you can group several together and get a single alarm.

After selecting your target instance, choose Next to set up the threshold:

Then set up the threshold (1000000000) and notification email:

In my example, I’ve already exceeded the threshold, so I get an email with this wording:

This message will also show up in the CloudWatch dashboard:

Setting this threshold to 1 billion should give you plenty of time to investigate the problem. It really depends on the problem’s cause how much time it will take to fix. The default autovacuum_freeze_max_age value is 200 million. If the age of the oldest transaction is 1 billion, autovacuum is having a problem keeping this threshold at the target of 200 million. It’s a luxury to have extra time to fix these issues. There is nothing more frustrating than trying to fix this type of problem while keeping the system under normal workloads.

The monitor has sent an alarm—now what?
Let’s say that the monitor has sent the alarm email and you know that there is a problem. I’ll walk through the steps that I take in identifying solutions to the most common problems I’ve helped customers resolve. One thing I like to do after I have identified the problem is to execute a verbose vacuum command and see how long the action takes. This approach can help me get out of immediate trouble and plan my long-term fix.

The three things to identify are:

• Where autovacuum is currently working.
• Which database is aging.
• Which tables are aging.

With this information, you can start to diagnose what is happening.

What is autovacuum doing?
To find out what autovacuum is doing, you can take the following action. This action requires that you are running RDS Postgres 9.3.12 or later, 9.4.7 or later, or 9.5.2 or later to have full visibility into the running rdsadmin processes. This query will show the information we want:

SELECT datname, usename, pid, waiting, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;

Which databases are aging?
To find out which databases are aging, the following query will tell us where to start work:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC LIMIT 20;

Which tables are aging?
To find out which tables are aging, connect to the first database indicated in the result from the preceding query. This database will have the table containing the oldest transaction, and this query will show the top 20 oldest tables and their size. Most likely, these tables are the ones you can observe autovacuum working on in the first query.

SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
pg_size_pretty(pg_table_size(c.oid)) as table_size
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r'
ORDER BY 2 DESC LIMIT 20;

Let’s look at two different scenarios based on the output of these queries.

The case of long-running autovacuum sessions
In our first example, there are sessions that have been running for a long time. This situation usually indicates maintenance_work_mem is set too small for the size of the table.

These sessions will most likely never complete. These are two common causes of this scenario:

1. The top problem I have seen is that maintenance_work_mem is too small for the size of the larger tables. This critical parameter determines how much tuple information the autovacuum worker can hold in memory while it’s processing tables. If this parameter is too small for a table, it will require multiple passes on the table to complete. For more information, see RDS recommendations for working with the maintenance_work_mem.
2. If you have adjusted your maintenance_work_mem parameter and the workers are still not keeping up, you might need to adjust the individual parameter setting for the larger tables. The benefit of this approach is that you can allow autovacuum to remain less aggressive on your entire system and just target larger tables. With the default parameters, autovacuum will take a nap of autovacuum_vacuum_cost_delay (20 milliseconds) every time the internal counter hits auto_vacuum_cost_limit. For a large table, it can be very beneficial to have autovacuum take no “breaks” at the expense of additional resource consumption. To make sure that’s the case, use this setting:

   ALTER TABLE mytable SET (autovacuum_vacuum_cost_delay=0);

   For more information, see RDS recommendations for working with the other autovacuum parameters and how you set them.

The case where autovacuum sessions seem to complete, but overall fall behind
The scenario where autovacuum sessions seem to complete but really fall behind can be a little trickier to fix. By default, autovacuum_max_workers is set to 3. This setting is usually fine. However, if you have an extremely high number of tables (30,000 or more), three workers can be overwhelmed. Recall the query above to check what autovacuum is doing. The results here seem to indicate things are okay:

This result actually looks normal. The autovacuum sessions haven’t been running long, and all three are working on tables. But we received an alarm indicating our database age has passed our threshold of 1 billion. So what is the problem?

If we look at the query that checks the age of databases and tables, we see that there is something preventing autovacuum from keeping up:

At this point, I check exactly how many tables I have for autovacuum to work through, because these queries don’t indicate any problem other than that there are still enough tables aging to trigger my alarm.

SELECT count(*) FROM pg_tables;

Schema design questions aside, there must be too many tables for our default three workers to traverse. The typical fix is to increase autovacuum_max_workers. Two things to note are that the cost parameter auto_vacuum_cost_limit is evenly distributed among active workers and that each worker can consume up to maintenance_work_mem. In this case, I recommend the following settings to see if progress is made and the CloudWatch graph shows a downward trend:

I also recommend taking a look at the schema to see if improvements can be made in the design and lessen the overall number of tables.

Other scenarios
This post identifies a process to monitor this critical PostgreSQL metric, gives steps to start troubleshooting the problem, and describes a couple of common scenarios I have worked with RDS customers to fix. Other situations can arise, but I hope that this post helps you start the troubleshooting process.

In conjunction with your CloudWatch metric, it’s also useful to enable autovacuum logging so you can see where time is being spent. However, it might be beneficial to execute a manual “vacuum freeze” on a table that is approaching that 2 billion “age” that we talked about at the beginning of this post. It’s useful to see how long the operation takes. When using the verbose keyword, you get a better sense at what is happening—for example, if you don’t need those indexes that cause this operation to take additional time, it’s a good time to get rid of them! Here the RDS documentation outlines the steps to execute a manual verbose vacuum.

You can check out more information about autovacuum in Working with PostgreSQL Autovacuum on Amazon RDS in the RDS documentation. If you have any questions, please let us know in the comments following.

Eran Schitzer is a product manager in Amazon Web Services.

Today we are pleased to announce the release of version 1.0.500 of the AWS Schema Conversion Tool (SCT).

Since version 1.0.100 was released at re:Invent last year, the SCT has helped customers convert their schema and database objects from commercial engines to Amazon Relational Database Services (RDS) open-source, engine-based databases. Because customer obsession is one of the key leadership principles at Amazon, we’ve used feedback from our customers to add many new and exciting capabilities to SCT version 1.0.500.

Some of those capabilities include:

• Copy your on-premises schema to an Amazon RDS engine: You can now use SCT to copy your existing on-premises database schema to an Amazon RDS database instance running the same engine. This feature can help you analyze potential cost savings of moving to the cloud and changing your license type.
  An SCT assessment report provides statistics on the conversion of schema from the source to the target database. A sample SCT assessment report looks like this:
  
  Figure 1: SCT assessment report showing conversion statistics for copying SQL Server to Amazon RDS for SQL Server
  For more information, see Creating and Using the Assessment Report.
• Create mapping rules: Before you use SCT to convert your schema, you can now set up rules that move objects from one schema to another and change the names of objects. For more information, see Creating Mapping Rules in the AWS Schema Conversion Tool.
• Export a JSON file to AWS DMS: After converting your schema to your preferred database engine, you can now generate a JSON file, which will be used by AWS Data Migration Service. The JSON file contains the list of objects to be migrated by AWS DMS and any transformations on those objects.

For more information about version 1.0.500, see AWS Schema Conversion Tool User Guide. To download SCT, go to https://aws.amazon.com/dms/#sct.

Darin Briskman (@briskmad) is a developer evangelist for Amazon Web Services.

We hope you enjoy this guest post from Joel Hensley, Engineering Director for the Community Tribe at Hudl, a leading software company that is revolutionizing the way coaches and athletes prepare for and stay ahead of the competition. You can reach Joel @fusbal.

Hudl offers coaches and athletes the tools to edit and share video, study associated play diagrams, and create quality highlight reels for entertainment and recruiting purposes. When football season starts in August, coaches and athletes from all over the country sign in to Hudl every day. After users sign in, they land on a tailored news feed like the one you see below. It includes team content and accounts the user is following. Because this page is our users’ first impression of Hudl, its performance is critical. That’s why we cache the news feed by using Amazon ElastiCache for Redis.

Before we discuss the details, it’s important to understand our data model. There are six main collections used by the feed:

Let’s consider two users: Sally and Pete. Sally decides to follow Pete. At Hudl, this means Sally is a follower of Pete and Pete is a friend of Sally. When Pete posts something, that content is added to his user timeline and to Sally’s home timeline. When Sally signs in to view her feed, the content on her home timeline is displayed in reverse chronological order. If she clicks on Pete, she sees his user timeline and can view all of his posts.
Each time a user hits his/her feed, we grab a batch of post IDs from the user’s home timeline, fetch those posts, and load all of the users referenced in those posts. Since the feed’s creation in April 2015, the database has grown quickly. The total size is now up to 120 GB. We currently have 18 million follower relationships and 30 million pieces of content. So where does caching come into play?

Redis
In the world of caching, there are two primary options: Memcached and Redis. For a long time at Hudl, our default option was Memcached, but with the introduction of our news feed, we decided to explore the Redis data structures. We’re really excited by what we found:

• Lists
  This alone would have been reason enough to use Redis! Timelines are stored as lists, and being able to represent them that way in cache is amazing. As posts are added to timelines, we simply do an LPUSH (add to the front) followed by an LTRIM (used to cap the list at a max size). The best part is that we don’t have to invalidate the cache as posts are added because it’s kept in sync with the database.

• Hashes
  Displaying the number of followers and friends for a user is a critical component of any feed. By storing these as fields in a hash for each user, we can quickly call HINCRBY to keep the values in sync with the database. No need to invalidate the cache whenever a follow or unfollow occurs.

• Sets
  We love to use RabbitMQ to retry failed operations. Sets are the perfect way to guarantee we won’t accidentally insert the same post on a user’s timeline more than once. There’s no need for an extra database call. We use the post ID as the cache key, each user ID as the member, and then call SISMEMBER and SADD.

Amazon ElastiCache
We needed to spin up and configure a server so we could start testing Redis. Because we love AWS, we decided to try Amazon ElastiCache for Redis. Within minutes, our first test node was up and running Redis. We connected to it through the StackExchange.Redis C# driver.
By using ElastiCache, we were able to easily configure our Redis deployment, node size, and security groups. We were also able to use Amazon CloudWatch to monitor key metrics. We were able to create separate test and production clusters without having to wait for an infrastructure engineer to manually set up and configure the servers.
Here are the details of our production cluster:

• Node type: cache.r3.4xlarge (118 GB)
• Replication enabled
• Multi-AZ
• 2 read replicas
• Launched in VPC

Our final deployment step was to configure alerts through Stackdriver, which integrates seamlessly with ElastiCache.
We were most interested in these three metrics:

• Current connections: If these drop to 0, our web servers can no longer access the cache and require immediate attention.
• Average bytes used for cache percentage: If this reaches 95% or more, we might need to move to a larger node type or lower our expiration times.
• Swap usage: If this reaches 1 GB or more, the Redis server requires immediate attention.

Results
Since its launch more than a year ago, we’ve been very happy with the performance of the news feed. This year, during the week of September 5, 1.2 million unique users accessed their feeds. The feed service averaged 300 requests per second, with a peak of 800.
Here are some quick ElastiCache stats from that week:

• Total cached items: 21 million
• Cache hits: 175K/min (average), 350K/min (peak)
• Network in: 43 MB/min (average), 101 MB/min (peak)
• Network out: 600 MB/min (average), 1.25 GB/min (peak)

Let’s take a look at two calls in the feed service:

• Get Timeline
  This operation just fetches the timeline list from Redis. There are no dependencies.

• Hydrate Timeline
  This call takes the post IDs in the timeline and loads all referenced users and posts. This includes time spent loading records from the MongoDB database if they are not cached and then caching them. This is the primary call used when loading the feed on the web and on our iOS and Android apps.

Based on the success of the news feed, ElastiCache for Redis is quickly becoming our default option for caching. In the last year, five other Hudl services have made the switch from Memcached to Redis. It’s easy to set up, offers blazing-fast performance, and gives users all of the benefits that Redis has to offer. If you haven’t already, we strongly recommend trying Amazon ElastiCache for Redis. Please let us know how it works for you.

For more information, see these sources:
Hudl: http://www.hudl.com/
Hudl Bits blog: http://www.hudl.com/bits
What is Redis? https://aws.amazon.com/elasticache/what-is-redis/
Amazon ElastiCache for Redis: https://aws.amazon.com/elasticache/redis/

Michael Labib, Specialist Solutions Architect, In-Memory NoSQL

You can use a number of AWS services to address the challenges associated with ingesting and analyzing time series data. In this blog post, I focus on IoT sensor data, but the principles and techniques described here can be applied to other data sources.

What Is Time Series Data?
A time series is simply a series of observations captured over a period of time. These observations can be stock quotes, application logs, clickstream data, Twitter feeds, device sensor data, or any other time-stamped data source.

When collected over a period of time, the data points tell a story about the behaviors, patterns, and trends occurring in the observed systems; this data can be used to improve an experience or process. The more data points captured, the richer the information gathered from that data. Accelerating the data analysis and ingestion process allows you to react more quickly to the information.

For example, let’s say you are gathering information from social networks in order to data mine what people are discussing. The sooner you’re able to process that streaming data, the sooner you’ll be able to target potential customers with ads or campaigns and improve your product’s position in the market. The same is true with log data. The sooner you’re able to determine the health of your applications, the sooner you can proactively fix faults in your systems and prevent disruption to your customers.

Storing those data points over time allows you to observe historical trends so you can predict and plan for future events.

In this post, we’ll design an IoT solution that captures sensor information and persists it in Amazon ElastiCache for Redis.

Use Case Requirements
Our use case is to gather sensor information from a consumer thermostat in order to understand the environments in which our customers will use them. We also want to identify, through information gathered by the sensors, any potential issues related to extreme temperature conditions.

We’ll first build a proof of concept to process and analyze the sensor information. Then, we’ll change our architecture to store historical information for additional reporting.

These are the requirements for our solution:

• Velocity: Our solution needs to be able to support hundreds of thousands of requests per second. We’ll be capturing device sensor data that includes information like temperature, humidity, device IP addresses, and other metadata.
• Volume: Although we are primarily concerned with the most active information, in the future we’ll want to store historical data. For this reason, our solution should be extensible.
• Variety: We want a flexible data model so our solution can adapt to changing requirements.
• Order: For accurate reporting and trend analysis, the solution must be able to maintain the order of the data that is ingested.
• Cost and agility: We want to go to market quickly while being cost-conscious. We don’t want to worry about patching, racking and stacking, and so on.
• High availability and scaling: The solution must be highly available and able to scale to support additional devices and load. We also want to be able to apply real-time processing of data for analytics.
• Security: We need to be able maintain end-to-end security to protect the data we are capturing.

Architecture

Let’s use AWS services to build an architecture:

These services offer layered security, ease of use, scalability, high availability, and zero administration.

• Data ingestion: AWS IoT automatically scales to support billions of devices and trillions of messages. It also allows you to easily process and route messages to other AWS services.
• Data processing: AWS Lambda automatically scales your application by running your function in response to a trigger. Just write code and upload to AWS Lambda. It’s completely serverless. When integrated with AWS IoT, it can also act as a continuous event data processor.
• Data storage: Amazon ElastiCache for Redis is a lightning-fast, in-memory data store that supports extremely high requests rates and data ingestion. It provides flexible scaling and advanced data structures that can speed development and reduce computational complexity. There is also no cost for requests to the service or for amounts of throughput. You pay only for the instances you provision, which makes it ideal to query against.

Data Model

This is the data model we’ll be using to capture active sensor data.

A Redis Sorted Set is used to capture and maintain the order of the events (in this case, sensor readings). The key for this data structure is SensorData. This set will maintain the order by using the timestamp value, which acts as the score. The date of the event is converted to a LONG value, which is sorted in reverse order to display the events starting from the newest. We are also storing the deviceId as the value for this set.

Alternatively, we could use a unique ID such as an event ID or request ID to capture the data for a historical view. (This true for the hash key, too.)

A Redis Hash is used to capture the temperature sensor data. An individual Hash is created for each deviceId, which is also the key for the Hash. The deviceId value is stored in the SensorData sorted set. When used together, the sorted set and deviceId provide the time the event occurred and the data associated with the device.

Redis Strings allow you to increment the interpreted integer of a string value for a given key. Our consumer thermostats will be used in various climates, so we’ll increment each individual counter when the temperature reading falls within a given range. These counters will provide analytics in the case we are interested in totals.

Now let’s build our solution!
Step 1: Create a Redis cluster and Redis client on EC2

For simplicity, we have created an AWS CloudFormation template for you to use.

Download the sensor-demo-redis.json template. Open the AWS CloudFormation console and run the template.

The sensor-demo-redis.json template will create a Redis cluster and an EC2 instance. The redis-cli and the Node.js application (temp-sensor.js) have already been deployed to the instance. The temp-sensor.js application will emulate a device sensor, which will be consumed by AWS IoT. The redis-cli will be used to query the Redis cluster. For your convenience, Node.js is also installed on the EC2 instance to run the sensor emulator application.

Step 2: Configure AWS IoT

Now we are ready to create a thing and connect our IoT device (which is an emulator application) to the AWS IoT device gateway. The device gateway allows secure, low latency, bi-directional communication between connected devices and AWS. AWS IoT also includes a rules engine, which we will use to enable continuous processing of data sent by our device. We will configure the AWS IoT rules engine to trigger an AWS Lambda function in a later step.

1. Sign in to the AWS Management Console and open the AWS IoT console at https://aws.amazon.com/iot.

2. Create a thing to represent your device. In Name, type SensorHub, and then choose Create.

3. Choose your thing (SensorHub), and then choose Connect a device.

4. For the device type, choose NodeJS, and then choose Generate certificate and policy.

5. Download the keys and certificate, and then choose Confirm & start connecting.

6. Copy the connection information. You will need it for the next step.

Step 3: Run the Node.js emulation code

The sensor emulator code is already deployed to your EC2 instance and located in /home/ec2-user. The code is available here for your review.

The application includes the following structure:

• node_modules are the required temp_sensor.js Node.js dependencies.
• utils/iot-properties.file is the file where you will place the parameters required by AWS IoT. The utils directory is also where you will place the certificates provided by AWS IoT.
• temp-sensor.js is the Node.js application that will emulate sensor data.

Let’s take a look at the code.

First, we will retrieve the properties stored in the iot-properties.file that we’ll be using to securely authenticate and connect to AWS IoT.

var properties = PropertiesReader ('./utils/iot-properties.file');

const device = awsIot.device ({
"host": properties.get('host'),
"port": properties.get('port'),
"clientId": properties.get('clientId'),
"thingName": properties.get('thingName'),
"caPath": properties.get('caPath'),
"certPath": properties.get('certPath'),
"keyPath": properties.get('keyPath'),
"region": properties.get('region')
});

Note: Be sure the property names in the code match exactly those in the iot-properties.file and place your AWS IoT keys in the utils directory.

In the following section of the code, we generate random data and publish it to the AWS IoT topic named ‘temp_readings’.

device.on('connect', function() {

console.log('\n===========Emulating Sensor Data=================\n');

setInterval(function () {

for (i=10; i>=0; i--) {
//Generate Random Sensor Data
var temperature = Math.floor((Math.random() * 110) + 1);
var deviceId = Math.floor((Math.random() * 5000) + 1);
var IP = Faker.Internet.ip();
var humidity = Math.floor((Math.random() * 100) + 1);

console.log('deviceId= ' + deviceId + ' temperature= ' + temperature + ' humidity=' + humidity + ' IP=' + IP );
device.publish('temp_readings', JSON.stringify (

{ "deviceId" : deviceId,
"temperature" : temperature,
"deviceIP" : IP,
"humidity" : humidity
}
));
}

}, 4000);

});

1. Connect to your EC2 instance by using SSH. Use the user name ec2-user and the SSH key that was created and downloaded earlier.
To find the hostname of the EC2 instance, look on the Outputs tab of the CloudFormation stack or go to the EC2 dashboard.
2. Place the AWS IoT certificate and private key you downloaded earlier in the./utils directory, and then update the ./utils/iot-properties.file with your connection information and the names of the key and certificate.
3. Run the Node.js application with node temp-sensor.js.
4. Go to the AWS IoT console.
5. Choose MQTT Client and create a test client to test whether temperature readings are being captured. Choose Connect.

6. Subscribe to the temp_readings topic, which is where the emulation code is publishing temperature readings. Choose Subscribe.

7. If everything was set up correctly, sensor data should start being published.

Task 4: Deploy a Lambda function and AWS IoT rule

This task will create the AWS Lambda function that will be used by the AWS IoT rules engine to persist the temperature readings into Amazon ElastiCache for Redis. The AWS IoT rule will trigger the execution of the Lambda function. The code will also create the Redis data model.

1. Download and unzip the PersistSensorData.zip package.
The application includes the following structure:

• node_modules contains the required PersistSensorData.js Node.js dependencies.
• PersistSensorData.js is the node application that will process the sensor events and persist the data into Amazon ElastiCache for Redis.

Let’s take a look at the code.

First, we will log the details of the event, making a connection to the Redis cluster using the node_redis client. We’ll use the Redis multi command for transaction support.

var redis = require("redis");

exports.handler = function(event, context) {

console.log("Request received:\n", JSON.stringify(event));
console.log("Context received:\n", JSON.stringify(context));

console.log(" deviceId:" + event.deviceId +
" temperature:" + event.temperature +
" deviceIP:" + event.deviceIP +
" humidity:" + event.humidity );

client = redis.createClient("redis://your-redis-endpoint:6379");
multi = client.multi();

2. Update the Redis connection endpoint in the Lambda function with the Redis cluster endpoint inside the PersistSensorData.js function.

To find the Redis cluster endpoint, open the Amazon ElastiCache console. In the Nodes column, click the 1 node link.

Your Redis cluster endpoint is the endpoint value.

Now update the Redis endpoint in the Lambda function with this endpoint. Leave the port value in there.

For example, this: redis.createClient("redis://your-redis-endpoint:6379");

becomes this:
redis.createClient("redis:// sen-re-a4mbqq65e605.rhpopm.0001.usw2.cache.amazonaws.com:6379");

In the following section of the code, we increment the Redis counters to group similar temperature readings. Those groupings will appear on a web dashboard.

if (event.temperature > 85 ) {

multi.incr("climate:hot");
climate = "hot";
multi.publish("Warnings",
" deviceID:" + event.deviceId +
" temperature:" + event.temperature + "" +
" awsRequestId:" + context.awsRequestId + " timestamp:" + date );

} else if (event.temperature > 75 ) {

multi.incr("climate:warm");
climate = "warm";

} else if (event.temperature > 65 ) {

multi.incr("climate:cool");
climate = "cool";

} else {

multi.incr("climate:cold");
climate = "cold";
}

We also update our sorted set SensorData with the deviceId value and sort it by the date (stored as a LONG), which acts as our score.

multi.zadd("SensorData", date, event.deviceId);

We then store the sensor data in a Redis Hash for each deviceId. In addition to the data we captured from the sensor emulator code, we’ve added properties like awsRequestId, timestamp, and climate. Finally, we execute the commands within the transaction.

multi.hmset(event.deviceId, "temperature", event.temperature,
"deviceIP", event.deviceIP,
"humidity", event.humidity,
"climate", climate,
"timestamp", date,
"awsRequestId", context.awsRequestId);

multi.exec(function (err, replies) {

if (err) {

console.log('error updating event: ' + err);
context.fail('error updating event: ' + err);

} else {

console.log('updated event ' + replies);
context.succeed(replies);
client.quit();

}
});
}

3. After you have updated the Redis connection endpoint in the Lambda function, zip the function and node_modules directory. Name the zipped artifact PersistSensorData.zip.

4. Open the AWS Lambda console.

5. When prompted to select a blueprint, choose Skip.

6. When prompted to configure triggers, choose Next. We will complete the configuration of the trigger in the AWS IoT console.

7. Name your function PersistSensorData and choose Upload.

8. For Handler, type PersistSensorData.handler. For Role, choose Create a custom role.

9. For IAM Role, choose a basic role that includes VPC execution, and then choose Allow.
Note: If you already have a role, you might need to open the IAM console and add the AWSLambdaVPCAccessExecutionRole managed policy to it.

10. Specify the VPC, subnets, and security groups this function should use. For VPC, we can use the default VPC and any of the subnets in it. For Security Group, choose the group created by the CloudFormation stack. It will start with SensorDemo-EC2InstanceSecurityGroup unless you gave your stack a different name. Choose Next.

11. Review the information on the page, and then choose Create Function.

Close this tab. Now that the Lambda function has been created, we can finish the configuration of our AWS IoT rule.

12. Open the AWS IoT console, and confirm the correct region is selected.

13. Choose Create a resource.

14. Choose Create a rule.

15. For Name, type PersistToRedis.
16. For Attribute, type * (asterisk).
17. For Topic filter, type temp_readings.
18. Leave Condition blank.
19. For Choose an action, choose Lambda.

20. Choose Add action.

21. Choose Create.

Task 4: Analyze sensor data by using redis-cli

1. Connect to your EC2 instance using SSH. Use the user name ec2-user and the the SSH key you created or downloaded earlier.
To find the hostname of the EC2 instance, look on the Outputs tab of the CloudFormation stack or navigate to the EC2 console. You should see the instance running in the console.

2. Navigate to the following directory and use the redis-cli to connect to Redis.

/usr/local/bin

3. Connect to your Redis cluster (for example,
redis-cli -h sen-re-a4mbqq65e605.rhpopm.0001.usw2.cache.amazonaws.com -p 6379).

4. The following are some commands you can use to analyze the sensor data.
Note: Make sure the node application, temp_sensor.js, is running. You might want to have two SSH sessions open.

What are the last n events?
zrevrangebyscore SensorData +inf -inf WITHSCORES LIMIT 0 5

Which events occurred during this time range?
zrangebyscore SensorData (1410000000000 14400000000000

Which devices match a particular pattern?
zscan SensorData 0 MATCH 2* COUNT 100

What is the temperature for a specific device?
hget 2942 temperature (This assumes there is a deviceId with a value of 2942.)

What are the totals for each climate type?
get climate:warm
get climate:cool
get climate:cold
get climate:hot

Show me all of the data for a specific device.
hgetall 2942

For a complete list of Redis commands, see http://redis.io/commands.
Task 5: Deploy the sensor dashboard application

Now we’ll deploy a web dashboard that will display the ingested sensor data so we can interact with it. The dashboard information includes what’s contained in the Sorted Set, Hash, and Counters. It also includes notifications from the Redis PUB/SUB channel.

The purpose of this dashboard is to illustrate how easily data can be retrieved from Redis. The .war application is available for download. The front end of the application has AJAX code that continuously queries the data in Redis through our Java service. The Java Redis client we’re using is Jedis.

We’ll use Amazon Elastic Beanstalk to deploy the dashboard.

Sorted set (SensorData)
We are using a set collection we named events and hydrating it with data from our SensorData data structure. For our web dashboard, we simply converted the data into a JSON object that is used in the dashboard UI. We’re using the ZREVRANGEBYSCORE command to return the reverse order of the values based on our timestamp (the score). This will give us our most recent events first. We are also retrieving the top 24 values each time we execute this command to display in the UI.

Gson gson = new Gson();
JsonObject sensors = new JsonObject();

Set events = jedis.zrevrangeByScoreWithScores("SensorData", Double.MAX_VALUE, Double.MIN_VALUE, 0, 24);

for (Tuple event : events)
sensors.add(event.getElement(), gson.toJsonTree(event.getScore()));

Hash (deviceId)
Each time we click a deviceId on our web dashboard, we query the Redis Hash for details of that deviceId. We simply create a Java map and hydrate it with results from the HGETALL Redis command, which returns all of the properties in a Redis Hash for a particular key (our deviceId). We also convert that Java map to a JSON object so it can be easily consumed by the UI.

Gson gson = new Gson();
Map<String, String> eventData = jedis.hgetAll(deviceId);
String json = gson.toJson(eventData);

Counters
For the chart on the dashboard that will show the temperature readings (the counts of the different climates), we simply make a GET request on each Redis key we want included. The counts are in a JSON object so they can be easily consumed by the UI.

Gson gson = new Gson();

JsonObject counters = new JsonObject();
counters.add("chartCold",gson.toJsonTree(jedis.get("climate:cold")));
counters.add("chartWarm",gson.toJsonTree(jedis.get("climate:warm")));
counters.add("chartHot",gson.toJsonTree(jedis.get("climate:hot")));

Pub/Sub
The PersistSensorData Lambda function processes the temperature readings and publishes a notification to the channel “Warnings.” The web dashboard is subscribed to this channel.

jedis.subscribe(new JedisPubSub() {

public void onMessage(String channel, String message) {

writer.println(message + "");
writer.flush();
asyncContext.complete();
}

public void onSubscribe(String channel, int subscribedChannels) {
}

}, "Warnings");

Let’s deploy this code!

1. Open the AWS Elastic Beanstalk console.

2. Choose Create New Application.

3. Provide a name and description for the application, and then choose Next.

4. Choose Create web server.

5. For Predefined configuration, choose Tomcat. Because this is just a demo, for Environment type, choose Single instance. Choose Next.

6. Upload the sample dashboard application, and then choose Next.

7. For Environment name, type a name for your application, and then choose Next.

8. Select the Create this environment inside a VPC box, and then choose Next.

9. On Configuration Details, , choose an instance type, and then select the EC2 key pair you created. You can also provide your email address to get notifications about changes to your environment. Choose Next.

10. Add tags to your environment, and then choose Next.

11. Add your VPC configuration information, including the subnets and security group, and then choose Next. The subnets and security group should match the values you used when you configured the Lambda function.

Note: Be sure to select the same subnet in which your ElastiCache cluster was created.

12. Choose the appropriate permissions for Elastic Beanstalk, and then choose Next.

13. Review the configuration, and then launch the environment.

14. After the application has been launched, choose Configuration.

15. Next, edit the software configuration.

16. Scroll to the bottom of the page, add the following environment properties, and then choose Apply.

For Property Name, type REDIS_CONNECTION_STRING.
For Property Value, add your Redis endpoint.

Your Elastic Beanstalk environment will be updated.

17. Navigate to your Elastic Beanstalk environment. You should see sensor data being populated to the dashboard.

If you don’t, be sure to run your Node.js application and verify:

• The Elastic Beanstalk application security group has access to write to the ElastiCache for Redis cluster.
• The Lambda trigger is configured. Open the AWS Lambda console and choose the Triggers tab.

Conclusion

Amazon ElastiCache for Redis is an excellent storage solution for time series data. It supports extremely high request rates, advanced data structures, high availability, and persistence. And because it’s fully managed, it requires zero administration.

AWS IoT and AWS Lambda are also fully managed services that scale automatically to ingest and process device data.

To help you get started, a free tier for ElastiCache for Redis is available for your use. Use it to kick off your projects and please let us know about your results!

Marie Yap is a solutions architect for Amazon Web Services.

As a managed service, Amazon RDS takes care of the scaling of your relational database so your database can keep up with the increasing demands of your application or applications.

In this blog post, we are going to take a look into how we can vertically and horizontally scale your RDS instance. You can scale vertically to address the growing demands of an application that uses a roughly equal number of reads and writes. Or you can scale horizontally for read-heavy applications.

Vertical Scaling
To handle a higher load in your database, you can vertically scale up your master database with a simple push of a button. There are currently over 18 instance sizes that you can choose from when resizing your RDS MySQL, PostgreSQL, MariaDB, Oracle, or Microsoft SQL Server instance. For Amazon Aurora, you have 5 memory-optimized instance sizes to choose from. The wide selection of instance types allows you to choose the best resource and cost for your database server.

Following are some things to consider when scaling up an RDS instance:

• Before you scale, make sure you have the correct licensing in place for commercial engines (SQL Server, Oracle) especially if you Bring Your Own License (BYOL). One important thing to call out is that for commercial engines, you are restricted by the license, which is usually tied to the CPU sockets or cores.
• Determine when you want to apply the change. You have an option to apply the change immediately or during the maintenance window specified for the instance.
• Storage and instance type are decoupled. When you scale your database instance up or down, your storage size remains the same and is not affected by the change. You can separately modify your DB instance to increase the allocated storage space or improve the performance by changing the storage type (such as to General Purpose SSD to Provisioned IOPS SSD).
• There is minimal downtime when you are scaling up on a Multi-AZ environment because the standby database gets upgraded first, then a failover will occur to the newly sized database. A Single-AZ instance will be unavailable during the scale operation.

To change the instance type, choose Modify from the Instance Actions menu on the RDS console.

Then choose the new DB instance class.

Finally, determine if you want to apply the change immediately or not. To apply the change immediately, select the Apply Immediately check box at the bottom of the Modify page. If you don’t apply the change immediately, then the change will be scheduled to occur during the preferred maintenance window that you defined.

Horizontal Scaling
In addition to scaling your master database vertically, you can also improve the performance of a read-heavy database by using read replicas to horizontally scale your database. RDS MySQL, PostgreSQL, and MariaDB can have up to 5 read replicas, and Amazon Aurora can have up to 15 read replicas.

Read replicas allow you to create read-only copies that are synchronized with your master database. You can also place your read replica in a different AWS Region closer to your users for better performance. Also, you can use read replicas to increase the availability of your database by promoting a read replica to a master for faster recovery in the event of a disaster. However, read replicas are not a replacement for the high availability and automatic failover capabilities that Multi-AZ provides.

Currently, RDS read replicas support transparent load balancing of queries or connections. Each replica has a unique Domain Name Service (DNS) endpoint so that an application can implement load balancing by connecting to the replica endpoint. Let’s look at the options on how we can make applications aware of RDS read replicas.

If your application is using the native MySQL driver, there are MySQL Connectors that allow you to do read/write splitting and read-only endpoint load balancing without a major change to your application. For example, if you have a PHP application, you can use the MySQL native driver’s PHP Mysqlnd replication and load-balancing plugin.

In addition to using a MySQL Connector, you can add a load balancer between your application and database servers. You make this addition so that you have a single database endpoint presented to the application. This approach allows for a more dynamic environment where you can transparently add or remove read replicas behind the load balancer without constantly updating the database connection string of the application. You can also perform a custom health check by using scripts.

As shown in the diagram, you can use a transport or a layer 4 load balancer together with the MySQL Connector. Currently, the Elastic Load Balancing (ELB) load balancer does not support the routing of traffic to RDS instances. Therefore, you might want to consider other options such as HAProxy, which is a open-source software-based load balancer that many people use. In this solution, you can configure HAProxy to listen on one port for read queries and another port for write queries.

Another option is to use a layer 7 SQL-aware load balancer, which allows you to forward queries to your databases using complex rules. This type of load balancer has a more sophisticated capability of understanding how to properly perform the read/write splits on multi-statements than a MySQL Connector does. This solution handles the scaling issues in a distributed database environment, so you don’t have to handle scaling on the application layer, resulting in little or no change to the application itself. To accomplish this, there are several open-source solutions (such as MaxScale, ProxySQL, and MySQL Proxy) and also commercial solutions, some of which can be found in the AWS Marketplace.

Conclusion
In summary, you can scale your RDS configuration up or out to meet the growing needs of your applications. RDS takes care of the heavy lifting in scaling your database so you can focus more on your application or applications.

Wendy Neu is a big data architect at Amazon Web Services.

The AWS Schema Conversion Tool (AWS SCT) is a project-style Java application that can be installed on a local computer or an Amazon EC2 instance. The tool can be downloaded from AWS and is supported on MS Windows, Mac OSX, and Ubuntu and Fedora Linux.

You can use the AWS Schema Conversion Tool to convert your existing database schema from one database engine to another. You can convert relational OLTP schema, or data warehouse schema. Your converted schema is suitable for an Amazon RDS MySQL DB instance, an Amazon Aurora DB cluster, an Amazon RDS PostgreSQL database instance, or an Amazon Redshift cluster.

SCT is simple to use and takes only minutes to get started after you install the tool locally on your machine. It requires only connectivity to the source and target databases. SCT uses JDBC drivers to connect to the source and target databases. When you set up your first DB connection to a new source, the wizard interface will prompt you for a driver. All supported drivers are listed by database engine in the Installing and Updating section of the documentation.

When both the source and target database connections are configured, simply right-click on the schema you want to analyze and choose Create Report.

SCT will examine in detail all of the objects in that schema, including the schema itself, tables, constraints, types, sequences, synonyms, collection types, views, procedures, functions, and packages. It will convert as much as possible automatically and provide detailed information about items it couldn’t convert. The report can be saved as a .csv file or a .pdf file for review.

Navigate to the Action Items tab in the report to see the items that the tool couldn’t convert and understand how much work you have to do.

When you’ve looked at the report, choose Actions, Convert schema from the menu bar to generate the data definition language (DDL) statements for the target database.

DDL for the target database is displayed in the project console, and you can edit it before applying it with SCT or simply saving it as an .sql file for application later.

In development, it’s a good idea to apply the changes directly from within SCT to a sandbox version of your database to understand what objects will be automatically converted, how many objects are involved, and what level of development effort is ahead. Beyond the sandbox and early development phases, it’s best practice to adhere to normal software lifecycle procedures within your organization. Scripted conversions will facilitate key processes like code reviews, automated builds, and automated deployments.

Working with Global Settings
The AWS Schema Conversion Tool is very easy to use and lets a DBA or developer move very quickly to analysis and deployment. However, sometimes the default settings might need to be modified. For example, such a change might be necessary if the wrong driver was entered through the wizard for a database engine, a single schema includes thousands of objects and places the Java virtual machine (JVM) under memory pressure, or verbose logging must be enabled. AWS Schema Conversion Tool exposes fine control over all these key areas through the Global settings dialog box. The sections following offer some suggestions on how to use global settings for fine control over these areas.

All sections following are accessed through the SCT console by choosing Settings and then Global Settings from the top menu bar.

Driver settings
You must have the required JDBC drivers installed for the AWS Schema Conversion Tool to connect to your source or target database. When a driver is downloaded, you can upload the driver into the tool through the wizard or through the Global settings dialog box. The advantage of uploading the drivers through the Global settings dialog box is that you can enter or update all of the drivers at one time.

Choose Drivers from the side navigation panel in the Global settings dialog box, and set the target database driver by choosing Browse, navigating to the local machine, and choosing the .jar file to associate with SCT for a particular database engine.

All projects within SCT for that database engine will use the selected driver.

Performance and memory settings

SCT has menu options that control how aggressively the tool will consume JVM memory:

• Fast conversion, but large memory consumption – This option optimizes for a fast conversion but might require more memory for the object reference cache.
• Slower conversion, lower memory consumption – This option minimizes the amount of memory used but results in a slower conversion. Use this option if your computer has a limited amount of memory.
• Balance speed with memory consumption – This option optimizes provides a balance between memory use and conversion speed.

To set these options, choose Performance and Memory from the side navigation panel in the Global settings dialog box, and change Performance and Memory Management Policy to the desired setting.

JVM size settings
The default amount of memory allocated to SCT is just less than a gigabyte. This memory size is usually sufficient for small databases of less than 1000 objects. For many databases, it is necessary to reconfigure the SCT JVM to use more memory even when the JVM is configured in the low memory consumption configuration.

The screenshot that follows shows an SCT JVM under duress, with the tasks allocated hitting the maximum boundary of JVM memory. The setting was Slower conversion, lower memory consumption and failed due to a too-small JVM memory setting.

You can increase (or decrease) the maximum and minimum memory allocated to the SCT JVM through configuration files.

Configuration files for SCT are located in the AWS Schema Conversion Tool installation directory under a subdirectory called app. The name of the main configuration file is AWS Schema Conversion Tool.cfg. You can modify the file using any text editor—however, don’t use a word processing tool (like Word or WordPad) because it might change certain characters in the file and prevent the file from being read correctly by the JVM.

The image following shows an AWS Schema Conversion Tool installation directory under C:\Program Files as an example.

The default file that is installed with SCT has a few parameters for the tool itself and a single JVM option. None of the existing parameters in the file should be changed without consulting AWS Support.

You can modify the memory settings of the JVM by adding two parameters under the [JVMUserOptions] section. However, use caution when configuring the JVM size. If your maximum size exceeds your system’s available memory, it might crash your machine.

The first of the two JVM memory parameters is the maximum JVM memory size, Xmx, which is recorded in megabytes and should be in standard recognized memory values. To set a maximum JVM size of 4 GB, set this value to –Xmx4096m with no spaces on a single line.

The second parameter is the minimum JVM memory size Xms, which is recorded in megabytes and should be in standard recognized memory values. To set a minimum JVM size of 2 GB, set this value to –Xms2048m with no spaces on a single line.

When you’re done editing, save the file and restart AWS SCT. You should see the values in the status bar change to reflect your new settings.

The screenshot that follows shows an SCT JVM running smoothly because the tasks allocated are operating comfortably within the maximum boundary of the JVM. The setting was Slower conversion, lower memory consumption, and the tool succeeded in converting nearly 5000 objects in about 10 minutes.

Logging
In addition to managing the memory settings of the AWS Schema Conversion Tool, it can be a good idea to increase the logging information produced by the tool when converting new projects. Although increasing logging information might slow conversion slightly, this change will give robust information to AWS Support if any unforeseen errors arise.

To change the logging settings, choose Settings and then Global Settings. In the left navigation pane, choose Logging. See the section above Memory Management for help.

In the logging pane, change Debug mode to true and choose a few key items to increase the logging information for. Setting the Parser, Type Mapping, and User Interface to TRACE can help with key problem areas during conversion.

You can also configure the log location. If the information becomes too verbose for the current file system where the logs are streaming, change the value to a location with sufficient space to capture the logs.

To transmit logs to AWS Support, navigate to the directory where the logs are stored, zip up all the files into a manageable single file, and upload the .zip file with your support case.
When the initial analysis is completed and ongoing development is taking place, simply return Debug mode to false to eliminate the verbose logging and increase speed.

Tip: To manage the log size and streamline reporting issues, remove the logs or move them to another location after a successful conversion. Doing this ensures that only the relevant errors and information are transmitted to AWS Support and keeps the log file system from filling.

All references in this document refer to Version 1 of the AWS SCT and were tested on build 401 successfully.

This post is written by Fabio Douek, an architect at AWS Partner Rubicon Red.

When we started to provision Oracle Fusion Middleware platforms with AWS, we took advantage of the ability to create AWS infrastructure within minutes. We can also use MyST to provision complex platforms that are EDG-compliant with Oracle Fusion Middleware in less than an hour.

One challenge that we faced was that we couldn’t use Amazon RDS for Oracle as the database for our Oracle Fusion Middleware installations. Our inability to do so was primarily because the RDS master user didn’t have the database privileges required to run the Oracle Repository Creation Utility (RCU). As a result, we implemented our own automation for provisioning the Oracle Database running on Amazon EC2 instances.

Although this approach works for running development and test workloads on AWS, when it comes to implementing production workloads, Amazon RDS for Oracle provides a number of additional benefits. These benefits include simplified administration tasks, including backups, software patching, monitoring, and hardware scaling.

In addition, Multi-AZ deployment with Amazon RDS for Oracle simplifies the implementation of a highly available architecture, because it contains built-in support for automated failover from your primary database to a synchronously replicated secondary database in an alternative Availability Zone in case of a failure.

Our approach to this challenge started to change late last year, with a number of our customers looking at running Oracle SOA workloads in production on AWS. Being an AWS Technology Partner, we provided this feedback to AWS, who in return invited us to collaborate with the RDS team.

We spent the last four months working with the Amazon RDS team to test the RCU capability within MyST. This process went extremely well, and the Amazon RDS team worked closely with us to support the go-live of our first customer on Oracle SOA 12.2.1 on AWS using Amazon RDS for Oracle —what we believe to be a world first!

The following diagram illustrates the type of architecture required.

More recently, Amazon has now announced that RCU is officially supported by RDS Oracle. That’s great news, because it means that we can provision an Oracle Fusion Middleware environment that is EDG-compliant and highly available within minutes. This way, we can take advantage of RDS to simplify ongoing operations.

To find out more detail on the steps required to enable Oracle SOA with RDS Oracle, see our blog post on the Rubicon Red website.