Issue
Performing database maintenance with Vista 4.5.0
Product Line
TAC Vista
Environment
- Vista 4.5.0
- SQL Server
Cause
It is necessary to perform backups for two reasons, first to be able to recover the system in the event of a fatal error and second to be able to manage the size of the database.
Resolution
Vista has a means of backing up databases on a Vista Server. It is necessary to perform backups for two reasons, first to be able to recover the system in the event of a fatal error and second to be able to manage the size of the database. The Vista database probably won’t grow very much after the initial engineering activity but the event and trend log database grows as events are received in Vista. In many installations maintaining an archive of event data is required so that is another reason for backing up the database. This paper presents a way to accomplish the backing up of the Event and Trend database and creating an archive file.
Before the backup process can be implemented it is first necessary to have a good estimate of the Event and Trend Log traffic anticipated for the installation. In the Vista 4.3.0 Technical Product Information (TPI), Ref No: C88-1-D43, there is section entitled Strategies for Calculating and Controlling Database Size. On page 5 the method for determining database size is explained and used in the following example:
Right clicking on the $Backup_LDB object in the Server View brings up this dialog.
Note that this example has an Event and Trend database that has reached 1121 (MB) in size. The default Size Alarm Limit is shown to be 1640 and if the database grows past that size, Vista may not operate reliably.
The TPI states on page 4 that events contain on average 2Kb of data. Combining that value with the expected traffic provides a reasonable estimate of the database expansion rate. If the installation traffic estimate is 4000 Events and Trend samples per day, the database expands at the rate of 8Mb per day. If the installation traffic estimate is 10,000, the database expands at the rate of 20Mb per day.
A conservative approach would limit the size of the active database to half of the alarm limit. That means the backup process must be done so that the size does not get to 820Mb. At 10000 Events and Trend samples per day that value would be reached every 41 days. Vista is designed to allow Event driven backup operations and the closest choice it offers to the 41 days is once a month so that is what will be chosen.
The configuration of backup is done in Vista Workstation by opening the Properties of the Workstation object in the tree. The first step is to select a location for the backup files to be saved. In the example shown below it is the folder outlined in red.
The “Max number of files” limits the number of backups that are retained by Vista on the hard drive. The number chosen here was two (2). The procedure that is described further down will actually move backup files to new directories which could be a network drive or a CD/DVD rom. (The latter option requires operator action so it might not be the most desirable especially since backups are usually done at off times when an operator might not be available)
Next the event that is to trigger the backup must be defined. The configuration of when the backup is done is set on the Log Backup tab of the Vista Server Properties dialog. In the example shown below the backup is scheduled to occur on the first of every month at 12:40AM. The selection of Event object is made from the options made available by the browse button.
The browse selection brings up the Select dialog from which the selection of Event type and properties is done. In the following example the selection is Monthly.
Right clicking on the $Event_LDB object opens the properties dialog box to configure the Event_Month object.
The final configuration option is what to do with older records. Vista will remove Events from the database if you so desire. The removal does not shrink the database but does make space available for new events to be written without expanding the size of the database. This is the key to keeping the database from exceeding the Alarm Limit.
The Event Log tab provides a way to delete old records from the database. In the example shown below records older than 30 Days will be deleted after the backup that was done be the Event_Month object has been completed. This means that each month the backup should have the most recent 30 days of Events and Trend samples.
The final step that can be taken is to also copy the backup file to a server that is backed up or to a CD/DVD Rom. This operation creates an archive in which Event and Trend data is stored. Windows task scheduler can be used to run a cmd at some time after the backup operation was scheduled. For the above example schedule it to run a 1:00am on 1st of each month. If backing up to a network drive create a folder such as shown below.
J:\Engineering\Vista Event Log BU.
In order for the task scheduler to run it is necessary to setup a user account on the pc that is not related to any actual user. I added a user to the User group named,
vista xcopy dbmgr.
This will be the user account used by the task scheduler. The Vista backup folder must have modify/write permissions for this user and the folder into which the backup is going to be copied must have Full control.
The command line for this example is shown below:
C:\WINDOWS\SYSTEM32\xcopy.exe "C:\Program Files\TAC\TAC Vista 4.5.0\Db2\$bkp" "J:\Vista Event Log BU" /M
The Windows task scheduler is configured in the Control Panel, Scheduled Tasks as shown below:
In the Run as: box enter the name of the new User created described above:
vista (xcopy dbmgr)
Schedule the task to run on the first of each month at 1:00AM.
The backup files can be restored to a Vista Server that is not performing actual site monitoring. This method is only way the “archived” event and trend data can be viewed as of Vista revision 4.5.0.
