cancel
Showing results for 
Search instead for 
Did you mean: 

SQL On Disk copies of databases -- any drawbacks to non-snapshotting?

Moltron1
Level 5

Hi there -

I'd like some guidance in this area I'm about to explain as I am a little confused.  For some of our SQL servers, it would be ideal to have copies of the databases exist on the local server, and so I'd like to use the option to place SQL database copies on disk when the jobs run.  I noticed that you can't do this without first disabling snapshotting.

Its that last part that gets me.  To me, it means going to Advanced Open File section, and unselecting Use Snapshot Technology.  So, wouldnt that apply to filesystem too?  I don't want to do that.  So I suppose you could then say, just seperate out the SQL database backup from the SQL server, and do them seperate. Okay - well what is the drawback to doing I guess what's called streaming SQL backups then, if they are not snapshot SQL backups?  Am I loosing anything by doing this?

Perhaps this would be less complicated if I just configure the SQL Management Studio to make DB copies periodically on disk, and leave BE out of it?

I'd appreciate any advice. 

 

Thanks! 

1 ACCEPTED SOLUTION

Accepted Solutions

VJware
Level 6
Employee Accredited Certified

By default, BE uses snapshot for SQL backups. Here is a KB detailing the pros / cons - http://www.symantec.com/business/support/index?page=content&id=HOWTO99571

By not running snapshot backups, you aren't losing anything. In fact, not only you would be able to achieve your objective of having SQL database copies, but also, the backup set size of non-snapshot SQL backup will be smaller than the backup set size of snapshot SQL backups.

View solution in original post

5 REPLIES 5

pkh
Moderator
Moderator
   VIP    Certified

1) It is always the recommendation to separate out the SQL backups from the file backups and don't use AOFO for the SQL backups.  This is because you would not be able to do a redirected restore of the SQL databases if you use AOFO.

2) BE invokes the SQL API to to its backup and restores, so using SQL Studio to do your backup does not give you anything extra.

VJware
Level 6
Employee Accredited Certified

By default, BE uses snapshot for SQL backups. Here is a KB detailing the pros / cons - http://www.symantec.com/business/support/index?page=content&id=HOWTO99571

By not running snapshot backups, you aren't losing anything. In fact, not only you would be able to achieve your objective of having SQL database copies, but also, the backup set size of non-snapshot SQL backup will be smaller than the backup set size of snapshot SQL backups.

Moltron1
Level 5

phk and VJware, thanks for the responses.  After reading your posts and the KB, I am left with 2 additional questions.

 

We backup exclusivly to Data Domains (deduplicating devices).  In the KB, it notes that snapshot technology should be used when backing up to dedupe devices, but makes no mention of why.  Can anyone explain that?

Also, why did BE go with a snapshot backup for SQL as default?   It seems that non-snapshot streaming SQL backups are slightly superior in that they are smaller, and can be redirected to another SQL server and you can do the additional copies to local disk.  Am I missing something? Some trade-off?

VJware
Level 6
Employee Accredited Certified

1) Verbatim from HOWTO74446.

Set the option Use snapshot technology as a backup job property for the SQL backup job. This option allows data to be deduplicated in the most effective way.

2) I guess, its due to the fact that SQL has its own VSS writer. Imho, these are few differences between a snapshot vs a streaming backup of SQL ~ Snapshot backups usually offers a better performance in terms of throughput, disk I/O, DeDupe ratios etc as compared to Streaming backups. Block-level incremental method is available for snapshot backups. The "Automatic" backup option is also available for snapshot backups, though few other options such as native SQL software compression, creating on-disk copies aren't.

 

Moltron1
Level 5

Thanks