Databases Reference
In-Depth Information
12.5
Design Exploration between Test
and Production Systems
It's quite common for database designers and application developers to develop on a test
system, and use a distinct system for production. Usually the test system differs signifi-
cantly from the production system in many ways, including the following:
1.
The production system usually stores a full complement of real data while the test
system usually has a sample.
2.
The production system has real data, while the test system usually has either a
sample of old data or entirely synthetic data.
3.
The production system is intended to support higher volumes of data and con-
currency and there is often a larger physical system than the test system used for
development.
4.
The production system services real customers of the application and database
and therefore cannot be experimented on lightly, while the test system exists
largely for the purpose of tinkering and experimentation.
However, physical database design depends significantly on the schema, data, statis-
tics, and resources of the server where the database will be run in production database
designs developed on a test server with all these differences, which can result in database
designs that are suboptimal for the production environment. Conversely, running data-
base design utilities on the production server may introduce disturbance and risk to the
production server that is not acceptable in highly available 24-7 environments. For
example, the advisor may consume too many resources to perform the evaluation, or
cause locking contention. To solve this problem many automated design utilities allow
the following modes of operation:
1.
Execute the utility on a test server, resulting in an initial though possibly subopti-
mal database design.
2.
Export the schema and statistics of the production server to the test server so
that the test server can be used to explore database designs that are meaningful
on the production server.
3.
Execute the automated design utility on the production server, perhaps during
off-peak hours.
Figure 12.18 illustrates these possibilities. All of the modes of operation are commonly
used in industry today. In fact the idea of exporting schema and statistics to a test server
is also used by database vendors as a customer-service aid, so that problems detected at
