How to Create a Test System(SQL)

Owned by Enoch Poston
May 15, 2017
6 min read

This article describes how to create a test copy of RTA systems with an SQL Backend. It requires existing knowledge of Microsoft SQL Management tools. Our default DB names are RTAFleet and RTALocks. We will use those names throughout this document. You’ll need to know the names of your databases if you opted to change them from our defaults.

 Version 6.8

Step by Step Guide

  1. Make sure all users are out of RTA then copy the fleet folder from the production server to your test server. This is the folder that contains the .rta files. It should have a number of .cobs, .xfds, .bmps, as well. Make sure you have read, write, create, and delete permissions still once copying finishes. (You can also use a restore of a recent backup if you prefer. See the note in Step 4 if you do this)
  2. Find the cblconfi file in the new directory and open it in word pad. Verify that there is no file-prefix line. If there is, comment it out by placing a # in front of it. Close and save cblconfi.
  3. Restore the backup of RTAFleet from the current SQL server to the test SQL server. Do not attempt to use DTS or SSIS to move the data from one sql server to another. You must use the backup/restore method or the detach/copy/attach method. Each table has an Identity column that will lose it's Identity property when created through DTS or SSIS, resulting in a 9D/515 error when you attempt to add a new record to RTA. (Note: If you chose to restore a backup in step 1, you'll need to restore the db from the same date/time so that the database and application directory are in sync. ie If you restored the app directory from Friday night's backup, you'd then restore the db backup from Friday night as well)
  4. Run 9121_Locks_and_Procs_NOMASTER_Script.sql on the test SQL server. This is found in the test fleet directory. This script creates necessary stored procedures in the Master database that are required for the runtime to access your SQL server and creates the RTALocks db. If you receive a 9D/11 error when attempting to log in to RTA, it is either because this step was not run or you have the wrong version of the setup script. Please contact RTA if you have run this script and still get the error. Note, if you changed the db names from the default, your .sql file may be named differently as well.
  5. You’ll need to add your user to the SQL Server, and assign it to the db_owner role of the RTAFleet and RTALocks databases. Ideally this user would also be mapped to the dbo schema.
    1. Note that prior to SQL 2012, Windows AD groups could not be mapped to a schema.
  6. Next, we need to edit the SQL DB info otherwise you will still be pointing at live data! Open a command prompt, and go to your fleet directory. Run the following command:
    "wrun32 -c cblconfi -w dblogin.cob"
    The following screen will come up

    Make sure that the server name, database name, lock database name, and authentication credentials are correct. You’ll need to make sure that your user has been added to the server before clicking OK.

  7. Launch RTA by running the command
    "wrun32 -c cblconfi -w main2.cob"
    Or by running the client\setup.exe and utilizing one of the shortcuts created.
    Verify you can access records, and that changes made to the test system aren’t appearing in the live system.

You now have a stand alone copy of RTA, free from the production environment. You'll need to make a copy of your production shortcut and point it to the new test directory.

* You should not set a test copy of RTA up on the same sql server that hosts your live copy. You should place it either in another instance of sql server on that machine, or set it up on an entirely different machine that also has sql server installed.

 Version 7.0+

Step by Step Guide

  1. Make sure all users are out of RTA then copy the Fleet folder from the production server to your test server. This is the folder that contains the .rta files. It should have a number of “.cob”, “.xfd”, and “.bmp” files as well. Make sure you have read, write, create, and delete permissions still once copying finishes. (You can also use a restore of a recent backup if you prefer. See the note in Step 4 if you do this)
  2. Find the cblconfi file in the new directory and open it in notepad. Verify that there is no file-prefix line. If there is, comment it out by placing a # in front of it. Close and save cblconfi.
  3. Restore the backup of RTAFleet from the current SQL server to the test SQL server. Do not attempt to use DTS or SSIS to move the data from one sql server to another. You must use the backup/restore method or the detach/copy/attach method. Each table has an Identity column that will lose it's Identity property when created through DTS or SSIS, resulting in a 9D/515 error when you attempt to add a new record to RTA. (Note: If you chose to restore a backup in step 1, you'll need to restore the db from the same date/time so that the database and application directory are in sync. ie If you restored the app directory from Friday night's backup, you'd then restore the db backup from Friday night as well)
  4. Run 1000_Locks_and_Procs_NOMASTER.sql on the test SQL server. This is found in the test fleet directory. This script creates necessary stored procedures in the Master database that are required for the runtime to access your SQL server and creates the RTALocks database. If you receive a 9D/11 error when attempting to log in to RTA, it is either because this step was not run or you have the wrong version of the setup script. Please contact RTA if you have run this script and still get the error.
    1. Note: if you changed the database names from the default, your .sql file may be named differently as well.
  5. You’ll need to add your user to the SQL Server, and assign it to the db_owner role of the RTAFleet and RTALocks databases. Ideally this user would also be mapped to the dbo schema.
    1. Note that prior to SQL 2012, Windows AD groups could not be mapped to a schema.
    2. When you restore the SQL Test DB to the new server the previous SQL User credentials do not get copied from one instance to another. Meaning old “RTA_User” (or whatever SQL user login you were using) user login does not exist in the new SQL instance. Since this is true the “RTA_User” user Password is not valid on the new SQL server. What you will need to do is the following.
    3. Under the RTAFleet DB Security\Users grant another SQL user "dbo" rights to the RTAFleet and RTALocks DBs, say the "dbo" user.
    4. Remove the "db_owner" role from the “RTA_User” user under the RTAFleet DB Security\Users.
    5. Delete the RTA_Admin User from the RTAFleet DB Security\Users list.
    6. Under the SQL main Security\Users create a new user, you can re-use RTA_Admin here, and assign him the same roles and access to the RTAFleet DB as the previous login.
  6. Next, we need to edit the SQL DB info otherwise you will still be pointing at live data! Open a command prompt, and go to your fleet directory. Run the following command:
    “rta.exe /p:dblogin.cob” or “rta_wrun32.exe -c cblconfi -w main2.cob”
    The following screen will come up:


    Make sure that the server name, database name, lock database name, and authentication credentials are correct. You’ll need to make sure that your user has been added to the server before clicking OK.
  7. Launch RTA by running the command:
    "rta.exe"
    Or by running the client\setup.exe and utilizing one of the shortcuts created.
    Verify you can access records, and that changes made to the test system aren’t appearing in the live system.

You now have a stand alone copy of RTA, free from the production environment. You'll need to make a copy of your production shortcut and point it to the new test directory.


*You should not set a test copy of RTA up on the same sql server that hosts your live copy. You should place it either in another instance of sql server on that machine, or set it up on an entirely different machine that also has sql server installed. *