Craig S. Mullins

Return to Home Page

May 2006

 

 

 

                                           



The DBA Corner
by Craig S. Mullins

 

Database Standards and Procedures

 

Even though it has been said that the best thing about standards is that there are so many to choose from, corporate standards and procedures for using database systems are important for ensuring smooth operational applications and systems.

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.

Naming Guidelines: 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. In all cases, database naming standards should be developed in cooperation with the corporate data administration department (if one exists). Wherever possible, the database naming conventions should peacefully coexist with other IT standards, but not at the expense of impairing the database environment.

The database object naming standard should be designed to minimize name changes across environments. In general, do not impose any unnecessary restrictions on the names of database objects. 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.

Roles and Responsibilities: 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.

Your organization can create whatever tasks you deem necessary in your roles and responsibilities matrix. Whatever the final composition of your roles and respobsibilities matrix, be sure to maintain it by keeping it accurate and up-to-date with new DBMS features and tasks, as well as new roles (as required) and employees. An up-to-date matrix makes it easier to define roles within the organization and to effectively apportion database-related workload.

Administration Standards: If a data administration (DA) group exists within your orgranization a basic DA standards guide should be developed to outline the scope of their job role. If a DA group does not exist, be sure to include DA standards in the DBA standards as appropriate.

The data administration standards should provide a clear statement of the organization’s overall policy with regard to data including its importance to the company, as well as guidelines for establishing data ownership and stewardship. Other issues that need to be documented include rules for data creation, meta-data management policy, conceptual and logical data modeling guidelines, and the organization’s goals with regard to creating an enterprise data model.

A basic set of database administration 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 for system administration or systems programming are required if your organization separates the SA function from the DBA function. System administration standards are needed for many of the same reasons that DBA standards are required. Standards for SA may include DBMS installation and testing procedures, upgrade policies, maintenance procedures, and so on.

Database Security: 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:

  •  Details on what authority to grant for specific types of situations.

  • An authoritative list of who can approve what types of database authorization requests.

  • Information on any interfaces being used to connect DBMS security with operating system security products.

  • Auditing requirements and implementation guide (especially important in this day and age of regulatory compliance).

  • Procedures for notifying the requester that database security has been granted.

  • Procedures for removing security from retiring, relocating, and terminated employees.

Application Standards: The development of database applications differs from typical program development. You should take care to document the special development considerations required when writing programs that access databases. The database application development standards should function as an adjunct to any standard application development procedures within your organization. 

Additionally, application migration and turnover procedures are needed to support development and production environments – along with when, and how, programs are moved between the two. 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?

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.

Additional standards and procedures that may be required include design review guidelines for proceduralizing the approval of applications as they are built, and operational support standards to ensure the efficient and proper operation of the database environment.

At any rate, you can use the cases above as a starting point for assembling a reasonable set of database standards and procedures. Keep in mind, though, that the above just skims the surface of what should be included in a thorough database standards and procedures guide. Additional research will be required to ensure that your standards are proper and complete.

 


 
 

 

 

 

From Database Trends and Applications, May 2006.

2006 Craig S. Mullins,  All rights reserved.

Home.