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.

DELL NPAR and VMware vSphere

DELL NPAR is Network Partitioning of single 10Gb NIC or better to say 10Gb CNA (Converged Network Adapter). NPAR technology is implemented on modern Broadcom and QLogic CNAs which allows to split single physical NIC up to 4 logical NICs. More about NPAR can be found for example here or here.

Please be aware that

• NPAR is not implemented on Intel 10G NIC (X520, X540)
• NPAR is not SR-IOV. More about SR-IOV is here and here.

The biggest NPAR value propositions are

• More logical interfaces partitioned from single interface which appears in the OS as normal PCI-e adapter.
• Switch independent solution. I'll explain what does it mean in the minute.

I have seen several customers complaining about NPAR. NPAR is just another technology and each technology has to be used correctly with respect for what purpose it was invented and designed. I have depicted NPAR architecture in the drawing bellow.

Let's describe the picture. On the picture you can see one physical server with ESXi hypervisor and two CNAs. Each CNA is divided into four logical partitions where each partition act as independent NIC with unique MAC address. You can see two physical wires interconnecting CNA ports with switch ports. Inside each physical wire are four "virtual wires" interconnecting CNA logical interfaces with single physical switch port. That's important!!! Four virtual ports on CNA are connected into single switch port. You can imagine it like four connectors on one side of the wire and just single connector on the other side.

That's not common, right?
The benefit of this architecture is switch independence.
The drawback is that ethernet flows between NPAR interfaces on single CNA port will fail.

So with this information in the mind let's explain NPAR architecture behavior in bigger detail.

Physical switch will never forward Ethernet frame back to the port from which the frame is coming. So, if src-mac and  dst-mac is on the same physical port switch (these are entries in switch mac-address-table) the L2 communication will be broken. That’s standard Ethernet switch behavior.
So what happen in NPAR architecture where are 4 virtual cables (NPAR interfaces with independent MAC addresses) connected into single physical switch port? No communication.

It is shown on picture below.

That’s the reason CISCO has VN-TAG (802.1Qbh) and HP has multi-channel VEPA (802.1Qbg)
These solutions multiplex Ethernet on both sides of the wire.

Note:
I have hands-on experirence with CISCO VN-TAG so I can admit it works correctly but I have never tested HP VEPA.

NPAR is relatively good technology to separate and prioritize Storage and Ethernet traffic on unified (converged) ethernet networks. It can be also used to separate and prioritize L2 traffic. But it will not work if L2 communication between logical NPAR interfaces are required.

Problematic scenarios can be for example following configurations

• vCenter in VM <-> ESX vmkernel management port in the same L2 segment but different portgroups routed through separated NPAR interfaces (uplinks) as depicted above.
• Cisco Nexus 1000v VSM in VM <-> ESX VEM communicate over L2 protocol routed through separated NPAR interfaces.

Hope this helps DELL and VMware community.

Deploying ESXi 5.x using the Scripted Install feature

Unfortunately I had no chance to design and implement automated vSphere deployment for any customer. I tried several automated deployment possibilities in the lab but I have never met the customer with such requirement. That's probably because right now I do vSphere consulting for small country in the middle of Europe where 32 ESX farm is "PRETTY BIG" vSphere environment ;-)
 
Nevertheless, excellent VMware KB article about PXE & KickStart file method of ESXi scripted installation is here.

VMware Update Manager DELL depot

DELL has VMware Update Manager (VUM) Depot at https://vmwaredepot.dell.com/index.xml

You can simply add the depot into VUM  Download Settings. It should looks like on the screenshot below.

You have to wait for next download task or you can click button "Download Now" to start download patches immediately. When patches are downloaded you can see them in "Patch Repository".

Why someone would use DELL VUM Depot? There are two DELL software components simplifying hardware management.

First component is OpenManage (a.k.a OpenManage Server Administrator or OMSA). This component is necessary when you want integrate your ESX host with 1:many management console OpenManage Essentials or with vSphere Management Plugin called "OpenManage Integration for VMware vCenter"

Second Component is iSM - Integrated Dell Remote Access Controller(iDRAC) Service Module. It is a lightweight optional software application that can be installed on Dell 12G Server or later. The iDRAC Service Module complements iDRAC interfaces – Graphical User Interface (GUI), RACADM CLI and Web Service Management (WSMAN) with additional monitoring data.

The nice thing on VUM is that everything is done automatically based on baselines and you don't need to search what version of plugin you need for different ESX versions.

Maybe you know I work for DELL Global Infrastructure Services so I can stop here. However I often do consulting for customers running non-DELL equipment in their datacenters. Right now designing vSphere on HP Blade system and 3PAR storage. So for HP hardware you can add HP VUM depot located at http://vibsdepot.hp.com/index.xml

VMware All Paths Down (aka APD)

All Paths Down (APD), a feature of the VMware ESXi host used in cases where all paths to the VM
go down because of storage failure or administrative error, is properly handled in ESX 5.1 as a
result of feature enhancement performed by VMware. Previously, in ESX versions 5.0 or 4.1, the
host would try continuously to revive the storage links and, as a result, performance would be
impacted for working VMs. A host reboot was required to clear this error.

I was engaged by several customers impacted with APD issue and it was always disaster. If you operate ESX 5.0 and older consider upgrade to ESX 5.1 or even better to ESX 5.5.

What is SAN Fill Word?

This is snip from Brocade SAN Admin Best Practicies ...

Note: Fill Word (apply for 8 Gbps platform only)

Prior to the introduction of 8 Gb, IDLEs were used for link initialization, as well as fill words after link initialization. To help reduce electrical noise in copper-based equipment, the use of ARB (FF) instead of IDLEs was standardized. Because this aspect of the standard was published after some vendors had already begun development of 8 Gb interfaces, not all equipment can support ARB (FF). IDLEs are still used with 1, 2, and 4 Gb interfaces. To accommodate the new specifications and different vendor implementations, Brocade developed a user-selectable method to set the fill words to either IDLEs or ARB (FF). Currently, setting the fill word can be done only via the CLI command portCfgFillWord (Ex: portcfgfillword [slot/]port, mode). There are four modes:

Mode 0 - Use IDLEs in link initialization and IDLEs as fill word (default mode).
Mode 1 - Use ARB (FF) in link initialization and ARB (FF) as fill words.
Mode 2 - Use IDLEs in link initialization and ARB (FF) as fill words.
Mode 3 - Try Mode 1 first; if it fails, then try Mode 2.

Traffic outside of frame traffic is made up of fill words: IDLEs or ARB (F0) or ARB (FF). Encoding errors on fill words are generally not considered impactful. This is why you may see very high counts of enc_out (encoding outside of the frame) and not have customer traffic affected. If many fill words are lost at once, the link may lose synchronization. On standard E_Ports, primitives are set to ARB, regardless of the portcfgfillword setting when not in R_RDY mode.

The recommended best practices are:

• Ensure that the fill word is configured to Mode 3.
• When connecting to a HDS storage device, set to Mode 2.
• When upgrading firmware, recheck the settings, since the fill word primitive has evolved over several Brocade FOS releases.

Do you know - MS Excel max file path is 213?

I have just tried open the .xls file in MS Excel 2010 and it failed with message like ...

"File could not be found. Check the spelling of the file name, and verify that the file location is correct."

... and because I've open the file by double click I was pretty sure file exists. BTW Notepad was able to open it. So what's the hell? The only idea what could be wrong was the absolute path length to the file. So I tried what is the maximum file path and I was surprised it is just 213 characters!!!

It's good to know, isn't it?

GSM/GPRS Modem Siemens ES75 - usefull AT commands

I have been asked by one customer to prepare some automated system which can dial admin cellular phone number in case of any trouble. They use PRTG for monitoring their environment. PRTG is IMHO very good monitoring system. It can send an email notification when sensor is down or some threshold is matched. Email is OK but when you have 24/7/365 SLAs it is important to know about critical events as soon as possible. My idea was to prepare simple system which checks periodically PRTG sensors over API and dial cellular phone in case of any critical sensor downtime.

So here is the system description. Hardware is based on SOEKRIS or ALIX hardware systems with FreeBSD installed on read-only CompactFlash. I use GSM modem Siemens ES75 connected via RS-232 serial cable to dial GSM phone number.

This blog post is not about hardware, FreeBSD or PRTG API integration but about Siemens ES75 usage but I believe recent overview is important to show you full context.

So, first of all we have to connect to the modem. We need some terminal emulator like Windows Hyper Terminal, putty, Minicom, etc. I use default unix terminal programm cu.

Default terminal speed of Siemens ES75 is 115200 bauds.

So here here is cu command syntax to connect modem over my USB<->RS-232 reduction for Mac.  

cu -s 115200 -l /dev/tty.usbserial-00007324

If you have FreeBSD the cu syntax is the same. Only COM port device is different. Below is connection over COM2 (/dev/cuau1).

cu -s 115200 -l /dev/cuau1

or

cu -s 115200 -l /dev/ttyU0

So when we are connected to the modem we can use AT commands to work with modem. Useful AT commands follows.  

Set the modem into factory defaults

at&f

If you want disable echo use

ate0

to enable echo use

ate1

Write running configuration to EEPROM

at&w

To slow down modem terminal speed to 38400 bauds

at+ipr=38400

Get modem vendor

at+cgmi 

Get modem model

at+cgmm

In my modem Siemens ES75 it Vendor and Model strings looks like this

at+cgmi
Cinterion 

OK
at+cgmm
MC75i

OK 

To display signal strength of the device  

at+csq

Returned signal value can be compared with table here.

Display SIM card identification number

at^scid

Extended event indicator control

at^sind

Here is example how to get all available  indicators

at^sind?
^SIND: battchg,1,5
^SIND: signal,1,99
^SIND: service,1,0
^SIND: sounder,1,0
^SIND: message,1,1
^SIND: call,1,0
^SIND: roam,1,0
^SIND: smsfull,1,0
^SIND: rssi,1,4
^SIND: audio,0,0
^SIND: simstatus,0,5
^SIND: vmwait1,0,0
^SIND: vmwait2,0,0
^SIND: ciphcall,0,1
^SIND: adnread,0,1
^SIND: eons,0,0,"","T-Mobile CZ"
^SIND: nitz,0,,,
^SIND: lsta,0,0
^SIND: band,0,3
^SIND: simlocal,0,1
OK

Before you can use GSM network you usually have to register and authenticate by your PIN. Here is example of AT+CPIN read command which will return if SIM PIN authentication is required.

at+cpin?
+CPIN: SIM PIN
OK

The return is SIM PIN so it means we have to enter PIN to register in to GSM network. Here is how to authenticate with PIN 3303

at+cpin=3303
OK

Right now we are registered in GSM network.  You can verify it by running AT+CPIN? read command again

at+cpin=?
OK 

There is no other authentication required so this is the proof we are registered in GSM network and we can use it. If you want completely disable PIN authentication you can use command

at+clck="SC",0,"3303"

So now let's call some mobile number.

atd602123456;
BUSY

Here I dialed phone number 602123456 on my mobile and because I dropped the call the status was  returned as BUSY.

And if you want to check incoming calls during the ringing you can see on terminal

RING

RING

RING

for every ring.

If you want to see caller phone number (aka calling line identification presentation) then you have to instruct modem by following command

at+clip=1 

OK

and during ringing you will also see caller identification

RING

+CLIP: "+420602123456",145,,,,0

RING

+CLIP: "+420602123456",145,,,,0

RING

+CLIP: "+420602123456",145,,,,0

or you can ask for caller phone number during ringing by command

at+clcc

and response is

RING

RING

RING

at+clcc

+CLCC: 1,1,4,0,0,"+420602525736",145

OK

RING

RING

And if you want to hang up incoming call you can use following command

ath
OK 

That's it for now. If you need more AT commands for GSM modem Siemens ES75 ask google for document "mc75_atc_01001_eng.pdf". I found one document here.