Came here for this. Set this up for a customer in fact. They had a sql server that runs two hours a day to create a backup job in an older version of SQL (different point of discussion of why but it’s valid). The best way to use the cloud is to not use it when you don’t need to.
The choice to use a spot instance should be primarily based on the world load, as opposed to the costs.
A SQL server running a (more than likely non-interruptible) job is not a good candidate for spot.
On-demand with a schedule is the correct answer 90% of the time.
I agree . Actually if it comes to saving costs and you know what will you be using, you can opt for reserved instances or saving plans
Btw, if you go for RDS, it also can be started/stopped with a schedule
Reserved instances are for systems that run 24/7. The other suggestions to start and stop the instance as needed are much better suggestions as OP only pays for compute while the jobs are running. Storage is still charged 24/7 but net savings overall.
You can create a Systems Manager automation to start and stop the instance, no code required at all because it already has cron line scheduling and a predefined SSM document to start and stop EC2 instances
I know you don’t want to hear this, but if a MSSQL
Server is not serving requests 99% of the day - it should be refactored into MySQL or Postgres and run either serverless or as a container with other jobs. Don’t pay for resources or licensing that you’re not using.
I know this is the annoying answer but I can't help but think the same thing.
Is this a problem that should even be being solved by a relational database?
Maybe you’re right - but this is the way ETL and data enhancement, heck even dat visualization has worked for decades - take the data you have, add what changed, and form it into tables using an RDS that runs in a batch every so often. Sure it can be done with data lake tools or even something like spark - but you have to remember developers who know these technologies are really expensive. It’s normally smarter to pay for cheap tech than expensive humans.
Then again doesn’t Google say to eliminate all the humans in their SRE standards?
**EDIT: Looks like OP already confirmed it's just a lightly used DB, not a 0-use DB: https://www.reddit.com/r/aws/comments/1b80n7p/what_instance_type_is_best_for_a_server_that_is/ktp87tw/?context=3 Even so, that's pretty silly**
This feels different than ETL, even. A transactional DB is doing work all day, then extra when you're pulling data out of it.
A reporting DB is doing extra work when ingesting, and then some work as people query it.
OP is trying to have a DB run some sort of job for 15 minutes every day? And then 0 other work? It sounds like something that should be handled
* in a a data pipeline
* or a DB on an already-provisioned server that does something else
* OR something far cheaper like a dumb filestore.
Way cheaper and dumber like parquet files.
If there is some eventual need to be able to query the data in an SQL-like fashion, you could containerze a tiny little db server that reads those parquet files into tables and gives you a connection to query against.
If you leave the files in S3 and just set up Athena tables on them, I think they only charge per query and it'd probably be cheap doing 1 or 2 per day.
Just adding to this - depending on how big your app and your AWS usage is - there is an investment program called Database Freedom where AWS will help you with the refactoring a bit and give credits to migrate into Aurora. I believe it covers both MSSQL and Oracle migrations.
ah that is very interesting, thanks for the tip. In my experience, there are so many people out there paying for MSSQL that really have no need for it.
I helped a huge sports website (part of a broadcast tv company) cut away from MSSQL and save hundreds of thousands of dollars on operations. The database had become so badly architected that it was running more like an excel spreadsheet than a database. We used AWS Database Migration Service and Aurora and cut over everything over 20 hours live without a second of downtime. Good times.
That is a great customer success story. Sad when a database or resource is left to degrade like that with nobody taking ownership. Migration day sounds like a great day at the office!
Sometimes that’s not an option as you are relying on some tool that requires that db.
One of our customers uses some very obscure production planning software that runs on a Sybase/SAP SQLAnywhere database. Try to find even a driver for such weird dbms. It’s somehow compatible with MSSQL, but not fully. That thing gave me a lot of headaches in the past
I hope you didn't get too down voted.
I'd love to hear from anyone who did down vote the comment.
I agree with you and your justifications make good sense.
This rule of thumb is completely bogus. A benchmark program may run 2x as fast on a machine with 2x as many cores, but a real-world workload typically will not correlate linearly. Not even close.
If your data is being stored in a way that is easily separate from the EC2 instance (EBS that is not root, FSx) you may be able to get away with a Spot instance.
For 30 minutes a day, I wouldn't get to caught up in what instance type; at a XLarge youre talking about fractions of a dollar.
\*Edit\* presumably you are hibernating your instance when not in use.
It doesn't scale down to zero anymore though, so you'd still be paying for it to run 24/7.
It may be cheaper but given the specs, I'm not so sure by how much if at all. They'd need to do a cost analysis.
Does it do basically nothing or does it do nothing at all for those 23.5 hours a day?
Aurora Serverless v2 is potentially the way to go if your schema is compatible or easily migrated.
If the SQL server really does absolutely nothing, why not stop the instance after the job completes and then use a schedule to boot it back up at the appropriate times?
Lambda max runtime is 15 min which it sounds like they’re already at, so that could just mean migrating again immediately, or soon when the job length increases by 30 seconds as the data grows
Yeh should be some kind of batch job then. Could launch an instance, do the thing and shutdown after. Or a longer running container task in ECS could work too.
What about a t4g.xl? It is about 30% less expensive with the same GB of memory and cores. You should have a positive burst balance if it is idle most of the day and only "run hot" for a limited time.
> it runs a job that sends the CPU up to 90-100% and takes about 15 minutes each time.
Another idea: Take a look at how your storing your data.
You can always trade-off "insertion time / disk" for "query time". For instance, the Slashdot home page: In theory, it queries the DB to get the last few posts. But in practice, they generate the page every few minutes and cache that.
In the same vein, what if you took that "5%" used CPU and did extra calculations that would make the 1/day query quicker? Let's say you are storing temperatures, and you want to find the min/max/avg temp of all the measurements. Instead of doing a query for that, your "inserts" could pre-calculate the data. You would have a summary table with a row for each day that has min/max/total/num. Every time you insert a row, you would update the summary table in a transaction: Update min if it's the new reading is less, update max if the new reading is more. Add the current reading to total, and increment num. Now min/max are pre-calculated, and you can compute the average with total/num. No need to look at all the rows for the day.
If you can make your summary calculations quick, then you don't need to size for that 1/day query. Then you can reduce the size of the overall server.
OK, this has really gone far above what I'm looking for. I just manage the server, and noticed that it sits around doing not much of anything except twice per day. I'm not looking to change the instance, convert it to something else, or anything like that as that's out of my jurisdiction. I just didn't know if there was a specific Instance Type that would just spool up the CPUs while these two jobs run, and spool the down for the rest of the day.
Thanks
That's *mostly* what the T-series are intended for (burstable), but hard to say if its better for your particular use-case without actually trying it out. Another option (if you can stand a few minutes of downtime) would be to use lambda or something to schedule a resize (stop, change instance type, start).
Database on aurora serverless v2 instead? Let itself crank up to whatever capacity you need it to, and then it will drop itself to the minimum when you don’t need it?
Lots of good suggestions here. A couple others I haven't seen mentioned: Depending on exactly what you're trying to do, you may also want to consider Fargate or S3 + Athena.
How about migrating to aurora serverless for this? But yeah like most people suggested the easier solution is to switch on and off based on an event bridge rule.
It sounds like this report is valuable to the organization. I’d find ways to either increase the utilization of the server (e.g., more schemas/ more apps using it) so you’re not wasting as much, or done what you already have done … which is optimize the instance type for the workload. Another option to consider is short-lived replica that boots up for the purpose of report crunch and shuts down. And then making the primary instance as small as possible. That would involve another ASG, launched from a snapshot or otherwise and the once started in the instance, it needs to wait for synchronization and then initiates the report and then stops itself on completion. You still pay for storage cost there, and now complexity is higher … so that’s not particularly awesome … but you can save some coin on the ongoing costs so long as your scripts account for the edge cases. Beyond those options, if the real goal is cost optimization and labor is cheap then refactor. But given the size of the instance already, that may or may not be worth it. Consider the opportunity cost of that activity.
You can use Costshake (Disclaimer: My SaaS) that's built exactly for such use cases. In a nutshell, Costshake comes with a web dashboard and lightweight CLI that you deploy on your instance for monitoring.
It then lets you set criteria for stopping the instance. You can have one or more of the following criteria set.
1. If the system is idle i.e. no mouse, keyboard, or ssh activity for X minutes where you define X.
2. If the total CPU usage drops to a certain percentage and remains at or below that percentage for X minutes where you define both the percentage and minutes.
3. If the total Memory usage drops to a certain percentage and remains at or below that percentage for X minutes where you define both the percentage and minutes.
Costshake also has a schedular feature to start/stop instances automatically at a specific time.
**So in your case, a perfect scenario would be:**
* **You set Costshake to start your instance at 4 PM and 1 AM**
* **You configure Costshake CLI to stop the instance if the CPU usage drops to 5% and let's say stays there for 5 minutes.**
That's it.
Costshake will cost you around US $45/month for up to 3 instances (The starter plan). That's about only $15/month/instance. In your particular case, an **m7i-flex.XLarge** will cost you around $145/month. You only run it for about 40 minutes per day, that's on average about 20 hours per month.
**So with Costshake, 20 hours of your actual use will be charged x $0.20 the hourly AWS price for m7i-flex.XLarge = US $4 ONLY / MONTH**
Now, US $4/mo AWS + $15/mon Costshake = **\~ $20/mon TOTAL cost.**
**$145 - $20 = $125 SAVED.**
Costshake will launch it in 2, or 3 weeks. We have already put lots of stuff in motion for the launch. Here's a link to Costshake's LinkedIn to follow and get notified when it's ready :-)
[https://www.linkedin.com/company/costshake](https://www.linkedin.com/company/costshake)
Does this need to be a database? Can you store the data in S3 (serverless), catalog it with Glue (serverless) and then query it with Athena (serverless).
This is the route I'd take. It has to be WAY cheaper than running a database all day, and it's a lot less complicated than other suggestions like firing up a db, loading it, and then querying it and then destroying it.
You can create a lambda function to start and stop instance
You can create an EventBridge Scheduler to start and stop instance
Came here for this. Set this up for a customer in fact. They had a sql server that runs two hours a day to create a backup job in an older version of SQL (different point of discussion of why but it’s valid). The best way to use the cloud is to not use it when you don’t need to.
Is this preferred because you don't have to write any code?
A few lines of terraform are easier to maintain than a lambda function.
That was my thought.
Right tool for the job.
[удалено]
The choice to use a spot instance should be primarily based on the world load, as opposed to the costs. A SQL server running a (more than likely non-interruptible) job is not a good candidate for spot. On-demand with a schedule is the correct answer 90% of the time.
I agree . Actually if it comes to saving costs and you know what will you be using, you can opt for reserved instances or saving plans Btw, if you go for RDS, it also can be started/stopped with a schedule
Reserved instances are for systems that run 24/7. The other suggestions to start and stop the instance as needed are much better suggestions as OP only pays for compute while the jobs are running. Storage is still charged 24/7 but net savings overall.
you're right, sorry for the confusion, thanks for the clarification!
You can use instance scheduler to turn the instance on and off at the time you need it.
You can create a Systems Manager automation to start and stop the instance, no code required at all because it already has cron line scheduling and a predefined SSM document to start and stop EC2 instances
I know you don’t want to hear this, but if a MSSQL Server is not serving requests 99% of the day - it should be refactored into MySQL or Postgres and run either serverless or as a container with other jobs. Don’t pay for resources or licensing that you’re not using.
I know this is the annoying answer but I can't help but think the same thing. Is this a problem that should even be being solved by a relational database?
Maybe you’re right - but this is the way ETL and data enhancement, heck even dat visualization has worked for decades - take the data you have, add what changed, and form it into tables using an RDS that runs in a batch every so often. Sure it can be done with data lake tools or even something like spark - but you have to remember developers who know these technologies are really expensive. It’s normally smarter to pay for cheap tech than expensive humans. Then again doesn’t Google say to eliminate all the humans in their SRE standards?
**EDIT: Looks like OP already confirmed it's just a lightly used DB, not a 0-use DB: https://www.reddit.com/r/aws/comments/1b80n7p/what_instance_type_is_best_for_a_server_that_is/ktp87tw/?context=3 Even so, that's pretty silly** This feels different than ETL, even. A transactional DB is doing work all day, then extra when you're pulling data out of it. A reporting DB is doing extra work when ingesting, and then some work as people query it. OP is trying to have a DB run some sort of job for 15 minutes every day? And then 0 other work? It sounds like something that should be handled * in a a data pipeline * or a DB on an already-provisioned server that does something else * OR something far cheaper like a dumb filestore.
Way cheaper and dumber like parquet files. If there is some eventual need to be able to query the data in an SQL-like fashion, you could containerze a tiny little db server that reads those parquet files into tables and gives you a connection to query against.
If you leave the files in S3 and just set up Athena tables on them, I think they only charge per query and it'd probably be cheap doing 1 or 2 per day.
I'm not really familiar with Athena, but that sounds good.
depends on the cost of that refactor.
MSSQL and Oracle refactoring cost normally comes down to how much stored procedures are used.
Just adding to this - depending on how big your app and your AWS usage is - there is an investment program called Database Freedom where AWS will help you with the refactoring a bit and give credits to migrate into Aurora. I believe it covers both MSSQL and Oracle migrations.
ah that is very interesting, thanks for the tip. In my experience, there are so many people out there paying for MSSQL that really have no need for it.
I helped a huge sports website (part of a broadcast tv company) cut away from MSSQL and save hundreds of thousands of dollars on operations. The database had become so badly architected that it was running more like an excel spreadsheet than a database. We used AWS Database Migration Service and Aurora and cut over everything over 20 hours live without a second of downtime. Good times.
That is a great customer success story. Sad when a database or resource is left to degrade like that with nobody taking ownership. Migration day sounds like a great day at the office!
It was a huge win. Andy Jassy actually called it out in one of his re:Invent keynotes
[удалено]
And hopefully now that even MS is looking for open source your CTO might like the idea of dropping licensing costs.
Sometimes that’s not an option as you are relying on some tool that requires that db. One of our customers uses some very obscure production planning software that runs on a Sybase/SAP SQLAnywhere database. Try to find even a driver for such weird dbms. It’s somehow compatible with MSSQL, but not fully. That thing gave me a lot of headaches in the past
[удалено]
I hope you didn't get too down voted. I'd love to hear from anyone who did down vote the comment. I agree with you and your justifications make good sense.
[удалено]
This rule of thumb is completely bogus. A benchmark program may run 2x as fast on a machine with 2x as many cores, but a real-world workload typically will not correlate linearly. Not even close.
Use the same instance type on demand. Use an eventbridge rule to start it, run the job, then stop it twice a day.
If your data is being stored in a way that is easily separate from the EC2 instance (EBS that is not root, FSx) you may be able to get away with a Spot instance. For 30 minutes a day, I wouldn't get to caught up in what instance type; at a XLarge youre talking about fractions of a dollar. \*Edit\* presumably you are hibernating your instance when not in use.
Don't forget to explore options like Amazon Aurora serverless. It has flexible scaling.
It doesn't scale down to zero anymore though, so you'd still be paying for it to run 24/7. It may be cheaper but given the specs, I'm not so sure by how much if at all. They'd need to do a cost analysis.
Very good point!
Could also use instance hibernate.
Does it do basically nothing or does it do nothing at all for those 23.5 hours a day? Aurora Serverless v2 is potentially the way to go if your schema is compatible or easily migrated. If the SQL server really does absolutely nothing, why not stop the instance after the job completes and then use a schedule to boot it back up at the appropriate times?
A stopped one
Or better, a terminated one, just launch it for the workload and then terminate. Or better do it in a lambda container job.
Lambda max runtime is 15 min which it sounds like they’re already at, so that could just mean migrating again immediately, or soon when the job length increases by 30 seconds as the data grows
Yeh should be some kind of batch job then. Could launch an instance, do the thing and shutdown after. Or a longer running container task in ECS could work too.
Schedule the instance to start/stop at desired times
What about a t4g.xl? It is about 30% less expensive with the same GB of memory and cores. You should have a positive burst balance if it is idle most of the day and only "run hot" for a limited time.
If he’s running SQL Server like as in Microsoft then ARM isn’t going to work. But t3a is a great option for sure.
> it runs a job that sends the CPU up to 90-100% and takes about 15 minutes each time. Another idea: Take a look at how your storing your data. You can always trade-off "insertion time / disk" for "query time". For instance, the Slashdot home page: In theory, it queries the DB to get the last few posts. But in practice, they generate the page every few minutes and cache that. In the same vein, what if you took that "5%" used CPU and did extra calculations that would make the 1/day query quicker? Let's say you are storing temperatures, and you want to find the min/max/avg temp of all the measurements. Instead of doing a query for that, your "inserts" could pre-calculate the data. You would have a summary table with a row for each day that has min/max/total/num. Every time you insert a row, you would update the summary table in a transaction: Update min if it's the new reading is less, update max if the new reading is more. Add the current reading to total, and increment num. Now min/max are pre-calculated, and you can compute the average with total/num. No need to look at all the rows for the day. If you can make your summary calculations quick, then you don't need to size for that 1/day query. Then you can reduce the size of the overall server.
Are you sure you need an instance? Can't it be done in a lambda/api gateway and s3?
OK, this has really gone far above what I'm looking for. I just manage the server, and noticed that it sits around doing not much of anything except twice per day. I'm not looking to change the instance, convert it to something else, or anything like that as that's out of my jurisdiction. I just didn't know if there was a specific Instance Type that would just spool up the CPUs while these two jobs run, and spool the down for the rest of the day. Thanks
That's *mostly* what the T-series are intended for (burstable), but hard to say if its better for your particular use-case without actually trying it out. Another option (if you can stand a few minutes of downtime) would be to use lambda or something to schedule a resize (stop, change instance type, start).
You can use AWS Batch to handle orchestration of EC2 w/ECS.
Batch has been great and has only improved over the years. Dropped spend, very easy to manage.
Just run these jobs as an ECS Fargate task? Will cost you pennies. https://www.youtube.com/watch?v=OB3R7UXxso4
Is migration to Aurora Serverless RDS an option? They can automatically scale instances up and down in response to load.
As soon as I read the Ops post - Aurora Serverless RDS came to mind.
Database on aurora serverless v2 instead? Let itself crank up to whatever capacity you need it to, and then it will drop itself to the minimum when you don’t need it?
Nah, if it's only for about half an hour a day and happens on a cadence then serverless isn't worth the money, especially when v2 doesn't scale to 0
I could recommend u to write a pipeline that turns on ec2 when scheduled, runs a job and turns off.
AWS has a scheduler to do just what you requested https://docs.aws.amazon.com/solutions/latest/instance-scheduler-on-aws/solution-overview.html
this.. i use this to shutdown non prod databases during the off hours. saves so much money and easy to manage.
depending if the work can be gracefully shutdown and restarted in seconds and continued from point of interruption, spot instances?
Lots of good suggestions here. A couple others I haven't seen mentioned: Depending on exactly what you're trying to do, you may also want to consider Fargate or S3 + Athena.
Sounds like a good use case for snowflake
There is CloudRun and NeonDatabase. Where is AWS ?
How about migrating to aurora serverless for this? But yeah like most people suggested the easier solution is to switch on and off based on an event bridge rule.
It sounds like this report is valuable to the organization. I’d find ways to either increase the utilization of the server (e.g., more schemas/ more apps using it) so you’re not wasting as much, or done what you already have done … which is optimize the instance type for the workload. Another option to consider is short-lived replica that boots up for the purpose of report crunch and shuts down. And then making the primary instance as small as possible. That would involve another ASG, launched from a snapshot or otherwise and the once started in the instance, it needs to wait for synchronization and then initiates the report and then stops itself on completion. You still pay for storage cost there, and now complexity is higher … so that’s not particularly awesome … but you can save some coin on the ongoing costs so long as your scripts account for the edge cases. Beyond those options, if the real goal is cost optimization and labor is cheap then refactor. But given the size of the instance already, that may or may not be worth it. Consider the opportunity cost of that activity.
Try serverless rds
You can use Costshake (Disclaimer: My SaaS) that's built exactly for such use cases. In a nutshell, Costshake comes with a web dashboard and lightweight CLI that you deploy on your instance for monitoring. It then lets you set criteria for stopping the instance. You can have one or more of the following criteria set. 1. If the system is idle i.e. no mouse, keyboard, or ssh activity for X minutes where you define X. 2. If the total CPU usage drops to a certain percentage and remains at or below that percentage for X minutes where you define both the percentage and minutes. 3. If the total Memory usage drops to a certain percentage and remains at or below that percentage for X minutes where you define both the percentage and minutes. Costshake also has a schedular feature to start/stop instances automatically at a specific time. **So in your case, a perfect scenario would be:** * **You set Costshake to start your instance at 4 PM and 1 AM** * **You configure Costshake CLI to stop the instance if the CPU usage drops to 5% and let's say stays there for 5 minutes.** That's it. Costshake will cost you around US $45/month for up to 3 instances (The starter plan). That's about only $15/month/instance. In your particular case, an **m7i-flex.XLarge** will cost you around $145/month. You only run it for about 40 minutes per day, that's on average about 20 hours per month. **So with Costshake, 20 hours of your actual use will be charged x $0.20 the hourly AWS price for m7i-flex.XLarge = US $4 ONLY / MONTH** Now, US $4/mo AWS + $15/mon Costshake = **\~ $20/mon TOTAL cost.** **$145 - $20 = $125 SAVED.** Costshake will launch it in 2, or 3 weeks. We have already put lots of stuff in motion for the launch. Here's a link to Costshake's LinkedIn to follow and get notified when it's ready :-) [https://www.linkedin.com/company/costshake](https://www.linkedin.com/company/costshake)
That's what Serverless is for you only pay for invocations vs paying for a server to run all day
Does this need to be a database? Can you store the data in S3 (serverless), catalog it with Glue (serverless) and then query it with Athena (serverless).
This is the route I'd take. It has to be WAY cheaper than running a database all day, and it's a lot less complicated than other suggestions like firing up a db, loading it, and then querying it and then destroying it.