Introduction
When GuardPoint Pro works with a MS-SQL database, the db needs continuous maintenance to keep it stable and in good performance shape.
Sensor developed this easy one-screen tool to allow users to define the required maintenance jobs. Using this tool, the user can set their preferences for frequency and the time of day each job should be performed. It also gives the possibility to define email settings so that upon success or failure of each job, a corresponding message is sent to the defined email address.
The email messages are designed to be very short in order to allow using Mail-To-SMS services and forward them within one standard SMS text message.
All these jobs are saved in the SQL Server and performed by the SQL Agent.
Therefore, they run regardless of whether GuardPoint Pro is running at that time or not.
It is recommended to set job execution for times when the system is less busy, usually at night.
The tool can work with all versions of GuardPoint Pro SQL. It consists of one execution file (secSqlMng.exe) that must be copied to the application folder and run from there.
Running the file opens this screen:
The tool allows definition of the following jobs:
Database Backup short term
Database Backup (short term) creates a backup file for the whole DB, including events history.
Example for backup file name: GPP_backup_20110126_084727.bak
Default settings: Every 1 day at 00:00. Keep last 5 copies
Database Backup long term
Database Backup (long term) is basically the same as the previous job, but this additional option is aimed for long term and thus it has the following changes:
- The File name includes the text 'DoNotDelete'
- The default settings are set to save 2 years history in 24 files, one per month
- There is an option to keep all the backups made by this job, i.e., never auto delete any of them.
Example for backup file name: GPP_DoNotDeleteBackup_20110126_092545.bak
Default settings: Monthly
Every first day of the month at 01:00 - keep last 24 copies.
Rebuild Indexes
This task reorganises data on the Data and Index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimises the distribution of data and free space on the index pages, allowing faster future growth.
Default settings: Weekly. Every Sunday at 02:00
Update Statistics
This task ensures that the query optimiser has up-to-date information about the distribution of data values in the tables, allowing the optimiser to make better judgments about data access strategies.
Default settings: Every day at 02:30
DB Check Integrity
The Check Database Integrity task checks the allocation and structural integrity of all the objects in the specified database.
Default settings: Weekly. Every Sunday at 03:00
Shrink DB
This task reduces the disk space consumed by the database and log files by removing empty data and log pages.
Default settings: Monthly. Every first day of the month at 03:30
DB Log - History
The 'DB Log History' task archives old events, removing them from the event log table of the main DB ([LOGt]) and puts them in new external databases.
These databases are created by the task in the SQL Server of the main DB.
This task will only start when both of the following conditions are true in the main DB:
- There are events older than 365 days (the default value, legal values are from 1 to 1999 days)
- There are at least 2 million events (the default value, legal values are from 1 to 4 million events)
- Each time the 'DB Log History' task runs, it archives batches of max. 30000 events per batch (the default value, legal values are from 1,000 to 99,000)
- The max. size of each archive DB is also 2 million events by default
- The name of the first archive DB would consist of the main DB name plus the text 'HistoryLog1' (e.g. 'GPPHistoryLog1'). Then, once the first archive is filled with 2 million events, the 'DB Log History' task would create another archive name with 'GPPHistoryLog2' and so on. The archive DB contains only one table [LOG], structured exactly like the [LOGt] table in the main DB.
Buttons
The tool bar at the lower part of the screen has the following buttons:
Update SQL maintenance settings
On first time operation, this takes all the definitions as set on the screen and creates the relevant jobs on the SQL server. Later, when the jobs have been created, this updates any change made on the screen to the relevant job.
Restore Defaults
Restores all the default recommended settings from the job time/date and frequency.
Mind that clicking this button by itself only changes the values on the screen, the actual changes are applied only after clicking 'Update SQL maintenance settings'.
Clear jobs history
This cleans historical data about Backup and Restore, SQL Server Agent, and maintenance jobs operations from the SQL DB - it doesn't delete any events from the GuardPoint Pro database.
Where does the SQL Server keeps the jobs definitions?
Under Management > SQL Server Agent > Jobs
Notes:
1. In places where there is already an existing maintenance plan built with MS-SQL tools, it is advisable not to use this tool - in order to avoid duplicate jobs.
Therefore make sure there's no maintenance plan before you start.
2. To avoid jobs execution overlapping, the tool expects at least 30 minutes delay between jobs.
Attempting to set shorter delays would give an error message.
3. The tool was tested on SQL2005 and SQL2008.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article