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