Hi Dave,
For Standard tier, currently there is no built-in mechanism to scale up/down automatically. You could set up an alert that triggers a script to scale up. Below is article that describes this method:
How-to auto-scale Azure SQL Databases
For Serverless tier, you can set min/max number of vCores and it will scale based on demand. Please note that your workload needs to be appropriate for serverless tier. Serverless is mainly for intermittent use cases whereby you are okay with the database auto-pausing after inactivity and then there being a delay (about a minute) next time you connect to it.
The other thing is cost--if your Serverless database will spend too much time being Online vs Paused then it will wind up costing more than if you had provisioned tier that is Online 24x7. Article below describes Serverless tier in more detail as well as mentions the autoscaling:
Serverless compute tier for Azure SQL Database
https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
In my experience only a relatively small number of applications are suitable for serverless, but you should run the numbers for yourself. If you need help calculating potential cost of serverless let me know and I can help.-TP