About SQL Server maintenance
- Last UpdatedDec 13, 2022
- 3 minute read
It is important to configure a Maintenance Plan for SQL Server databases, particularly those that have data written to them on a daily basis such as the AVEVA Production Management Data Repository and the CitectSCADA Reports Historian databases. These are referred to as "active" databases in this section.
AVEVA™ Production Management and CitectSCADA Reports configuration databases have lot less activity but it is worthwhile including them in a Maintenance Plan so that, if changes are made, they are not lost during disaster recovery.
It is also important to backup the system databases such as master, msdb and model. It is helpful to be able to restore these early in the disaster recovery process so that information about users, logins, Maintenance Plans, DTS Packages, SQL Server Agent Jobs and schedules etc are automatically restored.
File management is also an important part of any disaster recovery scheme. A common occurrence is that if a database is configured for "Full Recovery Mode" but backups are not scheduled, the transaction log grows forever and eventually fills up the SQL Server HDD, months or even years after completion of the project.
Similarly, the number of backup files and transaction log files can quickly grow out of control and fill up both SQL Server local drives and waste network resources.
SQL Server services
There are two services that are required to be configured appropriately to support the selected Maintenance Plan strategy MSSQLSERVER and SQLSERVERAGENT.
-
MSSQLSERVER is the main database engine and must be running to access any databases on the server. For this reason, it is set by default to run on start-up.
-
SQLSERVERAGENT is responsible for running scheduled Jobs on the server. By default it is NOT set to run automatically on start-up.
By default, both services run under the LOCAL SYSTEM account. This has to be changed if there is a requirement to access network shares either directly for back ups or to copy backup files after they are created on a local drive. In these circumstances, use a Windows account that has access to the network and a password that does not expire.
Conclusions
It is important to backup of the Configuration database and the Historian as well as the system databases (master. msdb and model) and to keep copies of the backup files at a location other than the SQL Server. Management of transaction logs and backup files is an important part of any Maintenance Plan to make certain the storage devices do not become clogged.
Questions as to whether backups are done across the network and whether compression is used are project specific and are decided on a case by case basis. Similar considerations apply to third party backup packages.
Traditionally, backups have tended to be on a local drive with the backup files copied at a later date to a network location. Improving technology (faster more reliable networks) and the belated introduction of compression for backup files in the next version of SQL Server could change the equation.
Recommendations
All active databases are configured for the Full Recovery Model and backed up regularly at an "off peak" time, typically 2:00 AM on Sunday morning (the SQL Server default).
Transaction logs are backed up more frequently (at least once a day) and perhaps more frequently. It may be useful to backup the transaction log every 5 or 10 minutes so that data can be restored to within 5 or 10 minutes of an event.
If data is considered more critical that a 5 minute time horizon, warm standby solutions such as Transactional Replication, Database Mirroring, Log Shipping and Clustering are considered. Such options are not described in this document.
The AVEVA™ Production Management and CitectSCADA Reports configuration are not nearly as active after initial configuration so that a Simple Recovery Model can be used.
During configuration, engineers are required to make frequent manual backups of the configuration databases. The use of the Full Recovery Model with frequent transaction log backups can be a way to automate configuration database backups during initial configuration and paves the way for quick recovery from any errors that may occur during configuration.
In addition to the project databases, the system databases, master, model and msdb are backed up.