14. March 2018 22:20
by Aaron Medacco
0 Comments

Migrating SQL Server Databases to Amazon RDS

14. March 2018 22:20 by Aaron Medacco | 0 Comments

If you're interested in moving an on-premises SQL Server database or a customer-managed SQL Server database powered by EC2 to RDS and need a simple method for doing so then this post is for you. Not everyone is familiar working with the AWS "lego-kit" and sometimes you just need to get things done. The following is meant to get your SQL Server data migrated without requiring a large time investment reading the AWS documentation.

Microsoft SQL Server

For this, I'm going to use the sample database backup found here. This is a small .bak file which, if you use SQL Server a lot, is something you're accustomed to working with.

As usual, please make sure you have installed and configured the AWS CLI on your workstation before invoking these steps. I won't be specifying the AWS region where these resources get provisioned so you'll need to configure the CLI to place everything where you'd like or provide the appropriate options through this process. Additionally, the IAM user I'll be using to invoke these commands has unrestricted administrator access to the account. 

Creating an RDS instance:

  1. If you've already provisioned an RDS instance and just need to move the database to it, you can skip this. In my case, I ran the following to provision a new database instance:
    aws rds create-db-instance --db-instance-identifier sample-instance --allocated-storage 20 --db-instance-class db.t2.medium --engine sqlserver-web --master-username aaron --master-user-password password --db-subnet-group-name default-vpc-07e6y461 --no-multi-az --publicly-accessible --engine-version 14.00.1000.169.v1

    Note: You'll need to provide your own subnet group and your own username and password that's more secure than my demo values.

  2. This commands creates a small RDS instance running SQL Server 2017 Web Edition in a non-Multi-AZ configuration using minimal resources and standard storage.

Creating and uploading your .bak file to S3:

  1. If you already have an S3 bucket and your .bak file stored within it, you can skip this. Otherwise we need to create an S3 bucket:
    aws s3 mb s3://aarons-sqlserver-rds-demo

    Note: Of course, you'll need to choose your own bucket name that is unique and available. Remember to substitute your bucket name for mine on subsequent commands where appropriate.

  2. And to upload the object, I can navigate to the directory where my .bak file lives and invoke the following:
    aws s3 mv AdventureWorks2017.bak s3://aarons-sqlserver-rds-demo/AdventureWorks2017.bak

    Note: Swap the name of your .bak file and bucket name with my example if different.

  3. Understand that this might not work for you depending on the size of the .bak file you are attempting to upload. You may need to use S3 multi-part upload or another method to move a file of a more significant size.

Creating an IAM role granting access to our .bak file for restore:

  1. Create a file named iam-trust-policy.json with the following contents and save it in your working directory:
    {
        "Version": "2012-10-17",
        "Statement":
        [{
            "Effect": "Allow",
            "Principal": {"Service":  "rds.amazonaws.com"},
            "Action": "sts:AssumeRole"
        }]
    }
  2. Create another file named iam-permission-policy.json with the following contents and save it in your working directory:
    {
        "Version": "2012-10-17",
        "Statement":
        [
            {
            "Effect": "Allow",
            "Action":
                [
                    "s3:ListBucket",
                    "s3:GetBucketLocation"
                ],
            "Resource": "arn:aws:s3:::aarons-sqlserver-rds-demo"
            },
            {
            "Effect": "Allow",
            "Action":
                [
                    "s3:GetObjectMetaData",
                    "s3:GetObject",
                    "s3:PutObject",
                    "s3:ListMultipartUploadParts",
                    "s3:AbortMultipartUpload"
                ],
            "Resource": "arn:aws:s3:::aarons-sqlserver-rds-demo/*"
            }
        ]
    }

    Note: This policy assumes you do not require encryption support.
    Note: Remember to swap your bucket name in for mine.

  3. Create the IAM role for RDS by running the following:
    aws iam create-role --role-name rds-backup-and-restore-role --assume-role-policy-document file://iam-trust-policy.json
  4. Write down the ARN value for the role you just created. You'll need it when we add an option to our option group.
  5. Create an IAM policy which defines the necessary permissions to grant RDS by running the following:
    aws iam create-policy --policy-name rds-backup-and-restore-policy --policy-document file://iam-permission-policy.json
  6. Write down the ARN value for the policy you just created. It should be returned to you from the command output.
  7. Now we just need to attach the IAM policy to our IAM role:
    aws iam attach-role-policy --policy-arn <policy-arn> --role-name rds-backup-and-restore-role

Adding the appropriate option group to enable native backup and restore:

In order to restore our .bak file into RDS we need to add an option group to the instance that enables the native backup and restore functionality. 

  1. To create an option group that does this, you can invoke the following command to create an option group. You will need to modify the command for your specific version and edition of SQL Server:
    aws rds create-option-group --option-group-name sqlserver-web-backupandrestore --engine-name sqlserver-web --major-engine-version 14.00 --option-group-description "Allow SQL Server backup and restore functionality."
  2. Now we need to add an option for the native backup and restore. This is where you need to enter the role ARN you saved from earlier:
    aws rds add-option-to-option-group --option-group-name sqlserver-web-backupandrestore --apply-immediately --options OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[Name="IAM_ROLE_ARN",Value="<role-arn>"]
  3. Finally, we need to modify our RDS instance to use the new option group we've setup:
    aws rds modify-db-instance --db-instance-identifier sample-instance --apply-immediately --option-group-name sqlserver-web-backupandrestore
  4. You may need to walk away for a few minutes and come back while RDS modifies the instance option group. If you want to know when the new option group is active then you can run this command:
    aws rds describe-db-instances --db-instance-identifier sample-instance
    and make sure you see a status of "in-sync" for the option group added.

Restoring the .bak file to your RDS instance:

At this point, we can actually begin restoring our backup. You'll need to connect to your database instance, presumably with SQL Server Management Studio or another tool. Assuming that you've defined the appropriate networking settings (enabling public accessibility on the database instance for example) and security group rules (SQL Server requires enabled traffic on port 1433), you can connect using the endpoint for your database instance. If you don't know your database endpoint, you can find it by running the describe-db-instances command again:

aws rds describe-db-instances --db-instance-identifier sample-instance

and checking the address value for "Endpoint".

  1. Once you have connected to the database instance, run the following stored procedure (targeting the "rdsadmin" database is fine). Remember to swap your own values where appropriate:
    exec msdb.dbo.rds_restore_database @restore_db_name='AdventureWorks2017', @s3_arn_to_restore_from='arn:aws:s3:::aarons-sqlserver-rds-demo/AdventureWorks2017.bak';
  2. After running the stored procedure, your restore request will be queued. To see the progress of your request while it executes, you can provide your database name and run this:
    exec msdb.dbo.rds_task_status @db_name='AdventureWorks2017';
  3. Once the request has a status of "SUCCESS", your database should now be available for use.

If you'd like to read the documentation yourself, you can find the relevant material here and here. I thought it would be helpful to condense everything into a step-by-step post for those developers or DBAs still getting up to speed with AWS.

I also encourage readers to review the limitations when using native backup and restore for SQL Server on RDS which you can find here. For instance, you can only restore databases that are 4 TB or less in size at the time of writing. If this solution doesn't work for you due to size limitations or if you require the database to remain online during migration, you may want check out AWS Database Migration Service as recommended by AWS.

Cheers!

7. December 2017 02:49
by Aaron Medacco
0 Comments

AWS re:Invent 2017 - Day 4 Experience

7. December 2017 02:49 by Aaron Medacco | 0 Comments

The following is my Day 4 re:Invent 2017 experience. Missed Day 3? Check it out here.

The common theme of not waking up early continues. Missed the entire Werner Vogels Keynote, which is fine since from what others were saying it was a storm to get in. I didn't have it reserved anyways. AWS added it to the event catalog after I had already decided my schedule. Not sure why I thought you were just supposed to show up. 

First session of the day, AWS Database and Analytics State of the Union - 2017 (DAT201). This took place in the Venetian Theatre.

AWS re:Invent 2017

Love this venue for sessions.

I wasn't sure what to expect from a "State of the Union" session. For the most part, this was a combination of sales pitch, history lesson into the origin of some of the AWS database services, and explanation of miscellaneous features. After the explanation of what the RDS Multi-AZ feature does (really? who doesn't know what this is?), the session moved on to highlight the motivations for building Aurora and DynamoDB. Essentially, AWS wanted to combine the benefits of commercial-grade performance provided by products like Oracle and SQL Server with the low-cost of MySQL, PostgreSQL and MariaDB. The product of these efforts became the Aurora product. 

AWS re:Invent 2017

Horrible quality pic. I don't know why.

After sharing some history, a few of the newest Aurora features came up. Specifically, Amazon Aurora Multi-Master and Amazon Aurora Serverless. Amazon Aurora Multi-Master allows you bring the disaster recovery of a failed master instance to almost nothing, 100 ms. The single-region version of this feature is available in preview now, with the multi-region version available later in 2018. Amazon Aurora Serverless allows you to essentially have an on-demand database that scales for you and is for applications that have unpredictable workloads. Being serverless, the customer manages hardly anything.

The origins for DynamoDB came from a disaster affecting Amazon.com. I didn't write down the exact year, but basically Amazon.com was leveraging Oracle to power the online retail site, however the site became unavailable during Christmas. The cause of this was traced back to limitations in Oracle or Amazon.com's implementation of it, of this I wasn't clear. In response, Amazon built the NoSQL database service, DynamoDB, to handle massive volumes of transactions it was experiencing.

AWS re:Invent 2017

DynamoDB. But does it work at scale? Haha.

The remainder of the session focused on general overviews of the rest of the data service catalog. Therefore, a lot of the session was review for anyone who regularly spends time learning Amazon Web Services. Several attendees started leaving during this time. There's a lot going on during re:Invent so while I understand time is precious during the week, I always stay for the whole session. Maybe it's just out of respect for the presenter. Either way, I did learn a few tidbits about Aurora and some origin history of some services I didn't know before.

AWS re:Invent 2017

Big Amazon Echo.

Grabbed a shuttle to the Aria to check out what was going on at the Quad. I was expecting it to be as large as the Venetian Expo. Boy, was that off the mark! The Quad was very underwhelming, especially after visiting the Expo. There was a small handful of booths, but nothing like the Expo which was aisles and aisles full of them. I smiled when I saw the Certification Lounge there, looked like a large-sized cubicle. At this point, it became clear to me that the Venetian was definitely the primary hub for re:Invent. Good to know for next year's hotel room reservations.

They did have a cool Lego section, though.

During my video filming of the re:Invent areas of the Aria, I got yelled at by some woman telling me I couldn't record. I waited until she stopped looking, and turned it back on after. What a joke! Now, I actually was told not to video record prior to this while in the Venetian casino, which while I'd argue is unenforceable, makes more sense to me. However, back in the Aria, what is there to film that can cause harm!? It's a bunch of nerds with laptops walking around banners, session rooms, and re:Invent help desks a quarter mile from the casino floor! Ridiculous. It's 2017, and there's a tech conference going on here. Are you going to watch every attendee's laptop, tablet and smartphone, too, because guess what, those devices can do video recording as well. Unenforceable and moronic. Anyways...

AWS re:Invent 2017

In case the re:Invent app fails you.

Returned to my room after grabbing a Starbucks and did some blogging until my next session at the Venetian, Taking DevOps Closer to the AWS Edge (CTD401). 

AWS re:Invent 2017

Emptier session. Was actually really good.

This session was possible my favorite of the conference. And I certainly wasn't expecting that. The session title, in my opinion, is misleading, however. Now, the presenter did say that the same session took place last year and included a demo involving saving CloudFormation templates to CodeCommit and managing a delivery pipeline with CodePipeline to push modifications to a development CloudFront distribution, perform some testing, and then do the same to a production CloudFront distribution. That seems more DevOps to me. What we got instead was an in-depth overview of how to incorporate the edge services of AWS into application design and development and how to use CloudFront

AWS re:Invent 2017

More terrible quality pics.

AWS re:Invent 2017

Logic determining the TTL CloudFront uses.

Most of the session was a deep dive and explanation of CloudFront and how it works between your origin (EC2, S3, etc.) and your clients (users). The presenter explained how the TCP connections, both from the user to the edge location, and from the edge location to the origin function as well as provided some tips for keeping your cache-hit ratio high using headers like cloudfront-is-mobile-viewer to reduce variability. Plus, there were some cool examples given of Lambda@Edge taking headers and custom modifying them in the in-between. 

AWS re:Invent 2017

Lambda@Edge examples.

I've not used CloudFront a lot, but I'm more confident about it after this session. A lot of people walked out throughout this session, probably hoping for something different. Can't say I wouldn't have wanted to do the same thing if I knew CloudFront inside and out already. Being a 400-level course, it was surprisingly easy to grasp, perhaps due to the presenter.

AWS re:Invent 2017

Lego blocks.

Back at the Bellagio, stopped in for some grub at the FIX Restaurant & Bar. Snatched a cocktail, salmon, and mashed potatoes.

AWS re:Invent 2017

9/10. Would have been 10/10 if the waitress had brought the mac n cheese I ordered. Maybe she didn't hear me? Don't know. The food coma was instant, so I took a power nap before going out to check out the re:Play party.

Which brings us to the re:Play party! AWS goes all out for this. You could only enter in through the Venetian Hall A even though it was behind the LINQ. 

AWS re:Invent 2017

Oomce, oomce, oomce.

Food and drinks were included and the place was packed. It took place under a set of large tents, one being totally dedicated to games like glow-in-the-dark pool, glow-in-the-dark ping pong, adult-sized ball container, putt-putt pool, batting cages, dodge-ball and more.

AWS re:Invent 2017

Guy got lost in the balls. They couldn't find him.

AWS re:Invent 2017

Another tent is where the rave was going on with DJ Snake.

AWS re:Invent 2017

Oomce, oomce, oomce.

AWS re:Invent 2017

Not sure what these were about.

And then a final tent was packed full of arcade style games. There were some other areas I didn't explore since the line was ridiculous or I wasn't clear how to get in. 

AWS re:Invent 2017

Ancient video games.

I didn't end up staying too long since everything had huge lines and I'm not one for live music anyways.

AWS re:Invent 2017

Walked back to the Venetian casino and played poker with other attendees from re:Invent. Lost another $300. What is going on, man! I'm just the worst, although I turned Aces up when a guy got a set. Understandable, but annoying. Came home with some Taco Bell (I know, classy) and turned in for the night.

Cheers!

3. December 2017 18:42
by Aaron Medacco
0 Comments

AWS re:Invent 2017 - Day 2 Experience

3. December 2017 18:42 by Aaron Medacco | 0 Comments

The following is my Day 2 re:Invent 2017 experience. Missed Day 1? Check it out here.

Day 2, I started off the day waking up around 11:30am. Needed the rest from the all-nighter drive Monday morning. Cleaning lady actually woke me up when she was making her rounds on the 4th floor. This meant that I missed the breakout session I reserved, Deploying Business Analytics at Enterprise Scale with Amazon QuickSight (ABD311). I'm currently in the process of recording a Pluralsight course on Amazon QuickSight, so I felt this information could be helpful as I wrap up that course. Guess I'll have to check it out later once the sessions are uploaded to YouTube. Just another reason to never drive through the night before re:Invent again.

After getting ready, I exposed my nerd skin to sunlight and walked over to the Venetian. This is where I'd be the majority of the day. I kind of lucked out because all of my sessions for the day besides the one I slept over were in the same hotel, and pretty back-to-back so I didn't have to get creative with my downtime. 

First session of the day was Deep Dive into the New Network Load Balancer (NET304). I was curious about this since the network load balancer's announcement recently, but never had a use case or a reason to go and implement one myself. 

AWS re:Invent 2017

I have to admit, I didn't know it could route to IP addresses.

AWS re:Invent 2017

Should have picked a better seat.

AWS re:Invent 2017

Putting it all together.

The takeaways I got was that the NLB is essentially your go-to option for TCP traffic at scale, but for web applications you'd still be mostly using the Application Load Balancer or the Classic Load Balancer. The 25% cheaper than ALB fact seems significant and it uses the same kinds of components used by ALB like targets, target groups, and listeners. Additionally, it supports routing to ECS, EC2, and external IPs, as well as allowing for static IPs per availability zone.

As I was walking out of the session, there was a massive line hugging the wall of the hall and around the corner for the next session which I had a reservation seat for (thank god). That session was Running Lean Architectures: How to Optimize for Cost Efficiency (ARC303). Nerds would have to squeeze in and cuddle for this one, this session was full. 

AWS re:Invent 2017

Before the madness.

AWS re:Invent 2017

Wasn't totally full, but pretty full.

AWS re:Invent 2017

People still filing in.

AWS re:Invent 2017

Obvious, but relevant slide.

I had some mixed feelings about this session, but thought it was overall solid. On one hand, much of the information was definitely important for AWS users to save money on their monthly bill, but at the same time, I felt a lot of it was fairly obvious to anyone using AWS. For instance, I have to imagine everybody knows they should be using Reserved Instances. I feel like any potential or current user of AWS would have read about pricing thoroughly before even considering moving to Amazon Web Services as a platform, but perhaps I'm biased. There were a fair number of managers in the session and at re:Invent in general, so maybe they're not aware of obvious ways to save money. 

Aside from covering Spot and Reserved Instance use cases, there was some time covering Convertable Reserved Instances, which is still fairly new. I did enjoy the tips and tricks given on reducing Lambda costs by looking for ways to cut down on function idle time, using Step Functions instead of manual sleep calls, and migrating smaller applications into ECS instead of running each application on their own instance. The Lambda example highlighted that many customer functions involve several calls to APIs which occur sequentially where each call waits on the prior to finish. This can rack up billed run time even though Lambda isn't actually performing work during those waiting periods. The trick they suggested was essentially to shotgun the requests all at once, instead of one-by-one, but as I thought about it, that would only work if those API calls were such that they didn't depend on the result of the prior. When they brought up Step Functions it was kind of obvious you could just use that service if that was the case, though.

The presenters did a great job, and kept highlighting the need to move to a "cattle" mentality instead of "pet" mentality when thinking about your cloud infrastructure. Essentially, they encouraged moving away from manual pushing, RDPing, naming instances thinks like "Smeagle" and the like. Honestly, a lot of no-brainers and elementary information but still a  good session.

Had some downtime after to go get something to eat. Grabbed the Baked Rigatoni from the Grand Lux Cafe in the Venetian. The woman who directed me to it probably thought I was crazy. I was in a bit of a rush, and basically attacked her with, "OMG, where is food!? Help me!"

AWS re:Invent 2017

Overall, 7/10. Wasn't very expensive and now that I think about it, my first actual meal (some cold pizza slices don't count) since getting to Vegas. 

Next up was ElastiCache Deep Dive: Best Practices and Usage Patterns (DAT305) inside the Venetian Theatre. I was excited about this session since I haven't done much of anything with ElastiCache in practice, but I know some projects running on AWS that leverage it heavily. 

AWS re:Invent 2017

About 10 minutes before getting mind-pwned.

AWS re:Invent 2017

Random guy playing some Hearthstone while waiting for the session to begin.

AWS re:Invent 2017

Sick seat.

Definitely felt a little bit out of my depth on this one. I'm not someone who is familiar with Redis, outside of just knowing what it does so I was clueless during some of the session. I didn't take notes, but I recall a lot of talk about re-sharding clusters, the old backup and recovery method vs. the new online managed re-sharding available, pros of enabling cluster mode (was clueless, made sense at the time, but couldn't explain it to someone else), security, and best practices. My favorite part of the session involved use cases and how ElastiCache can benefit: IoT, gaming apps, chat apps, rate limiting services, big data, geolocation or recommendation apps, and Ad Tech. Again, I was out of my depth, but I'll be taking a closer look at this service during 2018 to fix that.

After some more Frogger in the hallways, headed over to the Expo, grabbed some swag and walked around all the vendor booths. Food and drinks were provided by AWS and the place was a lot bigger than I expected. There's a similar event occurring at the Aria (the Quad), which I'll check out later in the week. 

AWS re:Invent 2017

Wall in front of the Expo by Registration.

There were AWS experts and team members involved with just about everything AWS scattered around to answer attendee questions which I though was freaking awesome. Valuable face-time with the actual people that work and know about the stuff being developed.

AWS re:Invent 2017

Favorite section of the Venetian Expo.

AWS re:Invent 2017

More madness.

Talked to the guys at the new PrivateLink booth to ask if QuickSight was getting an "endpoint" or a method for connecting private AWS databases soon. Ended up getting the de facto answer from the Analytics booth who had Quinton Alsbury at it. Once I saw him there, I'm like "Oh here we go, this guy is the guy!" Apparently, the feature's been recently put in public preview, which I somehow missed. Visited a few other AWS booths like Trusted Advisor and the Partner Network one, and then walked around all the vendor booths for a bit.

AWS re:Invent 2017

Unfortunately, I didn't have much time to chit chat with a lot of them since the Expo was closing soon. I'll have to do so more at the Quad. Walked over to the interactive search map they had towards the sides of the room to look for a certain company I thought might be there. Sure enough, found something familiar:

AWS re:Invent 2017

A wild technology learning company appears.

Spoke with Dan Anderegg, whose the Curriculum Manager for AWS within Pluralsight. After some talk about Pluralsight path development, I finished my beer and got out, only to find I actually stayed too long and was already super late to my final session for the day, Deep Dive on Amazon Elastic Block Store (Amazon EBS) (STG306). Did I mention how it's hard to try to do everything you want to at re:Invent? 

Ended up walking home and wanting to just chill out, which is how this post is getting done. 

Cheers!

1. December 2017 17:26
by Aaron Medacco
0 Comments

AWS re:Invent 2017 - Day 1 Experience

1. December 2017 17:26 by Aaron Medacco | 0 Comments

Welcome to the first in a series of blog posts detailing my experience at AWS re:Invent 2017. If you're someone who is considering going to an AWS re:Invent conference, hopefully what follows will give you a flavor for what you can expect should you choose to fork over the cash for a ticket. The following content contains my personal impressions and experience, and may not (probably doesn't?) reflect the typical experience. Also, there will be some non-AWS fluff as well as I have not been to Las Vegas before.

AWS re:Invent 2017

My adventure starts at about Midnight. Yes, midnight. Living in Scottsdale, AZ, I figured, "Why not just drive instead of fly? After all, it's only a 6 hour drive and there won't be any traffic in the middle of the night." While that was true, what a mistake in retrospect. Arriving in Las Vegas with hardly any sleep after the road trip left me in pretty ragged shape for Monday's events. Next year, I'll definitely be flying and will get there on Sunday so I get can settle prior to Monday. I actually arrived so early, I couldn't check into my room and needed to burn some time. What better activity to do when exhausted than sit down at poker tables. Lost a quick $900 in short order. Hahaha! Truth be told, I got "coolered" back to back, but I probably played bad, too.

Once I got checked into my room at the Bellagio around 9:00am, I headed back to the Aria to get registered and pick up my re:Invent hoodie. Unfortunately, they didn't have my size, only had up to a Small. I couldn't help but smile about that. I ended up going to the Venetian later to exchange my Small for a Medium. Anyways, got my badge, ready to go! Or was I?

By the way, kudos to the Bellagio for putting these in every room. Forgot my phone charger. Well, the correct phone charger at least...

 AWS re:Invent 2017

...except it didn't have a charger compatible with my Samsung Galaxy S8. Kind of funny, but I wasn't laughing. Alright, maybe a little. Would end up getting one at a Phone store among one of the malls at the Strip. Oh yeah, and I also forgot to buy a memory card for my video recorder prior to leaving. Picked up one of those from a Best Buy Express vending machine. Vegas knows.

By this time I was crashing. Came back to my room, fell asleep, and missed 2 breakout sessions I was reserved for. Great job, Aaron! Off to a great start! 

Walked to the Aria to go check out the Certification Lounge. They had tables set up, food and drink, and some goodies available depending on what certifications you'd achieved. The registration badges have indicators on them that tell people if you're AWS certified or not, which they use to allow or deny access. I didn't end up staying too long, but there were a decent number of attendees with laptops open working and networking. Here's some of the things collected this year by walking around to the events: 

AWS re:Invent 2017

The re:Invent hoodie picked up at Registration (left) and the certification t-shirt inside the Certification Lounge (right).

AWS re:Invent 2017

Water bottle and AWS pins were given away at the Venetian Expo (top-left), badge and info packet at Registration (right), and the certification stickers at the Certification Lounge depending on which ones you've completed (bottom-left).

Headed over to the MGM Grand for my first breakout session, GPS: Anti Patterns: Learning From Failure (GPSTEC302). Before I discuss the session, I have to talk about something I severely underestimated about re:Invent. Walking! My body was definitely NOT ready. And I'm not an out-of-shape or big guy, either. The walking is legit! I remember tweeting about what I imagined would be my schedule weeks before re:Invent and Eric Hammond telling me I was being pretty optimistic about what I would actually be able to attend. No joke. Okay, enough of my complaining.

AWS re:Invent 2017

Waiting for things to get started.

AWS re:Invent 2017

Session about half-full. Plenty of room to get comfortable.

AWS re:Invent 2017

Presenter's shirt says, "got root?". Explaining methods for ensuring account resource compliance and using AWS account best practices when it comes to logging, backups, and fast reaction to nefarious changes.

This was an excellent session. The presenters were fantastic and poked fun at mistakes they themselves have made or those of customers they've talked to have made regarding automation (or lack thereof), compliance, and just overall bone-headedness (is that a word?). The big takeaways I found were to consider using services like CloudWatch, CloudTrail and Config to monitor and log activity in your AWS accounts to become aware when stupid raises it's ugly head. They threw out questions like, "What would happen if the root account's credentials were compromised and you didn't know about it until it was too late?", and "You have an automated process for creating backups, but do you actually test those backups?". From this came suggestions to regularly store and test backups to another account in case an account gets compromised and using things like MFA, especially for root and privileged users.

Additionally, the presenters made a good argument for not using the management console for activities once you become more familiar with AWS, particularly if you're leveraging the automation tools AWS provides like OpsWorks and CloudFormation as that kind of manual mucking around via the console can leave you in funny states for stacks deployed with those services. Along those lines, they also suggested dividing up the different tiers of your application infrastructure into their own stacks so that when you need to make changes to something or scale, you don't end up changing the whole system. Instead, you only modify or scale the relevant stack. Overall good session. If they have it again next year, I would recommend it. You'll get some laughs, if nothing else. The guys were pretty funny.

Once out, I had a meeting scheduled to talk with a company (presumably about upcoming Pluralsight work) at the Global Partner Summit Welcome Reception. Now, I'll admit I got a little frustrated trying to find where the **** this was taking place! AWS did a great job sending lots of guides with re:Invent flags everywhere to answer questions and direct attendees to their events, and these guys were godsends every time except when it came to finding this event. I think I just got unlucky with a few that were misinformed.

AWS re:Invent 2017

These guys were scattered all over the strip and inside the hotels. Very helpful!

First, I was told to go to the one of the ballrooms. Found what appeared to be some kind of Presenter's Registration there. Then, found another guide who said to go to the Garden Grand Arena. Walked over there, total graveyard, and ironically, a random dude there who wasn't even one of the re:Invent guides told me where it actually was. He also said, "Oh yeah, and unless you want to be standing in line all night, you might want to reconsider." It was late enough at this point, I figured I'd just head back to the Bellagio for a much needed poker session, so that's what I did. However, on the way back, holy ****, he was right. I've never seen a line as long as the one to get into the GPS Welcome Reception in my life. It went from the food court, through the entire casino, out of the casino, and further back to I couldn't tell where. Apparently, I was the only one who missed the memo, since everyone else knew where to go, but still, that line. 

Long hike back to the Bellagio, played poker for about 3 hours, lost $200 (man, I suck), and on my way back to my room discovered I didn't eat anything all day. LOL! Picked up a couple pizza slices and crashed for the night. A good night's sleep? Yes, please. Tomorrow would be better.

Cheers!

21. March 2017 00:28
by Aaron Medacco
0 Comments

Moving Load From Your Master Database: Elasticache or RDS Read Replica?

21. March 2017 00:28 by Aaron Medacco | 0 Comments

You have a database managed thru the AWS RDS service. Your application's a massive success and your database's workload is now heavy enough that users are experiencing long response times. You decide that instead of scaling vertically by upgrading the instance type of your RDS database, you'd like to explore implementing Elasticache or an RDS read replica into your architecture to remove some of the load from your master database.

But which one should you choose?

Like always, it depends.

RDS read replicas and Elasticache nodes both enhance the performance of your application by handling requests for data instead of the master database. However, which one you choose will depend on your application's requirements. Before I dive too deep into how these requirements will shape your decision, let's talk about what we are comparing first.

Elasticache Or Read Replica

Note: For those already familiar with Elasticache and RDS features, feel free to skip down.

Elasticache Clusters

Elasticache is a managed service provided by AWS that allows you to provision in-memory data stores (caches) that can allow your applications to fetch information with blazing speed. When you use Elasticache, you create a cluster of nodes, which are blocks of network attached RAM. This cluster can then sit in between your application tier and your data tier. When requests that require data come in, they are sent to the cluster first. If the information exists in the cache and the cluster can service the request, it is returned to the requester without requiring any database engine steps or disk reads. If the information does not exist in the cache, it must be fetched from the database like usual. Obviously, their is no contest in performance between fetching data from memory vs. disk which is why the Elasticache service can really give your applications some wheels while also reducing the load on your database.

RDS Read Replicas

RDS read replicas offer an alternative to vertical database scaling by allowing you to use additional database instances to serve read-only requests. Read replicas are essentially copies of your master database where write operations are prohibited except thru asynchronous calls made from the master after the master has completed a write. This means that read replicas may return slightly stale data when serving requests, but will eventually catch up as write propagations invoked from the master complete. Read replicas have additional benefits as well. Since they can be promoted to serve as the master database should the master fail, you can increase your data's availability. The database engine you choose also determines available features. For instance, databases utilizing the MySQL database engine can take advantage of custom read replica indexes which only apply to the replicas. Want to take advantage of covering indexes for expensive queries without forcing your master database to maintain additional writes? With this feature, you'd be able to.

Great. So which is better?

Clearly, both of these services can reduce your master database's workload, while also boosting performance. In order to choose which service makes most sense for your situation, you'll need to answer these kinds of questions:

Can my application tolerate stale data? How stale? 5 minutes? 5 hours?

If you want to store your data in Elasticache nodes for long periods of time, and you need almost exactly current data, read replicas are likely the better option. Read replicas will lag behind the master database slightly, but only by seconds. On the other hand, if you can tolerate data that is more stale, Elasticache will outperform read replicas performance-wise (if data exists in the cache) while also preventing requests from hitting the master database. 

Are the queries generated by my application static? That is, are the same queries run over and over all day? Are they dynamically constructed, using items like GETDATE()?

If the queries being run by your application are ever-changing, your in-memory cache won't be very useful since it will have to continue to update its data store to serve requests it otherwise cannot satisfy. Remember, the cache can only act on queries it recognizes. Not only does this not prevent calls to the database, but it can actually degrade performance because you are effectively maintaining a useless middle man between your application and data tiers. However, Elasticache clusters will shine over read replicas in cases where queries do not change and request the same data sets over and over.

How much data is my application asking for?

The volume of data your Elasticache clusters can store will be limited by the amount of memory you allocate to the nodes in your cluster. If your queries return huge result sets, your cache is going to be occupied very quickly. This can create a scenario where queries constantly compete for the available memory by overwriting what's existing. This won't be very helpful, especially if you aren't willing to dedicate (and pay for) additional memory. Alternatively, read replicas will be able to serve the request regardless of the returned data size, won't incur the penalty of a wasted middle man trip, and will still prevent the request from hitting the master.

What would I do?

My approach would be to consider how effectively you'll be able to use an in-memory cache. If, given all you know about your application, you decide the cache will be able to catch a large portion of the read requests coming in, go with the Elasticache cluster. If you're right, you should notice a difference right away. If you're wrong you can always fall back to implementing read replicas. I've avoided pricing in this post, which is obviously important when making architectural decisions. Elasticache pricing and RDS pricing is available on Amazon Web Services' site. Readers will need to do their own analysis of the kinds of instances and node types they'd provision and how their costs compare to make a decision.

If anyone knows of some additional considerations I should include, leave a comment or reach out to me via e-mail: acmedacco@gmail.com. Shout out to the last guy who caught my miscalculation of pricing in the Infinite Lambda Loop post

Cheers!

Copyright © 2016-2017 Aaron Medacco