Once a DBMS has been chosen and installed, before it can be used effectively standards and procedures must be developed for database usage. Studies have shown that companies with high levels of standardization can reduce the cost of supporting end users by 35 percent or more as compared to companies with low levels of standardization.
Standards are common practices that ensure the consistency and effectiveness of the database environment, such as database naming conventions. Procedures are scripts that direct the processes required for handling specific events, such as a disaster recovery plan. Failure to implement database usage standards and procedures will result in a database environment that is confusing and difficult to manage.
Database standards and procedures should be developed by the DBA and stored together in a central place. The database standards and procedures should be a component of corporate-wide IT standards and procedures. They can be stored as a printed document, in an online format for easy access, or in both ways. Several vendors offer "canned" standards and procedures that can be purchased for specific DBMS products.
One of the first standards to be implemented should be a set of guidelines for the naming of database objects. Without standard database object naming conventions, it will be difficult to identify database objects correctly and to perform the proper administration tasks.
Database object naming standards should be developed in conjunction with all other IT naming standards in your organization. Wherever possible, the database naming conventions should peacefully coexist with other IT standards, but not at the expense of impairing the database environment. For example, many organizations have shop standard conventions for naming files. The DBMS maps database objects to operating system files or data sets. To coordinate the database object to the operating system file may require a specific format for database file names that does not conform to the shop standards therefore, it may be necessary to make exceptions to existing shop file naming standards for database files.
Be sure to create and publish naming standards for all database objects that can be created within each DBMS used by your organization.
In general, do not impose unnecessary restrictions on the names of objects accessed by end users. Relational databases are supposed to be user-friendly. A strict database naming convention, if not developed logically, can be antithetical to a useful and effective database environment. Some organizations impose arbitrary length limitations on database tables, for example an 8 byte limit even though the DBMS can support up to 128 byte table names. There is no practical reason to impose a limitation that prohibits the length of database table names.
Deploy standard conventions that will make database object names as English-like as possible. However, you will inevitably encounter situations that require abbreviations. Abbreviations should be used only when the full English text is too long to be supported as a database object name or when the full text renders the object name unwieldy or difficult to remember. Create a list of standard abbreviations that must be used whenever an abbreviation is required and forbid non-standard abbreviations from being used. For example, if ORG is the standard abbreviation for ORGANIZATION do not allow other formulations (such as ORGZ) to be used. Using standard abbreviations will minimize mistyping and make it easier for users to remember database object names. Adhering to this practice will make it easier to understand the database objects within your environment.
Though database object naming standards are important, you will need to develop and maintain other types of database usage standards. Be sure to develop a comprehensive set of standards and procedures for each DBMS used by your organization. Each of the following areas should be covered in your DBMS standards and procedures guide.
The successful operation of a DBMS requires the coordinated management efforts of many skilled technicians and business experts. A matrix of database management and administration functions should be developed that documents each support task and who within the organization provides the support. The matrix can be created at a departmental level, a job description level, or even by individual name. A sample roles and responsibilities matrix is shown in Table 1. An “X” in the matrix indicates involvement in the process, whereas a “P” indicates primary responsibility.
Table 1. Database Support Roles and Responsibilities.
Database Usage Policy
Data Modeling & Analysis
Governance and compliance
Backup & Recovery
App Design Reviews
Of course, you can create whatever tasks you deem necessary in your roles and responsibilities matrix. You may need additional tasks or fewer than in this sample. For example, you may wish to differentiate between stored procedure development, testing and management, by creating a different task category for each and breaking down the support requirements differently for each.
Whatever the final composition of your roles and responsibilities matrix, be sure to maintain it by keeping it accurate and up-to-date with new DBMS features and tasks. An up-to-date matrix such as this one makes it easier to define roles within the organization and to effectively apportion database-related workload.
A basic set of DBA standards should be established to ensure the ongoing success of the DBA function. The standards will serve as a guide to the DBA services offered and specific approaches taken to support the database environment. For example, standards can be developed that outline how requests are made to create a new database or make changes to existing databases, specify which types of database objects and DBMS features are favored and under which circumstances to deviate, establish backup and recovery procedures (including disaster recovery plans) and communicate the methods used to transform a logical data model into a physical database implementation. An additional set of DBA standards that cover database performance monitoring and tuning may be useful to document procedures for overcoming performance problems.
Although the DBA standards will be most useful for the DBA staff, the application development staff will need them to learn how best to work with the DBA staff. Furthermore, any performance tuning tricks that are documented in the DBA standards should be shared with programmers, too. The more the application programmers understand the nuances of the DBMS and the role of the DBA, the better the working relationship between DBA and development will be – and that should result in a more efficient database environment.
Standards should also be developed for Data Administration, System Administration, Database Development, and Operational Support functions.
The DBA unit often applies and administers DBMS security. However, at some shops, the corporate data security unit handles DBMS security. You should provide a resource outlining the necessary standards and procedures for administering database security. It should contain the following information:
The minimum number of environments for supporting database applications is two: test and production. Some organizations, however, create multiple environments. For example, different environments can be created to support different phases of the development life cycle, including:
Unit testing – for developing and testing individual programs.
Integration testing – for testing how individual program interoperate.
User acceptance testing – for end user testing prior to production status.
Quality assurance – for shaking out program bugs.
Education – for training end users how to work the application system.
When multiple environments exist, procedures are required for migrating database objects and moving programs from environment to environment. Specific guidelines are needed to accomplish migration in a manner conducive to the usage of each environment. For example, what data volume is required for each environment and how is data integrity to be assured when testing activity occurs? Should data be migrated, or just the database structures? How should existing data in the target environment be treated – should it be kept or overlaid with new data? Comprehensive migration procedures should be developed to address these types of questions.
The migration and turnover procedures should document the information required before any database object or program can be migrated from one environment to the next. At a minimum, information will be required about the requester, why and when the objects should be migrated, and the appropriate authorization to approve the migration. As a part of the migration procedure, the implementer of the request should document the methods used to migrate and record the verification process to ensure the success of the migration.
All database applications should be subjected to a design review at various stages of their development. Design reviews are important to ensure proper application design, construction, and performance. Design reviews can take many forms, but there are seven basic design review phases that should be conducted for database applications:
Without appropriate standards it can be challenging to appropriately implement and use a DBMS within your organization. Standards help to guide development and minimize problems. Be sure that you have developed reasonable shop standards for your DBMS environments.