3

I have database backups configured via SAN disk tool. In the backupset table I can see the VSS writer service is taking full backups of type snapshot.

There are 85 databases on the server.

I know about the concept of database snapshot and that initially it is of 0 size. And as the source db is updated, it pushes the changed to the snapshot thus increasing the snapshot size.

I want to ask whether:

  1. The database snapshot and VSS database snapshot backup (via VSS writer service) is the same concept?

  2. For both the database snapshot and the vss database snapshot backup- suppose there are 85 items (snapshots/snapshot backups) then this will cause overall poor IO on the sql server since it has to regularly maintain (update as the source db gets changes) snapshot of 85 databases?

8

Answering Your 1st question

The database snapshot and VSS database snapshot backup (via VSS writer service) is the same concept?

No, they are different concepts.

Database Snapshots

The Database Snapshots (SQL Server) (Microsoft | SQL Docs) provide you with the ability to create a read-only copy of an existing database on the same instance.

This is not a backup as you can read up on in the Limitations on Database Snapshots section of the reference article:

...
You cannot back up or restore database snapshots.
...

You are correct that I/O can become an issue for the source database, as stated in the Limitations on the Source Database portion of the same article:

...
Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
...

VSS Database Snapshots

SQL Server Backup Applications - Volume Shadow Copy Service (VSS) and SQL Writer are the programs that ensure you can create a snapshot backup of a given database. This is considered a valid database backup, but with limitations.

When a snapshot of a database is created via a 3rd party tool, then this will normally trigger the VSS writer of the Windows host system. The SQL Writer will handle the VSS interactions on the SQL Server side.

Database backups which are created using snapshot functionality will be marked accordingly in the msdb backup history in the column msdb.dbo.backupset.is_snapshot.

TLOG backups are not supported as per the documentation What is Not Supported.

...
Log backups are not supported by the SQL writer.
...

Answering Your 2nd question

For both the database snapshot and the vss database snapshot backup- suppose there are 85 items (snapshots/snapshot backups) then this will cause overall poor IO on the sql server since it has to regularly maintain (update as the source db gets changes) snapshot of 85 databases?

As per the documentations provided only the database snapshots will cause long term I/O issues.

The database snapshot backups cause only short term I/O issues during the creation of the snapshot.

...unless you are talking about VMware snapshots, which is a totally different issue. If this is the case, then this follow-up question would be better suited over on severfault.com

Answering Follow Up Questions From Comments

How is the snapshot backup able to finish so quickly?

The VSS database snapshots that uses the VSS service and the SQL Writer Service are triggered by an outside source. Why they are fast, depends on how the environment performing the snapshot implements the snapshotting feature and also depends on the underlying technology involved.

I can only give a second hand account on how NetApp performs such an action based on the information I have from our environment with VMware & Commvault. Commvault is the leader in our environment, as it performs our backups.

  1. Commvault triggers a snapshot of the server.

  2. VMware receives the request for a snapshot and hands this down to the virtual servers.

  3. The (virtual) Windows Servers receive the request and trigger the VSS service to perform a snapshot.

  4. The SQL Writer service is informed and freezes the reads/writes on the database.

  5. When everything is ready for the snapshot and synced, then the NetApp layer will perform a block level snapshot of the server's disks at a precise moment when everything is frozen.

    Because NetApp provides a block level pointer based snapshot feature (you'll have to ask them for details) at the disk level, all modified blocks are accounted for since the last snapshot and the pointers know where to find the state of the server at the snapshot time.

  6. Once the snapshot has been performed (mostly in miliseconds), then the frozen I/O on the SQL Server instances are released and the databases are available again.

I think that vss snapshot backup is quick because it works just like a normal snapshot. And the backup has to be continuously kept up to date when a change is made to original database. So, as the source db gets changes, the overall disk IO will be slow because the change has to be passed on to the snapshot backup as well

Yes, I would agree that a database snapshot will result in continuous I/O delays due to the changes that have to be logged to the database snapshot.

2
  • How is the snapshot backup able to finish so quickly?
    – variable
    Oct 8 '21 at 5:13
  • I think that vss snapshot backup is quick because it works just like a normal snapshot. And the backup has to be continuously kept up to date when a change is made to original database. So, as the source db gets changes, the overall disk IO will be slow because the change has to be passed on to the snapshot backup as well.
    – variable
    Oct 8 '21 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.