SQL

Virtualizing Microsoft SQL? Consider per-processor licensing

In a recent SearchVMware.com tip, I outlined situations where it does not make sense to have SQL or Exchange virtualized. The bottom line is that licencing depends on many factors, and you need to consider licencing when virtualizing such applications or you could end up spending more money than necessary. One point worth noting that is not in the tip is if you opt to license Microsoft SQL Server per-processor, it may carry additional benefits.

Continued »

New resources for the VirtualCenter certificate renewal process

Earlier this year, I posted a blog entry about the certificate configuration for VirtualCenter installations and the fact that the certificate does not get upgraded or renewed as you perform upgrades of VirtualCenter. The default certificate of a VirtualCenter installation is valid for two years.

Certificate management is not one of my areas of expertise. With that, I’m posting this series of blog posts with the hopes that it will help other admins complete this mundane task seamlessly. I am currently faced with upgrading the certificate configuration for my VirtualCenter 2.5 Update 2 system. The VirtualCenter default certificate is made up of three files: rui.cert, rui.key and rui.pfx. All are located in the C:\Documents and Settings\All Users\Application Data\VMware\VMware VirtualCenter\SSL folder for default installations. Now these certificates are SSL certificates, or web certificates that manage the communication between elements of VI3. This includes ESX hosts, VMware Infrastructure Client connections, the database and VirtualCenter Server connections.

In the earlier post, I mentioned VMware’s PDF as a good starting point for the certificate renewal process. The PDF explains a lot of different things, but leaves a few key areas out about how to fix the immediate problem. Luckily, I came across a very handy blog that simplifies things and gave me easy steps to follow. Leo Raikhman’s Ramblings blog gives direct guidance for those of us who are a little fuzzy with certificates. Leo points out in three separate blogs a basic way to address the problem, a 1-2-3 approach, and some in-depth explanation for an occasional zero-length pfx file causing generation issues.

I have successfully followed Leo’s materials for a seamless upgrade in an isolated test environment with no workload. Soon, I will repeat the drill on the live environment and let you know how it goes in another blog post.

VirtualCenter 2.5 database issue during upgrade

Last week, I wrote about about the database index defragmentation. I discussed reading the scan index and that if the percentage is poor, a defragmentation or index rebuild would be in line. There is more to the database and VirtualCenter 2.5 upgrade story, as I have found out, regarding special permissions. Today I’ll share those my findings with anyone else who may have upgraded to VirtualCenter 2.5 quickly.

The good news is that there is now a VMware KB article about this topic in the known issues section of the release notes. When I upgraded in December of 2007, this was not available. My issue was that although I had correct permissions with the username and password in SQL authentication to the VirtualCenter database, this account did not have the correct permissions to create the SQL jobs. VirtualCenter 2.5 creates three default SQL Agent jobs to manage statistics:

-Past Day stats rollup
-Past Week stats rollup
-Past Month stats rollup

These jobs move data from the VPX_HIST_STAT1 system to the VPX_HIST_STAT2, VPX_HIST_STAT3, VPX_HIST_STAT4 and eventually out of the system. If you upgraded your VirtualCenter with an account that did not have the ability to create these jobs, they likely are not running. The easy indicator is the VPX_HIST_STAT1 table will have millions of records, and the other VPX_HIST_STATx tables will have no records.

I had to call VMware support to confirm this, and once we noticed that the jobs were not present the solution was clear. However, the job took a long time to catch up on the statistics management.

The unfortunate situation is that the VirtualCenter install does not give an error if these jobs cannot be created.

VirtualCenter 2.5 database index defragmentation

I, like many virtualization administrators, have worked very hard to get the VMware virtual environment set up and running as expected. Now, one of my main tasks is to make sure that we do not do anything to adversely effect server performance. A good place to start in this regard is the VirtualCenter (VC) database. That being said, the VC database is critically important to a successful ESX implementation, so do not do anything that is not advised by VMware documentation or support services. Let’s discuss index defragmentation in particular here when using Microsoft SQL server 2000 for the VC database.

Index defragmentation on statistics

I will save you some work in what to look for in determining which tables will need index defragmentation - statistics. While we all like the statistics and graphing options available in the VMware Infrastructure Client and virtual appliances that may use the table, there can be a great amount of data in that table and it can quickly become fragmented. A fragmented index in a database is similar to a fragmented file system where the ordering of an index is not in the order of the index.

In my VC 2.5 environment, the VPX_HIST_STAT1 table is the heavy hitter. For this database maintenance, I’m going to start with the white paper entitled “VirtualCenter Database Maintenance” available from the VMware website. Here there is a command to check your current fragmentation level:

USE
GO
DBCC SHOWCONTIG (VPX_HIST_STAT1)
GO

I have modified the command to use the table name, as the white paper is VC 2.0 based on the table name, whereas this example is on VC 2.5. The result will look something like the following:

DBCC SHOWCONTIG scanning ‘VPX_HIST_STAT1′ table…
Table: ‘VPX_HIST_STAT1′ (800721905); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned…………………………..: 505458
- Extents Scanned…………………………: 78097
- Extent Switches…………………………: 457307
- Avg. Pages per Extent……………………: 7.4
- Scan Density [Best Count:Actual Count]…….: 22.34% [113183:905308]
- Logical Scan Fragmentation ………………: 3.81%
- Extent Scan Fragmentation ……………….: 0.47%
- Avg. Bytes Free per Page…………………: 187.2
- Avg. Page Density (full)…………………: 97.69%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The key takeaway is the Scan Density percentage. The white paper advises that a number close to 100% is good, meaning that the table index in the example above is quite fragmented. The white paper goes on to identify two correction levels for improving the index. Index defragmentation and, more aggressively, rebuild are the standard options to address the index. If the scan density after a index defragmentation does not do enough to improve the index, database admins will have to begin the rebuild process. A caveat: rebuilding requires VC downtime to perform the database maintenance.

By comparison, here the same command on the VPX_EVENT table. This table is busy, but not near as much as the statistics table:

DBCC SHOWCONTIG scanning ‘VPX_EVENT’ table…
Table: ‘VPX_EVENT’ (36195179); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned…………………………..: 594
- Extents Scanned…………………………: 86
- Extent Switches…………………………: 132
- Avg. Pages per Extent……………………: 6.9
- Scan Density [Best Count:Actual Count]…….: 66.39% [75:133]
- Logical Scan Fragmentation ………………: 6.73%
- Extent Scan Fragmentation ……………….: 98.84%
- Avg. Bytes Free per Page…………………: 150.5
- Avg. Page Density (full)…………………: 98.14%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This table is in better shape, but is much smaller than the statistics table.

Configure statistics logging level

As virtualization administrators, one safeguard we can perform is to limit the logging levels within the VMware Infrastructure Client. To access the logging levels select Administration menu, then VirtualCenter Management Server Configuration, then Statistics. Here you want to limit the number of high level logging to keep the VPX_HIST_STATx tables in check:

In selecting which level works best for your environment, be sure to identify any monitoring tools or virtual appliances that may read the selected tables. Also be sure to benchmark your database size and index fragmentation to see if you gain any improvements. Identifying the parts of the entire VMware Infrastructure environment that you can keep in maintenance mode will make your job as a virtualization administrator much easier.

ESX 3.5: Configuring for HA errors

I recently upgraded to ESX 3.5 on a test system, and had an issue that was really stressing me out. The issue was that each time I would perform the “Reconfigure for HA” task, I had errors causing the task to fail and the new ESX host sits there with a red triangle like a car broken down on the side of the highway. The log message that runs in the Virtual Infrastructure Client was largely useless, so I jump into the VMware ESX database. For this situation, I have looked into the VPX_EVENT table and saw the following event:

Event_Type
vim.event.HostShortNameToIpFailedEvent
Host_Name
ESX-35-DEV-001.AMCS.TLD

This message gave me a starting point and I found that in my service console network configuration the DNS suffix order was not the same for all hosts. Specifically, I forgot one DNS suffix in the order and that name made sense to me:

Therfore, the takeaway is when building ESX servers, ensure the configuration for all hosts in a cluster (or datacenter) within Virtual Center have the correct configuration where relevant for DNS, subnet mask, interface naming, and storage configuration.

Monitor the Virtual Infrastructure Database for authentication attempts

Because VMware ESX and Virtual Center (VC) have great magnitude in the datacenter, I determined it would be a good idea to have an audit trail of authentication attempts in to the Virtual Infrastructure Client and SSH on the host. In my recently upgraded VC 2.5 environment, I made a quick trip to the database to query some of the authentication events. Here are some queries of the VC database running on Microsoft SQL Server that may be useful.

Failed authentication attempts

This query in Query Analyzer will show the failed authentication attempts into VC or the ESX host:

[Using VMware Database]
select EVENT_TYPE, USERNAME, CREATE_TIME, HOST_NAME
from VPX_EVENT
Where EVENT_TYPE = ‘vim.event.BadUserNameSessionEvent’

This will show you the failed authentication attempts. What you want to look for is perpetual attempts, or attempts from usernames that you are not expecting to log into VC. If you want to run the same query with all fields, replace the EVENT_TYPE, CREATE_TIME, HOST_NAME with a ‘*’ or add additional criteria with time conditioning. You may consider putting in SQL monitors or alerts for this condition – or simply making a daily report for the failed authentication attempts that is accessible for audit purposes. Should you also have authentication attempts to the ESX host (SSH), those attempts would be failed and in this query result.

Successful authentication attempts
Just as it is important to monitor failed authentication attempts, you may have a need to have an audit trail of successful connections. Within the VC database, this query would run showing successful logon events within the Virtual Infrastructure Client or directly to the ESX host:

select EVENT_TYPE, USERNAME, CREATE_TIME, HOST_NAME
from VPX_EVENT
Where EVENT_TYPE = ‘vim.event.UserLoginSessionEvent’

This will show the successful results within the Virtual Infrastructure Client and any logon attempts via SSH to the ESX host. This can provide a solid audit trail with some SQL jobs or other reporting that you can do against the SQL database.

Database general rule of thumb for safety
Whenever you get into the database, use extreme caution. A good safe practice would be to back up the database and restore it new somewhere else, and practice all queries, jobs or reporting you want to do against the VC database. This way, once you have your monitoring elements safe and clearly defined you can roll them into your live environment confidently.