MSSQL & VMWare Snapshots

Taken from: https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

In general, snapshot usage should be limited and possibly avoided on VMs running production SQL Server workloads. For backup purposes, consider to use in-guest, agent-based backup. If snapshots should be taken, the following best practices should be considered:

Offline snapshot (a VM is powered off when a snapshot is taken) can be used without special considerations.

If an online snapshot (VM is powered on and Guest OS is running) needs to be taken:

  • Consider not using “Snapshot the virtual machine’s memory” option as this may stun a VM. Rely on SQL Server mechanisms to prevent data loss by losing in-memory data.

  • Use “Quiesce guest file system” option to ensure that a disk consistent snapshot will be taken. Special notes:

Consider not taking an online snapshot if VMware Tools are not installed or not functional as this may lead to the inconsistent disk state.Consider checking the status of the Volume Shadow Copy Service (VSS) on the Windows OS before taking a snapshot. If using a third-party utilities for snapshots, make sure there are VSS compliant

  • Be aware that on a highly loaded instance of SQL Server producing high number of disk I/O, snapshot operations (creation of an online snapshot, online removal of a snapshot) may take a long time and can potentially cause performance issues83. Consider planning the snapshots operations for non-peak hours, use offline creation/removal of snapshots, or use VVol technology with the storage array level snapshot integrations

Do not run a VM hosting a SQL Server instance on a snapshot for more than 72 hours

Depending on a workload and an environment this recommendation may vary, but in general should not exceed 72 hours, sometimes much shorter time is preferred

Snapshot is not a replacement for a backup. The delta disk file contains only references to the changes and not the changes itself.

Consider using VMFS6 and SEsparce snapshot for performance improvements.