Best Practices for Performance, Backups, and Data Integrity

Time and time again, we have found the practices below to mitigate most IT risks that companies face, be they performance issues or a company’s faith in the integrity of their data. Many of these practices mirror our own. While this document may not render a company’s server immune to issues, it can bring it closer to this ideal than one might think. 

In-House or Contracted Technician Should be SQL-Certified 

There are several reasons this is stated throughout our documentation and communication – the most important of which being that emergencies can never be predicted, and data cannot always be recovered. The hidden costs of an under-qualified tech – even when excluding losses in underutilized man hours – outweigh the on-paper savings by far.

Even when following the above best practices of having a qualified Network Analyst and DBA visit once a year, this really only guarantees better performance for at most a month – only a qualified in-house or contracted technician can guarantee a well-run network. 

An unqualified technician cannot be expected to know the complex internals of SQL innately, nor to even pick it up in time for a live deployment. Experience is and always has been the best shield against disaster and under-performance. 

Keeping in mind the next section is optional (though definitely recommended) if a company wishes to test their working knowledge against the certification, a company interviewer should expect the technician to understand the following:

Common SQL terminology (database, mdf, ldf, log file, SQL backups, maintenance plans, sa) Common SQL processes – how to run a backup, how to shrink a log file, how to setup a cliconfg connection, how to use ODBC, and how to change compatibility mode for a database.

Best Practices for Database Backups 

For the most up to date information in the case of an IT disaster, we recommend not only that daily full backups be run, but hourly log backups be run as well. 

If the company is using SQL Standard, we recommend instead the backup processes be done through the SQL Management Maintenance Plan Wizard. There are also third party backup programs – very inexpensive and reliable options are available online that can send emails to designated employees if the backup fails. 

As with Folder Backups, we recommend that the database backups that are made be backed up to cloud storage and/or a NAS, and/or an external. 

Technicians: please keep in mind that while in rare cases it may be troublesome and/or time- consuming to do so, almost all of the content inside the RFMS folder can be reproduced with a qualified technician and freely available RFMS documentation. However, without involving a qualified DBA, a data recovery expert, and often even the RFMS programming department, almost none of the content of the database can be reproduced, and even under these often costly circumstances, there is no guarantee of a full recovery. Please investigate any problems detected with database backups immediately and thoroughly to avoid catastrophic loss of data during an IT disaster. 

An example company following RFMS best practices for database backups to the best of their ability: 

• Flooring Company’s SQL Database is backed up through a third party program every night and a log backup is made every hour to not only keep the log file size as small as possible but to have the most up to date information when they need it. Their third party program is set up to only keep their chosen amount of data (for example, Flooring Company chose 3 weeks). 
• At 11:00 PM, another program performs an external backup of the .bak and .trn files created by the previously mentioned third party program for the past 24 hours. 
• At Midnight, another program backs up these same files to an external drive. 
• Every business day morning, the designated employee at Flooring Company checks each of these locations to make sure there is a new file matching the previous night’s date, and makes sure that there is room for more backups to be made. Flooring Company now has weekly image backups, and daily network/cloud/external drive backups of their data.

Best Practices for Folder Backups 

Our best practices are to keep a daily copy of the RFMS folder to minimize downtime during a migration or IT disaster to its lowest possible amount. Please configure the RFMS folder backup system to only run at a certain time/times in the day for the RFMS folder. Constant backups will slow RFMS performance considerably (for example, a Dropbox folder by default will automatically sync any files placed to Dropbox up to the latest version. Carbonite and other cloud backup systems can be configured in a similar way, and we have also seen them set themselves like this as default.) 

Best practices for keeping folder backups are to keep multiple backup sets as with the database backups system – simply keeping an extra copy on the hard drive is better than no copy but does not adequately protect against viruses, hard drive failure, etc. For example, an airtight system is to do a weekly server backup, a daily cloud backup, and a daily external backup (external drive/NAS/SAN/etc.). We realize it is not financially reasonable for all companies to use this method, and are not recommending expending money the company does not have, but using the best multiple-backup methods the company can afford is absolutely in their best interest – the more sets of backups they have, the more secure their data is against an IT disaster. 

An example company following RFMS’ best practices for folder backups to the best of their ability: 

-Flooring Company closes at 7:00 pm. Every night, two hours after they close, their RFMS Folder is backed up through the cloud. 

-Every night, at 10:00 PM, another program performs an external backup (external drive/NAS/SAN/etc.) of the RFMS folder. 

-At 1 AM that Monday, another program automatically makes a backup image of the server to a NAS/SAN/other server.

-Every business day morning, the designated employee at Flooring Company checks each of these locations to make sure there is a new file matching the previous night’s date, and makes sure that there is room for more backups to be made.

How to Test the Integrity of a Backup 

Currently, we recommend creating a practice database not only for the ease of training of employees, but for the testing of backups. If the company does not possess the document “How to Make a Practice Database”, please contact RFMS by sending an email to [e-mail].

This same document can also be used to make a third database and folder used explicitly for testing if the company desires unlimited flexibility for training and testing and has the resource capabilities to handle all three. 

To perform basic tests for backup integrity:

• Run “dbcc checkdb” on the restored database as a query inside SQL management studio.
• Open and close each core RFMS module.
• Briefly look over recent orders in order entry to make sure they are showing up.
• Open and save an order in order entry.
• If any errors show up at any point during these tests, reboot the server, make a new backup of the original RFMS database, and try again. If the errors persist, test again using the original RFMS database to compare. If the errors still persist, submit a ticket to [e-mail] for guidance. 

Some additional tests that can be run for further verification:

• Close the general ledger in accounting in the test db and examine results for Compare against the results of a soft-close in accounting in the active database to eliminate human error.
• Print a brief material analysis report.
• Print a brief management report in accounting. 

Please keep in mind there are no fool-proof methods due to unpredictable variables such as hard drive errors, failing memory sticks, or other hardware faults – but these tests have been proven rigorous enough for all environments observed thus far.

How to Test an Update’s Compatibility with a Company’s Environment

The same idea for testing backups can be used to fully test the compatibility of an update with the company’s environment. 

To do so, reboot the server, run the update on the practice folder (or testing folder, if the company has a third folder explicitly for testing) instead of the active RFMS folder, then perform the tests listed below on the updated practice/test folder:

• Print PO in order entry.
• Add an internal note in order entry.
• Run a brief material analysis report.
• Have an employee perform a common task in every module if possible.
• If any errors show up at any point of these tests, please reboot the server again, re-run the update, and test again. If errors persist, please submit a ticket to [e-mail] for guidance.

Annual SQL Database Administrator (DBA) Visit at Minimum, A Biannual Visit Is Recommended. 

While generally a certified SQL DBA will do as their experience dictates alongside Microsoft Best Practices, the following is a list of ten protections against the most common SQL issues we see, and should be things the SQL DBA is able to check during their routine visit.

Note: While we recommends a minimum of biannual database maintenance checks many clients find it beneficial to ensure maintenance tasks are scheduled to run more frequently, such as weekly, bi-weekly, or monthly, depending on your needs. 

1. Check tables for any abnormalities, particularly if one is unusually large.
2. Run the query “dbcc checkdb” on all RFMS databases or the equivalent maintenance plan)
3. Check for fragmentation, and to see if any indexes need to be rebuilt.
4. Look at SQL connections in SQL config – see if the network can be made to run more lean and if it would benefit (ie use only TCP/IP instead of named pipes, or vice versa)
5. Check log file size of RFMS databases.
6. Check mdf size of RFMS databases, make sure the company is not approaching the limits of their SQL version.
7. Make sure SQL compatibility mode is at least 2012 for all production RFMS databases.
8. Inspect the maintenance plans for all RFMS databases. 
9. Make sure the latest service packs have been applied for the instance.
10. Inspect SQL server error log. 

Repeat the above list for all RFMS instances in use in the environment. Keep in mind the above list is by no means comprehensive, but should cover the most common issues we see and serve as a deterrent from most potential emergencies or SQL-related performance issues. 

Even the most fine-tuned network will not be enough when the database itself is what is causing the issue! Please make sure to hire a qualified DBA to do the procedures in the above list. A company does not need to find one particularly familiar with RFMS – only one with the proper experience and qualifications to handle a SQL database. There should be no issues with a qualified DBA reviewing the database, a SQL database is a SQL database. 

RFMS does not keep a DBA on staff, we do have a company that we can put you in touch with, if one is needed. Please submit a request to [e-mail] and we will have an agent reach out to with information.  

Annual Certified Network Analyst Visit at Minimum, A Biannual Visit Is Recommended. 

While generally a certified network analyst will do as their experience dictates alongside Microsoft Best Practices, the following is a list of ten protections against the most common network issues we see, and should be things the Network Analyst is able to check during their routine visit: 

1. Check connection speeds across network – for optimal RFMS performance, all PC connections should be a gigabit.
2. Replace any NIC’s that are failing or beginning to fail.
3. Do the same for any bad cables or cables that are beginning to go bad.
4. Evaluate the integrity of the switch – are any ports going bad? Is the switch itself reliable?
5. Evaluate the compatibility of network hardware with environment.
6. Evaluate the security of the network.
7. Run a network scan to check for extraordinary Round Trip Times (RTT) for any particular machine.
8. Check ODBC and Cliconfg connections of problematic workstations – are they still using a connection to an old server and having to fail over each time to the new one?
9. Run reports against the fastest machines versus the slowest machines – ask employees with the fastest machines about the baselines estimates at the bottom of this Is everything truly as fast as it seems? Or are some merely less slow than others?
10. Evaluate connections between servers. 

Keep in mind the above list is by no means comprehensive, but should cover the most common issues we see and serve as a deterrent from most potential emergencies or network-related performance issues. 

Please keep in mind that while RFMS is available for contact during these visits, we can only answer compatibility questions in a general capacity (questions such as “do you recommend this particular model Cisco switch?” “Do you recommend this particular model SAN to run RFMS off the network?” are not things we can answer. We also cannot answer any questions from a security standpoint.)

Baseline Estimates, SQL Server Specifications 

Please keep in mind before reading our SQL Server Specifications – they are subject to change much as the features of our programs change and the features of SQL change and the insides of Operating Systems change and the bandwidth requirements of third party softwares change, etc. 

We recommend staying within Microsoft’s intended lifecycle of a product not just for security but for the ability to perform as intended. 

Our SQL server specifications document can be found here: RFMS Core System Recommendations – RFMS AUSTRALASIA

Based on information from our Quality Assurance department, here are some approximate expected load times for a given order in order entry for use as a baseline, given that the client is running at least RFMS version 15.1.2. We would like to also note that it is possible, though uncommon for it to run up to twice as fast as these baseline estimates.

# Of lines on the order	Time to open the order from workstation
~10 line order	3-5 seconds
25 line order	3-5 seconds
50 line order	6 seconds
75 line order	8 seconds
100 line order	10 seconds
125 line order	10 seconds
167 line order	10 seconds
201 line order	11 seconds
253 line order	12 seconds
310 line order	12 seconds
363 line order	13 seconds