Craig S. Mullins 

Return to Home Page

March 1999

 
Computing News&Review
 
ERP Requires DBA
By Craig S. Mullins
 
Enterprise Resource Planning (ERP) is all the rage these days. Some of the largest software vendors on the planet are offering ERP solutions. These include companies like SAP, Oracle, and PeopleSoft. An ERP solution is basically a packaged-application that provides enterprise level computing support for key functions such as human resources, manufacturing, and financials. The recent success of ERP is driven by a several key factors:

Organizations racing to fix the Year 2000 problem have turned to packaged application software to ensure compliance. Implementing an ERP solution can be less costly than trying to fix older, legacy systems that may be poorly documented, written in various languages, and difficult to decipher, let alone fix.
 
Another reason for the success of ERP centers on economies of scale. It is much easier for a dedicated software vendor to create, manage, and maintain development for a general-purpose application. Organizations have turned to ERP packages for computing functionality that does not deliver competitive advantage. The argument goes: "Why should I custom build an HR system (for example) that should be functionally very similar to most every other HR application in use at every other company? Perhaps we should let a specialist (ERP vendor) build it and maintain it. Then our company can concentrate on building the custom applications that will differentiate our organization and provide a competitive advantage." This enables banks (for example) to build systems to support banking, instead of "wasting" time building systems to support general, enterprise services.
 
Additionally, there are other factors influencing organization to implement packaged applications including business process reengineering, time-to-market pressures, and internal skill shortages.

But once the ERP applications have been purchased, implemented, and placed into use, they must be managed. And every major ERP application is built on top of relational database technology. If you have an ERP solution, you have a relational DBMS. And, if you have relational databases, you need database administration support.
 
Enterprises implementing packaged applications should solicit DBA participation in the package selection process to avoid future database management problems. It is wise to plan for DBA support before and during ERP implementation, instead of taking the reactive approach of implementing (or consulting) DBA after ERP implementation. This approach will ensure that an effective and appropriate infrastructure will be in place to manage the performance of the ERP application and its underlying databases.
 
Database administration needs to be involved in every step of the ERP implementation process. During the planning and selection phase the DBA needs to understand the database schema and the options for DBMS support. Most ERP vendors support multiple RDBMS products. The DBA function will be able to best decide which of the various options makes the most sense. For example, should this new PeopleSoft systems be implemented using SQL Server on Windows NT, Oracle on Sun Solaris, or DB2 on the mainframe. Each option has its strengths and weaknesses. The DBA can help to enumerate these and make the best decision for your organization based on the current environment.
 
Factors influencing the choice of DBMS include whether it is supported by the package vendor, whether the DBMS is already used in-house, the level of staff experience with the DBMS, how the DBMS impacts performance, and the relationship between the DBMS vendor and the package vendor. Avoid purchasing a new DBMS solely for the packaged application if a currently used DBMS will suffice. When choosing a DBMS, it is important to consider how rapidly new versions are supported and certified by the application vendor. Avoid migration to a new DBMS version until the application vendor certifies that the new DBMS software operates properly with the application. Slow certification can cause enterprises to delay migration to a new DBMS release or to support multiple versions of the DBMS.
 
A problem that occurs in some organizations is when there is no current DBA function in the organization. For some companies, the ERP application is their introduction to database management systems and relational technology. In this case, a DBA function should be created before the ERP system is selected to ensure that the right decisions are made. Failure to do so can result in bad decisions, which in turn can cause the ERP implementation to fail. If the implementation does not fail completely, other problems can occur afterward that are difficult to fix and could have been avoided with proper up-front planning. Examples of the problems that can occur include performance degradation, unmet service levels, and improper or non-existent backup and recovery plans. Consult Figure 1 for a listing of the potential causes of database performance degradation. All of these problems can negatively impact the acceptance of the ERP solution within the organization. And all of the problems are avoidable with proper up-front database planning.
 

Figure 1: Database Performance Tuning
The following items comprise the major database performance tuning opportunities, most of which still apply when tuning databases for packaged applications:
  • System Parameters: Concurrent Sessions & Users, Checkpoints, Locking, Logging
  • Memory Utilization: Processes, Database Kernel, Buffer Pool/Data Cache, Log Buffers, Other System Buffers, Sort Work Area
  • I/O and Disk Utilization: Raw Partitions vs. File Systems, Partitioning, Replication, Fragmentation, Extents, Free Space, Hot Spots, Row Chaining, Page Splits
  • Processor (CPU): CPU Speed, Parallelism, SMP/MPP, Resource Governors
  • Database Design: Physical File (e.g., Parameters, Layout, Size), Table Design, Data Clustering, Index Design, Normalization and Denormalization, Constraints (referential, check, unique, etc.)
  • SQL and Application Code: SQL Coding, Joins and Subqueries, Avoiding Sorts, Statistics, Optimization, Explain and/or "show plan", Static vs. Dynamic SQL, Stored Procedure/Trigger Compilation, user-defined functions


 
Probably the most important determinant of relational performance is creating an effective indexing scheme. Improperly defined indexes can significantly degrade application performance, particularly for high-volume OLTP applications. Although the actual code in the ERP application cannot be modified without risking the loss of vendor support, indexes can be added, removed and changed to optimize data access. Some packages enable customers to specify their own keys. In this situation, the DBA must create supporting indexes because the ERP vendor does not have the necessary information to supply the appropriate indexes. Yet, it is important to create only those indexes that are required to maintain uniqueness or enhance performance because irrelevant indexes incur DBMS overhead thereby degrading overall application performance.
 
Of course, this is only one example of how DBAs can optimize an ERP implementation. Tweaking any or all of the items in Figure 1 potentially can improve performance. As part of the planning process the DBA group will need to ensure that the appropriate database management tools are in place to support the ERP application. Although ERP vendors usually provide some level of DBA support within their products, most organization with heavy ERP usage plans and those with more than one database application will need to acquire more sophisticated database administration tools. Failure to do so may cause problems such as inflexible production data structures or data loss during recovery.
 
One of the first tools to be considered is usually a performance management tool. Though many ERP packages provide built-in performance monitoring capabilities, these are rarely sufficient for in-depth, enterprise-wide performance management. As usage of the packaged application grows, or if multiple packaged or in-house applications are deployed, agent-based performance management tools will be required. Many of these tools have specialized components for managing packaged applications.
 
Agent-Based Performance Management tools enable real-time, near-time (interval-based) and historical performance monitoring and analysis; reporting and graphing; drill down from system level to specific performance details; and the ability to automate tuning using thresholds and alerts. These tools are able to communicate with end users and other agents reacting to specific pre-defined events and initiating action to correct the identified problem situations.
 
Another area where DBA participation is key is release management and migration to new versions of the ERP package. Each new release of the package most likely will be vastly different than the prior release. Each company has its own specific database implementation encompassing database security, object sizes, customized views, indexes, and other differences from the vanilla ERP system. Finding these hundreds or thousands of differences can put quite a strain on the DBA requiring months of tedious effort. Unless automated tools are used that quickly pinpoint where customizations and differences exist between the new version of the database in the latest ERP version and a customer's already-implemented version of the software. Database change management, comparison, and migration tools exist that DBAs can use to minimize this effort.
 
Some administration options may be built into the ERP package. This may include rudimentary DBA tools for creating and altering database objects. When the package is the only application using the DBMS, this interface may be useful. However, the DBA functionality provided by such interfaces will be minimal, and most enterprises should plan to use the DBA tools provided by third-party tool vendors. It is imperative that the DBA knows about these ERP administration options and understands how to use them in conjunction with the other DBA tools that will need to be used.
 
Synopsis
Organizations implementing packaged applications should plan and budget for ongoing database administration as a key component of supporting the packaged application. The database administration function will ensure the acceptable, consistent performance for the packaged application, as well as ensuring an effective backup and recovery procedure, a workable database change management process, release management and migration, and enhancing overall data availability. To accomplish this requires a working knowledge of both the package itself and the DBMS used by the package. Without a mixture of these skills, the performance of the application is likely to suffer. In the worst case scenario, the ERP implementation may fail completely.
 
From Computing News&Review, March 1999.
 
1999 Mullins Consulting, Inc. All rights reserved.
Home.   Phone: 281-494-6153   Fax: 281-491-0637