How does it work?
The Experda Maintenance Plan Generator provides a comprehensive suite of automated maintenance solutions tailored to meet your database management needs. Maintenance actions can be executed directly from the server object tree or scheduled through the maintenance settings for streamlined operations.
Within the maintenance settings, it is recommended to utilize maintenance templates to group similar servers for consistent management. For instance, all production environment servers can be grouped under a single “Production Template,” which facilitates more frequent backups and scheduled index rebuilds on a weekly basis.
For more granular control, you can define custom server maintenance rules to address specific requirements for individual servers. If a server remains linked to a template, you can either exclude it from a specific rule or create a custom rule that overrides the template configuration.
Scheduled maintenance rules are implemented using SQL Server Agent jobs. To ensure compatibility across all MSSQL editions, including Express editions, a specialized solution is employed. For Express editions, maintenance rules are executed via Windows Task Scheduler, offering alternative scheduling options supported by the Windows operating system.
Manually executing a maintenance rule
To manually execute a maintenance action:
- Select Databases: From the server list in the main company view, select one or more databases where you wish to apply the maintenance action.
- Single Database Maintenance:
- Select a single database from the menu and click on the options (represented by three dots).
- Choose from available actions, such as Restore or other Immediate Actions, to view and execute the desired maintenance tasks.
- Multiple Server Maintenance:
- a. For actions across multiple databases, select the checkboxes corresponding to the databases you want to include.
- Access the three-dot menu at the server level to apply maintenance actions to all selected databases one after the other.
Supported Maintenance Rules
The following is a list of maintenance rules supported by Experda, each offering a comprehensive set of configurable options that will be detailed later. For optimal database performance and reliability, it is recommended to implement these rules effectively:
Summary
Backup: Safeguards against data loss by creating recoverable copies of the database. Essential for disaster recovery and restoring data integrity following unexpected events.
Reindex: Mitigates index fragmentation caused by frequent data modifications, optimizing query performance and reducing IO overhead for efficient resource utilization.
Update Statistics: Refreshes metadata used by the query optimizer, ensuring accurate execution plans and improving query efficiency.
Integrity Check: Executes comprehensive database integrity validations (e.g., DBCC CHECKDB) to detect corruption and maintain structural reliability and health.
Shrink Database: Reclaims unused storage space. Should be used sparingly, as it may lead to fragmentation and impact performance.
Restore Database: Facilitates recovery by restoring a database to a consistent state, ensuring minimal downtime and maintaining business continuity after failures.
Maintenance Template View
Reach the maintenance template view by pressing the settings button and choosing Maintenance G Backup.
| Legend | |
| 1 | Selection Between Maintenance Template View and Custom Server View
Both views serve distinct yet complementary purposes for managing configurations. The Template View enables users to manage and apply predefined maintenance templates across multiple servers efficiently, ensuring consistency in configurations. Conversely, the Custom Server View provides detailed visibility and control over individual server configurations, allowing for granular adjustments tailored to specific requirements. |
| 2 | Choose between affiliated servers and the rules tabs. |
| 3 | 3-Dot Menu – Additional Options for a Template
The 3-dot menu provides advanced options for managing templates. These options include:
|
| 4 | Create template button – opens a create template dialog |
| 5 | Search template by name |
| 6 | Navigating Between Backup and Optimization Sections
Easily toggle between the Backup and Optimization sections within a template. This feature is particularly useful when managing templates with numerous rules, allowing quick access to relevant sections without scrolling through extensive configurations. |
| 7 | Summary + inner rule textual search |
| 8 | Add new backup rule button |
| 9 | Rule Row Features
A rule row in the maintenance configuration interface provides several interactive options and functionalities for efficient rule management:
|
| 10 | Add new optimization rule. Choose from reindex, integrity or update statistics. |
Maintenance Template View – Affiliated Servers
| Legend | |
| 1 | Navigation between Affiliated servers or Rules |
| 2 | The Add Server button opens the server selection dialog, allowing users to choose and add or remove servers to the template. The final list of selected servers is confirmed within the dialog before association. |
| 3 | This list displays all servers currently linked to the template, providing a clear overview of server associations for efficient management. Pressing the X on a server will detach it from the template. |
Note:
If a server is detached from a rule, all linked rules associated with the server will be removed. However, any unlinked or custom rules will remain intact and unaffected by the detachment.
Common options for maintenance rules
DB Source selection
Maintanance Rule Settings – Source Selection
Within the settings for each maintenance rule, you will find the Source Selection option. This setting allows you to fine-tune the selection of databases or database groups to which the maintenance rule should be applied.
By default, the following database groups are predefined and can be applied at both the Maintenance Template and Custom Maintenance levels:
| Source Database Type | |
| All databases | The rule will apply to all databases |
| System databases | The rule will apply to all system databases (msdb, model, tempdb, master) |
| User databases | The rule will apply to all user databases (all databases except the system databases) |
| Availability group databases | The rule will apply to all databases that belong in an availability group. |
| User databases except availability group databases | The rule will apply to all user databases except those in availability groups. |
| Full databases | All databases that have are in full recovery mode |
| Simple databases | All databases that have are in simple recovery mode |
Source Database Type in Template and Custom Maintenance Levels
1. Template Level:
- The Include/Exclude section is used to include or exclude specific servers from a rule.
- This functionality ensures that rules are applied only to the relevant servers, as multiple servers can be attached to a single template.
2. Custom Server Level:
- The Include/Exclude section allows for granular control by including or excluding specific databases from a rule.
- This flexibility is not available at the template level, as templates manage multiple servers sumultaneously.
3. Additional Custom Server Settings:
- In the Custom Server Level, the Include/Exclude tab may offer additional configuration options depending on the rule type.
- For example, in the Integrity rule, you can select specific objects to include in the rule.
- Detailed settings for each maintenance rule are available within their respective configuration dialogs.