I know that in the past the recommendation for SQLA was, that backups should be created using the internal mechanisms. Anyway I am wondering if with the arrival of new storage technologies this rule might have changed.

So my question is, would it be possible and if yes, what disadvantages would I encounter if the underlying storage system will perform snapshot copies of the database every now and then. Will these snapshots be usable and if yes, how much data might I loose.

asked 06 Mar '14, 04:36

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

Comment Text Removed
Comment Text Removed

This should be a comment because that's where rhetorical questions go, but I wanted to share a video...

Let's talk about a specific "snapshot" implementation: Windows Shadow Copy, also known as Volume Snapshot Service (VSS), and in particular, that version of VSS delivered with all editions of Windows 7: It allows "any file to be retrieved as it existed at the time any of the snapshots was made".

Presumably, that means snapshots of the SQL Anywhere database.db and database.log are made at the same instant in time, and (again presumably) the snapshot is perfect as far as the bytes on disk that Windows knows about are concerned.

The question arises: From a freshly-started SQL Anywhere engine's perspective, during restore/recovery, are "the bytes on disk that Windows knew about" sufficient to launch a new executing instance of the database?

What about all those "dirty" and "cleanable" pages that were in the RAM cache and nowhere else, at the time the snapshot was taken? (with the difference between "dirty" and "cleanable" discussed recently).

FWIW SQL Anywhere dbbackup.exe takes a checkpoint before the backup starts. Plus, if the database is large enough, even snapshot copies of both files at the same time, every time, might be too slow, and multiple incremental log backups in between full copies of the database.db might be necessary... how does that fit with shadow copy behavior?

That's as far as I can go down the rabbit hole, I had to give back the red key :)...

Morpheus

alt text

permanent link

answered 06 Mar '14, 08:37

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 06 Mar '14, 11:13

(06 Mar '14, 08:38) Breck Carter
Replies hidden

To cite the docs:

VSS issues a freeze command to checkpoint and then suspend all activity on all databases on all database servers. Each SQL Anywhere database server waits a maximum of 60 seconds for all databases to suspend all activity. Typically, this process takes a few seconds.

That would make me think in case the checkpoint(s) can be executed fast enough, the copied files should be as good as a normal backup for recovery (however without the normal "goodies" of already applying the checkpoint log and the like). But that's just my understanding, surely not a reliable source here:)

(06 Mar '14, 09:54) Volker Barth
1

Reading that doc page is like reading a HIPAA legal agreement... when you're done, you're tired, you're head is buzzing, and you feel less-well-informed than when you began.... which means you STILL have no idea what you're getting into :)

(06 Mar '14, 11:16) Breck Carter

Here is my understanding of things in a nutshell:
If the underlying OS/file system supports a 'point-in-time' type of snapshot of the filesystem (ie. they guarantee that the snapshot backup contains the copies of all files as of the exact same point in time), then they should work fine with SQL Anywhere. The only caveat is that if you recover from the snapshot, your require both the database and the log file because the database file will almost certainly have to go through recovery when you start it up.

For example, the Windows VSS service will work with SQLA even if the SQLA VSS service is not running. The SQLA VSS service attempts to provide a 'clean' copy of the database file for the snapshot, so that the database file doesn't need to go through recovery when it is restarted from the backup. If the SQLA VSS service isn't running, or takes to long to clean up, it simply means that if you recover from the snapshot, you will need both the database and the log file and the database will have to go through recovery before it comes back online.

permanent link

answered 06 Mar '14, 16:23

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

edited 06 Mar '14, 16:24

1

Does a backup technique that relies on snapshots requires a full backup (matching backup copies of the database and log) every time? I.e. a snapshot of just the log is useless.

Is a full database-and-log backup created via snapshot incompatible for recovery purposes with subsequent log-only backups taken via dbbackup?

Are snapshot backups just plain incompatible with dbbackup backups? I.e., if you're in for a penny you're in for a pound.

(06 Mar '14, 20:56) Breck Carter
Replies hidden
2

If the SQLA VSS service is not running, or it was unable to provide a "clean" copy, when a VSS snapshot backup was taken, is it possible that lots and lots and lots of data will be lost because (a) the engine hadn't finished writing data to the log for recent COMMIT operations and (b) the engine hadn't done (or finished) a CHECKPOINT?

It's one thing to say "recovery does an pretty good job of not losing data when rolling forward after a crash", it's a question of "how trustworthy is a snapshot backup at actually containing a snapshot?"... the snapshot taken by a dbbackup is flawless as a snapshot, is the same true of VSS?

To put it another way: A backup is not the same as a crash. One expects a certain amount of data loss after a crash, but one does not expect the same to be true of a backup.

(06 Mar '14, 21:11) Breck Carter
Replies hidden
3

I would say that the best practice would be to get both the db and log every time you take a snapshot.
dbbackup and snapshot backups are not compatible.

(07 Mar '14, 07:32) Jason Hinspe...
4

If a transaction was successfully committed (ie. the statement completed and returned to the client), then the committed data is there in the database file and/or the log file, regardless of anything that happens after that. The only thing that would (should) cause data loss after a successful commit is a disk failure.
If a commit has not yet completed when the snapshot occurs, and you need to recover from that snapshot, then you will lose that uncommitted transaction, the same as if the server had crashed, or if you had taken a backup of the log file at that point in time.
I agree, a backup is not the same as taking a snapshot copy of the database and log. It is, however, better than not taking a backup at all.

(07 Mar '14, 07:41) Jason Hinspe...
1

Thanks!

For the record, here is worthwhile article on the subject: The Basics of the Volume Shadow Copy Service (VSS)

(07 Mar '14, 09:11) Breck Carter
2

That would be a very valuable statement to add to the VSS doc page, methinks...

(07 Mar '14, 10:35) Volker Barth
1

I would also agree that it is best practice to get both the db and log every time; however, backups of all kinds are compatible. The only thing a database cares about for recovery (or manually applying logs) is essentially that the log must contain the log offset as of the previous checkpoint of the database file.

(08 Mar '14, 16:11) John Smirnios
2

Note that taking a snapshot copy of a database & log is semantically the same as using the backup utility except as follows:

  • a snapshot backup creates backup images of all of the dbspaces and the transaction as of a single instant in time (ie, the start of the backup).

  • the backup utility essentially creates a backup image up the dbspaces as of the time the backup started plus a backup image of the transaction log as of the time the backup ended.

Suppose, for example, backing up an enormous database takes 1hr for either method and you start it at 1am. With snapshot, the backup contains all transactions committed as of 1am. With the backup utility, the backup completed at 2am will have all transactions committed as of 2am (ie, the copy of the log in the backup will contain any operations applied between 1am and 2am).

In most cases, that difference doesn't matter -- it is usually just desirable to have one backup per day (for example) plus any logs created afterwards.

There is no way to truncate/rename the transaction log when using VSS. You might consider backing up just the database file with VSS, then rename the transaction log then backup the renamed log.

(08 Mar '14, 16:27) John Smirnios
1

@John: So Jason's statement "dbbackup and snapshot backups are not compatible." is not true?

Honestly, I share Breck's point of view that the usability of the backup files (and the possible pitfalls) should be as clear as possible, including the documentation, and that's still not the case in that respect, obviously...

(09 Mar '14, 11:49) Volker Barth
1

"The only thing a database cares about for recovery (or manually applying logs) is essentially that the log must contain the log offset as of the previous checkpoint of the database file."

That's a powerful, interesting and possibly valuable statement! I use the word "possibly" because you used the word "essentially"... words like those allow for an infinite variety of failure :)

At the moment, I trust dbbackup with my life. Can I trust VSS?

(09 Mar '14, 13:49) Breck Carter
1

"You might consider backing up just the database file with VSS, then rename the transaction log then backup the renamed log."

I'm having a hard time getting my head around that statement. Cynical Breck says "I consider a lot of things, like hang gliding", then Paranoid Breck asks "If something goes wrong, will I be able to recover?" :)

Seriously, folks are sensitive about the safety of backup and recovery, especially recovery, so please excuse our nit-picking.

(09 Mar '14, 13:58) Breck Carter

While John is right and technically backups of all kinds are compatible, I stand by my comment that the two are not compatible. Trying to mix and co-ordinate snapshot and dbbackup methods is going to set you up for problems that you don't need.

(10 Mar '14, 09:20) Jason Hinspe...

Now I guess us customers would really like need a clear point of view from you, dear company - you are invited to form a statement about the compatibility of normal and snapshot backups internally and publish it here (or elsewhere).

I do understand that there are differences between what should work technically and what ought to be recommended to work generally. However, I think that a backup method should be as reliable as possible, and I'm ready to follow the official guidelines. - If, by chance, I would not follow them, and would then get to know (in a recovery situation) that two backup files are still usable for recovery although that is not officially "granted", well, that's a nice-to-have... Nevertheless, we would need a method that is designed to work generally.

(10 Mar '14, 11:09) Volker Barth
showing 3 of 13 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84
×6
×4

question asked: 06 Mar '14, 04:36

question was seen: 3,532 times

last updated: 10 Mar '14, 11:11