virtuallyGhetto

vCenter Server Database

VCSA alarm for VCDB space utilization in vSphere 6.5

by 4 Comments

With prior releases of the vCenter Server Appliance (VCSA), there was little to no visibility to the underlying vCenter Server Database (VCDB) which uses an embedded vPostgres Database. This was especially true for being able to get basic storage utilization of the VCDB including the breakdown of the different data types being stored. More importantly, there was no easy way to even monitor the storage utilization of the VCDB to help prevent the rare case where the VCDB could be filling up for whatever reason.

In vSphere 6.5, there have been huge amount of improvements to provide customers with greater visibility into the VCDB. Not only can customers get granular into the specific types of data being consumed: Stats, Events, Alarm & Tasks (SEAT), Transaction Log & VC Inventory within the VCDB, but this information can also be easily accessed both from a UI as well as API (using the VAMI REST API) standpoint. The Virtual Appliance Management Interface, better known as the VAMI for the VCSA has received a huge face lift in vSphere 6.5. As you can see from the screenshot below, there is now a Database section which gives you the current utilization of your VCDB. In addition, you can also see how this utilization trends over time for the various data types.

vcdb-space-utilization-vcenter-alarms-1
From a reporting and visibility standpoint, this is great but how do you go about operationalizing this data and ensuring that you do not run into situation where your VCDB is out of space or is close to being out of space? Another improvement that has been made to the VCSA 6.5 is that there is now a default vCenter Server Database Health alarm that will monitor the space utilization of your VCDB.

vcdb-space-utilization-vcenter-alarms-0
The way in this work is that system will check the VCDB space utilization every 15minutes with the following trigger events defined:

  • If the current storage utilization is at 80%, a Warning alarm will be triggered
  • If the current storage utilization is 95%, an Error alarm will be triggered and the action is to shutdown the vCenter Server application to protect the database

These default triggers can be changed by simply editing the following vCenter Server advanced settings: vpxd.vdb.space.errorPercent and vpxd.vdb.space.warningPercent (restart of VC service is not required).

vcdb-space-utilization-vcenter-alarms
Customers can also extend these alarms to send an additional email and/or SNMP trap to their monitoring system so that not only is this visible in the vSphere Web Client but the appropriate administrators can also be notified. The above is just one of the many improvements the VCSA 6.5 has received and I definitely recommend customers spend some time looking at what is now available in the VAMI UI as well as being able to pull this information using our new VAMI REST API.

vCenter Server Database retention purge schedule

by 5 Comments

The size of your vCenter Server Database is largely based on the amount events/tasks and performance statistics that you retain for your vSphere environment. You can view and edit these settings by going to the vCenter Server "General" settings as shown in the screenshot below (documentation here and here):

vcenter-server-data-retention
A common misconception when changing any one of these retention policies, especially when decreasing the amount of data to be retained, is that the existing data would be purged immediately to comply with the new settings. This is actually not the case and for data that is applicable for removal, there are a set of purge jobs that run on a specific schedule to perform the clean up. Below is the schedule in which these database jobs run for each of the data types:

Performance Statistics:

  • Daily Level - Once every 30 minutes starting at 00:00 (e.g. 00:00, 00:30, 01:00, etc.)
  • Weekly Level - Once every 2 hours starting at 01:45 (e.g. 01:45, 03:45, 05:45, etc. )
  • Monthly & Yearly Level - Once a day at 02:15

Events and Tasks:

  • Once a day at 00:15

For customers that are looking for immediate results and reclaim storage from within their VCDB, you can take a look at the following VMware KB 1025914 which outlines the specific instructions. This can especially be useful if you are looking to perform a Windows vCenter Server to vCenter Server Appliance Migration and wish to reduce the overall amount of data that is being copied over from your existing environment.

How to check the size of your Config & SEAT data in the VCDB in vPostgres?

by 2 Comments

After publishing my article on how to check the size of your vCenter Server's Configuration and Stats, Events, Alarm & Tasks (SEAT) data for both a Microsoft SQL Server and Oracle based database, I had received a few requests for doing the same for the vPostgres database which the vCenter Server Appliance (VCSA) uses exclusively. Thanks to one of our Engineers who works on the VCDB, I was able to quickly get the relevant SQL query to perform the exact same lookup as the other two databases.

Since the VCSA is harden and locked down by default, being able to remotely retrieve this information will actually require some additional configuration changes to your VCSA which may or may not be acceptable. Because of this constraint, I will provide two options in how you can perform this SQL query.

The first option (easy) will be running the SQL query directly from within the VCSA. You just need SSH access and no other information or credentials will be required. The second option (complex) will be to remotely connect to the vPostgres database (generally not recommend) which will require the VCDB's credentials which I will show you how to retrieve. Lastly, I want to quickly mention that in the upcoming vSphere 6.5 release, this information will be super easy to view not only from a UI but also API as shown in tweet below.

 

Option 1:

Step 1 - Download the following shell script called queryVCDBvPostgres.sh which contains the respective VCDB SQL query.

Step 2 - SCP the shell script to your VCSA and then login via SSH.

Step 3 - Run the following command to make the script executable:

chmod +x queryVCDBvPostgres.sh

Step 4 - Run the script by issuing the following command:

./queryVCDBvPostgres.sh

Here is a screenshot of what you should see which is a break down of your Config + SEAT data:

query-vcdb-config-seat-data-vpostgres-0

Option 2:

Step 1 - Login to the VCSA using SSH.

Step 2 - Edit /storage/db/vpostgres/postgresql.conf and add the following entry:

listen_addresses = '*'

This will allow vPostgres to be connected to from any address or if you want to restrict it to a specific IP, you can also just specify that.

Step 3 - Edit /storage/db/vpostgres/pg_hba.conf and add the following entry:

host    all             all             172.30.0.0/24            md5

Similiar to the previous configuration, you can either specify a network range using CIDR notation or a specific IP Address.

Step 4 - Edit /etc/vmware/appliance/firewall/vmware-vpostgres and replace it with the following entry:

This will open up the VCSA's firewall to allow remote connections to the vPostgres port which the default is 5432.

Step 5 - Next, we need to reload the firewall configuration by running the following command:

/usr/lib/applmgmt/networking/bin/firewall-reload

Step 6 - We can verify by running the following command:

iptables -L | grep postgres

Here is a screenshot of what you should see as the output:

query-vcdb-config-seat-data-vpostgres-2
Step 7 - Lastly, we need to restart the vPostgres service by running the following command:

service vmware-vpostgres restart

Step 8 - To verify that you can now remotely connect to the vPostgres DB, run the following command:

netstat -anp | grep LISTEN | grep tcp | grep 5432

Here is a screenshot of what you should see as the output:

query-vcdb-config-seat-data-vpostgres-3
At this point, you have now enabled remote connections to the VCSA's vPostgres DB. The next step is to retrieve the VCDB credentials which you will do so using a PowerShell script that I have written to perform the remote SQL query. This will also require that you setup an ODBC connection on your client system to communicate with the vPostgres DB. Please have a look here for more information on how to setup the ODBC connection.

Step 9 - Login to VCSA via SSH and then look at the /etc/vmware-vpx/vcdb.properties and you should see the password to your VCDB. Go ahead and record this some where as you will need it in the next step. The username for the DB will be vc which you can also make a note of.

Step 10 - Download the following PowerShell script called Get-VCDBUsagevPostgres.ps1 and provide the connection details that you retrieved in Step 9. If everything was properly configured, you can run the PowerShell script and it should produce a similiar output as shown in the screenshot below.

query-vcdb-config-seat-data-vpostgres-1

How to check the size of your Config + Stats, Events, Alarms & Tasks (SEAT) data in the VCDB?

by 6 Comments

I think many of you know that I am not a fan of anyone poking around in the vCenter Server Database (VCDB) and having to manually craft SQL queries to retrieve information about their vSphere environment. This is especially true when you can easily and painlessly retrieve all of this information by simply using the vSphere API.

Having said that, there is one use case that is currently not available today in the API, yet. The use case that I am referring to is having better visibility into the storage utilization of our VCDB for things like the Core inventory configuration as well as the Stats, Events, Alarms and Tasks (SEAT) data which generally makes up the bulk of the VCDB data. Some of the benefits to having this information includes understanding the size of your VCDB given your current inventory size + data retention policy, whether or not you should consider reducing/truncating your dataset and even ensuring that vCenter Server rollup jobs have properly ran by simply getting visibility into the current storage footprint of your VCDB.

The other really nice benefit of having this information for those looking to use the recently released VCSA Migration Tool (migrating from Windows vCenter Server to the vCenter Server Appliance) is that it can be used to help calculate the estimated amount of downtime that is required for the migration to complete. The process is currently outlined in the following VMware KB 2146420 which requires customers to manually run a specific SQL query to retrieve the specific tables within the VCDB, perform some basic arithmetic with the results and then plugging them into an excel spreadsheet to provide the time estimations for migration.

UPDATE (10/20/16) - For customers already on the VCSA, you can also perform this query for vPostgres DB by taking a look at this blog post here.

Note: The migration time estimates from VMware are just that, estimates. There are many other factors such as source and destination hardware capabilities, network and storage bandwidth that may influence the amount of time a migration may take. It is recommended that customers use the estimates as guidance and still add a time buffer to their maintenance window.

To help simplify the consumption of the KB, I have created a small PowerShell script called Get-VCDBUsage.ps1 which will allow you to remotely connect to your VCDB (assuming you have enabled remote connectivity) to execute the correct SQL query based on your database platform and provide you with the results. The script also includes an optional parameter which will automatically take the results and calculate the estimated amount of downtime required for migrating from your Windows based vCenter Server to the VCSA. This makes gathering the information about your VCDB quite easy without having to manually go through the KB which can be challenging if you have a large amount of vCenter Servers.

The script supports the following 3 modes:

  • Running "locally" on the Microsoft SQL Server DB (requires Windows PowerShell Extensions for SQL Server as I rely on the Invoke-Sqlcmd cmdlet)
  • Running "remotely" connected to the Microsoft SQL Server DB
  • Running "remotely" connected to the Oracle DB (requires Oracle ODAC Client to be installed on the Windows system running the script)

For the first mode, you only need to specify the dbType, connectionType and dbInstance parameters as it will use the existing local ODBC connection so you do not have to provide any DB credentials. Here is an example command:

Get-VCDBUsage -dbType mssql -connectionType local -dbInstance VCDB

For the second mode, you will need to specify the dbType, connectionType, dbServer, dbPort, dbInstance, dbUsername and dbPassword parameters as you will be connecting remotely and the additional DB information will be needed. Here is an example command:

Get-VCDBUsage -dbType mssql -connectionType local -dbServer sql.primp-industries.com -dbPort 1433 -dbInstance VCDB -dbUsername sa -dbPassword VMware1!

Here is a screenshot of what the output would look like whether you run this against a VCDB running on either Microsoft SQL Server or Oracle system. As you can see, you get a nice break down of the 4 more interesting tables: Core configuration, Alarm, Events and Stats data.

how-to-check-size-of-vcenter-server-database-0
If you wish to also calculate the estimated VCSA migration time, you simply just need to append the -migration_type parameter which accepts a value of option1 or option2. When performing the Windows vCenter Server to VCSA Migration, customers have the option of either only migrating the Configuration + Alarm data which I am referring to as Option 1 (default) or you can migrate all data which includes Configuration + Alarm + Event + Stats which I am referring to as Option 2. By simply changing the parameter in the script, you can get an idea of the time estimate as well as the amount of data (in GB) that would be migrated. Here is an example command:

Get-VCDBUsage -dbType mssql -connectionType local -dbServer sql.primp-industries.com -dbPort 1433 -dbInstance VCDB -dbUsername sa -dbPassword VMware1! -migration_type option1

Here is a screenshot of what the output would look like with the additional parameter.

how-to-check-size-of-vcenter-server-database-1
As you can see, you can easily run this script non-disruptively against your VCDB and assess the amount of data that could potentially be migrated as well as the amount of downtime required for a given migration scenario. This is also a great time to consider whether or not you need all of this data, especially when it comes to the Performance Stats. For Tasks/Events, this data is generally useful for auditing purposes and some of our customers must retain a certain amount for compliance purposes. However, for the Performance Stats, this information may not be as useful as some of you may think. As vCenter Server performs its daily, weekly and monthly rollup jobs, the statistics are continuously averaged out to the point where the granularity of the original data points are pretty lost. This means that you end up storing a ton of data that is really not all that useful. For fine grain historical stats, solutions like vRealize Operations Manager should be considered and vCenter Server should really be used for short term historical stats and quick ease of access for troubleshooting purposes. For more details on calculating the estimated amount of downtime for migration, please refer to VMware KB 2146420.

One last note, as you may have noticed from the screenshot or running the script that at the end of the output there is a question asking if you would like to compare your VCDB stats with others. If you do decide to share  the information(completely optional) which only includes the size for the each of the tables and number of rows that will be sent off to a public github repository https://github.com/migrate2vcsa. If we get enough submissions, we may do some fun things with the data and report back to the community. The data is anonymous and it might be interesting to see how your data set compares to others.

Schedule automated backups of VCSA 6.0 vPostgres embedded database to Amazon S3

by 6 Comments

A couple of weeks back, I had received a question around backing up and restoring the Embedded vPostgres Database found within the new vCenter Server Appliance (VCSA) 6.0. At the time, the only thing I had seen was KB 2110294 and vSphere 6.0 Documentation here which recommends that a full VM backup be taken for either the vCenter Server for Windows as well as the VCSA to be able to properly protect your vCenter Server.

It was just recently that I came across VMware KB 2091961 which provides some details on just backing up the individual vPostgres DB. Having said that, just having a database backup is not sufficient to perform a proper restore in the case of completely losing your vCenter Server. There are other sources of data within the vCenter Server as well as the Platform Services Controller that are required and restoring a database would only work if you still had access to the original system. This is why a full VM backup is still the recommended approach.

For those who want to be able to just restore the database, the process listed in the KB is currently a manual step which uses a Python script that is provided in the KB. I thought it would be useful to demonstrate how you could schedule continuous backups during off peak hours using a simple cronjob and more interesting to me, is the how and where of the overall process? One option would be to mount a backup NFS share directly onto the VCSA and place all backups on that volume. Another option could have the backups directly uploaded to a Storage Cloud Provider like an Amazon S3 for example. I decided to take a look into the latter option.

In searching online, I found that Amazon offers a nice CLI called AWS CLI which provides S3 functionality like the 'cp' command and I was able to install it on the VCSA without any issues. You can find the instructions for installing the AWS CLI here and I would also recommend that you create a dedicate user assigned to the S3 bucket for storing the backups and then following the steps here to configure access to the AWS CLI. When asked about the Amazon Region as part of the configuration, I found this page to be helpful in listing the region names.

Disclaimer: Installing 3rd Party tools and products on the (VCSA) is not officially supported, you may be asked by GSS to remove them during troubleshooting.

If everything is installed correct, you should be able to run the following command to ensure you can reach the S3 bucket:

aws s3 ls s3:\\[NAME-OF-YOUR-S3-BUCKET]

To tie everything together, I created a simple shell script called backup_vcsa_vpostgres_db.sh which contains a couple of variables that you will need to edit:

  • VPOSTGRES_BACKUP_SCRIPT - The path to the Python vPostgres backup script
  • AWS_CLI - The full path to the AWS CLI binary
  • AWS_S3_BUCKET - The name of the S3 bucket using syntax s3:\\NAME-OF-YOUR-S3-BUCKET

Before creating the cronjob, I would recommend that you manually run the script to ensure everything works as expected and you are able to upload to your S3 bucket. Here is an example execution of the script which is backing up to my S3 bucket which I called "vcsa-backup".

backup-vcsa-vpostgres-db-to-s3-1
You can quickly verify that the backup has been uploaded to the S3 bucket by running the "ls" command as shown earlier or you can login to the Amazon S3 console and you should be able to see the backup files as shown in the screenshot below.

backup-vcsa-vpostgres-db-to-s3-0
To schedule the script to automatically run during a certain period, you can create a cronjob by running the following command:

crontab -e

For more information about setting up a cronjob, you can take a look here or Google your favorite resource. If you plan on storing backups with a Cloud Storage Provider and do not have direct internet access like most customers do, you can configure an HTTP(S) proxy by editing /etc/sysconfig/proxy If you prefer not to install AWS CLI, you can also use this simple bash script which uses an HTTP POST to upload to Amazon S3.

A kitten dies, every time you query the VCDB

by 14 Comments

vcdb1
Okay, maybe I am being a bit dramatic 😉 However, this is what crosses my mind every time I hear someone trying to query for something in the VCDB (vCenter Server Database) instead of using the vSphere API. Every so often I see a customer request asking for a specific SQL Query to find something in the VCDB. It just baffles me on why someone would want to go through such pain, not to mention this is not supported nor a recommended best practice.

Disclaimer: No kittens were harmed during the writing of this article

There are many disadvantages by going directly to the VCDB versus going through the vSphere API:

  • There are no guarantees on backwards compatibility of the database schema or database views from release to release
  • The data is in a very raw form, there is no abstraction which is usually provided by an API to make consumption easier
  • Risk of crafting queries that could negatively impact the performance of vCenter Server
  • Not officially supported by VMware, unless directed by VMware GSS
  • Pulling hair out while trying to understand the internal relationships of various objects, especially with a complex system like vCenter Server

Although the vSphere data is eventually persisted in the VCDB, the main purpose of the vSphere API, like many other software APIs is to provide a well defined interface for interacting with the underlying platform. I think there are still many vSphere/System Administrators who fear the word "API". Instead of being afraid of APIs, we should all embrace it and start to better understand how they work as it is just a way of interacting with the underlying system.

I can speak from my own personal experience, the first task for me out of college and into the real world was to look into building an application impact assessment report when an vSphere HA event occurred. We knew which Virtual Machines were restarted, but we did not have a good idea of what applications were affected. I had to correlate the impacted Virtual Machines with a list of applications and application owner from our homegrown CMDB which ran on MS SQL Server. At the time, I did not know any better and I thought best way of getting this information was to go directly to the VCDB (at the time this was Virtual Center 2.5). I found myself creating super complex SQL queries and trying to reverse engineer the relationships between all the different tables to get basic information from vCenter Server. If I knew what I know now back then, I would have gone straight to learning the VMware APIs, as I could have gotten everything I needed in just a couple of lines of code. After completing the project among few others which I leverage the VCDB directly, I realized that I needed to learn this VMware API. This early lesson has greatly helped me in my career to further automate IT/VMware infrastructure and help us move towards this new world of a Software-Defined Datacenter.

The diagram below provides an overview of some of the ways the vSphere API can be consumed by customers. An example is the vSphere Web Client and the legacy vSphere C# Client, this is a UI interface that customers can use to interact with the vSphere platform which in turns uses the vSphere API. There are also a variety of CLIs and scripting/programming languages that are built for easily extracting information and performing operations against vSphere that could be as simple as one-liner. The vSphere API can also be consumed by 3rd party companies to further abstract and provide new and interesting ways of interacting with vSphere, a great example of this is the CloudPhysics Card Builder platform that makes creating custom vSphere reports a breeze.

vcdb-vs-api
Note: The diagram above is just an example of some of the SDK/CLI/UIs that VMware provides to our customers. This is not meant as an exhaustive list and you can find all SDKs/CLIs for vSphere here.

Hopefully with this information, customers will better understand the benefits of using the vSphere API versus going directly to the VCDB.