I recently was asked by a DBA to investigate moving a vCenter 5 DB from SQL 2005 over to SQL 2008R2 and determine the best way to do it. I checked around and found that it was a fairly straight forward process, while adding a few manual steps for the DBS to recreate some of the SQL Agent jobs in the MSDB (You should really automate this VMware).
The DBA moved the DB without an issue and recreated the Agent jobs. All seemed well for awhile, but then something wierd happened. I was looking through some of the VM’s Performance charts in the Advaned tabs, and then somehow clicked on the Overview button (which I almost never look at). Bang – a big red error bullet – “Perf charts service experienced an internal error”. Hmmm. So I go to my friend Google and try my luck. VMware has two KB articles that I found that contained the subject I was looking for – KB1035561 and KB1012812. Both offered very little help since they are both written (and NOT updated) for vCenter 4. But, they did help me look for the right log – stats.log, although it is in a different location on Windows 2008R2 (Program DataVMwareVmware VirtualCenterLogs). In that log, I found the issue –
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host mysqlserver/INF, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
Our DBA’s have a 2008R2 cluster that serves many, many databases. In order to do that, SQL requires you to use different TCP ports for the seperate instances, not only for DB seperation, but also increased security. So, our instance isn’t listening on TCP 1433, but rather TCP 54321. On that note, I thought it was funny that VMware suggests to fix the non-standard port problem is to go in and change it back to 1433 on the SQL server. Ok VMware, so let’s just throw security out the door on this one, huh?
So I found the culprit, now to try and fix it. I tried several different things that Googling had me chasing my tail on like changing the ODBC string to add the port (the same as the Regsitry edits that VMware suggested – <mysqlservername><instance>,<port>). This didn’t work. After getting the DBA’s involved and running traces, they were insistent that the slashes in the default vcdb.properties file (the connection file that the Management Webservices service uses to make stats reports (i.e. Performance charting) were wrong. The original vcdb.properties file looked like this:
# For Windows, just deduce the JDBC URL and user/password from the # data source information in the VC registry
usevcdb=true
url=jdbc:sqlserver://mysqlserver/INF;databaseName=vcenterDB;integratedSecurity=true
dbtype=mssql driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
I then commented out the usevcdb=true line (as per VMware KB) and changed the url line to reflect the new port:
#usevcdb=true
url=jdbc:sqlserver://mysqlserver/INF:54321;databaseName=vcenterDB;integratedSecurity=true
Still no joy. The DBA’s said, “Trust us, add another slash before the instance name.” Ok.
url=jdbc:sqlserver://mysqlserver//INF:54321;databaseName=vcenterDB;integratedSecurity=true
Nope, still no-workey. On a whim, i Googled again, this time for JDBC. I found a document in the vSphere online documentation titled “JDBC URL Formats for the vCenter Server Database”. It showed examples of using the double slahes, but in the opposite direction (they still did not have any example for using an Instance Name though). So I tried this:
url=jdbc:sqlserver://mysqlserver\INF:54321;databaseName=vcenterDB;integratedSecurity=true
I restarted the Management Webservices service and Bingo! The stats log looked good with no errors, and I opened the vCenter client, clicked on the Overview button, and everything looked like nothing had ever gone wrong. All the charts were there.
This was a head-scratcher of a problem, confusing both myself and the DBA’s. VMware’s notorious lack of updated documentation and KB articles did not help the issue either. but, in the end, me and my best friend Google saved the day… again.