Properly Manage RTA in a SQL Environment
- Jacob Turley
- LJ Sirrine
- Enoch Poston
How to make sure that your RTA data is safe
Summary
This article only applies to customers using Microsoft SQL Server 2012-2017 to store RTA's data. We strongly recommend the employment of a DBA who knows the structure and functions of SQL Server to create a maintenance plan for your SQL Server. This article was designed with the intent to show DBA's ways that help to improve performance and decrease down times and assumes you have a working knowledge of SQL Server Management Studio.
System Access Rights for RTA Application
As a matter of necessity for the RTA application to run and perform all of the functions it contains properly, the security settings on the database have to be open. We suggest that you make sure the user(s) have rights to access and update the two RTA databases (RTAFleet and RTALocks). You can use either SQL authentication or NT Authentication. Regardless, you will need to assign the db_owner role to this user/group on both databases (RTAFleet and RTALocks) as well as assign the Default Schema as dbo for both databases (RTAFleet and RTALocks).
Backup Strategy
One of the first things you will need to decide on is a backup strategy. You should be familiar with the different types of recovery models (Full, Bulk Logged, and Simple) and understand the pro's and con's of each. You should also be familiar with the different types of backups (Full, Differential, Log, and File/Filegroup) and how each works.
RTA strongly recommends setting the RTALocks db to Simple Recovery. This database contains all of the locked record information and does not need to be backed up. It can be recreated with a script provided by RTA and contains no critical data.
Users who do not need point in time recovery for RTA should choose to use the Simple recovery model for the RTAFleet db. If your system requires point-in-time recovery (where you can recover the database to a specific point in time) you have to use the Full recovery model. We have seen a number of users who have the recovery model of RTAFleet set to Full, but are not implementing Transaction Log backups. As such, the trans log will grow as large as it is able. If autogrow is enabled, it will grow until the physical disk is full. If autogrowth is not enabled, it will grow to the limit specified. In either case, you will eventually recieve a 9D/9002 error in RTA once the log file has grown too full. You will need to backup the log and possibly shrink it depending on the circumstances (ie if the physical disk is full, you will need to shrink the file before any other data can be written to the disk). If you are going to use the full recovery model, you must do Transaction log backups regularly in order to free up space in the log file and keep the overall size of the log file down.
We have found the majority of our customers do not need point in time recovery, so RTA's recommendation is to use Simple recovery for the RTAFleet db and to backup often using full and differential backups.
You will want to consult with your end users to identify how often data is entered into the system, and how much data they can afford to lose/re-enter. Identifying how much data you can afford to lose will help identify which recovery model to use, which types of backups to implement, and how often they need to be created.
For additional questions regarding backups, please contact RTA Tech Support.
A few good articles about backup strategy can be found here:
http://www.dbazine.com/sql/sql-articles/larsen12
MSDN Article: Backup Overview
http://msdn.microsoft.com/en-us/library/ms175477.aspx
MSDN: Backups using Simple Recovery
http://msdn.microsoft.com/en-us/library/ms191164.aspx
MSDN: Backups using Full Recovery
http://msdn.microsoft.com/en-us/library/ms190217.aspx
Index Fragmentation
If you have never rebuilt or reorganized the indexes within RTA you may have fragmented indexes. While fragmented indexes can slow down the system in some aspects, it will not be the primary reason for performance degradation.
That being said, you can choose to rebuild the indexes, or simply reorganize them to help report performance and assist the speed of some queries. Depending on your version of SQL Server, the commands to do this will be varied and it's recommended that a DBA familiar with them run these processes. It is in no way the responsibility of RTA to ensure that these processes are run, or that they are run correctly.
Viewing sys.db_dm_index_physical_stats will show you the fragmentation of your indexes within RTA. Please refer to MSDN documentation on these processes in order to understand what it is showing you.
Once you have found which indexes need to be reorganized (defragged) or rebuilt, you will need to find out which processes to run. Typically, running alter index reorganize on an index will reduce fragmentation considerably in an index. You can also choose to completely rebuild the index by using the alter index rebuild command. This is typically an offline only operation (RTA can't be in use) but can be run in an online mode on some versions of SQL Server. You'll need to reference MSDN documentation on these processes to see if your version qualifies.
There are stored procedures that you can find online as well that have been written to make this a more automated process if you would like to set up a recurring job to defrag your indexes automatically based on fragmentation. Links to articles we found helpful have been included below:
Article on Indexes: Overview and Maintenance
MSDN Article on Alter Index
http://msdn.microsoft.com/en-us/library/ms188388.aspx
MSDN Article on DBCC ShowContig
http://msdn.microsoft.com/en-us/library/ms175008.aspx
MSDN Article on sys.dm_db_index_physical_stats
http://msdn.microsoft.com/en-us/library/ms188917.aspx
SQL Server Configuration
There are preferred configurations when dealing with SQL Server that will help performance, but for most RTA systems, a standard setup will be more than sufficient. Documentation can be found on MSDN and SQL Server Books Online outlining the different configurations you can have (particularly hard disk configuration). RTA has no set stance on what you should or should not be doing, as it really all depends on your needs and environment.
Quite literally you could write a book (as many people have) with all of the different suggestions and parameters that you can tweak to optimize your SQL Server configuration. Again, it really all depends on your needs and your environment. Some questions this may shed light on would be: what type of RAID configuration should I use for SQL Server running RTA, where should I put my log files and data files, should I set up some type of mirror or log shipping? None of these things have a standard answer. If you are looking to implement some of these things and want to see how they will impact RTA performance, feel free to contact RTA Tech Support.
Related articles