Maintenance plans

This topic provides reference information comparing database maintenance tasks between Microsoft SQL Server and Amazon Aurora PostgreSQL. You can understand the key differences in how these two database systems handle common maintenance operations such as backups, index management, statistics updates, and consistency checks.

SQL Server Usage

A maintenance plan is a set of automated tasks used to optimize a database, performs regular backups, and ensure it is free of inconsistencies. Maintenance plans are implemented as SQL Server Integration Services (SSIS) packages and are run by SQL Server Agent jobs. You can run them manually or automatically at scheduled time intervals.

SQL Server provides a variety of pre-configured maintenance tasks. You can create custom tasks using TSQL scripts or operating system batch files.

Maintenance plans are typically used for the following tasks:

Maintenance plans can include tasks for operator notifications and history or maintenance cleanup. They can also generate reports and output the contents to a text file or the maintenance plan tables in the msdb database.

You can create and manage maintenance plans using the maintenance plan wizard in SQL Server Management Studio, Maintenance Plan Design Surface (provides enhanced functionality over the wizard), Management Studio Object Explorer, and T-SQL system stored procedures.

For more information, see SQL Server Agent and PostgreSQL Scheduled Lambda.

Deprecated DBCC Index and Table Maintenance Commands

The DBCC DBREINDEX, INDEXDEFRAG, and SHOWCONTIG commands have been deprecated as of SQL Server 2008R2. For more information, see Deprecated Database Engine Features in SQL Server 2008 R2 in the SQL Server documentation.

In place of the deprecated DBCC, SQL Server provides newer syntax alternatives as detailed in the following table.

For the Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) alternatives to these maintenance commands, see Aurora PostgreSQL Maintenance Plans.

Examples

Enable Agent XPs, which are disabled by default.

EXEC [sys].[sp_configure] @configname = 'show advanced options', @configvalue = 1 RECONFIGURE ;

EXEC [sys].[sp_configure] @configname = 'agent xps', @configvalue = 1 RECONFIGURE;

Create a T-SQL maintenance plan for a single index rebuild.

USE msdb;

Add the Index Maintenance IDX1 job to SQL Server Agent.

EXEC dbo.sp_add_job @job_name = N'Index Maintenance IDX1', @enabled = 1, @description = N'Optimize IDX1 for INSERT' ;

Add the T-SQL job step Rebuild IDX1 to 50 percent fill.

EXEC dbo.sp_add_jobstep @job_name = N'Index Maintenance IDX1', @step_name = N'Rebuild IDX1 to 50 percent fill', @subsystem = N'TSQL',
@command = N'Use MyDatabase; ALTER INDEX IDX1 ON Shcema.Table REBUILD WITH ( FILL_FACTOR = 50), @retry_attempts = 5, @retry_interval = 5;

Add a schedule to run every day at 01:00 AM.

EXEC dbo.sp_add_schedule @schedule_name = N'Daily0100', @freq_type = 4, @freq_interval = 1, @active_start_time = 010000;

Associate the schedule Daily0100 with the job index maintenance IDX1.

EXEC sp_attach_schedule @job_name = N'Index Maintenance IDX1' @schedule_name = N'Daily0100' ;

For more information, see Maintenance Plans in the SQL Server documentation.

PostgreSQL Usage

Amazon Relational Database Service (Amazon RDS) performs automated database backups by creating storage volume snapshots that back up entire instances, not individual databases.

Amazon RDS creates snapshots during the backup window for individual database instances and retains snapshots in accordance with the backup retention period. You can use the snapshots to restore a database to any point in time within the backup retention period.

You can backup database instances manually by creating an explicit database snapshot. Use the AWS console, the AWS CLI, or the AWS API to take manual snapshots.

Examples

Create a manual database snapshot using the Amazon RDS console

Restore a snapshot using the Amazon RDS console

You can also restore a database instance to a point-in-time. For more information, see Backup and Restore.

For all other tasks, use a third-party or a custom application scheduler.

Rebuild and reorganize a table

Aurora PostgreSQL supports the VACUUM, ANALYZE, and REINDEX commands, which are similar to the REORGANIZE option of SQL Server indexes.

VACUUM MyTable;
ANALYZE MyTable;
REINDEX TABLE MyTable;

For more information, see ANALYZE, VACUUM, and REINDEX in the PostgreSQL documentation.

Convert deprecated DBCC index and table maintenance commands

Update statistics to help the query optimizer get updated data distribution

For more information, see SQL Server Managing Statistics and PostgreSQL Table Statistics.

Summary

The following table summarizes the key tasks that use SQL Server maintenance plans and a comparable Aurora PostgreSQL solutions.

For more information, see Working with backups in the PostgreSQL documentation.