Frequently Asked Questions
The following are common questions regarding how Volume Shadow Copy Service (VSS) and Microsoft SQL Server integrate.
- Frequently Asked Questions
- Can we replicate VSS snapshots?
- Can a SQL Server database be restored to a Point-in-Time (PiT)?
- How do I leverage FlashProtect Snapshots with Microsoft SQL Server?
- Can Volume Shadow Copy Service (VSS) be used with Microsoft SQL Server databases deployed in a virtual environments?
- What is the impact of enabling Transparent Data Encryption (TDE) on SQL?
Can we replicate VSS snapshots?
No. Important points to note:
We replicate based off of a Protection Group. Volumes that are targeted at having an application consistent snapshot cannot currently be part of a Protection Group.
There is a workaround, Using Protection Groups with VSS Snapshots, that fully details how to support VSS and Protection Groups.
Can a SQL Server database be restored to a Point-in-Time (PiT)?
The Pure Storage VSS Hardware Provider is called by a VSS Writer, which is called by a VSS Requester. We currently support the use of Diskshadow as the Requester. Diskshadow is a tool that ships with Windows Server. The Writer called is the SqlWriterService which quiesces the SQL Server and then calls the HW Provider to take a volume level snapshot.
It is important to understand the state of the database when a Point in Time recovery is happening. The database can only be recovered from a FULL backup file and then logs played forward.
In order to play logs forward to a point in time, the database must be opened in recovery mode (restore from full backup) and the database cannot have it’s own Log Sequence Numbers (LSN) chain. Once the database is restored, it has it’s own LSN chain and logs can no longer be applied. Since we do not take a full backup of the database, the only thing we can do with the snapshot taken using VSS is “ATTACH” the database. Once the database is attached, it is open and has it’s own LSN chain. Therefore, no logs can be applied.
For more information see Backup Under the Simple Recovery Model.
How do I leverage FlashProtect Snapshots with Microsoft SQL Server?
- Refreshing Test/Dev environments is a excellent way to leverage FlashProtect Snapshots. Examples: SAP, Business Intelligence or Data Warehouses.
- The Microsoft SQL Server 2012 Reference Architecture recommends placing data (MDF/NDF) and log (LDF) files on a single volume. With all of the core files of a database being on a single volume a crash consistent snapshot can be taken of that volume. With this volume snapshot it can be connected to a host and then attach the database to that Point-in-Time (PiT). This makes for a quick and easy method of "copying" a database.
Can Volume Shadow Copy Service (VSS) be used with Microsoft SQL Server databases deployed in a virtual environments?
Yes, but only if the virtual guests running SQL Server have the database disks presented as VMware Raw Device Maps (RDMs) or Hyper-V Pass-through-Disks (PTDs).
Please reference the following documents for using VSS with Pure Storage VSS Hardware integration, and the Microsoft support policy for SQL Server in a virtual environment,
Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment
Note: If a customer is trying to achieve application consistency for their entire virtual environment, it can be done with a mingling of taking VMware-based snapshots to quiesce the virtual disk and then FlashProtect Snapshots. This can get complicated and require custom scripting. The process of recovering from the VMware-based snapshots can be cumbersome because of the various snapshot steps involved. Even after creating a scripting solution the virtual machine will be a crash consistent copy versus an application consistent copy.
In the case of Hyper-V the story is quite similar of creating virtual machine snapshots along with FlashProtect Snapshots of the volume or Clustered Shared Volume (CSV) which contains the virtual machines files (VHD/VHDX).
What is the impact of enabling Transparent Data Encryption (TDE) on SQL?
Enabling TDE on SQL will impact data reduction ratio on the FlashArray volumes connected to that SQL server, and may result in 1:1 DRR. Also performance can decrease due to the lack of compressibility of the data. While TDE may not be a best practice with our FlashArray, if it must be used, we would advise to enable row and page level compression on the SQL side.