Recently I finally bit the bullet and decided to bring the vCenter portion of a vSphere environment up to version 6.5. Since the migration from a Windows based vCenter to the VCSA is now a supported path I thought it would also be a good time to migrate to the appliance as well. So with that I ran through a few blogs I found in regards to the migration, checked out the vSphere Upgrade Guide and peeled through a number KB’s looking for gotchya’s. With my knowledge in hand I headed into the migration.
At this point I had already migrated my external windows based PSC to version 6.5 and got started on the migration of the windows-based vCenter Server. Following the wizard I was prompted for the typical SSO information along with where I would like to place the appliance. The problem though came when I was prompted to select a deployment size for my new VCSA. My only options available were Large and X-Large. Might not be a big deal if in fact this environment required this amount of resources – Looking at the table below those deployment sizes are scoped to fit at a 1000 host and above mark.
Did this environment have 1000+ hosts and 10000+ VMs? Absolutely not! At its largest it contained maybe 70 hosts and a few hundred VMs running on them – a Small configuration at best, medium if you want to be conservative! At first I thought maybe I was over provisioned in terms of resources on my current vCenter Server – but again, it only had 8 vCPU’s and 16GB of RAM. With nothing out of the ordinary with vCenter itself I turned my attention to the database – and that’s where my attention stayed as it was currently sitting at a size of 200GB. Honestly, this seemed super big to me and knowing that it had been through a number of upgrades over the years I figured I would make it my goal to shrink this down as small as possible before trying again! TL;DR; version – The database was the culprit and I did end up with the “small” option – but I did a number of things after a frenzy of Google’s and searches – all listed below…
[symple_box color=”yellow” fade_in=”false” float=”center” text_align=”center” width=””]WAIT!!!! Don’t be that guy! Make sure you have solid backups and can restore if things here go sideways – engage VMware GSS if needed – don’t just “do what I do” :)[/symple_box]
Reset the vpx provider
The vpx data provider basically supplies the object cache for vCenter – caching all inventory objects such as hosts, clusters, VMs, etc in order to provide that super-snappy response time in the vSphere Web Client 6.0 (Is this sarcasm?). Anyways, resetting this essentially will reduce the size of our Inventory Database. Now, the problem in versions prior to 5.5 Update 3 is that there was no way to reset individual data providers – in order to do one you had to do them all – and that meant losing all of your tags, storage profiles/policies, etc. Thankfully, 5.5 U3 and 6.0 allows us to simply reset just vpx, leaving the rest of our environment in-tact. In order to do so we must first get into the vSphere Inventory Managed Object Browser (MOB) and get the UUID of the vpx provider. **NOTE, this is different than the MOB you may be used to logging into, see below ***
First, log into the Inventory Service MOB by pointing your browser to https://vCenterIP/invsvc/mob1/ From there, simply click the ‘RetrieveAllProviderConfigs’ link within the Methods section as shown below
In the pop up dialog, click ‘Invoke Method’, then run a search for vpx
It’s the providerUuid string that we are looking for – go ahead and copy that string to your clipboard and return to https://vCenterIP/InvSvc/mob1/ – this time, clicking the ‘ResetProviderContent’ link under Methods. In the pop up dialog, paste in your copied UUID and click ‘Invoke Method’ as shown below…
After a little while the window should refresh and hopefully you see no errors! The process of resetting for myself took roughly 5 minutes to complete….
Getting rid of logs
Although vCenter does its own log rotation you may want to check out and see just how much space your logs are taking up on your current vCenter server before migrating as some of this data is processed during the migration/upgrade. I freed up around 30GB of disk by purging some old logs – not a lot, but 30GB that didn’t need to be copied across the wire during the migration. There is a great KB article here outlining the location and purpose of all of the vCenter Server log files – have a look at it and then peruse through your install and see what you may be able to get rid of. For the windows version of vCenter you can find all of the logs in the %ALLUSERSPROFILE%\VMware\vCenterServer\logs\ folder. I mostly purged anything that was gzipped and archived from most of the subfolders within this directory. Again, not a difference maker in terms of unlocking my “Small” deployment option – but certainly a time-saver during the migration! So what was culprit that was not allowing me to select “Small” – yeah, let’s get to that right now…
My Bloated vCenter Database
Yeah, 200GB is a little much right – even after resetting the vpx provider and shrinking the database files I was still sitting pretty high! So, since I had no intention of migrating historical events, tasks and performance data I thought I’d look at purging it before hand! Now if you have ever looked at the tables within your vCenter Server database you will find that VMware seems to create a lot of tables by appending a number to the VPX_HIST_STAT table. I had a lot of these – and going through them one by one wasn’t an option I felt like pursuing. Thankfully, there’s a KB that provides a script to clean all of this up – you can find that here! Go and get the MSSQL script in that KB and copy it over to your SQL Server. Once you stop the vCenter Service we can simply run the following command via the command prompt on our SQL Server to peel through and purge our data.
sqlcmd -S IP-address-or-FQDN-of-the-database-machine\instance_name -U vCenter-Server-database-user -P password -d database-name -v TaskMaxAgeInDays=task-days -v EventMaxAgeInDays=event-days -v StatMaxAgeInDays=stat-days -i download-path\2110031_MS_SQL_task_event_stat.sql
Obviously you will need to assign some values to the parameters passed (TaskMaxAgeInDays, EventMaxAgeInDays, & StatMaxAgeInDays). For these you have a few options.
- -1 – skips the respective parameter and deletes no data
- 1 or more – specifies that the data older than that amount of days will be purged
- 0 – deletes it all!
For instance, I went with the 0, making my command look like the following….
sqlcmd -S IP-address-or-FQDN-of-the-database-machine\instance_name -U vCenter-Server-database-user -P password -d database-name -v TaskMaxAgeInDays=0 -v EventMaxAgeInDays=0 -v StatMaxAgeInDays=0 -i download-path\2110031_MS_SQL_task_event_stat.sql
After purging this data, and running a shrink on both my data and log files I finally had my vCenter database reduced in size – but only to 30GB. Which, in all honesty still seemed a bit large to me – and after running the migration process again I still didn’t see my “Small” deployment option. So I went looking for other large tables within the database and…..
Hello VPX_TEXT_ARRAY
It’s not very nice to meet you at all!!! After finally getting down to this table – and running “sp_spaceused ‘VPX_TEXT_ARRAY’” I found that it was sitting a whopping 27GB. Again, a flurry of Google! What is VPX_TEXT_ARRAY and what data does it hold? Can I purge it? Well, yes….and no. VPX_TEXT_ARRAY, from what I can gather keeps track of VM/Host/Datastore information – including information in regards to snapshots being performed on your VMs. Also from what I can gather, from my environment anyways, is that this data exists within this table from, well, the beginning of time! So, think about backup/replication products which constantly perform snapshots on VMs in order to protect them – yeah, this could cause that table to grow. Also, if you are like me, and have a database that has been through a number of upgrades over the years you may end up having quite a bit of data and records within this table as it doesn’t seem to be processed in any sort of maintenance job. In my case, 7 million records resided within VPX_TEXT_ARRAY. Now, don’t just go and truncate that table as it most likely has current data residing in it – data vCenter needs in order to work – there’s a reason it tracks it all in the first place right? Instead, we have to parse through the table, comparing the records with those that are in the VPX_ENTITY table, ensuring we only delete items which do not exist. The SQL you can use to do so, below…
DELETE FROM VPX_TEXT_ARRAY
WHERE NOT EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID)
A long and boring process – 18 hours later I was left with a mere 9000 records in my VPX_TEXT_ARRAY table. Almost 7 Million removed. Just a note, there is a KB outlining this information as well – in which it says to drop to SINGLE_USER mode – You can if you wish, but I simply just stopped my vCenter Server service and stayed in MULTI_USER so I could check in from time to time to ensure I was still actually removing records. an sp_spaceused ‘VPX_TEXT_ARRAY’ in another query window will let you track just that. Also, it might be easier, if you have the space, to set the initial size of your transaction logs something bigger than the amount of data in this table. This allows SQL to not have to worry about growing them as it deletes records – you can always go back in the end and reset the initial size of the tlogs to 0 to shrink them.
So – a dozen coffees and a few days later I finally ran another shrink on both the data and log files, setting their initial sizes to 0 and voila – a 3GB database. Another run at the migration and upgrade and there it was – the option to be “Small”! Again, this worked in my environment – it may not work in yours – but it might help get you pointed in the right direction! Do reach out if you have any questions and do ensure you have solid backups before you attempt any of this or anything you read on the net really 
Also, there’s always that Global Support Services thing that VMware provides if you want some help! Thanks for reading!
This was helpful. Thanks for the post.