You are here

Features

Configuring a good maintenance plan and monitoring it is a difficult task that requires deep knowledge and significant amounts of time, especially if you are not a database administrator (DBA).

QMB is a functional and convenient scheduler that is intended for performing database maintenance on Microsoft SQL Server.

For Novice Administrators

By using an assistant, even novice administrators will be able to configure database maintenance in minutes while adhering to best practices and predefined sql server maintenance scripts:

  • Create backup copies, and copy them to a network folder
  • Regularly check backup sequences by restoring them
  • Maintain indexes and statistics
  • Configure substantive e-mail notifications
  • Monitor available disk space
  • And much more

For Those Who Love SSMS and its Maintenance Plan

  • The ability to regularly check backup sequences by restoring them (on a local or remote SQL Server)
  • The ability to automatically generate a script to restore databases according to an XML plan in case of failure
  • The ability to update database copies (on a local or remote SQL Server)
  • The ability to copy backups to a network folder and delete out-of-date ones
  • Monitor available disk space on SQL Server disks
  • Perform scripts in T-SQL, CMD, PowerShell, VBS, JavaScript, and other languages according to a schedule
  • Collect statistics on database size growth

At the same time, you can continue to use the maintenance plan for backups and maintenance of indexes and statistics.

The program has two built-in sets of SQL Server Maintenance Scripts:

  • QMB tasks. T-SQL scripts obtained from open sources
  • Scripts that utilize storage procedures created by Ola Hallengren (DBA from Sweden)

Thus, the program has two sets of maintenance policy templates, which are based on the first or second set of tasks. When you create a policy from a QMB template, it will also check some of the database properties and optimize them, if necessary.

Backing Up Databases

The predefined maintenance policy templates implement SQL Server backup best practices to ensure a Full and Simple Recovery Model. These strategies make it possible to optimize the use of disk space and to ensure a high restoration rate for most databases.

The program allows you to:

  • Create all types of backups: full, differential, and transaction log backups
  • Delete old files without interrupting the backup sequence

Maintaining Indexes

As the amount of data and the number of users increase, database performance decreases. Therefore, it is imperative to regularly maintain indexes and statistics. Predefined maintenance policy templates include such tasks as:

  • Complete rebuilds of indexes
  • Defragmentation of indexes
  • Complete or selective updating of statistics

Checking Backups by Restoring Them

As you know, there is only one 100% verified method of checking your backups: you have to try to restore them. There is no other way. SQL Server backup best practices recommend that you make periodic restorations so that you can check your backup strategy and be ready to quickly correct accidents. QMB helps you automate this process to the maximum degree and allows you to:

  • Schedule backup sequence restorations: Full -> Differential -> Transaction Log Backups
  • Restore to the SQL Server source or another SQL Server
  • Restore to a temporary database that is deleted after the transaction is completed in order to free up space
  • Restore to the same database. For example, to ensure database copies are up-to-date
  • Check the integrity of the database after restoration

XML Restoration Plan

QMB has implemented a XML restoration plan mechanism, which allows you to:

  • Automatically generate and execute database restoration scripts. For example, if you need to restore SQL database on a different server, then the XML plan saves you from wasting valuable time on manually creating a restoration script. Rather, it allows you to immediately begin restoration
  • Maintain up-to-date copies of databases on test and backup SQL Servers
  • Restore databases from backups that are stored on a network share

Copying backups to a network folder or an additional drive is a good practice. The program allows you to perform the following operations on the backup files:

  • Copy to a network or local folder
  • Copy only the files that were changed, thereby reducing network traffic
  • Copy backups from only certain databases
  • Delete old backup copies in the network folder. The expiration dates for the local SQL Server disk and the network folder may be different
  • Create your own file copy routines that implement any logic or compression, and ability to upload these to the cloud, FTP, DropBox, GoogleDrive, etc.
  • Ability to run a copy operation in the background. For example, in order to optimize the maintenance window while another operations are running

A QMB task is not just a T-SQL script. The program can execute CMD, Java Script, Power Shell, and other scripts. This feature allows you to:

  • Optimize the maintenance window and use a scheduler for logically related tasks. For example, immediately after the backup is completed, the PowerShell script that will archive these files and transfer them to the FTP can be executed. Usually in this case the SQL Server agent will execute one task, and the Windows scheduler will execute the second one. Thus, the tasks must be load distributed over the time, which will inevitably increase the amount of information displayed in the maintenance window. These types of tasks can be executed in QMB one after the other without waiting
  • Execute the scripts safely on the SQL Server side without using xp_cmdshell
  • Run other applications. For example, preload data from external sources into the database, restart the service, etc.
  • All console messages received during the script execution process can be written to the QMB maintenance log

QMB tracks available disk space on SQL Server disks. Monitoring is configured when the server is registered for each drive. When the free space threshold is reached, the administrator is notified. In this way you can avoid a situation where SQL Server suddenly "stops" or backups stop being created.

QMB uses its own mechanism to send notifications (the Database Mail component is not used), which allows:

  • Use of a built-in or custom SMTP account
  • The ability to send notifications to different email-addresses when errors occur and tasks are successfully executed
  • Inclusion of the text of the error in the notification message (first 15 lines)
  • Minimization of the number of identical messages that are sent

QMB allows you to:

  • Maintain multiple SQL Servers with a single program
  • Copy maintenance policies between servers
  • Perform tasks across multiple threads at the same time

The program can be installed on any machine on the network!

  • Manual execution of scripts for one or more databases
  • Detailed maintenance logs will help you to quickly understand the causes of errors
  • Gathering statistics on the size of databases and displaying information in the form of a chart
  • Calculating the average net increase in the amount of data (excluding reserved space)
  • Manual restoration of databases from backups that were made at a specified date and time
  • The interface is available in English and Russian