SQL commands for information of vCenter Server Database usage

Let's assume we have simple installation of vCenter Server database leveraging MS SQL Express and we want to know how much database space is currently used. The simplest way is to use existing sqlcmd program. Connect to MS Windows server where vCenter is installed. Open command prompt or PowerShell and use following SQL commands ...

sqlcmd -E -Slocalhost\VIM_SQLEXP
1>use VIM_VCDB
2> go
Changed database context to 'VIM_VCDB'.
1> sp_spaceused
2> go
database_name
         database_size      unallocated space
---------------------------------------------------------------------------------------- ------------------ ------------------
VIM_VCDB
         244.94 MB          0.93 MB
reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
234824 KB          152800 KB          63200 KB           18824 KB

1>

There you can see that VIM_VCDB database size is 244.94 MB.

Note: In this particular environment I have just two ESX hosts with eight virtual machines.

Script to create VMware SRM database

VMware SRM installer creates tables in database automatically but you must prepare MS-SQL database, DB schema and ODBC data source before SRM installation.

Note: SRM has technical requirement to use database schema having the same name as DB user.

Here is the script to prepare MS-SQL database (SITE-A-SRM), schema (SRMlogin) and DB user (SRMlogin) with password (SRMpassword) for SRM:

CREATE DATABASE [SITE-A-SRM];
GO
USE [SITE-A-SRM];
GO
CREATE LOGIN [SRMlogin] WITH PASSWORD=N'SRMpassword', DEFAULT_DATABASE=[SITE-A-SRM], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
BEGIN
EXEC sp_changedbowner 'SRMlogin'
EXEC sp_executesql N'CREATE SCHEMA SRMlogin'
END

I hope it is obvious to everybody that the same procedure (probably with little bit different colored items) must be done in database on SITE-B for second SRM instance.

I generally believe that scripts are better, faster, more consistent and less error-prone then manually creating something via GUI.

Credits: SQL script has been created  by customer's database admin (Ladislav Hajek) contributing with me on SRM project.

Working with VCSA embedded database

It's not often but sometimes you have to work with vCenter database. Usually it should be done only if you are instructed by VMware Support or there is VMware KB article (like this one http://kb.vmware.com/kb/1005680) solving your problem.

Please do it very carefully in production systems.

VMware vSphere admin veterans usually have experience with MS-SQL but what about vCenter Server Appliance (VCSA) with embedded database? It is not very different. VMware uses Postgresql database (aka vPostgres) so logically it is the same as in any other SQL database. I would say even easier than in MS-SQL but that's highly dependent on administrator background and previous experience. I'm probably biases due to my *nix history and open-source (GNU) general preference.

Here are basic logical steps how to work with vCenter database.

• Connect to database server
• Discover database tables
• Issue SQL commands
• Exit from database server

CONNECT TO DATABASE SERVER

Change working directory to vpostgres

cd /opt/vmware/vpostgres/current/bin/

Display database configuration

cat /etc/vmware-vpx/embedded_db.cfg

output should looks like

EMB_DB_INSTALL_DIR='/opt/vmware/vpostgres/9.0'
EMB_DB_TYPE='PostgreSQL'
EMB_DB_SERVER='127.0.0.1'
EMB_DB_PORT='5432'
EMB_DB_INSTANCE='VCDB'
EMB_DB_USER='vc'
EMB_DB_PASSWORD='WZL2^y<-k8boy br="" fa="">EMB_DB_STORAGE='/storage/db/vpostgres'

connect to database

./psql VCDB -U vc

Update 2015-09-15: For VCSA 6 use /opt/vmware/vpostgres/current/bin/psql 
-d VCDB -U postgres (password is not required)

and you are in.

DISCOVER DATABASE TABLES

It's really good to know what tables are in the database. You need table names to compose SQL commands allowing you to select, insert and update data in the database.

Postgresql have special DBA (database administrator) commands witch start with character \ (slash). You can list all DBA commands by sequence \?

The output looks like this

vc01:/opt/vmware/vpostgres/current/bin # ./psql VCDB -U vc
psql.bin (9.0.13)
Type "help" for help.

VCDB=> \?
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S]  [PATTERN]      list conversions
  \dC     [PATTERN]      list casts
  \dd[S]  [PATTERN]      show comments on objects
  \ddp    [PATTERN]      list default privileges
  \dD[S]  [PATTERN]      list domains
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers

We want list database tables so the command we are looking for is

\dt

where output looks like

                    List of relations
 Schema |              Name              | Type  | Owner
--------+--------------------------------+-------+-------
 vpx    | vpx_access                     | table | vc
 vpx    | vpx_alarm                      | table | vc
 vpx    | vpx_alarm_action               | table | vc
 vpx    | vpx_alarm_disabled_actions     | table | vc
 vpx    | vpx_alarm_expr_comp            | table | vc
 vpx    | vpx_alarm_expression           | table | vc
 vpx    | vpx_alarm_repeat_action        | table | vc
 vpx    | vpx_alarm_runtime              | table | vc
 vpx    | vpx_alarm_state                | table | vc
 vpx    | vpx_binary_data                | table | vc
 vpx    | vpx_bulletin_operation         | table | vc
 vpx    | vpx_change_tag                 | table | vc
 vpx    | vpx_compliance_status          | table | vc
 vpx    | vpx_compute_res_failover_host  | table | vc
 vpx    | vpx_compute_res_user_hb_ds     | table | vc
 vpx    | vpx_compute_resource           | table | vc
 vpx    | vpx_compute_resource_das_vm    | table | vc
 vpx    | vpx_compute_resource_dpm_host  | table | vc
 vpx    | vpx_compute_resource_drs_vm    | table | vc
 vpx    | vpx_compute_resource_vsan_host | table | vc

ISSUE SQL COMMANDS

If we want select and view some data from database we use SQL statement SELECT. As an example  we will use first table from the list an it is vpx_access. Table vpx_access contains all vCenter users/groups who has access to vCenter and their roles. Here is SELECT statement:

select * from vpx_access

and output

 id  |          principal          | role_id | entity_id | flag
-----+-----------------------------+---------+-----------+------
   1 | root                        |      -1 |         1 |    1
 101 | VSPHERE.LOCAL\Administrator |      -1 |         1 |    1
 201 | VPOD01\vsphere-admins       |      -1 |         1 |    3
(3 rows)

Update and delete statements can be composed in similar manner following ANSI SQL Standard. Postgresql is ANSI-SQL:2008 standard.

EXIT FROM DATABASE SERVER

To exit from database server simply use DBA command \q

That's it pretty easy, isn't it? Working with vCenter database is not daily task of vSphere admin however we all know that sometimes you can be instructed by VMware support or KB to change something in the database. Don't be afraid - it's easy.