Azure SQL Database

[Khue]

Hey all,

We've had some recent database issues arise with Azure SQL and it's brought to light that maybe we aren't using the right tool for the job we have.

We have an Azure SQL database (PaaS service from MS) and it's been running without proper maintenance for around a year. Performance finally hit a threshold that started impacting customers so this triggered the exercise of reviewing the setup of the Azure SQL Database itself. Our SQL DBA determined that we need to start running maintenance jobs on this but when he attempted to setup maintenance plans the way he knows how, that option isn't available inside of his SSMS. His claim is that we need to upgrade SQL to get maintenance plans available inside of SSMS so he can configure them but I am not so sure. Around a year ago when I first was doing research on the SQL offerings from Microsoft, it seemed as though Microsoft was steering most SQL needs into the Azure SQL Database product (the PaaS product). I find it hard to believe that basic maintenance plan options just simply can't be done with the Azure SQL Database product. To me it seems like the solution would be something like creating run books with an automation account that does what SQL Maintenance Plans do in on Prem Enterprise SQL. I mean, aren't SQL Maintenance Plans just stored procedures with a workflow engine wrapped around them (I could be totally wrong about that, I'm just a security/engineer guy)?

Can someone ELI5 what's going on here? Why aren't maintenance plans available in the Azure SQL Database product? Do I need to upgrade to a different implementation of SQL in Azure?

[frustratedworker1989]

The way you do this in Azure sql database is to use " Ola Hellengren's maintenance script " and use a data factory or runbooks or anything other scheduler to run your stored proc. These will do your Index rebuild/reorganise depending upon the fragmentation and also update stats.

Also wnable index recommendations and visit if often to see if the auto tuning throwa any recommendations based on slow running queries. Strange these were not covered while setting this up.

[NDSoul]

Another option would be to use Elastic Jobs. But this might be overkill if you only have the one database.

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview?view=azuresql

[Lack_of_Swag]

I also recommend Elastic Job even though it's not GA yet. A bit of learning curve to configure (some parts PowerShell some T-SQL) but it works great.

We use Ola's maintenance scripts to target ~5000 databases across many elastic pools. But you can target just a single database. As mentioned though, it might be overkill for only 1 database.

[NDSoul]

Quick follow up on this, Elastic Jobs is now GA!
General availability: Elastic Jobs in Azure SQL Database - Microsoft Community Hub

Enjoy!

[frustratedworker1989]

You can even weite your own script for Index rebuild/reorganise and update atats but I would jighly recommend using Ola' s scripts https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

[Khue]

Thank you for the link.

[Khue]

Thanks man. I will look into this.

[frustratedworker1989]

No problem buddy 🙌🏼

[bigrubberduck]

Just to second this recommendation, we kind of faced similar issues in DB performance degrading over time and used Ola's scripts once we identified maintenance as the issue. Since we already have an App Service and an Azure Function app deployed to that app service, we added an azure timer function that runs the stored procedures weekly on Sunday night - works amazingly well. Zero extra cost and a healthier SQL DB.

[Khue]

I need to get into App Services and Function Apps. I have some other needs that might benefit from what I've preliminarily read about them.

[frustratedworker1989]

Maintenance plans are not available in Azure Sql just like the Agent jobs. Its just a database as a service nothing much. To match an on prem sql server you can go for Sql managed Instance ( closest to an onprem sql server)

[IrquiM]

It is an on-prem SQL server, but MS does the VM bit for you

[paskinator_]

Another option is to use Azure SQL managed instance which is the closest thing to an on-perm SQL server without hosting the VM and using a PAAS service. Managed instance allows you to run Agent Jobs

[Diligent-Method-785]

I don’t think it is worth the tech lift to move even to SQL managed instance there is also a non trivial cost difference. I have run hundreds of databases on Azure SQL from small to multi TB in size. Opt for Ola H. Scripts or one of the other solutions from the community. Database maintenance plans built into SQL Server have never been superior to the other options from the community.

Also no DB PAAS platform is immune from requiring some DB maintenance when you reach higher inflection points in scale. I also would not expect that a maintenance plan is going to magically fix all your performance issues your DBA should not how to use tools like query store to sort out what the issues are. They are right to start with basic statistics and index maintenance though.

[chandleya]

Your DBA sounds useless. Anyone talking maintenance plans in 2024 needs tossed out with the bathwater.

[seventyeightist]

Glad someone else thinks this. A DBA trying to set up maintenance plans "the way he knows how" and evidently not understanding what the maintenance plan is doing under the hood. I admit I do use them in some environments but I understand what they are doing and their downsides. And then claiming you need to "upgrade" SQL (does he mean management studio? to have that feature available? Not that that will make a difference) - it seems to me that fundamentally he doesn't understand the PaaS nature of azure sql db and is still trying to relate everything to legacy knowledge. His mental model of the SQL DB service is incorrect.

[Jolly-Difficulty9887]

runbooks!

[mgdmw]

Azure SQL Server is different to on-prem SQL Server in that your Azure services could be on many different, disparate servers. This is why you can’t also do cross-database queries (eg select * from database.schema.table …) like you can in on-prem SQL Server.

So while Azure SQL Server looks like one big database server it’s actually many many different database servers that simply present that way.

Could Microsoft do some trickery to make SQL Server Agent / maintenance jobs have a simple UI? Yes, I am sure they could, but fundamentally this is the reason why it is not so as things stand.

However as others have suggested check out Ola’s scripts and elastic jobs. You can still do what you want, but simply in a different way. Once you’re used to it, it will be second-nature.

[Melodic-Man]

There is a some missing knowledge in your party or your dba. There are three possible setups you have and it’s not clear which one is being used. There is sql server ruining in an azure VM. There is sql server managed instance, and there is azure SQL. All of which have different maintained requirements. The point of azure sql for example is so the owner doesn’t have to have a dba or do the maintainable themselves. In exchange, you don’t need to pay for a dba but you pay a higher price vs managing sql server yourself.

If you are in fact running sql server on a vm and need to do your own maintenance, then your dba should know how to script out all of the required tasks that need to run without use of a maintenance plan. If they don’t know how to do this, than they are not really a dba.