Hyper-V Clustering: Guest Connection Problem

Virtualization is pretty neat. It allows you to consolidate several servers that you have into fewer servers, this will allow us to cut down on power consumption (make us more green and save us some money). For server virtualization, Microsoft has Hyper-V. With Windows Server 2008 and Windows Server 2008 R2, we can set up Hyper-V to have failover clustering. This is cool since it would allow us to have a high availability environment associated with the virtualized environment. In addition, since Windows Server 2008 R2, Microsoft has added live migration capability in Hyper-V … super cool. Live migration basically allow us to move virtual system (while it is live/running) from one host node to another host node. You don’t have to shut down the virtual system. From the system administrator perspective, this is very helpful, let say that you need to reboot one of the host node (let say after a Microsoft security patch), you can just live migrate the guest system from that host node to another host node in the cluster, and reboot the host node without causing significant downtime to the guest system.

Setup

Recently, we have the opportunity to set up Hyper-V clustering on Dell PowerEdge 710 servers connected to a SAN box using Windows Server 2008 R2 Enterprise Edition. The set up itself went without any problem. We were able to set up a guest (virtual) system and was able to do the live migration back and forth from one host node to another without any problem.

Problem

Everything was running smoothly for a few days, then suddenly one day, the guest system just suddenly dropped from the network. We could not even ping the system. The only way to connect to the system is by using the Hyper-V manager. The guest system itself is up and running, but its network connection just died or hang. We waited to see if it would recover by itself (for about a half day), but it would not. The only way to get the network connection back is to restart the guest system. There are not much clue given by the Windows event log on the guest system, and nothing on the host system. The only error that we are seeing in the Windows event log on the guest system was:

The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ hung.

And

The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ reset.

In term of patches, it seemed that we have downloaded and applied the latest Microsoft patches on both the host and guest systems. We noticed that this problem happened during the time when we copied large sized files (about 500+ GB in total size) from the guest system onto another system in the network. So we tried that again (copying large sized files from the guest system onto another system in the network), and sure enough the network adapter on the guest system would stop working. Strangely, the problem did not appear when we tried copying large sized file from other system in the network onto the guest system. What could it be?

Solution

After searching the Microsoft and Dell sites, we found one Microsoft KB article that seems to provide a hotfix to the problem that we have. It is KB article 974909 (The network connection of a running Hyper-V virtual machine is lost under heavy outgoing network traffic on a Windows Server 2008 R2-based computer). You can find it here. Well what do you know … the title of the KB article actually describe the problem that we were having. So here’s what we did:

• Request the hotfix download from Microsoft
• Download the hotfix
• Backup the guest system
• Shutdown the guest system
• Apply the hotfix on all of the host nodes of the Hyper-V cluster
• Restart the Hyper-V host nodes
• Restart the guest system
• Re-install the Integration service on the guest system

Once those steps were done, we tested the guest system again by copying large sized file from that guest system onto another system in the network. That hotfix seems to have fixed the problem.

One thing that I must say is that before you attempt to re-install the integration service on the guest system, I would strongly recommend that you backup the guest system, one possible way is by creating a snapshot in Hyper-V manager of that guest system. The reason for this is because, when I was trying to re-install the integration service on one of the guest system, I ran into the following error during the install process:

An error has occurred: One of the update processes returned error code 61658.

To resolve this error, we had to do the following:

• Restore the guest system from the snapshot
• While the guest system is off, add the Legacy Network Adapter to the guest system. We did this from the Hyper-V manager.
• Turn on the guest system
• Install the Integration service on the guest system
• Turn off the guest system
• Removed the Legacy Network Adapter
• Turn on the guest system

Lots of steps but somehow that seems to do the trick. We got this solution from Michael Phillip blog post. Since then, the guest (virtual) system has been running great, no more connectivity problem.

Some Notes:

• While researching on this problem, I came across Michael Hanes’ blog in which he listed the hotfixes needed for Windows Server 2008 R2. You might want to check his blog out. It can be found here.
• You might want to also consider calling the Microsoft Support. They might charge you, but they are usually good at troubleshooting this type of problem.

Troubleshooting SQL Server Performance Problem

Probably one of the most inquiries that a Database Administrator get from users are regarding SQL Server performance. As data, number of users, and activities grow, SQL Server performance *might* take a hit. There are many reasons for SQL Server performance problem, such as missing indexes, out of sync query optimization statistics, lack of CPU power or memory, and others. Microsoft actually has come out with great papers on how to troubleshoot SQL Server performance problem. They provide some guidance on how to diagnose the SQL Server performance problem. The white papers are using tools that are readily available such as SQL Server Profiler, System Monitor and DMVs (so no third party is needed).

The white papers:

• For SQL Server 2005, you can find it here.
• For SQL Server 2008, you can find it here.

SQL Table Space Utilization

Working as a database administrator, more often than not, I want to know which are my largest tables. This can be handy to help us to identify tables that might be good candidate for table partitioning. We could also use the information to help determine if data in a particular table might be suitable to be archived.

One stored procedure that can be use to get the detail about space usage of a particular table in SQL Server database is:

sp_spaceused

Detailed information about this stored procedure can be found here.

How to Use the Stored Procedure

Let say that we want to find the space usage for Person.Person table in AdventureWorks database. You can get that information by running the following query:

USE [AdventureWorks];

EXEC sp_spaceused 'Person.Person';

Ok, that's great, but that only return space usage information for one table. What if we want to get space usage information for all tables within one particular database (let say AdventureWorks). To do that, you can run the following query:

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats;
 
DROP TABLE #TableStats;

In the query script above, basically we created a temporary table and then run the sp_spaceused stored procedure against each tables within the AdventureWorks database using the sp_MSForEachTable stored procedure. Once that's done, we just query the temporary table and drop the temporary table.

Now, let say that I want to find the top 10 largest table in AdventureWorks database in term of data size. The Data column returned by the sp_spaceused is using VARCHAR data type. So one way to sort it correctly, we can use the following query (basically in the ORDER clause we just need to convert the VARCHAR into INT.):

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT TOP 10 Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats
ORDER BY CAST(REPLACE(Data,' KB', '') AS INT) DESC;
 
DROP TABLE #TableStats;

Some Notes:

• As noted in the SQL Server Book on line, if you use sp_spaceused without passing any parameter, you will get the space utilization of the database that you are currently connected to.
• There is a second optional parameter that you can passed onto sp_spaceused, and that's to specify whether or not to update usage statistics (using DBCC UPDATEUSAGE). The parameter accept 'TRUE' or 'FALSE' value, with 'FALSE' set as the default.

Auto Shrink Problem

A few months back, on one of our SQL Server 2005 system, our ETL process would failed and in the application log, we would get the following error:

Error: 1105, Severity: 17, State: 2

Could not allocate space for object ‘[Table_Name]’ in database ‘[Database_Name]’ because the ‘PRIMARY’ filegroup is full.

When seeing this error, my immediate thought was that we might have run out of disk space or the maximum file size that was set on the database Autogrowth setting has been reached. But then when I check those things, those were not the case. We seemed to have more than enough disk space to accommodate the new data, and both the data and log current file size were far below the specified maximum file size.

What could cause this error? So I decided to re-run the ETL process and monitor it. During one of the ETL process, one weird thing that I noticed is that in the middle of the ETL process, the target database data file size suddenly reduced in size, and not long after that, the ETL process would stopped with the above error. Granted that in one of the ETL steps, we truncate data from several of our existing tables, but I was not expecting to see the database data file shrink. Seeing this behavior, I quickly looked at the target database options and sure enough the Auto Shrink option for the database is turned on.

Auto Shrink feature could help to release free space that you have on your database file automatically. The problem is that there is no additional settings that allow you to control on when the Auto Shrink feature should run. In my case, the Auto Shrink shrank the database file during the ETL run and that was not good. So I turned off the Auto Shrink option on the database, and since then I have not gotten the above errors on subsequent ETL runs that we have.

Some Notes:

• Paul Randall (blog | twitter) explains it best on this article in regards to why we should turn off Auto Shrink option from our database.
• Microsoft best practices recommendation is to set the Auto Shrink option off, based on this article.

Patching a Punctured Aerobed

Aerobed is a great invention. It is one of those "great to have" gizmo in the closet. If you have family members/guests coming over for the night, all you need to do is to take the Aerobed out from the closet, plug it into the power outlet, flick a switch ...lo and behold in less than 5 minutes you have an extra bed. It is not as comfortable as a spring bed (although some people might argue that), but it is definitely much more comfortable than sleeping on a sleeping bag, or a couch (in some cases).

Air Leak

One of the worse things that can happen to an Aerobed is a puncture hole. Even the smallest hole can render the Aerobed useless. This actually happened to my Aerobed. Every time I inflate the Aerobed, within a few hours it would sagged so badly that I can not use it comfortably.

Fixing the Leak

Normally, Aerobed comes with a repair kit. But it seemed that I have misplaced mine. So I ended up using:

• Kwik Tek Vinyl Repair Kit (the kit contains a tube of glue like substance and several vinyl patches)
• Eclectic Shoe Goo Clear

As for patching the small hole that was on the Aerobed, I follow the following steps:

• Clean the area around the small hole on the Aerobed
• Apply generous amount of the glue from the Kwik Tek Vinyl Repair Kit to one of the vinyl patch. Then cover the small hole with the vinyl patch.
• Put some weight on the patch and let it sit for about 24 hours. I used the weight of several books. (Notes: After 24 hours, I tried to inflate the Aerobed, however, I still notice some air leak around the edges of the vinyl patch).
• So, I applied generous amount of Eclectic Shoe Goo around the edges of the vinyl patch, and let it sit for another 24 hours.

After I allow the Eclectic Shoe Goo to dry, I inflated the Aerobed and use it for several days. I must say that so far things are looking good. I did not notice any air leak coming from the area of the hole. Also for those several days, the Aerobed stayed inflated and firm.

Some Notes:

• I must say, if the hole on the Aerobed is really small, I think we can fix the Aerobed by just applying a generous portion of Shoe Goo.
• The glue from the Kwik Tek Vinyl Repair Kit and the Shoe Goo have a strong odor, so you might want to use them in well ventilated room.