ONGOING MAINTENANCE AND GROWTH - Database Design and Development: An Essential Guide for IT Professionals - page 531

Let us list a few significant issues relating to the test or development database
environment.
Initial creation. Create the initial test database system by copying the production
schema. The test system will start out as a replica of the production system.
Data from production. Move a sufficient number of rows from production tables to
corresponding test tables. It is not practical to have the entire contents of the pro-
duction database copied into the test database. However, you must have enough
data to support adequate testing of application enhancements. Have a suite of
special programs to select related records from various files. For example, if you
select 1000 rows from the customer table, you must select the related orders, not
random ones, to populate the orders table. Also, the suite of programs will usually
have programs to copy full reference and code files. For example, you would want
to copy the entire product file because the selected orders may contain many sets
of products.
Test data. In addition to selected data moved from the production database, from
time to time, special test data need to be created to test specific conditions. Have
programs ready for creating special test data.
Test database operation. The production database is up and running every day
during the designated hours. Should the test database be also up and running every
day? Most companies discover the necessity of keeping the test database up every
day to support the testing of application enhancements. Similar to the production
job that is used to start the production database every day, a test start-up job is
created to point to the test database files.
Test database maintenance. While test versions of application programs access the
test database, very often, program malfunctions corrupt the database. As the test
versions of the programs are still being tested, they are not free from error condi-
tions. After some time, the test database gets so corrupted that it becomes useless
for further testing. Therefore, periodically, you have to reload the test database from
the production database and recreate any special test data.
Duration of test system. Is there a life span for the test database? Should it be kept
indefinitely? Almost all organizations with database systems carry a test system
indefinitely. However, the composition and data content are likely to keep changing.
Test program versions. Ensure that the test program versions are restricted to
access only the test database and never the production database. Only after the
programs are fully tested and approved, should they be promoted to access the
production database.
Recovery. When failures occur in the test environment, should the test database be
restored? Usually, this is not done except when you want to test your recovery pro-
cedure. Otherwise, simply refresh the test database with selected production data
and proceed.