VMWare + SQL Server = Random Dropped SQL Server Connections

Hi there!  I have an ESX 2.5.3 (build 22981) VMWare installation running, among other things, a SQL Server 2005 under Windows Server 2003 Enterprise.  Periodically, at what seems to be random (un-logged) intervals, the SQL Server "loses" various client connections.  Just disconnects them, regardless of KeepAlive settings (which I moved from 30,000 down to 10,000).

Things could be complicated by the fact that the Client software is a hokey Accounting package written using MS Access 2K3 as a front end, but connecting to SQL Server for data.  But I'm not too sure that Access is to blame, this time - it appears that the common problem is the (Virtualized) SQL Server.

Before we virutalized everything, and the SQL Server/2K3 Server were "metal", this did not occur.  Searching VMWare's KBs have shed literally no light here, and Microsoft's documentation seems to think that by setting the KeepAlive to a shorter interval will resolve the problem - which it hasn't.

I am aware and have studied <http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx> and <http://support.microsoft.com/kb/137983/?sd=RMVP&fr=1>, neither of which seem to help.  And my users, while patient, are curious to see if we might de-virutalize this server - if that will help their connectivity issues.

Any ideas what to check?  I'm admittedly a noob when it comes to VMWare - I love it, but I'm not sure I know enough to get the most out of it.

Thanks (in advance) for your time and trouble,

- The Lurking LongFist
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How many VM's are being hosted on the host? I've had issues where SQL is robbed of memory as VMWare was starved and trying to divvy it up as it saw fit.

LongFistAuthor Commented:
Thank you for your prompt response!

There are three of 'em "in there": the DNS Box, the Intra-Net Server Box, and the SQL Server Box.  Intra-Net is currently idling, as its application(s) was/were lost during the big (ugly) crash of May, wherein we learned that my predecessor had no clue at all what a disaster recovery system was, nor how to impliment one (if he knew).  So, we've got basically a DNS/File Server and a SQL Server, apparently in a shootout for resources.

How could I tell?  The machine they're on is never over 33% utilized at any one time:

Virtual Machines: 25 %
System Services: 6 %
System Total: 31 %
...and the only place it gets even close is RAM utilization - but it's not close to "the red" yet:

Total Memory (3.8 G)
Virtual Machines: 1.8 G
System Services: 601.6 M
System Total: 2.4 G
...is there some sort of logger or other tool (beyond the web interface) that might allow me to watch this?
I'm going to guess that each VM is allocated 512MB ? From memory; that's the lowerst reccomended memory for SQL. If you've got a lot of connections using SQL; you may find it cant keep up due to resource issues.

I would suggest bumping the SQL Server memory upto 1024MB / 1GB and see how you get on; If that's no good; try increasing the memory upto 1.5GB; then to 2GB.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LongFistAuthor Commented:
Will do - unfortunately I can only perform these changes with the VM down (I know, tell you something we all don't already know - sorry!) so I'll need to wait until after 17:00 to implement the changes - I think you may have hit it right on the head.

Last night, after all the excitement died down, I found that someone had re-started a now-defunct server, which (among other things) was responsible for "pushing into the orange" on RAM.  So I killed it, and watched the numbers drop:

Virtual Machines: 14 %
System Services: 5 %
System Total: 19 %

Memory (3.8 G)
Virtual Machines: 1.3 G
System Services: 535.0 M
System Total: 1.9 G
...and for some strange reason, nobody had any complaints this morning!

So, I need to "upgrade" the SQL Server box with more RAM; starting out at 1GB and (possibly) moving up.  Sounds like a plan.

I will return and report results soonest - but it'll be about 7 or so hours (from this post) before I can effect change, and approximately 14 hours after that before any real results can be noted.  However, I will monitor this channel for the duration, just in case.

Thanks for your timely help and advice!!!
LongFistAuthor Commented:
Changes complete: VM now has 1024 MB (1 GB) assigned to it - and the first thing it did was take up 769.0 MB of it!  In less than ten minutes it had claimed all 1024 MB available to it - so I guess it's just hide-n-watch time, right?

I'll return and report soonest.  Thanks again for your time and timely attention!
No Worries.

By nature; SQL, IIS and Exchange all try and grab as much memory as possible. Dont forget that the OS will be taking a portion of that 1GB. Depending on how brave you are and if you can replicate this in a test environment; you can control how much memory SQL grabs by using the Max Memory settings (SQL Management Studio; Right Click on Server Name, Properties -> Memory)

LongFistAuthor Commented:
Doubling the available RAM has resolved the connection issue -  even though the microsoft systems are RAM-hungry.  I'll have to keep an eye on my other VM servers - "butterfly effect" may cause other unintended consequences - but we're up and running - thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.