Solution for Appstack and writable volume attachment getting failed – VMware AppVolume

I am trying to put down the issues and there resolutions which comes up during daily activities that will help me and also save someone’s day.

So lets start….

 Issue:   Appstack and writable volume attachment getting failed.

I started getting calls of user’s saying they are not getting Appstacks and Writable Volumes are also not getting attached.

So the first thing came in mind to check the VMware Appvolume Manager Logs, so logged into to the Web of Appvolume Manager and went to activity tab  and then checked the System logs and below is the snippet of the logs.

Logs checked:
Appvolume Manager System logs:

Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to establish connection to Hypervisor <VMware ESX#25905900 root@esxi1.virtgeek.local>
Apr 02 2019 10:43PM
Failed to connect to vSphere at “root@esxi1.virtgeek.local”: SSL_connect returned=1 errno=0 state=error: certificate verify failed
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to establish connection to Hypervisor <VMware ESX#25905900 root@esxi1.virtgeek.local>
Apr 02 2019 10:43PM
Failed to connect to vSphere at “root@esxi1.virtgeek.local”: SSL_connect returned=1 errno=0 state=error: certificate verify failed
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Validating SSL certificate for “esxi1.virtgeek.local”: Rejected because certificate is neither verified nor trusted
Apr 02 2019 10:43PM
Unable to locate “Machine <VDIVM-02> (50048335-d1cc-e853-78f0-2f494c06d9b0)” on “esxi1.virtgeek.local” — Using default hypervisor instead

I was frequently seeing a single Esxi host i.e. esxi1.virtgeek.local with error Rejected because certificate is neither verified nor trusted.

Now we have seen the issue and also the logs, so it’s time to get it resolved as quickly as possible.

Here is what i did to get it resolved :

1) First check the credential which is given in Appvolume Manager for ESXi host and Actual Credential of ESXi host is same. If not you have to change the password of ESXi.

You need to check the credential of the hosts which is coming up in the logs.

2) If credentials are same for host in Appvolume Manager and ESXi host, go to next step.
3) Login to Appvolume webpage
4) Go to Configuration Tab –> Machine Managers —> Expand vCenter and Click on Edit.
5) Uncheck Mount on host and mount on local.
6) Enter vCenter credentials and click on Save.
7) Wait for couple of Minutes
8) Again go to Machine Managers, click on Mount on host and give ESXi credentials and for vCenter give vCenter credential.
Woooohoooo !!!!! The issue should get resolved with this.

You owe me a beer 🙂 if this helps you someday 😉 

If you want to know what caused it to resolve, please read below :

Unchecking the mount on host will remove the host certificate from appvolume database and thereafter again checking the mount on host option will update the host certificates in the database and thereafter appvolume will start proper communication with the host and this helped to resolve the problem.

I would be more than happy to reply to your queries.

Rajesh Poojary

Where to find me 😉

Twitter : @RajjeshPoojary

Mail : Rajjesh.poojary@gmail.com

 

Steps to reduce or shrink vCenter DB Size

Recently i came up with the issue which was causing the vCenter DB (SQL DB) to grow huge, after digging on the internet and spending some time, i was able to find the cause and the solution for my vCenter DB size.

I am making this for the easy future reference , incase if faced same issue again and this will also help the other VMware Admins who are also facing the similar issue in their environment.

Environment: 

  1. Windows based vCenter 6.0 with external SQL DB
  2. SQL DB 2012 Standard

Issue:  vCenter DB size has grown huge more than expected and need to reduce the vCenter DB size.
Analysis: VPX_HIST_STAT tables has grown huge and that includes the orphaned VPX_HIST_STAT tables.

This may be happen if you have upgraded the vCenter to the new build.
VMware KB Article Followed:
https://kb.vmware.com/s/article/2058670
https://kb.vmware.com/s/article/1036738

The Script mentioned below are present in the above mentioned KB article.

This is very very very important thing to be noted

Note : Before doing any changes to the database, Please ensure you take the backup of the database on which you are working and here you need to make sure you have taken the latest vCenter Database backup.

If you want to know how to take the SQL DB backup:

  1. Login to the SQL Management Studio
  2. Right Click on the vCenter Database –> Go to Tasks–> Backup
  3. Browse the path to where you want to save the backup and give the filename to the backup file with .bak at the end of the filename . For e.g. vcenterdbbackup.bak

Steps to be followed to reduce the SQL DB size:

Note: This SQL Query may take a long time to complete the query execution depending upon the number of orphaned VPX_HIST_STAT tables and VPX_HIST_STAT tables which has grown huge to be deleted.

For me it took around 4 hours to complete the query execution
1) Stop the vCenter Service
2) Login to the SQL DB server and run the below SQL Query on vCenter Database
Note :SQL Query Starts from” /*” and ends at “SET NOCOUNT OFF”

You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.

=====================================================================
/*
This script will delete data from VPX_HIST_STAT* tables which have no parent record in
corresponding VPX_SAMPLE_TIME* table.

You are strongly advised to shut down the VirtualCenter server and make
a complete backup of your database before running this script.

VirtualCenter Server must be stopped while this script is running.

Configurable parameters:
@delete_data – Set @delete_data to 1 to delete orphan rows.
In case of delete_data <> 1 the script will only count how many rows are orphaned
@delete_data must be 1 to actually delete rows.
@batch_size – Number of rows to delete per one transaction

The script may be executed via SQL Server Management Studio or from command line.

USER which executes the script must have privileges to delete data from vCenter Server database and
the user must have default schema equal to the vCenter Server database schema.

*/

SET NOCOUNT ON
— Create temporary tables
— #ORPHAN_STATS – contains table name and time_id which should be deleted
— #DYNAMIC_SQL – contains sql scripts which be executed later
IF OBJECT_ID(‘tempdb..#ORPHAN_STATS’) IS NOT NULL
DROP TABLE #ORPHAN_STATS
GO
CREATE TABLE #ORPHAN_STATS (table_name VARCHAR(30) NOT NULL, time_id BIGINT NOT NULL, deleted_rows BIGINT)

IF OBJECT_ID(‘tempdb..#DYNAMIC_SQL’) IS NOT NULL
DROP TABLE #DYNAMIC_SQL
GO
CREATE TABLE #DYNAMIC_SQL (orphan_sql varchar(2000) NOT NULL)

DECLARE @delete_data BIT
DECLARE @batch_size INTEGER
DECLARE @affected_rows INTEGER

print ‘*********************************************************************’
print ‘* This script is for deleting data from VPX_HIST_STAT tables which *’
print ‘* have not master record in corresponding VPX_SAMPLE_TIME table. *’
print ‘*********************************************************************’

SET @delete_data = 1
SET @batch_size = 10000

IF @delete_data = 1
PRINT ‘*** DATA WILL BE DELETED ! ***’
ELSE
BEGIN
PRINT ‘* Currently script only counts how many records will be deleted. *’
PRINT ‘* set @delete_data to 1 in order to actually delete rows *’
END
PRINT ‘*********************************************************************’
— Populate #DYNAMIC_SQL with scripts for collect orphan rows
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time1 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat1%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time2 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat2%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time3 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat3%’
INSERT INTO #DYNAMIC_SQL
SELECT ‘SELECT ‘ + CHAR(39) + name + CHAR(39) + ‘, time_id, count(*) FROM ‘ + name + ‘ D WHERE NOT EXISTS (SELECT 1 FROM vpx_sample_time4 M WHERE D.TIME_ID = M.TIME_ID) GROUP BY time_id ‘ FROM sys.tables WHERE name like ‘vpx_hist_stat4%’

DECLARE @l_orphan_sql VARCHAR(2000)
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQL

OPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql

SET @affected_rows = 0
–Populate #ORPHAN_STATS with table names, time_id and count of rows for delete
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #ORPHAN_STATS
EXEC (@l_orphan_sql)
SET @affected_rows = @affected_rows+ @@ROWCOUNT
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
END
CLOSE c_exec_sql
DEALLOCATE c_exec_sql

SELECT CAST(SUM(deleted_rows) AS VARCHAR) + ‘ rows will be deleted from table ‘ + table_name
FROM #ORPHAN_STATS
GROUP BY table_name

IF @affected_rows > 0
BEGIN
IF @DELETE_DATA = 1
BEGIN
DECLARE c_exec_sql CURSOR FOR
SELECT orphan_sql FROM #DYNAMIC_SQL
— Populate tamp table #DYNAMIC_SQL with scripts for DELETE
TRUNCATE TABLE #DYNAMIC_SQL
INSERT INTO #DYNAMIC_SQL
SELECT ‘DELETE TOP (‘ + cast (@batch_size as varchar) + ‘) FROM ‘ + table_name + ‘ WHERE time_id = ‘ + cast (time_id as varchar) FROM #ORPHAN_STATS
OPEN c_exec_sql
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
WHILE 1=1
BEGIN
BEGIN TRAN
EXEC (@l_orphan_sql)
SET @affected_rows = @@ROWCOUNT
COMMIT TRAN

PRINT ‘EXECUTED SQL: ‘ + @l_orphan_sql
PRINT ‘Affected rows: ‘ + cast( @affected_rows as varchar)
PRINT ‘——————‘

IF @affected_rows < @batch_size BREAK
END
FETCH NEXT FROM c_exec_sql INTO @l_orphan_sql
END

CLOSE c_exec_sql
DEALLOCATE c_exec_sql
END
END
ELSE
PRINT ‘There are not any orphan rows.’

SET NOCOUNT OFF

Ohh so the query execution completed 🙂

Now here we are to the last step, we have to run one more query that will shrink the database as we have deleted VPX_HIST_STAT tables. 

3) SQL query to Shrink the Database:

You need to login to the SQL Management Studio, Select the vCenter Database and run the below mentioned SQL Query.

SQL Query :
dbcc shrinkdatabase ( vcdbname , 20);
go
Note: When DBCC SHRINKDATABASE is specified with target_percent, the Database Engine calculates the target size to be thetarget_percent amount of space free in the file after shrinking.

And here we goooooo… You should have reduced the vCenter DB Size & create some free space in your drive and now you can start the vCenter service.

Enjoyyyyyy 🙂

Again Thanks to the VMware KB article to helping in resolving the issue 🙂

I would be more than happy to reply to your queries.

Rajesh Poojary

Where to find me 😉

Twitter : @RajjeshPoojary

Mail : Rajjesh.poojary@gmail.com

 

My VCAP DCV – Deploy 2018 exam (3V0-21.18) experience !!! Wohhooo I am so excited!!! Rajesh Poojary

I am so so excited to tell you guys that after 3 months of sleepless night studies and spending  some money on preparing my Home Lab for daily practice.

I had successfully cleared VCAP DCV – Deploy 2018 exam in my first attempt 🙂

So i would like you to share my exam experience as i have given the exam recently… Ahhh the question about the result time taken… Yes it took 4 long weeks to get to know my results. But never mind i am happy with my result 😉

I had started preparation for the exam 3 months back and i had sleepless nights during this period. I did spend around 3-4 hours and sometime more than that to complete all the study guide given in Exam Blue Print, also spending time on labs.

Exam has 17 questions which are all lab scenarios and if you are working in deployment and in enterprise environment , it would make exam more easier, but the time management is the key for success during exam.

The Exam is total lab based environment, i would suggest to have a good hands on experience of using VMware HOL (https://labs.hol.vmware.com/HOL/catalogs/) , your exam environment would be totally similar to VMware HOL.

I would suggest whoever is going to give this exam should check the exam sections in exam preparation guide (https://www.vmware.com/education-services/certification/vcap-dcv-deploy-2018-exam.html).

Not everything given in exam blue print will come , but don’t take chance be prepared for everything. Make sure you have completed and checked all the tasks given in exam preparation guide.

I would suggest to have a good hands on experience of VSAN and Availability Management.

While i was searching for VMware HOL labs on vSphere 6.5, most of the labs are replaced by vSphere 6.7. So i decided to prepare my own homelab to test the scenarios. Still if you don’t want to spend money on preparing HomeLab, you can still count on VMware HOL vSphere 6.7.

I followed multiple websites for the study resources and i would like to thank everyone who had shared so much study materials which made to clear the exam successfully.

Please find some of the links which i did referred and helped me highly in scoring.

https://sostechblog.com/

https://www.jortechnologies.com/vcap6-dcv-deploy-3v0-623-exam-experience-jorluis-perales/

http://vcallaway.com/vcap6-dcv-deploy-study-guide/

https://blog.mwpreston.net/8-weeks-of-vcap/

http://virtualg.uk/vcap6-dcv-deploy-exam-simulator-free/

and many more… Feel free to search as many study resources and go through it.

The Exam lab will have multiple vCenters in linked mode, so while doing any task make sure you are doing it on correct vCenter which is given in exam questions.

If you feel at any point you are not able to find the task or options make sure you recheck the vCenter on which you are doing is the right one.

I would like to repeat this again, during exam Time Management is the key to pass the exam. Read the questions properly and if you feel that specific question is time consuming or while doing any task given in exam , if you feel that this gonna take some time. I would suggest you to skip that question and go ahead for next one which you feel you can complete it quickly.

You can always come back and do the question which you had skipped.

Even though i tried to make all the time management, still i was not able to attempt couple of questions even though i was aware of the solution.

So i would request you to make the time management the priority, refer the links given above , do as much lab as possible and once go through all the deployment guides and you should be able to clear this exam.

Happy Learning Guys 🙂

Feel free to like, comment and share.

I would be more than happy to reply to your queries.

Rajesh Poojary

Where to find me 😉

Twitter : @RajjeshPoojary

Mail : Rajjesh.poojary@gmail.com