Database Administration Best Practices
The Business Side of DBA Services
Network Security for Remote Database Administration
Consumer Trends
The day-to-day (or operational) management of a client’s (or potential client’s) business data is a service that is required to effectively run that business. As a business grows from inception to market leader it usually follows the following database management trend:
| Stage of Business | Data Type | Data Size | Data Managed By |
| Created | On-line transaction (Retail, financial, ticket sales, manufacturing) | 0 – 5 Mb | Owner |
| Building | On-line transaction | 5 – 30 Mb | Unqualified employee |
| Established | On-line transaction | 30 – 80 Mb | Qualified Employee or The DBA Shoppe |
| Thriving | On-line transaction | 80 Mb – 1Tb Mb | The DBA Shoppe or Full-time certified DBA |
| Created | Decision Support (government, scientific, medical) | 0 – 30 Mb | Owner |
| Building | Decision support | 30 – 150 Mb | Unqualified employee |
| Established | Decision support | 150– 200 Mb | Qualified Employee or The DBA Shoppe |
| Thriving | Decision support | 200 Mb – 2 Tb | The DBA Shoppe or Full-time certified DBA |
Sectors within Industry
There are several sectors within the database administration services industry as follows:»Oracle Database vendor sector
Clients who use the Oracle database management system tend to be larger in company size and/or data set size. Oracle is both a comprehensive and expensive database option and is most often used by companies that understand the importance of their data to the operation of their business. Oracle vendor clients tend also to understand the importance of having professional database administrators manage their databases.
»Microsoft Database vendor sector
Clients who use the Microsoft SQL Server database management system tend to be mid-sized in company size and/or data set size. SQL Server is a historically less comprehensive and less expensive database option and is most often used by companies that understand the importance of their data to the operation of their business but do not fully accept the cost associated with proper management of that data..
»IBM Database vendor sector
Clients who use the IBM DB2 database management system tend to have either legacy mainframe systems that must be utilised and/or be a specific niche market that is best suited to DB2 (e.g. scientific). DB2 clients tend to fully understand the importance of their data but tend to come from the non-Windows mind-set, which makes finding qualified DBAs a challenge.
»Public domain Database vendors sector
Clients who use public domain database vendor solutions tend to have either a small IT budget and/or be a specific niche market that is best suited to the given system (e.g. scientific). Public domain database vendor clients vary greatly in their understanding of both the importance of their data and in their acceptance of the cost associated with managing that data.
The non-day-to-day (or project) tasks to manage a client’s (or potential client’s) business data are usually very likely to be completed in-house by a qualified employee or by a project out-source company like The DBA Shoppe.
Database Administration Best Practices
Database administration best practices include the following, regardless of DBMS platform:
I/O Management has historically been an essential best practice for database administrators using Oracle, SQL Server and DB2. The proper distribution of database files, whether they be Oracle datafiles or SQL server database files, was often paramount to successful operation and tuning of the database and, subsequently, the applications that were accessing the database. Although still an important best practice issue, the management of I/O has been reduced in importance due to the advent and improvements in central disk storage techniques and products in recent years.
Cursor Use Management is a tricky best practice issue to discuss across DBMS platforms as each vendor has their own peculiarities when it comes to cursor implementation and handling. Basically, it is critical to understand the proper use of cursors for the DBMS in which you are working and to assist application developers to understand and use cursors correctly whenever possible. Some indicators of poor or non-existent cursor use include the generation of dynamic SQL and poor return times for application processes that access large tables and/or record sets.
Table Scan Management is particularly important in databases that support applications that have interactive online or other high volume operations. Poor performance on table scan operations are often due to bad transaction statement design, poor SQL code design and/or missing indicies.
Connection Management is a problem that can have huge impact on the performance of a database and its supported application. Always ensure that calls to the database are not performed with individual connects for each call.
Disk Sorting Management is another tricky but critical best practices area across all DBMS platforms. Always make sure that as many sorts as possible occur in memory rather than on disk and make sure that indicies are in place on appropriate fields and that application SQL is optimized to best handle the sort data that will be returned from the database.
The Business Side of DBA Services
Database Administration is very interesting role within the IT department of a company or client. DBAs need to know their “stuff” technically and be able to translate that “stuff” effectively to colleagues including other DBAs, programmers who may be programming against the database, designers who may be considering adding to the database structure, network and sys admin staff, middle and upper management. Not always an easy soft skill set to develop! A DBA needs, more than most roles in IT, to be able to see the 10,000 foot big picture to understand and react (or even better proact) to requests and concerns from all directions and to be able to accept these concerns and requests objectively, professionally and efficiently. A good DBA is very much like parent in as much as decisions regarding the health and sustainability of their database charge must be made with limited information, conflicting reports and sometimes possibly questionable guidance. Database Administration is not for the faint of heart but a DBA that is able to meet the technical and communication requirements will find the role challenging, exciting and rewarding.
Network Security for Remote Database Administration
Network security for remote database administration…a mine field of discussion points. Suffice it to say that data is, in its nature, private, proprietary and essential. These characteristics demand that proper precautions be taken when accessing a database and when transferring the data via queries, etc. between the DBA workstation and the database server.
There are two basic ways to administer a database remotely while maintaining data security: VPN connection and dedicated lines. Whenever possible VPN technology should be utilized to maximize the use of well-established tools and utilities. When this is not possible (such as with mid-sized companies or IT departments) then proper connectivity should be set up, maintained and monitored closely on a daily basis.