Create A Customized Database Performance View In Dbwatch

About the Author:

Chad is a Pre-sales Engineer for dbWatch and a DBA who specializes in SQLServer high availability setups and disaster recovery planning and configurations. He is an AWS Certified Solutions Architect Associate, a cloud enthusiast who specializes in architecture and designing scalable, high available, and fault-tolerant systems on AWS.

As a database administrator managing many different database instances on various platforms, it can be difficult for you to track various aspects of your performance within your database farm. You need help from specialized tools to monitor your database key performance metrics, a database monitoring solution that offers the functionality for customization and allows you to view the most important metrics you need in a single window.   

Creating a view is not that complicated, like creating and modifying views in SQL Server and Oracle databases. With dbWatch, you can easily create, customize, or modify existing views. You can also incorporate your existing performance scripts and display it on a single view in dbWatch.  

This section will show you how you can create a custom view using dbWatch properties and your existing monitoring script.   

In this example, we will be creating a dbWatch property and sub-properties (using dbWatch EM12), which will be used to create a custom view where we could monitor all sessions running in our SQLServer and Oracle instances.  

  1. Download and update the XML Template you will use and modify depending on the performance counter you would like to create as a view and Monitor.
    (Note: read the REPLACE comments on the XML file)
  2. After modifying the document, save it as an XML file
  3. Go to your dbWatch Monitor. At the top3, choose Configure and click Upload Resource.

4. A new window will appear; choose the xml file we have modified then click Upload. 

   5. After modifying the document, save it as an XML file 

          6. A new window will appear which helps you create a new tab and a view.   

Please specify the name of the view in this example: I will name it as Oslo-Cluster Session Info.  Click OK. 

7. A new window will appear, where you input the FDL(Farm Data Language) query to retrieve the columns from the XML file we’ve modified. (FDL is a powerful language dbWatch has developed to help you query and retrieve data across database platforms. Learn more about FDL by clicking here)  

Earlier, we have configured our select query for both Oracle and SQLServer within the XML file template. We will select all the columns and data from SQLServer and Oracle session tables in the question below.  

FDL Query:  

$instance->i/session_id{“Session ID” #hide}->sid  

/$I/name{“Instance”}  

/$sid/username{“username”}  

/$sid/logon_time{“logon time”}  

/$sid/db_name{“database”}  

/$sid/status{“status”}  

/$sid/host{“host”}  

/$sid/program{“program”} 

    8. At the top, choose File and click Save. 

The new view contains the different columns which we would like to display has been created.   

By following these steps, you can easily create dbWatch properties and customize your performance view based on your existing performance script.  

If you have any questions or would like to know more on how dbWatch can assist you in your current enterprise database monitoring and management situation.  

Please contact us through the email: chadwick@dbwatch.com  

For more information, visit www.dbWatch.com or wiki.dbWatch.com  

Other Blogs:

dbWatch Control Center for managing Database Farms

About the Author:

Chad Pabalan is a Pre-sales Engineer for dbWatch and a DBA who specializes in SQLServer high availability setups and disaster recovery planning and configurations. He is an AWS Certified Solutions Architect Associate, a cloud enthusiast who specializes in architecture and designing scalable, high available, and fault-tolerant systems on AWS

Introduction to Database Farms 

Companies use different database management systems to store large amounts of data for processing and analysis; some companies have massive data warehouses (OLAP) for analytics and huge database farms that cater to different Online Transaction Processes (OLTP) in the enterprise. 

In small businesses, they start with a few database servers that a database administrator is managing. The DBA monitors these servers manually or by using his customized scripts as he investigates the servers one by one to check for the health and performance of their databases, to make sure that every server is running efficiently and serving its purpose.   

And as years pass by, the company grows, and from a small group of database servers, it becomes a vast database farm that shares resources to form a cluster. The DBA doesn’t have the spare time to remote into each of the servers manually one by one to check each database’s health status.  

The DBA needs a dedicated solution to support him in his day-to-day work monitoring and managing the whole database farm. His customized scripts for a few servers that he was using back then might not be as effective in tracking or handling the current database farm he manages currently. 

A database farm comprises multiple database servers, instances of various platforms, and versions used in an enterprise to support their day-to-day business operations or functions.    

As a DBA managing a colossal database farm, one of the most important things is to have a complete overview of your database farm.

You should be able to answer questions such as:

– What is the health status of all databases/instances? Which of the databases needs attention right now, and which should I prioritize? – How many disks, memory, and CPU cores been allocated to the database farm?
– What is the resource usage rate for each instance?
– How many instances exist in my database farm?
– What are the database platforms and versions running in the database farm?

In large virtualized environments, resource allocation and utilization is an issue. As a DBA,
you should carefully analyze all the resources allocated within your database farm; you should have a clear overview of how the various resources like your disk, memory, and CPU are utilized in your database farm. You need to determine if your servers have been over or under-provisioned with resources.

Documenting a huge database farm is also a big issue as the business grows your database farm grows. With proper documentation, you have up-to-date information on your databases in the database farm. It makes maintenance easier, helps with management or DBAs decision-making, and diminishes risk. It’s more straightforward to make an assessment using your database documentation as a guide. As a DBA managing a database farm, you don’t have time to create your database farm’s complete documentation manually. It would be best if you had tools to automate the documentation process.

In your database farm, you need to ensure that all your backup jobs are running optimally and running as scheduled. You need to have complete control over your databases’ backup and recovery plan; it is essential if an unexpected situation like corrupted databases, sudden server failure, or other forms of data loss occurs. As a DBA, you will use existing backups to restore lost information to the system. 

Imagine having a database farm with hundreds or up to thousands of instances. As a DBA, do you still have enough time to remote into each database server to know their health status? 

Whether you are a  DBA or a manager, you need a top-level view of what’s happening in your database farm and an option to drill down into the specific instance when manual intervention is necessary.  

dbWatch Control Center offers, in addition to the traditional database instance monitoring functionality, a feature-rich database farm solution to monitor, manage and control your database farm. 

dbWatch Control Center offers the latest database farm management functionality, enabling DBAs to view all your instances in single views. The image below shows that Control Center displays total instances and databases with their corresponding platform and versions, providing you a top-level overview of your database farm. 

dbWatch Control Center helps users with their capacity and resource planning projects.   

dbWatch Control Center has built-in views that display the capacity overview like your database resource consumption in the database farm.   

This overview provides DBAs and managers information to determine which instances consume the most or least memory resource in the database farm.   

dbWatch Control Center gives you clear information on which instances have excess resources allocated to them, which you can reallocate to instances that have more need for them. 

This view displays the total disk usage in the database farm for you to determine which instances have the most significant number of disks or volumes allocated in the instance; it also shows the usage rate per volume.  

dbWatch Control Center is a complete database farm monitoring and management solution, which offers automation, consolidated views, and performance reports giving you the key information you need at your fingertips. Automating your routine tasks help you save time and effort on manually managing databases in your farm by focusing on more critical tasks on hand. At the same time, Control Center does all the proactive monitoring of your database farm, allows you to have complete control over your database farm. 

Control Center offers the following: 

– Farm overview – consolidated views for performance, capacity, and maintenance for your entire database farm
– Monitor in-depth all your instances and platforms
– Performance – identify performance bottlenecks within the farm
– Scalability – ability to adapt with your growing database farm
– Alerting – alerting and third-party extensions tailored to your business requirements
– Security – role-based access controls, AD and Kerberos integration encrypts connections and supports security certificates

Summary 

With dbWatch Control Center as your partner in monitoring and managing your database farm helps you achieve the following: 

– Proactive monitoring displays a health overview of all databases within your database farm. Provides you the necessary information for you to take proactive action against possible issues.

– Provides complete information of your database farms’ total allocated hardware and resource utilization like your CPU, Memory, and Disk usage.

– Displays information on all instances in the database farm.

– Auto-discover instances on specified network ranges.

– Delivers information for performance metrics and identify which database platforms and versions are running in your database farm.

Try Control Center today! https://info.dbwatch.com/download-dbwatch-controlcenter 

 

For more information, visit www.dbWatch.com or https://wiki.dbwatch.com/ControlCenter/ 

Other Blogs: