WilliamLam.com

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.

Would you like MSSQL (Microsoft SQL Server) support for the VCSA (vCenter Server Appliance)?

by // 4 Comments

Many of you know that I am a huge fan of the VCSA (vCenter Server Appliance), especially when it comes to a new deployment and how easy it is compared to the Windows version. I especially like how upgrades work for the VCSA by deploying a new VCSA and then performing a migration based upgrade to the new appliance. This provides a nice roll-back mechanism in case something happens and all I need to do is just power on the original VCSA to get the original environment up and running again.

Having said all this, I know the VCSA is still currently lacking a few features which may prevent customers from fully adopting the solution for their production workload. However, if you take a look a how far the VCSA has come from its initial release in vSphere 5.0 release, it has greatly improved and we continue to enhance it with every release. With release of vSphere 5.5, we now support the following configurations maximums:

ESXi Hosts Virtual Machines
Embedded vPostgres DB 100 3,000
External Oracle DB 1000 10,000

Even before vSphere 5.5 release, VMware has internally pushed the boundaries of the VCSA (vSphere 5.1) and the embedded vPostgres database by running one of the most dynamic and demanding workload in a very short amount of time which is the VMworld's Hands On Lab. This really goes to show the type of scale and performance the VCSA and the embedded vPostgres database can support.

One of the most frequent piece of feedback that I have heard from customers regarding the VCSA is to provide support for Microsoft SQL Server database. This request is quite understandable, especially for a Windows shop where you may already have a team of Database Administrators who are quite familiar with the operational and management aspects of maintaining a MSSQL database.

VMware is actually not opposed to supporting MSSQL for the VCSA which runs on SuSE Linux but the challenge in the past was the lack of a Microsoft ODBC driver for SLES. Well it turns out last year Mircosoft released a Community Tech Preview Microsoft ODBC Driver for SQL Server on SLES, however the driver is currently only a Tech Preview and to do justice for VMware customers, we would want to use a GA (General Available) driver which means that there would be full support from Microsoft.

If you would like the VCSA to have MSSQL support, you can help by providing this feedback to your local Microsoft representative or filing a feature request. The more customer demand we have for this, the more likelihood MSSQL DB support can become a reality.

In addition to providing feedback to Microsoft, I think it would also be useful to let our PMs know how important MSSQL DB support & the VCSA is to you which will also help with the prioritization of features. If you can help fill out this quick survey below, that would also be great.