Running VMware’s vCSA on MSSQL
I really like the new vCenter Appliance, but I am not a fan of either the embedded DB2,DB2 or Oracle database options. It seems unusual that VMware did not support MSSQL on the first release of the appliance. I suspect they have their reasons but I prefer not to wait for it when I know it runs without issue in my lab. If you’re interested in the details read on and discover how I hacked it into submission.
The vCSA Linux host contains almost all the necessary components to drive an MSSQL DB. The missing elements are an MSSQL ODBC and JDBC driver. They are both available from Microsoft and can be installed on the appliance. Now as you should know VMware would not support such a hack and I’m not suggesting you run it in your world. For me it’s more about the challenge and adventure of it. Besides I don’t expect VMware to support it nor do I need the support.
Outside of these two Microsoft products it is necessary to modify some of the VMware property files and bash code to allow for the mssql drivers.
The appliance hosts 3 major application components. A web front end using lightpd, a vpxd engine which appears to be coded in C and a Tomcat instance. Surrounding these elements we have configuration scripts and files that provide end users an easy way to setup the appliance. The first area to address for MSSQL connectivity surrounds Microsofts 1.0 ODBC driver for Linux. It can be directly downloaded and installed on the vCSA using curl.
Enter YES to accept the license or anything else to terminate the installation: YES Checking for 64 bit Linux compatible OS ..................................... OK Checking required libs are installed ................................. NOT FOUND unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED unixODBC Driver Manager configuration correct ...................... NOT CHECKED Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK Symbolic links for bcp and sqlcmd created ................................... OK Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED
You will find the install places the ODBC driver in /opt/microsoft
We need to edit the appliance odbcinst template file to include the newly added driver.
vcsa1:/ # vi /etc/vmware-vpx/odbcinst.ini.tpl
We need to append the following ODBC driver entry:
[MSSQL] Description = Microsoft ODBC driver for SQL v11 Driver = /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 UsageCount = 1 Threading = 1
The Microsoft driver will expect to have Openssl 1.0 available. It’s not installed on the appliance and I don’t feel it’s necessary either. We can just point to the installed 0.9.8 code and it will have no issues. Some symbolic links are all we need to get things rolling as shown here.
vcsa1:/tmp # ln -s /usr/lib64/libcrypto.so.0.9.8 /usr/lib64/libcrypto.so.10 vcsa1:/tmp # ln -s /usr/lib64/libssl.so.0.9.8 /usr/lib64/libssl.so.10
Tomcat as well needs to access the MSSQL server which requires a Microsoft JDBC driver and can be as well downloaded with curl.
vcsa1:/ # cd /tmp
vcsa1:/tmp # curl http://download.microsoft.com/download/0/2/A/02AAE597-3865-456C-AE7F-613F99F850A8/sqljdbc_4.0.2206.100_enu.tar.gz -o sqljdbc_4.0.2206.100_enu.tar.gz
vcsa1:/tmp # tar -xvf sqljdbc_4.0.2206.100_enu.tar.gz
vcsa1:/tmp # cp sqljdbc_4.0/enu/sqljdbc4.jar /usr/lib/vmware-vpx/common-jars/
I suspect that the JDBC driver is used within the Tomcat application to collect status info from ESX agents, but don’t hold me to that guess.
Once we have our MSSQL drivers in place we need to focus on hacking the config files and shell scripts. Let’s start with the web front end first.
Within /opt/vmware/share/htdocs/service/virtualcenter we find the appliance service configuration scripts and other various files. We need to edit the following files.
layout.xml – Database action fields field.properties – Database type field list values
We need to add the mssql DBType values to give us the option from the database configuration menu and to enable the action.
Layout needs the following segment replaced.
<changeHandlers> <!-- actions can be enable,disable,clear --> <onChange id="database.vc.type"> <field id="database.vc.server"> <if value="embedded" actions="disable,clear"/> <if value="UNCONFIGURED" actions="disable,clear"/> <if value="db2" actions="enable"/> <if value="oracle" actions="enable"/> <if value="mssql" actions="enable"/> </field> <field id="database.vc.port"> <if value="embedded" actions="disable,clear"/> <if value="UNCONFIGURED" actions="disable,clear"/> <if value="db2" actions="enable"/> <if value="oracle" actions="enable"/> <if value="mssql" actions="enable"/> </field> <field id="database.vc.instance"> <if value="embedded" actions="disable,clear"/> <if value="UNCONFIGURED" actions="disable,clear"/> <if value="db2" actions="enable"/> <if value="oracle" actions="enable"/> <if value="mssql" actions="enable"/> </field> <field id="database.vc.login"> <if value="embedded" actions="disable,clear"/> <if value="UNCONFIGURED" actions="disable,clear"/> <if value="db2" actions="enable"/> <if value="oracle" actions="enable"/> <if value="mssql" actions="enable"/> </field> <field id="database.vc.password"> <if value="embedded" actions="disable,clear"/> <if value="UNCONFIGURED" actions="disable,clear"/> <if value="db2" actions="enable"/> <if value="oracle" actions="enable"/> <if value="mssql" actions="enable"/> </field> </onChange> </changeHandlers>
Field.properties needs the following edit where we are adding mssql to the assignment statement.
database.type.vc.values = UNCONFIGURED;embedded;oracle;mssql
Once we have the web front end elements populated with the new values we can focus on the bash shell script. The scripts are located in /usr/sbin. We need to work the following script.
vpxd_servicecfg – This script needs the following subroutines replaced with one that formats the database connection string for mssql. There are two areas which need modification, do_db_test and do_db_write. The test section needs to accept mssql as a valid DBType and will, based on the DBType make a connection using a series of input parms like the server address user and instance. The cfg write routine needs to also detect the mssql DBType and do a custom mod for the db connection url. These calls depend on a proper mssql odbc driver configuration.
############################### # # Test DB configuration # do_db_test() { DB_TYPE=$1 DB_SERVER=$2 DB_PORT=$3 DB_INSTANCE=$4 DB_USER=$5 DB_PASSWORD=$6 log "Testing DB. Type ($DB_TYPE) Server ($DB_SERVER) Port ($DB_PORT) Instance ($DB_INSTANCE) User ($DB_USER)" case "$DB_TYPE" in "mssql" ) log "DB Type is MSSQL" ;; "oracle" ) ;; "embedded" ) set_embedded_db ;; *) log "ERROR: Invalid DB TYPE ($DB_TYPE)" RESULT=$ERROR_DB_INVALID_TYPE return 1 ;; esac if [[ -z "$DB_SERVER" ]]; then log "ERROR: DB Server was not specified" RESULT=$ERROR_DB_SERVER_NOT_FOUND return 1 fi ping_host "$DB_SERVER" if [[ $? -ne 0 ]]; then log "ERROR: Failed to ping DB server: " "$DB_SERVER" RESULT=$ERROR_DB_SERVER_NOT_FOUND return 1 fi # Check for spaces DB_PORT=`$SED 's/^ *$/0/' <<< $DB_PORT` # check for non-digits if [[ ! "$DB_PORT" =~ ^[0-9]+$ ]]; then log "Error: Invalid database port: " $DB_PORT RESULT=$ERROR_DB_SERVER_PORT_INVALID return 1 fi if [[ -z "$DB_PORT" || "$DB_PORT" == "0" ]]; then # Set port to default case "$DB_TYPE" in "db2") DB_PORT="50000" ;; "oracle") DB_PORT="1521" ;; *) DB_PORT="-1" ;; esac fi #Check whether numeric typeset -i xport xport=$(($DB_PORT+0)) if [ $xport -eq 0 ]; then log "Error: Invalid database port: " $DB_PORT RESULT=$ERROR_DB_SERVER_PORT_INVALID return 1 fi #Check whether within valid range if [[ $xport -lt 1 || $xport -gt 65535 ]]; then log "Error: Invalid database port: " $DB_PORT RESULT=$ERROR_DB_SERVER_PORT_INVALID return 1 fi if [[ -z "$DB_INSTANCE" ]]; then log "ERROR: DB instance was not specified" RESULT=$ERROR_DB_INSTANCE_NOT_FOUND return 1 fi if [[ -z "$DB_USER" ]]; then log "ERROR: DB user was not specified" RESULT=$ERROR_DB_CREDENTIALS_INVALID return 1 fi if [[ -z "$DB_PASSWORD" ]]; then log "ERROR: DB password was not specified" RESULT=$ERROR_DB_CREDENTIALS_INVALID return 1 fi if [ `date +%s` -lt `cat /etc/vmware-vpx/install.time` ]; then log "ERROR: Wrong system time" RESULT=$ERROR_DB_WRONG_TIME return 1 fi return 0 } ############################### # # Write DB configuration # do_db_write() { DB_TYPE=$1 DB_SERVER=$2 DB_PORT=$3 DB_INSTANCE=$4 DB_USER=$5 DB_PASSWORD=$6 case "$DB_TYPE" in "embedded" ) set_embedded_db_autostart on &>/dev/null start_embedded_db &>/dev/null if [[ $? -ne 0 ]]; then log "ERROR: Failed to start embedded DB" fi ;; * ) set_embedded_db_autostart off &>/dev/null stop_embedded_db &>/dev/null ;; esac set_embedded_db ESCAPED_DB_INSTANCE=$(escape_for_sed $DB_INSTANCE) ESCAPED_DB_TYPE=$(escape_for_sed $DB_TYPE) ESCAPED_DB_USER=$(escape_for_sed $DB_USER) # these may be changed below ESCAPED_DB_SERVER=$(escape_for_sed $DB_SERVER) ESCAPED_DB_PORT=$(escape_for_sed $DB_PORT) case "$DB_TYPE" in "db2") # Set port to default if its set to 0 if [[ "$DB_PORT" -eq 0 ]]; then DB_PORT=50000 ESCAPED_DB_PORT=$(escape_for_sed $DB_PORT) fi DRIVER_NAME="" URL="" FILE=`$MKTEMP` $CP $DB2CLI_INI_OUT $FILE 1>/dev/null 2>&1 DB_FILES[${#DB_FILES[*]}]="$DB2CLI_INI_OUT $FILE" # Store file tuple $SED -e "s!$TNS_SERVICE_SED_STRING!$ESCAPED_DB_INSTANCE!" -e "s!$SERVER_NAME_SED_STRING!$ESCAPED_DB_SERVER!" -e "s!$SERVER_PORT_SED_STRING!$ESCAPED_DB_PORT!" -e "s!$USER_ID_SED_STRING!$ESCAPED_DB_USER!" $DB2CLI_INI_IN > $DB2CLI_INI_OUT ;; "oracle") # Add [ ] around IPv6 addresses echo "$DB_SERVER" | grep -q '^[^[].*:' && DB_SERVER='['"$DB_SERVER"']' ;; "mssql" ) TNS_SERVICE=$DB_INSTANCE # Set port to default if its set to 0 if [[ "$DB_PORT" -eq 0 ]]; then DB_PORT=1433 ESCAPED_DB_PORT=$(escape_for_sed $DB_PORT) fi ;; esac if [[ "$DB_PORT" -eq 0 ]]; then DB_PORT=`get_default_db_port $DB_TYPE` fi # Save the original ODBC and DB configuration files FILE=`$MKTEMP` $CP $ODBC_INI_OUT $FILE 1>/dev/null 2>&1 DB_FILES[${#DB_FILES[*]}]="$ODBC_INI_OUT $FILE" # Store filename FILE=`$MKTEMP` $CP $ODBCINST_INI_OUT $FILE 1>/dev/null 2>&1 DB_FILES[${#DB_FILES[*]}]="$ODBCINST_INI_OUT $FILE" # Store filename # update the values ESCAPED_DB_SERVER=$(escape_for_sed $DB_SERVER) ESCAPED_DB_PORT=$(escape_for_sed $DB_PORT) # Create new configuration files $SED -e "s!$DB_TYPE_SED_STRING!$ESCAPED_DB_TYPE!" -e "s!$TNS_SERVICE_SED_STRING!$ESCAPED_DB_INSTANCE!" -e "s!$SERVER_NAME_SED_STRING!$ESCAPED_DB_SERVER!" -e "s!$SERVER_PORT_SED_STRING!$ESCAPED_DB_PORT!" -e "s!$USER_ID_SED_STRING!$ESCAPED_DB_USER!" $ODBC_INI_IN > $ODBC_INI_OUT $CP $ODBCINST_INI_IN $ODBCINST_INI_OUT 1>/dev/null 2>&1 do_jdbc_write "$DB_TYPE" "$DB_SERVER" "$DB_PORT" "$DB_INSTANCE" "$DB_USER" "$DB_PASSWORD" return 0 }
At this point the appliance MUST be restarted to work correctly.
With the hacks applied, our appliance is now capable of driving an MSSQL database. On the MSSQL server side you need to have the database created and named VCDB. You will also require an SQL user named vc which needs to be initially set as a sysadmin and once the database is initialized you can downgrade it as a dbo of only the VCDB.
The steps to add your database to the appliance are very easy and here are some screen shots of the web console database config panel to demonstrate this ease of implementation.
If your interested in trying it out I have included the files for release 5.0.0-455964 here.
/etc/vmware-vpx/odbcinst.ini.tpl
/opt/vmware/share/htdocs/service/virtualcenter/layout.xml
/opt/vmware/share/htdocs/service/virtualcenter/fields.properties
I have found no issue to date in my lab after 15 days, this does not mean it’s issue free and I would advise anyone to use caution. This was not tested with heavy loads.
Well I hope you found this blog entry to be interesting and possibly useful.
Regards,
Mike
Site Contents: © 2012 Mike La Spina
Subscribe in a reader
Follow me on Twitter