The following article provides information on how to create a disaster recovery plan for Agvance. The article covers topics such as determining how much downtime your company can tolerate, creating backups of data, and restoring database images. It also emphasizes the importance of working with a hardware technician familiar with SQL Server and conducting drills to test the entire disaster recovery plan.
Disaster Recovery
The first place to start when thinking about disaster recovery is your hardware technician. Before talking to your tech, ask yourself the following questions:
- How much down time can our company tolerate?
- How much data re-entry following a data restore would be acceptable?
The answers to these questions will help you and your tech outline a disaster recovery plan and decide what types of backup and restoration mechanisms are required.
Note: In version 4.5, there are two additional files associated with Agvance, pntdata.mdf and seedmod.mdf. These will create On Demand image files in LastImage called AVDATApntdata.dat and AVDATAseedmod.dat, and corresponding files in the AgvImage folder. These data files are incorporated into the main database in version 5.0.
SQL Server
Agvance data is controlled by Microsoft SQL Server. SQL data cannot be copied like other files, so it requires special backup procedures. There are two options for backing up your Agvance database:
- Use special backup software that is designed to back up live SQL databases.
- Create images of your Agvance data that can be copied onto your backup media.
Database Images
A database image is a copy of the Agvance data at a specific point in time, in a file format that can be copied to a backup media. Database images can be scheduled to happen automatically at a specific time (usually after hours) or created on demand.
Database images can be created while other users are working in Agvance. The users may notice a slowdown while the image processes.
Once the image files are created, any file copy program can be used to move them onto the backup media - usually tape, CDs, DVDs, flash drives, or external hard drives. Talk to your hardware tech about what backup media is most appropriate for your company.
Scheduling Database Images
The safest way to handle your backups is to schedule them so they happen every day automatically, and you don’t have to rely on anyone to remember. In Agvance, under Hub / Utilities / Make Database Image, you can set up a Scheduled job in SQL Server to create images for you automatically at a specific time. The data below is set to create an image every night at midnight. The Scheduled image job should be set to run at least a couple hours prior to the Scheduled backup.
It is not necessary to make Scheduled images of prior year databases. This only needs to be set up in the current data.
Important: The free Express edition of SQL Server does not include the Server Agent which is used by SQL Server to create Scheduled backups. If using the free Express edition of SQL Server, make On Demand backups or use a third party tool to make Scheduled backups.
Making an On Demand Database Image
Database images can also be created On Demand. All users will need to make On Demand images as part of the month and year end process and any time a manual backup is needed. On Demand images do not require the SQL Server Agent.
It’s a good idea to create zipped images because the zip file is smaller, but Agvance cannot create a zipped image that will be larger than 4GB. If the image is not zipped, the image will be about the same size as the actual data file.
Some do a combination of On Demand and Scheduled images. For example, if a Scheduled image is set up to run at night prior to the backup, an On Demand image could be done every day at noon as an extra insurance policy, or vice versa.
Finding Database Images
The most recent image (Scheduled or On Demand) for any database is located in the LastImage folder inside the data folder. Typically when doing a backup, the contents of the LastImage folder are copied to the backup media.
Each prior year database has its own LastImage folder. Prior year database names typically start with FY for fiscal years and PY for payroll years. It is important to have backups of all prior year databases so they can also be restored.
There is another folder on the server next to the AVDATA folder called AgvImage. This folder contains a history of database images - a week’s worth of Scheduled images and every On Demand image that has not been deleted.
4.5 Note: In version 4.5, customer maps are stored outside the database in folders. These companies should back up the entire database folder (for example, AVDATA) instead of just the LastImage folder. In version 5.0, maps are stored in the database so only LastImage needs to be backed up.
Restoring Database Images
If the data server needs replaced, the backed up Agvance database can be restored once SQL Server is installed and running on the new machine. Restore instructions are found in the Restoring Data article.
The Replacing a Terminal Server article has information about installing a new Terminal Server. All the instructions may not be possible to follow if the old server has been lost, but it will give an idea of what to expect.
Securing the Backup
Copying a database image onto backup media is a great start, but additional steps are needed to ensure the backup is safe and available any time it is needed.
- Keep more than one copy of the data. For example, if data is backed up on flash drives, have a different drive for each day of the week and rotate between them.
- Take daily backups off-site every day and store in a fire-proof safe or bank vault. This way, a fire, tornado, or other disaster that affects the office building will not also destroy any backups.
- Have a backup of every prior year database in that same secure off-site location. It is a good idea to make new images and backups of prior years every year or two to keep them current.
Other Disaster Recovery Tips
There are a few other disaster recovery strategies to keep in mind:
- Make a disaster recovery plan with the help of a hardware tech, taking into account as many situations as possible. This plan should include:
- Emergency contact phone numbers for the tech and anyone else who will need to assist with the recovery.
- The location of the off-site backups.
- Instructions for getting the data off the backup media.
- Find out how quickly the technician can replace the server if needed.
- If special software is required to retrieve data from the backup media, store a copy of the install and instructions in the same place as the off-site backups.
- If there is an automated backup, make a habit of checking the logs. An email or text notification may be available to warn if the backup fails.
- Periodically check the backups and test restoring the images.
- Consider backing up each user’s Agvance configuration file. This file is called Agvance.ini in version 4.5 and user.config in version 5.0. It is stored in each user’s Windows profile and contains some Agvance settings such as printer defaults.
- Hold drills to test the entire disaster recovery plan and keep it up-to-date.
It is very important to work with a hardware technician familiar with SQL Server and one who has experience handling backups for other companies of similar size. Data loss is much more expensive than the cost of retaining a good tech. If a hardware technician who can meet these backup needs is not available, SSI has partnered with Harland Technology Services to offer IT services to Agvance users.
Backup Logs Note
If choosing to back up the entire data folder, the backup log may show that some files were skipped. These are the live database files attached to SQL Server and should have extension .mdf or .ldf. This is expected. It is important that the backup software does not end the backup when it encounters these files but simply notes that they were skipped.
Image Files
Naming of Image Files
The Create Database Image process makes a snapshot of two files in the data folder (agvance.mdf and agvsec.mdb) and places the image in that data folder’s LastImage folder, and also in the AgvImage folder on the data drive.
Images are named by the data folder the source data resides in. For the examples below, the most common data folder name (\AVDATA) will be used.
Images in the LastImage folder
Both On-Demand and Scheduled backups save a copy of the Agvance database into the LastImage folder. These are files that could be created in LastImage:
- AVDATA.dat – Image of the Agvance database, agvance.mdf
- Agvsec.mdb – Copy of the agvsec.mdb file in the data folder
- AVDATA.zip – Zip containing the above database image files
Images in the AgvImage folder
The names of the images saved in AgvImage vary depending on which method of creating the image is used.
- On Demand images will have the date and time embedded in the name of the file (AVDATA20221025_105852.dat for a file created October 25, 2022 at 10:58:52 am).
- Scheduled images have the day of the week embedded in the name of the file (AVDATAMonday.dat). Because the name of the images created each day of the week are the same, no more than seven Scheduled images are archived in AgvImage for each databaseNote
Note: In version 4.5, there are two additional files associated with Agvance, pntdata.mdf and seedmod.mdf. These will create On Demand image files in LastImage called AVDATApntdata.dat and AVDATAseedmod.dat, and corresponding files in the AgvImage folder. These data files are incorporated into the main database in version 5.0.
