Maintenance plan in AWS MSSQL 2017 RDS

Question

We recently migrated from MSSQL 2017 on-prim to AWS MSSQL 2017 RDS, so earlier we were using MSSQL Maintenance plan wizard to create plans for DBCC checkDB, updatestate, Rebuild critical indexes etc.

But in RDS I dont see this option Maintenance Plan. My question is how we can achieve this in MSSQL 2017 RDS. One option is to write scripts and scheduled them in as a SQL jobs.

Any recommendations on this would be highly appreciated

user_6303548 · Answer

create procedure dbo.sp_updstats
with execute as 'dbo'
as
exec sp_updatestats
go

grant execute on dbo.sp_updstats to [admin]
go

exec dbo.sp_updstats

and using the following scripts I was able to configure all the activities. Download MaintenanceSolution.sql.

Be noted: The stored procedure DatabaseBackup is not supported on Amazon RDS. There is a logic in 1 solution that checks for it.

Maintenance plan in AWS MSSQL 2017 RDS

1 Answers