This how-to will present guidelines for setting up Mendix SQL maintenance plans.
This how-to teaches you how to do the following:
Set up SQL basic maintenance plans
Set up notifications
Set up user database maintenance plans
Prerequisites
Before starting this how-to, make sure you have completed the following prerequisites:
For the purpose of this how-to, the following Mendix SQL data structure will be used, so please adapt the folder tree according to your organization’s data structure:
Make sure the SQL Server Agent Service is running, preferably using the Mendix service account for authentication
Consider the amount of soft backups that need to be kept
Consider what email address will receive maintenance notifications
SQL Basic Maintenance Plan
The Basics
Maintenance plans are created by the editor in the SQL Server Management Studio.
To add a task step, follow these steps:
Drag the corresponding item from the Maintenance Plan Tasks toolbox:
Drag connecting lines between the items to initiate a sequence of events.
To change a notification job from Success (which is the default connector) to Failure, right-click the connecting line and choose Failure:
Notifications
To configure notifications, follow these steps:
Start the SQL Management Studio.
Create an SQL operator (SQL Server Agent). The operator will receive maintenance notifications.
Right-click DatabaseMail and select Configure Database Mail:
Make sure the email profile is set to both Public and Default Profile:
Verify your email configuration by sending a test email:
Next, create two maintenance plans:
For the sake of uniformity, the plan names MP_SYSDB and MP_USERDB will be used throughout this document. Changing the plan names to a different convention will not influence the functionality of the plan.
On every (sub)plan, notifications will be configured. Whether to only send Failed events or both Failed and Successful is up to the user.
This is the pattern for a notification:
Subject: SQL Server Message - <SQL instance> - <maintenance plan name> - <subplan name> <successful/failed>
For example: SQL Server Message - Mendix-SQL1 – MP_USERDB - weekly maintenance failed
For example: Mendix-SQL1 – database backup (full) successful
User Databases
In this section, you are going to create a new maintenance plan called MP_USERDB containing subplans:
Create a subplan called backup database (full).
Schedule the plan to run every working day at 22:00 (or whatever time does not conflict with your external backup application):
Choose to back up to disk.
Back up to X:\sqldata\backup.
Select create a sub-directory for each database.
Back up the file extension .bak.
Select verify backup integrity.
Set the backup compression to Compress Backup.
Create a subplan called backup database (trn).
Schedule the plan to run every working day, as often as is necessary. If hourly backups are run, database recovery will be possible per hour. When run with a smaller interval, database recovery to a more recent point in time will be possible. High volume databases that perform many hourly transactions should back up more often than lesser-used databases.
Choose Transaction Log for the backup Type.
Choose to back up to disk.
Back up to d:\sqldata\backup.
Select create a sub-directory for each database.
Back up file extension .trn.
Select verify back up integrity.
Set the backup compression to Compress Backup.
Create a subplan called weekly maintenance.
Schedule the plan to run every Sunday at 01:00 (or whatever time the application is least used):
Set Change free space per page percentage to: 10% and select Keep index online while reindexing (please note that this option will only work in enterprise versions of Microsoft SQL server):
Create a subplan called daily maintenance.
Schedule to run every Tuesday till Friday at 02:00 (or whatever working days apply for your organization):
.
You should adapt the times set to your desired backup retention time. The cleanup might be set to smaller intervals in order to save disk space.
System Databases
In this section, you are going to create a new maintenance plan called MP_SYSDB containing the following subplans:
Create a subplan called backup database (full).
Schedule this to run Tuesday through Friday at 02:00 (or whatever working days apply for your organization):
Select to back up to disk.
Back up to d:\sqldata\backup.
Set create a sub-directory for each database.
Back up the file extension .bak.
Select verify backup integrity.
Set the backup compression to Compress Backup.
Create a subplan called weekly maintenance.
Schedule this to run every Sunday at 01:00:
Set the rebuild index task to reorganize pages with the Default free space per page and select Keep index online while indexing:
