Oracle

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

09.29.2016 by William Lam // 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.

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.

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.

vCloud Suite Virtual Appliances: Passwords, Databases, URLs, etc

01.07.2013 by William Lam // 11 Comments

I recently re-organized my home lab and I got rid of a bunch of VMs for random projects that I have been working on last year. Part of this re-organization was to re-deploy a few of the virtual appliances found within the vCloud Suite. As part of the deployment, I often find myself scouring various documents looking for default credentials to the OS, VAMI interface or the application. It is not always easy to find and I often end up going to Google or the VMTN forums for the answer.

As a fun little exercise, I thought why not deploy all of the latest virtual appliance that are available in the vCloud Suite and just document the latest usernames/passwords for the application, OS, VAMI interface, database configurations, URLs, etc.? This would primarily be a reference for myself, but thought it might also benefit others as well. Duncan Epping had done this awhile back for vCloud Director and few other virtual appliance and funny enough, his site was one of the first ones I found for the default vCloud Director password.

Not only have I deployed all the virtual appliances from the vCloud Suite, which can be seen from the screenshot below, but I also went through each appliance and validated the credentials for the application, OS or VAMI interface if applicable as well as identify all database credentials and configurations which are not all publicly documented (this took a bit of digging in the appliances, but was not too difficult if you know where to look).

[Read more...]

Automating vCloud Director 1.5 & 5.1 and Oracle DB Installation

09.21.2010 by William Lam // 23 Comments

While playing around with the new VMware vCloud Director, I found myself rebuilding the vCD system twice. This meant I needed to automate the deployment so I did not have to do it for the third time if needed. The following script is used to deploy VMware vCloud Director and Oracle Express database on a single CentOS 5.5 64bit system. This setup is very similar to the one documented by Duncan Epping - Creating a vCD Lab on your Mac/Laptop, though our setup is running in our ghettoDatacenter.

This script will assume that you have installed CentOS 5.5 64bit OS on a system and it has direct or proxy access to the internet. You will also need to make sure you have provisioned two virtual NICs for the system, which the second one will automatically be configured for you.

UPDATE: The scripts below have been updated to support the latest release of vCloud Director 5.1.

For our setup, I used CentOS-5.5-x86_64-netinstall.iso to do a network installation using ftp://ftp.cs.ucsb.edu/mirrors/centos/5.5/os/x86_64/images and using all the defaults for "Server" type deployment. Since I can not re-distribute the installers for VMware vCloudr Director and Oracle Express, you will need to download the installers and transfer it to your CentOS system. The last step before you being is to transfer the vcd_setup.sh script and vcd.rsp response file to your CentOS system.

Download: vcd_setup.sh
Download: vcd.rsp

You will need make a few tiny edits the vcd.resp file to fit your environment which will be used to deploy vCD and Oracle Express, including the configurations.

Here is the sample vcd.resp that is included:

If you have read Duncan's vCD article or the vCD installation guide, you will be familiar with all these configurations. The only required changes you will need to make will be the secondary IP Address and the passwords for Oracle Database. Once you have made all the necessary edits, we can start the installation.

You should have the following 4 files located in the same directory on your vCD system:

vcd_setup.sh
vcd.resp
oracle-xe-10.2.0.1-1.0.i386.rpm
vmware-cloud-director-1.0.0-285979.bin

Note: Make sure you set the executable permission on the vcd_setup.sh, else you will get a permission denied error.

1. Start the installation and type y to begin:

The script will exit if it can not locate both vCD and Oracle Express installers, make sure those are available in the same working directory.

2. The YUM repository will be configured from a generic mirrorlist from CentOS and package dependency for vCD will begin:

3. The next section is disabling the firewall and selinux which is not a best practices, but for a lab, this is fine. NTP is also configured and enabled upon bootup:

4. Oracle Express will now be installed and configured based on the initial configuration file passed to the script (vcd.rsp). An oracle response file is created in /tmp which allows for a silent and unattended installation:

5. Per VMware's best practices, two separate tables spaces (CLOUD_DATA and CLOUD_INDX) are created along with a separate user account with the required privileges per the documentation using sqlplus and configuration file generated on the fly:

6. We now configure the secondary IP Address and generate the keystore for both HTTP and CONSOLE PROXY and stores it in /opt/keystore/certificates.ks:

Note: Make sure that both IP Addresses used for HTTP and CONSOLE PROXY have both their forward and reverse DNS properly configured. The "host" utility will be used to query their hostnames which is used in creating the keystore certificates.

7. vCloud Director will not be installed which actually extracts an embedded RPM for the actual installation:

8. We now configure vCloud Director from our response file and a configuration file is generated again for a silent and unattended installation:

Note: The script does not cover syslog server and syslog port configuration. If you need this, you can easily edit the script to support this.

9. Once the vCloud Director installation and schema creation is completed, vCloud Director is finished installing:

10. Now we can start vCloud Director and wait for the initialization to be completed which the script watches /opt/vmware/cloud-director/logs/vcloud-container-info.log for completion:

11. You can now open up your browser point it to your vCD system and if everything went accordingly, you should see vCloud Director landing page:

I hope this script will be helpful for those that want to kick the tires on vCloud Director but prefer not to go through all the manual steps of configuring an Oracle database and vCD. In total, the script took ~8minutes to complete from start to finish.