December 9, 2009 at 11:15 am
I just read this article "SQL Server and VMWare: A Potentially Fatal Combination", link http://sqlsolutions.com/articles/articles/SQL_Server_and_VMware-A_Potentially_Fatal_Combination.htm
The point seems to be that when using SQL Server on VMWare, if a power failure occurs, there is a much greater likelihood of database corruption. Has anyone else encountered anything that would support what the author of this article says?
Our company has been moving all of our SQL Servers to VMWare, so this is relevant to us.
Thanks,
Randy
December 9, 2009 at 11:20 am
I am yet to read the article.
My immediate answer is, we are using or SQL Servers in VMWare, but also have a UPS that protects us from power failure.
Any database server, running either on dedicated system or on a virtual server has the potential chance of database corruption when a power failure occurs.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 9, 2009 at 11:24 am
One of the author's concerns was that the corruption problems didn't occur when SQL Server was on a physical server, but that the corruption consistently occurred when SQL Server was on a virtual server.
December 9, 2009 at 11:38 am
Yes I did read the article, and the author's concern might be right.
There are Pros and Cons for every thing (including SQL Server on VMWare), only way we keep moving forward is to avoid them (Power failures in this case)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 9, 2009 at 7:46 pm
I think the problem exists because...
SQL Server may ask Windows to tell it when an I/O operation is complete. This is mostly required to ensure that things like the transction log records are actually on disk before SQL Server is prepared to say that the transaction is committed. Once the transaction log record is committed, SQL Server knows that it the server crashes, it can roll forward(or backwards) using the transaction log records.
When VMWare gets involved, Windows does not actually access the physical drives. Instead there is a driver of some form within VMWare that pretends to be the disk drive that Windows sees. Physical disk write activity may be cached by VMWare even though Windows thinks that it has been written to the drive.
So, if the computer crashes, SQL thinks the data is on disk because Windows said is on disk. Windows thinks it is on disk because the VMWare drivers said that it is on disk. VMWare cached the data and may not have written it just yet.
So what you end up with is the MDF/NDF files having a mixture of pre-transaction data and some checkpointed data (not sure whether this is the correct term but SQL may write to the data files during a transaction) and no transaction log records to use to make everything consistent after transaction recovery has run (when the server restarts).
I wouldn't simply blame VMWare for this scenario. I am not a VMWare person but I would think that there may be some configuration settings that may be able to change this behaviour.
I have actually seen a similar scenario but with Windows physical server where the disk controller was configured to cache write activity but did not have any non-volatile RAM to use just in case of power outage. We had to reconfigure the disk controller so that it didn't cache writes (with a consequential drop in performance)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply