Delta Watch: Database management

Delta Watch (DW) uses a local database (DB) to store things like imported and processed data. After importing a data file, the original file will also be stored as a zip archive in the local archive folder of the relevant project for save keeping. Having a local DB allows DW to easily make DB backups and manage these on a rotational basis. But over time, the DB size does grow and requires more disk-space. For example, the DB can grow by adding new data or reprocessing existing data into smaller intervals. When data is deleted from within DW then it is not actually removed but rather marked to not be used. To permanently remove this deleted data and shrink the DB, a special utility will need to be used. Additionally, it is also possible to clean up the DB by removing raw data and only keeping the calculated results. This article covers some methods of managing the DB, these include making and creating manual backups as well as size reduction options.

What is in this article:

• Introduction to DB management utility.
• Configure automatic DB backups.
• Manually creating a backup.
• Restoring a backup.
• Reducing DB size.
• Space calculation example.

If Delta Watch is configured to make regular backups of the DB on a user configured location, then, when required, any of these DB's could be used for restoring purposes. By default, these backup files will be in a subfolder of the Delta Watch program called \Backup:

                [drive]:\Topcon Delta Watch\Backup

Depending on how the DB backup was created, the file name and extension might differ. But all can be restored via the same methods as discussed later on. The DB could have a filename extension of either .bak or .bku, and the filename could look something like one of the below, depending on how the backup was created:

• tunisdb_20211205183059.bku
• tunisdb_firebird_20211205181529.bku
• tunisdb_manual_20211205181013.bku

Configure Delta Watch for automated backups

Delta Watch can create regular DB backups on a rotational basis at a user specified interval and start time. It also allows creating a second copy of the backup, which can be placed on an off-site location for extra security. Backup intervals of less than 24 hours will put additional workload to Delta Watch and should be avoided, considering it is very fast to re-process just the last 24 hours of data again in case a backup does need to be restored.

To configure the automated backup, open the System Configuration module in the Administration group and locate the item Automatic backup in the tree. Configure the below minimum fields.

Once configured, click on Apply in the toolbar. If required, a manual backup could be started. How, see the section Create backup using Delta Watch, and study the section on Space requirements.

There are 2 ways of manually creating a DB backup.

1. Using Delta Watch
2. Using Tunis.Utils.exe program

When using Tunis.Utils utility, there is the option to reduce the DB size by permanently removing deleted records, and well as being able to specify a custom source DB.

Create backup using Tunis.Utils program

The DB management utility, as described above, should be used when additional actions have to be performed. Such as permanently removing deleted records from the DB or deleting messages from the message archive.

Close Delta Watch. Open the utility as described under section DB management utility. Select the option Create backup, see illustration on the right. Select the source DB. Assuming a backup is being made of the working DB; Navigate to the \Database folder in the DW program directory and select the file TUNIS.FDB Click Next Check the destination name of the backup DB. By default, it goes to the same folder as under 4) with the file extension .bku. It is possible to save it on another location. Click Next Specify if Garbage collection needs to be skipped. By enabling this, DW will permanently remove any deleted data. Click Next to start the backup process. Progress is shown on screen and when done the Finish button will be enabled. Perform the same steps for the SCHEDULER DB if required.

Restoring a DB backup might need to be done for reasons such as:

• Accidental loss of data; User deleted wrong data.
• Computer operating system issues, requiring a re-installation of complete system.
• Management action like DB clean-up and size reduction.

The backup might have been created by any of the below methods:

• Automated procedure as configured by the user in DW.
• Manual backup triggered from within DW via the menu About.Database backup....
• Manual backup performed using Tunis.Utils program.

The following steps will explain the process of restoring an existing DB backup using the Tunis.Utils program.

If at step 4 above the DB was not saved in the \Database folder with the correct name for Delta Watch, either TUNIS.FDB or SCHEDULER.FDB, then it needs to be manually moved and renamed before it can be used.

8. Using Windows file explorer, navigate to the \Database folder of Delta Watch. On a default installation, this can be found on: [drive]:\Topcon Delta Watch\Database
9. Rename; move or delete the current TUNIS.FDB and SCHEDULER.FDB files. These are the current Delta Watch DB files.
10. Move the restored DB, from step 4 above, into the \Database folder. The filename might look something like tunisdb_firebird_20210302113853.fdb.
11. Once moved, rename the file to: TUNIS.FDB
12. Repeat step 10 and 11 but for the SCHEDULER file if applicable
13. Start Delta Watch

Databases are great, but they tend to grow the more we put into it, and when you delete something it is not always really deleted. The same is true for a Delta Watch DB. It is possible to reduce the size of the DB, by how much, depends on the information it contains.

We can reduce the Delta Watch DB with 2 different actions:

1. Removing raw observation data from epochs
2. Permanently removing delete data

When DW processes raw geodetic data and converts that to coordinates, it does keep the raw data in the DB but also stores things like the network adjustment results. On a long-running project, over time, the storing of these raw data values will add to the growing size of the DB. If there is no need to reprocess older data then, at regular intervals, removing the raw data from the DB will help in keeping the required disk space to a minimum. This is called: cleanup data in Delta Watch.

When performing a cleanup data action in DW, it marks all unnecessary information of the selected time range that it can be deleted. This does not remove anything yet, that happens when Tunis.Utils is used to permanently delete the data. Follow the below steps to perform the cleanup data action, there is a slight difference depending on which version of Delta Watch is used.

In the Projects module, activate the project to clean. Open the Automated Geodetic networks module in the Administration group, and select a network. Right-click on either a complete month, or expand the month and right-click on a single day. Select Cleanup data, as per illustration on the right. Repeat step 3) to 4) for any other months and/or days in this network. Repeat step 1) to 5) for any other network and other projects. Close Delta Watch and continue with the Tunis.Utils steps below. >

In the Projects module, activate the project to clean. Open the Epochs module; Select and load the network and timeframe to be cleaned. Select either a complete month or a single day. Select Cleanup data from the main toolbar, as per illustration on the right. Repeat step 3) to 4) for any other epochs and networks in this project. Repeat step 1) to 5) for other projects if required. Close Delta Watch and continue with the Tunis.Utils steps below.

It is important to make regular assessments of the space requirements on the monitoring server.  The monitoring DB is growing on a daily basis and Delta Watch needs a certain amount of free disk space to be able to continue processing, but also to be able to create and maintain the backups of the DB.

When Delta Watch creates a DB, it first creates it on a temporary location. Once created, the DB is copied to the backup folder and then an integrity check is performed before the temporary DB file is deleted.

Further, we need to take into account the number of DB backups to keep as configured in the System Configuration module. All-in-all, this requires a certain disk space on top of the space needed for the active DB.

It is advised to have a minimum of 150GB free space, and for many of the projects this should be enough. But it is good practice to keep an eye on the available space and perform regular DB maintenance to help further reduce the space requirement.

To calculate the total space needed to create and maintain the backups, don’t forget to take into account any space already used by existing backups, as this will be freed when they are too old.