Wednesday, October 14, 2009

SQL Anywhere Product Futures Discussion

Sybase forum users will recognize "SQL Anywhere Product Futures Discussion" as the title for the sybase.public.sqlanywhere.product_futures_discussion newsgroup on the forums.sybase.com NNTP server. It's available from your web browser, but I won't put a link here because every one of the several HTTP / HTML interfaces to the NNTP forums are, well, inadequate.

Besides, the NNTP forums are reaching the end of their usefulness, which is why I'm not going to post suggestions there any more... I'm going to post them here, starting now...

DB_PROPERTY ( 'BackupInProgress' )

There should be a database property which answers the question, "Is a database backup process in progress?" It would apply to the BACKUP statement and the dbbackup.exe utility.

The question arises, should the answer be as simple as yes/no, or more complex? The continuous live backup process comes to mind, where the answer would always be yes... maybe live backups should be excluded.

And what about database versus log-only backups? Should the response be db/log/both/no?

That's what "discussion" is for; if you have an opinion, post a comment here.

One reason for having this property is so database monitor programs, like Foxhound and the SQL Anywhere Monitor, could adjust their behavior when a backup is running; e.g., they could recognize that server I/O processing will change in behavior, and that checkpoints will be suspended.

Another reason might be so you can write code that starts a backup as long as one isn't running already, perhaps a second redundant process that makes sure a backup is taken even if some other backup process failed.

DB_PROPERTY ( 'BackupSuccessfulAt' )

This database property should answer the question, "When was the last backup taken?" It should return NULL if there never has been a successful backup, or the timestamp when the most recent successful backup finished.

In my opinion, this request doesn't need any justification... it's hard to think of any question more important than one involving database backups. And today, there is just no reliable way to answer the question, certainly not easily.

I'm guessing live backups don't count here, if only because then never finish unless there's a problem; e.g., the server crashes, so dbbackup loses connectivity... and a database property is useless if the server ain't running.

Should there be different properties for different backup types? e.g., 'DBBackupSuccessfulAt', 'LogBackupSuccessfulAt', 'FullBackupSuccessfulAt'... more discussion!

5 comments:

Breck Carter said...

Oops, forgot this motivation: The database monitor programs could send email alerts when a database backup is overdue.

Unknown said...

BackupSuccessfulAt would certainly be HIGH on my list of desired features!

we have someone manually tracking backups once-a-week. Not great, but better than not knowing at all.

we would love to have an email alert if the backups weren't running. and it would be a bonus if the db monitor wasn't notifying us every night that the performance thresholds were exceeded due to backups.

Bill

Anonymous said...

"NNTP forums are reaching the end of their usefulness"

Could not agree more. You may be interested to know that Stackexchange - the hosted version of Stackoverflow - is now in beta 3. Check out http://stackexchange.com/.

You can sign up here:
http://beta.stackexchange.com/sign-up

Hoping to see a SQL Anywhere SE site some day, either at Breck's or Sybase's initiative.

Justin Willey said...

I like the idea, also you mention Live Backup and built-in things to monitor the presence or absence of the live backup process would be helpful. At the moment we monitor the live backup's connection, but it doesn't really tell us if its actually working.

BTW - have you had any feedback from i-anywhere about alternatives to NNTP?

Breck Carter said...

Justin: If a live backup connection exists, I think it's probably safe to say "live backup is working". Also, FWIW, live backup has pretty much fallen from favor after the introduction of high availability, at least IMO. One way of looking at HA is that it's "Live Backup On Roids" :) Earlier versions of SQL Anywhere don't have HA, of course... but then again, those earlier versions aren't being enhanced any more.

HOWEVER, you can still ask... I am sure Live Backup isn't going to be dropped. Be specific about the manner in which you want SQL Anywhere to "tell us if its actually working."

As for alternatives to NNTP, the game is afoot, but I haven't heard the details.