logo for information-management-architect.com
leftimage for information-management-architect.com

Database Management Best Practices

Improve effectiveness with David Bowman’s information management guidelines for database management best practices

This site is designed for Information Technology professionals who need  to improve effectiveness and require guidance and direction to help teams consistently produce error free results.

It provides information management guidelines for database management best practices and roles, responsibilities, tasks and deliverables.

Development Database Administrator

A development database administrator (DBA) is responsible for creating the various environments required for development, test and production. They also work closely with the data architect and help with capacity planning.  Because of the unique characteristics of data warehouse and business intelligence databases, the development DBA is often dedicated to the project, as opposed to being part of a pool of online transaction processing database administrators.

Development phase responsibilities include:
  • The creation and implementation of personal schema’s, development environments, integration test environment, quality assurance test and user acceptance test environments;
  • Executing data definition language (DDL) scripts, created by the data modeler, and creating the data base schemas;
  • Applying DDL changes to each environment as required;
  • Preparing data manipulation language (DML) scripts to backup and restore data that might be impacted by data model changes;
  • Creating test data from production data and manipulating it as necessary to meet data security requirements;
  • Creating back-ups of development and test databases and restoring as necessary to meet requirements;
  • Creating operations/infrastructure specifications;
  • Creating operations/infrastructure scripts; and
  • Working with development teams to optimize data base queries and performance.
Quality assurance testing responsibilities
  • Should promote DDL and DML from the development environment to the quality assurance environment. 
  • Should be heavily involved with quality assurance testing, as the development team should not have access to this environment.
  • Should include creating scripts to prepare data for testing, executing DML scripts, creating back-up/restores of databases as appropriate and performance tuning.
Release to production
  • The project DBA is actively involved with release testing to ensure that all portions of the database change are tested prior to moving to production.
  • They are also responsible for preparing the DDL portion of the release package. This is handed over to a production DBA, as part of the release process.
  • The production DBA is responsible for executing scripts in the production environment.
Production Database Administrator Roles
  • Should take ownership for the data warehouse as it transitions to production
  • Responsible for the physical instance of the development and production data warehouse environment
  • Should perform query optimization reviews as a measure of prevention and when performance incidents occurs that require immediate remedial action
  • Should manage performance, database utilization and growth monitoring and tuning, backup and recovery
  • Should establish database clustering and partitioning strategies
  • Should promote test databases to the production environment
  • Should implement and monitor backup and restore procedures
  • Should implement appropriate security scheme for the data warehouse
Database Management Best Practices

information management guidelines suggest that database administration standard be established to address the following:

Database Selection

As with all information technology tools, database management systems (DBMS) should  be selected from an approved list of information management technology standards. The selected RDBMS technology is a key systems architecture decision and shall be clearly documented in each system’s Architecture Specification

Once the platform has been selected from the information management technology standards, all production and non-production databases should adhere to the technical requirements

Database Management Best Practices for Data Ownership

The appropriate business executive supported by the database should assign a database owner and database guardian for all databases. These assignments should  be documented, published and maintained.
  • All data within a production database should be assigned a data guardian and data steward as defined in the in the information management metadata standard
  • All databases used to support or execute business processes; production and non-production databases for the organization should be registered and assigned an application database administrator and a production support database administrator;
  • Database administrators (DBA’s) should be responsible for creating, maintaining, upgrading and restoring all production and non-production databases;
  • DBA’s should be responsible for creating, maintaining, upgrading and restoring their respective databases;
  • Database owners should maintain and approve the inventory of all of the databases for which they are accountable that support or execute business production processes.  This inventory should  be maintained within the information management configuration management database and should include the assigned database administrators, database owners and database guardians.
Database Objects
  • Objects (tables, indexes, views, etc.) created in a production database should be named in compliance with an approved  data model standard
  • Names, definitions and other metadata for objects created in a production database should be submitted to the appropriate metadata repository following approved procedures.
Database Security
  • All production, quality assurance and test databases should be compliant with an approved Data Access Policy;
  • Developers should have no access to production data;
  • Access to production data should comply with requirements defined in an approved Data Access Policy
Data Archiving, Purging and Retention
  • Archiving and purging data within a production database should comply with approved requirements;
  • The data purge and/or archive processes should be included as part of all production database implementations;
  • Data purging should be performed using approved and controlled batch processes; and
  • Data retention processes should comply with the procedures identified in approved requirements.
Database Management Best Practices for Data Usage
  • All data should be used only for business purposes;
  • Production data containing any personal information should not be propagated to non-production databases;
  • Data classified as confidential or proprietary should  be unavailable in non-production databases; however, data classified as such may reside in Quality Assurance and Operational Readiness Testing environments if in compliance with an approved Data Access Policy
  • Data contained in non-production tables or databases should not be used in the production environment or processes;
  • User-owned data and data within user-owned tables should not be used in the production processes unless implemented in processes which are compliant with Change Management Control Standards
Data Exception Handling
  • Production databases should have automated mechanisms to ensure referential integrity;
  • All data errors and data exceptions should be documented, analyzed and evaluated for the purpose of detecting database or data quality issues;
  • Data exception handling should be capable of generating metrics that can be monitored by operators to provide insight into the current health and status of data within production databases;
  • Data movement processes should  comply with the procedures identified in an approved Data Movement Standard.
Summary...

The data storage discipline focuses on the management of persistent data stores. It covers such topics as database selection guidelines, backup and recovery procedures, data retention/archiving policies, and disaster recovery planning.

This site provided information management guidelines for database management best practices, roles, responsibilities, tasks and deliverables.