Introduction to Database Maintenance and Optimization
IMPORTANT: Your database may not be getting backed up. If you are not on Aptora’s hosting plan, please read this important help topic.
Your Total Office Manager (and related software such as Aptora Mobile II) company information is stored in a Microsoft(r) SQL Server(r) file. That file needs regular maintenance and backups. Improper database maintenance is the leading cause of slow software performance and stability problems.
This article is meant for IT professionals familiar with MS SQL Server. It explains what database maintenance is and why it is important. We cover the basics of database backups and offer some database configuration tips.
IMPORTANT NOTE: Aptora takes care of this for all hosted (cloud) users. If you are hosted (on the Aptora cloud), you do not to read any further.
Summary of Steps
- Users need to setup a backup system within SQL Server. It is not sufficient to just backup the computer. If that is all you do, the company file (database) will NOT be backed up. If the hard drive fails, the company information is gone forever.
- They must back up Document Link related files.
- SQL Server must be carefully setup. There are a lot of options, settings, and preferences. Getting them wrong means slow performance or worse. Most companies and IT people get them wrong.
- They must also setup SQL Server so that it will run maintenance on the database including Reorganize index pages, rebuild indexes, update statistics on the indexes, shrink data and log files by removing empty pages, backup database and transaction log, perform internal consistency checks, and cleanup tasks.
- They must also carefully update MS SQL Server for speed, security, and stability reasons.
We take care of all of this for our cloud users. Read no more. :)
Tables, Queries, Indexes, and Keys
Your Total Office Manager data is stored in a Microsoft SQL Server Database. That database includes tables. Tables look a lot like worksheets in MS Excel. This is where the bulk of your database is stored.
When Aptora software needs information, it runs a query. A database query is a request for data from a database. Many times, the request is to retrieve data; however, data can also be manipulated using queries.
In order to make queries faster, databases include indexes. Inside indexes are Keys. Keys might be thought of as a “card catalog” or a set of bookmarks.
A database index allows a query to efficiently retrieve data from a database. Indexes are related to specific tables and consist of one or more keys. A table can have more than one index built from it. The keys are a fancy term for the values we want to look up in the index. The keys are based on the tables’ columns.
We Must Maintain Indexes
The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.
On the other hand, the Rebuild Index task is a resource-intensive task. In addition, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked until the rebuild is complete. As such, the Rebuild Index task is considered an offline activity, to be run when as few people as possible are accessing a database. In general, this means during a scheduled maintenance window.
Run Database Maintenance button
This process will rebuild and reorganize the indexes in your database. We provide a manual method for your convenience.
This process should be done automatically using a system that your IT person sets up with SQL Server Management Studio. You should also be performing a backup of your Total Office Manager database.
Backup Your SQL Server Database
This important topic should be discussed with your IT person.
We generally recommend that you use the Simple Recovery Model. This is done inside Microsoft SQL Server Management Studio. Backing up your server is NOT enough. You must create a backup file of your company database for your backup system to have something to back up.
SQL Server Simple Recovery Model
Source: https://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/
Explanation
The “Simple” recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the “Full” recovery model.
Here are some reasons why you may choose this recovery model:
- Your data is not critical and can easily be recreated
- The database is only used for test or development
- Data is static and does not change
- Losing any or all transactions since the last backup is not a problem
- Data is derived and can easily be recreated
Type of backups you can run when the data is in the “Simple” recovery model:
- Complete backups
- Differential backups
- File and/or Filegroup backups
- Partial backups
- Copy-Only backups
MS SQL Server Editions and Versions
There are several editions available. Most of our users will be able to use the free version called SQL Server Express. You should use version 2016. It is possible that version 2008 R2 will work but we do not guarantee it. We test using SQL Server 2016 and this edition is much faster than all previous editions.
Microsoft offers SQL Server Standard and Professional editions. The Standard edition is not free and will be required for database files that exceed 10GB. We recommend that you consider the Standard edition when your database file reaches 4GB. That extra margin will allow the Transaction Log file to grow during the day.
Compatibility Level
The compatibility level is associated with each database. It allows the behavior of the database to be compatible with the specific version of SQL Server it is running on.
Have you IT professional check your SQL Server Database Compatibility Level to make sure that it matches your SQL Server Version. This step is very important for speed and stability. It is also critical for Aptora’s software updates to properly update your company database. Aptora takes care of this for all hosted (cloud) users.
SQL Server Version and Native Compatibility Level
SQL Server 7.0 = 70 (Not Compatible)
SQL Server 2000 = 80 (Not Compatible)
SQL Server 2005 = 90 (Not Compatible)
SQL Server 2008 and R2 = 100 (Not Compatible)
SQL Server 2012 = 110 (Not Recommended)
SQL Server 2014 = 120 (Not Recommended)
SQL Server 2016 = 130 (Recommended)
SQL Server 2017 = 140
SQL Server 2019 = 150
Recommended Backup Software
SQL Server Standard Edition does not include the ability to back up its files. You can purchase an inexpensive program to do this for you. We use and like SQL Backup Master. We are not affiliated with the company that makes this product.
