Aurora-Server | ESP32 controlled , addressable RGB LED strip server

We have a bunch of on-premises applications each running their own local MySQL servers. Our workload is light, with occasional bursts of activity (a B2B business model with some specific times of the month in which it is more profitable to use our application, and therefore we see usage spikes during those days). We decided that it would be a good idea to simplify the infrastructure by moving all the databases into one server/cluster, and after some discussion decided that buying a managed solution would be better than trying to set up and maintain our own MySQL cluster (none of us are DBAs).

We did a thorough amount of research, and eventually settled on Amazon Aurora Serverless as a solid candidate for its auto-scaling capabilities, and therefore (potentially) lower cost compared to the alternatives we examined (AWS MySQL RDS and DigitalOcean managed MySQL), due to our usually-light workload with occasional bursts of activity.

However, from what I can gather it is impossible to just simply connect to AWS Aurora Serverless (see Not able connect Amazon Aurora Serverless from SQL client for example) from our on-premises applications, so my question is:

1. What is the best-practice, modern way to solve this problem - should we use a site-to-site VPN to connect our on-premises hosts to the cloud? Would this end up costing us significantly more?
2. Is Aurora Serverless really the best solution at all, or should we fall back to Amazon RDS, or DigitalOcean's managed MySQL cluster, both of which allow assigning public IPs but neither of which will auto-scale (meaning we'd need to buy a tier based on our peak usage, and potentially waste a lot of money as it will sit almost idle for a large part of the month)?

What we want to achieve is a simple, fire-and-forget MySQL cluster set up that's managed by someone else, ideally auto-scales, and doesn't cost the earth or end up being more difficult to manage than the current, on-premises solution.

We are not cloud-averse, but neither do we want to suddenly start moving everything into the cloud all at once just for the sake of a simpler database infrastructure.

To throw an extra spanner into the works, we don't manage our own firewalls - so setting up a site-to-site VPN could be tricky and involve coordinating with a third party (our network provider). Ideally I'd like to avoid this hassle too, if at all possible.

I have an existing project with Rails 5.2 and Mongoid 7, I had previously disabled all activerecord-related modules.

We are working on some synchronization with business intelligence data warehouses, and I discovered this gem that seems like a good starting point : I'm planning to use ActiveRecord with this adapter to easily implement code that will push data to my Amazon Aurora DB for BI purposes.

I have created a simple model

From Aurora Serverless's document, there are 3 ways to create an Aurora serverless DB cluster: AWS management console, CLI, and RDS API. (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/aurora-serverless.create.html)

Form my understanding, one would use EngineMode in the RDS API to create Aurora Serverless, but this property is not available in AWS::RDS::DBCluster yet (https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbcluster.html).

Would it be possible to create an Aurora Serverless Cluster from cloudformation? Any advice would be appreciated!

So I've been trying to setup a BI tool with my Serverless backend i.e. Aurora MySQL.

Architecture as below:

Implementation on these lines: tutorial-link

Now, I'm exploring options for connecting this backend with a BI/Analytics tool for quick querying and visualisation purposes.

Since, our backend is a Aurora MySQL Serverless, the limitation would be to not be able to connect directly to the endpoint like how one could do with a provisioned one.

As stated here: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.html

On similar lines, I'm trying to setup a AWS Direct Connect, which is currently in requested stage:

I need assistance whether this is right approach, if so, are there any tutorials for using Direct Connect with Aurora Serverless?

On the other hand, are there any bi tools anyone has successfully used with Serverless backends.

Need is to be able to get up and running in the shortest amount of time, to get quick inferences. And then move to ETLs or more complex use cases.

Any assistance much appreciated!

Thanks, Arnab

AWS documentation states that Aurora Serverless DB cannot have a public IP Address:

You can't give an Aurora Serverless DB cluster a public IP address. You can access an Aurora Serverless DB cluster only from within a virtual private cloud (VPC) based on the Amazon VPC service.

However, this confuses me as I was just able to set "Public accessibility" to "Yes". I was also able to connect to the database outside the VPC using the endpoint specified.

Am I misunderstanding the limitations (Public IP)? Or is this something that was made possible just recently?

Thank you!

I set up an Aurora Database (provisioned) in a newly created VPC and no public accessibility. As I want to run a Lambda function in the VPC which is able to both, access the RDS instances as well as the Internet, I changed the routing tables of the RDS instances to allowing traffic from a NAT gateway which I placed in a public subnet in the same VPC.

For the Lambda function itself, I created a separate private subnet, also just allowing traffic from the NAT gateway in the routing table. I assigned this subnet and VPC to the Lambda function in the Lambda settings. The internet connection works fine with this configuration but I can not access the database. That's why I followed this post (https://serverfault.com/questions/941886/connect-an-aws-lambda-function-triggered-by-api-gateway-to-aurora-serverless-mys) and added the IP CIDR of the Lambda subnet to the Security Group of the RDS instances (called rds-launch-wizard).

Still, the Lambda function is able to interact with the public internet but can not connect to the RDS instances (timeout). I'm running out of ideas, what is wrong here?