- The DBA: Revered or Reviled?
- Why Learn Database Administration?
- The Management Discipline of Database Administration
- Database, Data, and System Administration
- DBA Tasks
- Types of DBAs
- Staffing Considerations
- Multiplatform DBA Issues
- Test and Production
- New Technology and the DBA
- DBA Certification
- The Rest of the Book
Test and Production
At least two separate environments must be created and supported for a quality database implementation: test and production. Completely separating the test and production environments ensures the integrity and performance of operational work. New development and maintenance work can be performed in the test environment while operational applications are run in the production environment. Failure to separate test and production will cause development activities to impair the day-to-day business of your organization. Errant program code in the early stages of development could access or modify production data and cause production performance problems or invalid data.
The test and production environments need not be identical. While the production environment contains all of the data required to support the operational applications, the test environment needs only a subset of data required for acceptable application testing. Furthermore, the test DBMS implementation will usually not command the same amount of resources as the production environment. For example, less memory will be allocated to buffering and caches, data set allocations will be smaller and on fewer devices, and the DBMS software may be a more recent version in test than in production (to shake out any bugs in the DBMS code itself before it is trusted to run in production).
The test and production environments should be structured similarly though. Both environments should have access to the same system software because the programming staff needs to create applications in the same type of environment in which they will eventually run.
DBAs may need to create multiple copies of databases in the test environment to support concurrent development by multiple programmers. Furthermore, the programming staff must be able to control the contents of the test databases. Because programmers may need to run data modification programs multiple times during the development process, they must be able to ensure that the data at the beginning of each test run is the same. Failure to do so can render the results of the tests invalid. Therefore, the DBA must assist the programming staff in the creation of database load and unload jobs to set up test databases. Prior to a test run, the database must be loaded with the test data. After the test run, the programmer can examine the output from the program and the contents of the database to determine if the program logic is correct. If not, he can repeat the process, loading to reset the data in the database and retesting. Automated procedures can be put in place to unload the databases impacted by the program and compare the results to the load files.
Predicting how test applications will perform once they are moved to production is difficult, but the DBA can assist here as well. A relational DBMS typically provides a method to gather statistical information about the contents of its databases. These statistics are then used by the relational optimizer to determine how SQL will retrieve data. (This topic is covered in more depth in Chapter 12.) But remember, there will be much less data in test databases than in production. In some cases, though, the DBA can set up scripts to read the production statistics and copy them to the test environment, thereby enabling developers to gauge more accurately how test applications will perform in production.
Some organizations implement more than two environments, as shown in Figure 1-10. If special care is needed for complex application development projects, additional levels of isolated testing may need to occur. For example, a unit test environment may exist for individual program development, while an integration testing environment ensures that new programs work together or with existing programs. A quality assurance environment may be needed to perform rigorous testing against new and modified programs before they are migrated to the production environment.
Figure 1-10 Establishing multiple database environments