How does SQL Anywhere interact with the Windows Volume Shadow Copy Service (VSS)?

[VolkerBarth]

A while ago there has been a discussion on the SQL Anywhere Volume Shadow Copy Service (VSS) (dbvssX.exe) here:

SQLA and storage system snapshots

Besides the insights shared there, I'm still not sure what the following statements (particularly the 2nd one, emphasized in bold) from the docs does mean (note, it's basically the same text for v11 - v17):

By default, all SQL Anywhere databases can use the VSS service for backups if the SQL Anywhere VSS writer (dbvss12.exe) is running. You can use VSS without the SQL Anywhere VSS writer to back up databases. However, you might need to use the full SQL Anywhere recovery procedures to restore those databases.

The following paragraphs contain a description of the interaction between the OS VSS service and the SA VSS Writer but that again does not tell what happens when that SA VSS Writer is not running.

So what exactly does not happen (and what effects does it have) when the SA VSS Writer is not running (or has been disabled for the particular database engine via "-vss-")?

---

Aside: I'm about to set up a SA service in a Windows Server 2012 R2 Hyper-V environment with live migration, so I'm trying to find out whether the DBVSS12.exe would be required here.

[MCMartin]

and please share your experiences afterwards 😉

[VolkerBarth]

@Martin: As to the 2012 R2 Hyper-V environment:

What I wanted to find out is whether a SQL Anywhere engine running in a VM that is having a "Live Migration" (aka moving a VM from one host to another) would require a VSS writer. It does not, and the SQL Anywhere engine log does not reveal anything about that move as it seems to be invisible to the processes running within the VM. As I'm told by our sysadmins, the only thing a client app could notice would be a few lost pings. In my (few) tests, both applications running on the VM and on different client boxes continued to run without any issues.

(For VM experts: This is probably a very expected outcame - it's just not my area of expertise...)

---

Nevertheless, I have noticed that it is helpful to use the VSS Writer in our setup:

The whole VMs are backed up once a day (via Symantec BackupExec), and when that happens, the VSS Writer is activated and assures that a checkpoint is made so the database files are in a defined state then. (Of course, that's not our primary backup strategy, normal backups are regularly done from within the VM.)

[johnsmirnios · Accepted solution]

VSS creates a point-in-time snapshot of the filesystem(s). Without dbvss, using VSS to backup your database is equivalent to trying to recover you database from a crash (the files are in whatever state they happen to be in at the time the snapshot is taken). You need to go through crash recovery when you start the database.

Dbvss checkpoints the database and prevents modification to the database while the VSS snapshot is taken. This way, the snapshot contains a checkpointed version of the database and crash recovery is not required to start the database.

Strictly speaking, though, VSS imposes time limits on how long it will allow any "VSS writer" (such as dbvss) to prepare its files prior to taking the snapshot. So, it is still theoretically possible (although very unlikely) that the image of the database within the snapshot will not be checkpointed. Always back up your logs too.

[VolkerBarth · Accepted solution]

Ah, that makes it much clearer - so basically, without dbvss, there is simply no interaction at all between VSS and the database engine, and VSS - resp. the original backup process that has triggered VSS - will copy each database file "as is".

Dbvss checkpoints the database and prevents modification to the database while the VSS snapshot is taken.

As to the details: Does DBVSS really issue an explicit CHECKPOINT statement for each according database (which also gets logged in the console log and the like), or does it mimic the behaviour?

[Former Member · Accepted solution]

It's a real checkpoint. An immediate and complete one and the console log should show that happening on every database to be touched by VSS.

[johnsmirnios · Accepted solution]

There is one other possible (and subtle) feature of using dbvss. It tells the VSS system all of the files that are needed to back up the component properly: it lists all of the dbspace files & the log file. That can guide a user of VSS (ie backup software) to snapshot all involved disk volumes at the same time or to know which files to backup for a certain component (such as SQLAnywhere).