From Craig Mullins' Database Administration: The Complete Guide to Practices and Procedures, Addison Wesley Professional; ISBN: 0201741296; 1st edition (June 14, 2002).

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5  |  Part 6

Multiplatform DBA Issues

Managing a multiplatform environment complicates the job of database administration. A whole batch of different problems and issues arise that need to be addressed. The first task is to define the scope of each DBA’s job. Does a single DBA administer all of the different DBMSs or does each DBA focus on supporting only one DBMS?

This is a particularly thorny issue. On the one hand, the functionality of a DBMS is strikingly similar regardless of platform and vendor. A DBMS is designed to store, retrieve, and protect data. Programmers, programs,and end users all inter-act with the DBMS to access and modify data. Administration issues are similar— design, creation, optimization, and so on—though each DBMS implements these items differently. So, the case can be made that a DBA should support multiple DBMSs and databases, regardless of platform or vendor.

On the other hand, each DBMS offers different features, functionality, and technology. Keeping all of the differences and nuances straight is a monumental task. Wouldn’t it be better to develop platform-expert DBAs? That way, your Oracle DBAs can focus on learning all there is to know about Oracle, your DB2 DBAs can focus on DB2, and so on.

Every organization will have to make this determination based on their particular mix of DBMSs, features, and DBA talent. If your organization uses one DBMS predominantly, with limited use of others, it may make sense for each DBA to support all of them, regardless of platform or vendor. Sparse usage of a DBMS usually means fewer problems and potentially less usage of its more sophisticated features. By tasking your DBAs to be multi-DBMS and multiplatform, you can ensure that the most skilled DBAs in your shop are available for all database administration issues. If your organization uses many different DBMSs, it is probably wise to create specialist DBAs for the heavily used platforms and per-haps share administration duties for the less frequently used platforms among other DBAs.

When DBA duties are shared, be sure to carefully document the skills and knowledge level of each DBA for each DBMS being supported. Take care to set up an effective and fair on-call rotation that does not unduly burden any particular DBA or group of DBAs. Furthermore, use the organizational structure to promote sharing of database standards and procedures across all supported DBMS environments.

Keep in mind, too, that when multiple DBMSs and platforms are supported, you should consider implementing DBA tools, performance monitors, and scripts that can address multiple platforms. For this reason, DBA tools from third-party vendors are usually better for heterogeneous environments than similar tools offered by the DBMS vendors.

When your organization supports multiple DBMSs, the DBA group should develop guidelines for which DBMS should be used in which situations. These guidelines should not be hard-and-fast rules, but instead should provide guidance for the types of applications and databases best supported by each DBMS. Forcing applications to a given DBMS environment is not a good practice. The guidelines should be used simply to assure best fit of application to DBMS. These guidelines should take into account:

• Features of each DBMS
• Features and characteristics of the operating system
• Networking capabilities of the DBMS and operating system combination
• DBMS skills of the application developers
• Programming language support
• Any other organizational issues and requirements

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 develop-met 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 environ-met to support concurrent development by multiple programmers. Further-more, 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 pro-g ramming 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.

Figure 1: Establishing multiple database environments.

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. 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. 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.

Previous — Types of DBAs
Next — New Technology and the DBA

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.