| Column | |
|---|---|
|
How to make sure that your RTA data is safe
...
This article only applies to customers using Microsoft SQL Server 2000/2005/2008 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.
...
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 over all size 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.
...
Viewing sys.db_dm_index_physical_stats (this replaces dbcc showcontig in SQL Server 2005 and later) 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. Note that alter index reorganize replaces dbcc indexdefrag in SQL Server 2005 and later. You can also choose to completely rebuild the index by using the alter index rebuild command. Please note this replaces the dbcc dbreindex command in SQL Server 2005 and later. 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 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.
We also highly recommend reading "SQL Server 2005: Implementation and Maintenance" by Solid Quality Learning. It's published by Microsoft Press as part of their Exam Prep series. 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.
...