Skip to main content
Pure1 Support Portal

Microsoft SQL Server FAQs

Frequently Asked Questions

The following are common questions regarding how Volume Shadow Copy Service (VSS) and Microsoft SQL Server integrate.


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)?

No.

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?

  1. Refreshing Test/Dev environments is a excellent way to leverage FlashProtect Snapshots. Examples: SAP, Business Intelligence or Data Warehouses.
  2. 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). 

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 tricky because of the various snapshot steps involved. Even after the developing 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).