Estimating AWS EC2 EBS Snapshots

Estimating and understanding what AWS EC2 EBS Snapshots will cost you can be more difficult than you may think.

Here are some key points to keep in mind:

  • Snapshots are not compressed. Therefore your first snapshot will be equal to the GiB used in the source EBS volume.
  • Additional snapshots are incremental. Each incremental snapshot uses pointers, pointing to the prior snapshot’s blocks that have not changed. New blocks are recorded.
  • You can use the AWS Cost Explorer to view past usage. Today is not available. Filter down by “Usage Type Group” and set the value to “EC2: EBS – Snapshots”. Narrow down further by region and/or tag.
    • Usage (GB) are measured by “GB-Month”. So if there are 30 days in that month, multiple the metric by 30 to get that day’s actual usage.
  • As of 12/10/2018, the cost of a snapshot is $0.05/GB/mo

The hard part is estimating the amount of change per snapshot. The most lenient method would be to use a 100% change value. But that’s not practical.

Let’s say you estimate that 3% of your total volume size will be modified per snapshot. Therefore plan on an additional cost of $.15/mo for every 100 GiB of used volume space on every snapshot produced..

SQL Server Agent Jobs on AWS RDS Multi-AZ

When running AWS RDS Microsoft SQL Server you may run into a configuration issue that may trip you up either during failover or instance upgrades.

This is taken from the Microsoft SQL Server Multi-AZ Deployment Notes and Recommendations section under the “Multi-AZ Deployments for Microsoft SQL Server with Database Mirroring” document:

If you have SQL Server Agent jobs, you need to recreate them in the secondary, as these jobs are stored in the msdb database, and this database can’t be replicated via Mirroring. Create the jobs first in the original primary, then fail over, and create the same jobs in the new primary.

This is one of the weaknesses in the Multi-AZ for RDS Server Server service.

They use mirroring to keep two RDS instances loaded with identical user table data, but they can’t mirror MSDB because it’s a system database.

One of the reasons jobs are so confusing on Multi-AZ SQL Server is, if you start off as Single-AZ, and move to Multi-AZ, all of your jobs are copied as part of the move to Multi-AZ. That’s because AWS takes a snapshot of all your data (including MSDB) and recreates it on the mirrored instance. This is where it can get confusing: people who look at a multi-AZ instance, and at a “was Single-AZ, now is Multi-AZ” and see inconsistent behavior in the jobs. But it can all be understood if you apply two rules:

  1. Jobs created when you’re Single-AZ will be copied when you move to Multi-AZ, because AWS takes a snapshot of all databases (including MSDB), but
  2. Other than that, no changes to jobs will ever be copied to the mirror unless the changes are done manually on both servers.

Continue reading

AWS Database Migration Service Endpoint Connection Issue

When setting up an AWS Database Migration Service (DMS) endpoint to an EC2 instance, within your VPC, you may get the error stating the connection could not be established and there’s a login timeout.

Test Endpoint failed: Application-Status: 1020912, Application-Message: Failed to connect Network error has occurred, Application-Detailed-Message: RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ODBC general error.

This may be due to lack of ingress into your EC2 instance. Create a security group that allows the appropriate port into your EC2 instance, for example 1433 for SQL Server, limited to the private IP address of the DNS instance. Then attach that security group to the EC2 endpoint (database).

That’s the easy part. But how do you find the private IP? It’s not listed anywhere in the DMS console.

  1. Go to your DNS Replication Instance and copy the VPC and public IP address listed.
  2. Go to Network Interfaces inside your EC2 console.
  3. Look for the network interface with the copied public IPv4 address and VPC ID.
  4. Copy the Primary Private IPv4 IP.
  5. Go to Security Groups.
  6. Select or create on that is associated with your database endpoint instance.
  7. Add the copied IP into the source field of an inbound rule.