Experiment: SQL Server and VMware)

In this test, we use WMPlayer 2.0.2 as the guest system and Vista HP as the host system. This issue may not affect ESX servers, but we are still waiting for confirmation from VMware: ESX servers fully respect Microsoft SQL Server's core requirements in terms of input/output.

Nowadays, virtual machines are becoming increasingly popular. They are often used to create security sandboxes on physical machines ". Administrators may mount some programs into these virtual machines and occasionally load SQL Server. Unfortunately, loading SQL Server into a virtual machine may cause a disaster!

Let's take a brief look at the database situation 30 years ago. What are a key feature of all databases before they have stored procedures, partition views, XML, and all other additional features today? From the very beginning, all databases, such as MySQL, and other "Toys" databases, include some types of pre-written log mechanisms to ensure data integrity. Ms SQL's LDF log, Oracle Database's Undo) log and redo) log, both ensure that the data is saved after the COMMIT statement is executed.

To implement this mechanism on Windows, SQL Server marks WRITETHRU and NOBUFFERING for the input and output operations. When SQL Server reads data, it uses its own high-speed cache to read data, rather than the operating system's high-speed cache. When SQL Server writes data, it requires the operating system to wait until the operation is completed. Note: SQL Server puts everything under its control and does not use the operating system's high-speed cache. This is a key difference between SQL Server and other applications ).

 

From the perspective of the host system, what happens when SQL Server runs in a virtual machine? The host system does not know the content in the security sandbox. The host system does not know whether it is running Windows, Linux or another operating system. This makes it completely unaware of the operating system tag in the virtual machine. Therefore, when the Virtual Machine "requests" the host system to write data, the method to meet this request is exactly the same as that of a common non-SQL Server application. The write operation is not synchronized. For details about using the cache, see ).

This difference has several influences, both good and bad. Let's talk about the good impact. Performance Monitor Perfmon) the data of the tool shows that SQL Server running under VMWare is often faster )!
 

 

Considering that the Performance Monitor Tool only updates the chart once per second, we had to manually slow down the input/output system to see its impact ., After the write operation is completed on the virtual machine, the vertical green line is used as the demarcation point), there are still write operations on the physical machine.

This has a bad impact. Note: When a COMMIT statement is executed on a VM, the data is not sent to the input/output system. Data is only sent to the physical machine. Unfortunately, when SQL Server and VMWare run together, interference factors such as power outages may cause you to lose transaction data reported as "committed. Even worse, in case of such interference, the cache will change the write order, resulting in database corruption!