Standard operating procedure automation in database administration

• US 7,571,225 B2
• Filed: 06/28/2007
• Issued: 08/04/2009
• Est. Priority Date: 06/29/2006
• Status: Expired due to Fees

• Current Assignee: Micro Focus LLC (Open Text Corporation)
• Original Assignee: Stratavia Corp. (HP Inc.)
• Inventors: Devraj, Venkat S., Luistro, Rainier L.
• Primary Examiner(s): Pwu; Jeffrey
• Assistant Examiner(s): Bruckart; Benjamin R
• Application Number:
  US11/770,454
• Publication Number:
  US 20080016123A1
• Time in Patent Office: 768 Days
• Field of Search: 709/223
• US Class Current: 709/223
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

A plurality of modules and/or engines to manage the operation and health of a plurality of databases through the use of standard operating procedures (SOPs). Modules of the present invention are configured to assist database administrators to create, and thereafter store in an accessible memory resource, a plurality of SOPs. These SOPs define procedures to address various database informational states. Database state information is monitored, collected and analyzed to determine, based either on a predetermined rule set or on manual input from an authorized database administrator, whether one or more SOPs will be applied to a particular database environment. These SOPs can be combined and/or executed on the database either automatically or manually.

Claims

1. A computer system for managing a database environment, the computer system comprising:

a machine capable of executing instructions embodied as software; and

a plurality of software portions, including

one of said software portions configured to create a plurality of standard operating procedures (“

SOPs”

) wherein each SOP defines one or more procedures to address a specific state of the database environment or perform a requested task within the database environment and is associated with a database type and each database type associated SOP is further associated with, and compatible with, database environment policies and wherein each database type associated SOP is compatible with each other SOP associated with that database type;

one of said software portions configured to create a plurality of task recipes wherein each task recipe includes a tasking request identifying at least one SOP;

one of said software portions configured to store in a memory the plurality of SOPs and the plurality of task recipes;

one of said software portions configured to monitor state information about the database environment including information to assess operational readiness of the database environment to execute at least one of the plurality of SOPs and information to enable real-time modification of each of the plurality of SOPs;

one of said software portions configured to determine whether said monitored state matches a predefined rule set and responsive to said monitored state matching said rule set automatically initiating a responsive tasking request;

one of said software portions configured to receive an external tasking request based on a task recipe;

one of said software portions configured to prioritize each tasking request based on database environment policies;

one of said software portions configured to identify a subset of the plurality of SOPs associated with each tasking request;

one of said software portions configured to retrieve the subset of the plurality of SOPs from the memory for each tasking request;

one of said software portions configured to prioritize each SOP in each subset of the plurality of SOPs and responsive to a conflict existing between two or more prioritized SOPs and/or tasking requests, resolving the conflict based on pre-established criteria;

one of said software portions configured to execute the subset of the plurality of SOPs on the database environment as prioritized

one of said software portions to determine whether execution of the subset of the plurality of SOPs on the database environment has addressed the associated tasking request.

2. The computer system of claim 1 wherein SOP creation includes the creation of a corresponding task recipe in human readable format.

3. The computer system of claim 1 further comprising one of said software portions configured to execute said tasking request based on a specific time interval.

4. The computer system of claim 1 wherein each rule set determines which parameters of state information about the database environment, or deltas in such information, trigger a SOP response of the responsive tasking request.

5. The computer system of claim 4 wherein one or more of said SOPs of said subset of the plurality of SOPs identified with said responsive tasking request receives one or more real time metadata injections regarding the database environment prior to and during SOP execution.

6. The computer system of claim 1 further comprising a software portion configured to collect and store at a central repository a result-set of data from said execution of said subset of the plurality of SOPs.

7. The computer system of claim 1 wherein SOP creation and modification is limited to designated senior database environment administrators.

8. The computer system of claim 1 wherein at least one of the plurality of SOPs is a task-based SOP.

9. The computer system of claim 8 wherein task based SOPs are configured to perform database administration tasks selected from a list consisting of database backup, database refreshes, database cloning, running a database health-check, managing database memory resource space, database coalescing, rebuilding fragmented database segments, analyzing database optimizer statistics, performing database root cause analysis, database patching, database upgrades, database migrations, and database server migrations.

10. The computer system of claim 1 further comprising a software portion configured to analyze the monitored state information to determine the database environment'"'"'s capability to conduct tasked operations.

11. The computer system of claim 10 wherein at least one of the plurality of SOPs is a healing SOP to correct the database environment'"'"'s failure to conduct tasked operations.

12. The computer system of claim 11 wherein the healing SOPs are triggered by altered database environment state information.

13. The computer system of claim 1 wherein SOPs are stored separate from the database environment.

14. The computer system of claim 1 wherein monitoring identifies database environment failure and creates an alert based on a pre-defined rule set.

15. The computer system of claim 1 wherein state information about the database environment is periodically collected for future analysis.

16. The computer system of claim 15 wherein analysis of database environment state information uses a plurality of mathematical models including linear regression, queuing theory, simulation, and fuzzy logic to predict future database state and performance.

17. The computer system of claim 1 further comprising a software portion configured to link a first subset of SOPs to a second set of SOPs to accomplish the requested task.

18. The computer system of claim 17 wherein subsets of SOPs include SOPs associated according to a particular database environment task.

19. The computer system of claim 1 wherein designated SOPs are executed automatically based on pre-determined state information of the database environment.

20. The computer system of claim 1 wherein a database administrator can monitor execution of SOPs at the database environment real-time and provide manual intervention.

21. The computer system of claim 1 further comprising a software portion configured to generate an audit trail of monitored database environment state information and SOP execution on the database environment.

22. A computer implemented method for managing a database environment, the method comprising:

monitoring state information about the database environment;

collecting state information about the database environment;

determining whether said collected state information matches a predefined rule set and responsive to said collected state information matching said rule set automatically initiating a responsive tasking request;

receiving said responsive tasking request based on collected state information about the database environment;

receiving an externally initiated tasking request directed to the database environment;

retrieving from a storage medium at least one of a plurality of standard operating procedures (“

SOPs”

) based on each received tasking request wherein each associated SOP defines procedures to address a specific state of the database environment or perform a requested task within the database environment and is associated with a database type and each database type associated SOP is further associated with, and compatible with, database environment policies;

prioritizing each SOP of the plurality of SOPs and responsive to a conflict existing between two or more prioritized SOPs and/or tasking requests, resolving the conflict based on pre-established criteria;

at the database environment, executing the at least one of a plurality of SOPs

determining whether execution of the at least one of the plurality of SOPs on the database environment has addressed the associated tasking request.

23. The computer implemented method of claim 22, wherein retrieving from said storage medium includes at least one task recipe corresponding to the at least one of the plurality of SOPs.

24. The computer implemented method of claim 22, wherein the responsive tasking request is automatically generated during runtime based on an analysis of collected state information.

25. The computer implemented method of claim 22, wherein the at least one SOP based on said responsive tasking request receives one or more real time metadata injections regarding the database environment prior to and during SOP execution.

26. The computer implemented method of claim 22, wherein the at least one SOP is linked with at least another SOP based on the responsive tasking request.

27. The computer implemented method of claim 22, wherein SOP creation and modification is limited to authorized database administrators.

28. The computer implemented method of claim 22, wherein the at least one SOP performs a database administration task selected from a list consisting of database backup, database cloning, running a database health-check, managing database memory resource space, database coalescing, rebuilding fragmented database segments, performing database root cause analysis, analyzing database optimizer statistics, database patching, database upgrades, data migration, and database server migration.

29. The computer implemented method of claim 22, further comprising analyzing collected state information to determine the database environments inability to conduct tasked operations based on a pre-defined rule set.

30. The computer implemented method of claim 29, wherein the least one SOP is a healing SOP to correct the database environments inability to conduct tasked operations.

31. The computer implemented method of claim 29, wherein responsive to determining the database environments inability to conduct tasked operations, creating an alert.

32. The computer implemented method of claim 22, wherein the analyzing of collected state information employs mathematical models including fuzzy logic, linear regression, ratio modeling or queuing theory.

33. The computer implemented method of claim 22 further comprising linking a first subset of SOPs to a second set of SOPs to accomplish the responsive tasking request.

34. The computer implemented method of claim 22, wherein the at least one SOP is executed at the database environment automatically based on pre-determined state information about the database environment.

35. The computer implemented method of claim 22, wherein a database administrator can monitor execution of the at least one SOP at the database environment real-time and when necessary manually intervene in the SOP'"'"'s execution.

36. The computer implemented method of claim 22, further comprising generating an audit trail of monitored database environment state information and SOP execution at the database environment.

37. A computer-readable storage medium tangibly embodying a program of instructions executable by a machine wherein said program of instruction comprises a plurality of program codes for managing a database environment, said program of instructions comprising:

program code for creating a plurality of database standard operating procedures (“

SOPs”

) wherein each SOP defines procedures to address a specific state of the database environment or perform a requested task within the database environment and is associated with a database type and each database type associated SOP is further associated with, and compatible with, database environment policies and wherein each database type associated SOP is compatible with each other SOP associated with that database type;

program code for creating a plurality of task recipes wherein each task recipe includes a tasking request identifying at least one SOP;

program code for storing in a memory the plurality of SOPs and the plurality of task recipes;

program code for collecting state information about the database environment including information to assess operational readiness of the database environment to execute at least one of the plurality of SOPs and information to enable real-time modification of each of the plurality of SOPs;

program code for determining whether said collected state information matches a predefined rule set and responsive to said collected state information matching said rule set automatically initiating a tasking request;

program code for receiving an externally initiated tasking request;

program code for identifying a subset of the plurality of SOPs associated with each tasking request;

program code for retrieving each subset of SOPs from the memory;

program code for prioritizing each SOP in each subset of SOPs and responsive to a conflict existing between two or more prioritized SOPs and/or tasking requests, resolving the conflict based on pre-established criteria;

program code for automatically executing the subset of SOPs on the database environment, and

program code for determining whether execution of the subset of SOPs on the database environment has addressed the associated tasking request.

38. The computer-readable storage medium of claim 37 wherein the program code for creating includes creating task recipes in human readable format corresponding to each SOP.

39. The computer-readable storage medium of claim 37 wherein said tasking request is generated automatically at runtime based on state information about the database environment.

40. The computer-readable storage medium of claim 37 wherein each rule set determines which parameters of state information about the database environment, or deltas in such information, trigger a SOP response associated with the tasking request.

41. The computer-readable storage medium of claim 40 wherein one or more of said SOPs of said subset of the plurality of SOPs identified with said tasking request receives one or more real time metadata injections regarding the database environment prior to and during SOP execution.