Skip to main content
Contact Us
Register
Pure Technical Services

Microsoft SQL Server FAQs

  1. Last updated
  2. Save as PDF

Currently viewing public documentation. Please login to access the full scope of documentation.

Frequently Asked Questions

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

  1. Frequently Asked Questions
    1. Can we replicate VSS snapshots?
    2. Can a SQL Server database be restored to a Point-in-Time (PiT)?
    3. How do I leverage FlashProtect Snapshots with Microsoft SQL Server?
    4. Can Volume Shadow Copy Service (VSS) be used with Microsoft SQL Server databases deployed in a virtual environments?  
    5. What is the impact of enabling Transparent Data Encryption (TDE) on SQL?

Can we replicate VSS snapshots?

Yes.   Important points to note: 

  • The FlashArray natively replicates based off of a Protection Group. Volumes that are targeted for a VSS, application consistent snapshot, cannot currently be part of a Protection Group. 

  • Using Protection Groups with VSS Snapshots, fully details how to support VSS and Protection Groups.

 

Can a SQL Server database be restored to a Point-in-Time (PiT)?

Yes, but not using diskshadow. For this you'll have to leverage our SSMS extension and/or our Powershell Backup SDK.

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, a tool that ships with Windows Server, and our SQL Server Management Studio extension as the Requester. The Writer called is the SqlWriterService which quiesces the SQL Server and then calls the HW Provider to take a volume level snapshot.

If the SSMS extension is used, and, if transaction log backups are made independently of the SSMS extension they can be used in a no-recovery VSS restore.

vssfaq1.png

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. A snapshot can be either restored with VSS or the snapshot can be copied to a volume and the database can be attached. Once the database is attached, it is open and has it’s own LSN chain.  Therefore, no logs can be applied. If a no-recovery restore is required, the SSMS extension must be used to perform a "no-recovery" restore. Additional transaction log backups can be copied to the log location and then the administrator can roll forward to a point in time.

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 with in-guest iSCSI, or 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, 

Volume Shadow Copy Management
https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/L_Volume_Shadow_Copy_Service_(VSS)/001_Volume_Shadow_Copy_Management

Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment
https://support.microsoft.com/en-us/help/956893/support-policy-for-microsoft-sql-server-products-that-are-running-in-a

Note:  If a customer is trying to achieve application consistency for their entire virtual environment, it can be done with a mingling of taking hypervisor-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 hypervisor-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. 

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.