Azure SQL/Data Warehouse database maintenance - Automation

Question

Azure SQL/Data Warehouse database maintenance - Automation

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
January 3, 2017 at 1:22 pm

#333549

Hello Folks,
Due to the absence of SQL Agent functionality in Azure(PaaS), is there a way to automate database maintenance in Azure SQL/Warehouse?
If you can provide stored procedures/scripts that would be much helpful.
Many thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 1

We use Azure Automation. You can schedule it pretty much like Agent. Currently we don't have any specific maintenance for Data Warehouse because we do a daily reload. There's nothing else we have to schedule. The only Data Warehouse scripts I recommend running are for statistics updates if you do trickle loads since there is not automatic statistics updates.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Thanks Grant,

Is it possible to schedule update stats using azure automation. Do you have a doc/scripts to do so?

Many thanks!

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 3

It is. I don't have an example handy.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

SQL!$@w$0ME

SSChampion

Points: 12349

January 4, 2017 at 12:10 pm

#1920733

Thanks

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 5

I'm going to try to work up a blog post on this over the next couple of days. If I get it done I'll come back here & post a link.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 6

SQL!$@w$0ME

SSChampion

Points: 12349

January 4, 2017 at 4:07 pm

#1920780

Appreciate your help on this Grant. Thanks a lot!

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 7

Wrote up an example blog post on this. It should be published about 10am Eastern. Here's the link.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 8

Grant Fritchey - Wednesday, January 18, 2017 6:07 AM
Wrote up an example blog post on this. It should be published about 10am Eastern. Here's the link.

Thanks a lot Grant!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 9

Grant Fritchey - Wednesday, January 18, 2017 6:07 AM
Wrote up an example blog post on this. It should be published about 10am Eastern. Here's the link.

Nice post Grant.

Also I got to know of LogicalApp in Azure which we can utilize stored procedure to execute and schedule through this. We can also use email notifications using SMTP/Outlook.

Also I found a least preferred way to accomplish this by creating linked server to azure sql data warehouse from on-premise sql or azure VM and execute stored procedures.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 10

SQL!$@w$0ME - Wednesday, January 18, 2017 10:46 AM
Grant Fritchey - Wednesday, January 18, 2017 6:07 AM
Wrote up an example blog post on this. It should be published about 10am Eastern. Here's the link.
Nice post Grant.
Also I got to know of LogicalApp in Azure which we can utilize stored procedure to execute and schedule through this. We can also use email notifications using SMTP/Outlook.
Also I found a least preferred way to accomplish this by creating linked server to azure sql data warehouse from on-premise sql or azure VM and execute stored procedures.

That last method is absolutely a little problematic. You will be moving some amount of data out of Azure that way adding (admittedly a tiny amount) cost.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning