SQL Backups (VSS, VM)

Hey,

I have what I think is a simple question.
We have SQL server running in a VM, we back up the VM using Acronis Virtual Edidtion which uses VSS. The question is, if the VM is being backed up by VM aware software (Acronis) do we also need SQL aware backup software or will taking a backup using VM aware software (the whole VM) be enough for a safe restore?

Thanks!
dqnetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haresh NikumbhSr. Tech leadCommented:
Nope, only in condition after backup you wont find any error events in  VSS category inside Virtual machine.

make sure your VSS writers are stable in the VM ( to check vss writer status run following command in command prompt)

Vssadmin list writers

if you see all writers in stable state, and there is no error event in the event category then your database backup is consistent

if you found Event id 3041 on VM that means database backup is not consistent.
0
Haresh NikumbhSr. Tech leadCommented:
For safer side i will always use Windows backup for backing SQL database in non production hours ( make sure both schedule does not get conflict with each other)

So the backup plan for sql will be

VM will get backup with acronis   ( of course it will backup database along with it )

and Windows backup (inbuild backup tool for Windows) will used for backing up SQL database
0
PadawanDBAOperational DBACommented:
I am going to have to strongly disagree with TakeCoffe on this one.  Glenn Berry really says it so well with regards to this topic:


Never let anyone, whether it is a SAN vendor, a server administrator from your operations team, or your boss, talk you into not doing SQL Server backups as appropriate for your recovery point objective (RPO) and recovery time objective (RTO) requirements.  This cannot be emphasized enough!  There is absolutely no substitute for having SQL Server backup files, although you will undoubtedly be pressured throughout your career, by different people, into not running SQL Server backups.  Stand your ground.  The old saying is true: "If you don't have backups, you don't have a database."

With in-stream compression coming in all editions of SQL Server 2008+, I personally view the SQL Server backup/restore capabilities more than sufficient in most cases (which you can combine with some powershell scripts to shuffle the backups around to different storage locations or however you so choose to do that).  Personally, and I may be jaded by past experiences on this one, I never trust a third party utilities that are "SQL Aware" to perform my SQL backups (exceptions being things like SQL Backup Pro from RedGate or NetVault LiteSpeed for SQL Server from Quest - as they are specifically designed for SQL Server backups).  

BTW, Acronis Backup & Recovery 11 Virtual Edition does say that it is MS SQL Server aware, but again...  There is no substitute for SQL Server backups, IMO.
0
Haresh NikumbhSr. Tech leadCommented:
@ PadawanDBA

I guess you have not read my 2nd comment on this post

ID: 39502409

 
even i do not trust third party backup solution and recommend to have separate backups for SQL server.

And yes

 Acronis Backup & Recovery 11 Virtual Edition is VSS aware backup application which freez VSS aware application services (SQL services) to take consistent  backup for the database.


 Acronis Backup & Recovery 11.5 takes a snapshot of the disk using Acronis’ patented disk-imaging technology, eliminating the need to take a server offline for a backup. It also utilizes Windows Volume Shadow Copy (VSS) to notify applications and guarantee application-level consistency.

http://www.acronis.com/backup-recovery/enterprise.html#benefits-backup
0
Haresh NikumbhSr. Tech leadCommented:
In sort i will suggest to remove Acronis backup for this VM..

this backup will always used full in the disaster recovery case ( where complete server is gone)

No need to install OS / application and configure server from scratch.

Also it gives Bare metal restore option where you can restore complete backup on the different hardware.
0
PadawanDBAOperational DBACommented:
@takecoffe - My apologies!  I was assuming you were talking about the actual windows backup as reliable for backing up SQL and not actual SQL Server Backups.  If i was wrong in my assumption, then I would say that it looks like we agree!
0
dqnetAuthor Commented:
I'm a little confused.
With regards to the SQL backup's I have them all set in maintenance plans so they get backed up prior to the entire VM getting backed up. As you say Acronis does pause the VM for a few seconds to run it;s 'technologies'.

The question here is if the VM is paused for those few seconds (I believe it needs to be no more then 10 seconds for the VMM writer to hold writes(cache) and be successful?) does that out SQL in a 'incorrect' state. Remember the VSS writer is running on the parent and acronis is also on the parent. Does the guest OS know its being backed up using VSS?

sorry just a little more detail please :)
0
Haresh NikumbhSr. Tech leadCommented:
if no agent is install on guest machine..and parent machine pausing vm..then database backup will not consistent.. you need some thing on guest machine which give signal to sql sevices to freez before taking snapshot so data which is there on memory or process should get commited on disk..and then take snapshot....
0
dqnetAuthor Commented:
Unless i am understanding wrong doesn't acronis conduct this task. It's doing it for the entire VM which should result in SQL not even knowing anything happened as the VSS writer took care of the whole operation at a OS wide level.

Also, Acronis doesn't need to install anything on the virtual machines to back them up using VSS anyway so what's the difference?
0
Haresh NikumbhSr. Tech leadCommented:
let  me explain in details.. correct me if i am wrong if i understand steup wrongly

1) parent machine is Hyperv .. where SQL server is running as a VM
2) you have installed backup agent on the Hyperv and backing up VM (SQL )
3) as you mention earlier whenever VSS job get trigger it pause SQL VM .. and then take snapshot

if this is the case .. the Snapshot which is occurring is consistent for the HyperV .. cause it pausing / Freezing services. and not allowing to get new data for the VM which is getting backuped up.

so the snapshot which is getting created for the SQL is consistent only in case for the Hyperv. ( in short if you restore complete VM) from that snapshot then it will not throw any error or restore will have complete state which was available during backup.

but in the case of restoration of database. ( mounting snapshot ) and just coying database and log file for the restore will not be consistent... the reason is..

Guest OS did not gave any signal to SQL services to pause /freeze ( to get committed all the data which was in the process / memory or not yet written on the disk)

from hyperv point of view .. the VM backup is consistent.. but from OS level its normal backup ( not vss aware - the reason is it pause VM without pausing SQL services.. and in this process what ever data was in memory or process lost.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqnetAuthor Commented:
Ok excellent, totally understand.
Now the question is this
1. What is the solution?
2. Is it safe to assume that if nobody is using the database server at that time (late night and all staff are at home sleeping)  it is ok to continue like this?

Thanks.
0
dqnetAuthor Commented:
?
0
Haresh NikumbhSr. Tech leadCommented:
yep.. you already having 2 backups

1) complete backup for VM
2) database backup ( from windows )
0
dqnetAuthor Commented:
But doesn't the database go into an inconsistent state and possibly corrupt the databases?
0
Haresh NikumbhSr. Tech leadCommented:
if you try to do complete system restore then yes. there are chances for database may get inconsistent state ( but i am sure you have schedule this job in night time where no one is working on this machine .. that means none of the transaction is going on )

even though if Complete system restore found database inconsistent .. then you already having 2nd copy of database backup which is taken from Windows ( that is consistent )

as Window backup use shadow copy service. which pause SQL service before taking snapshots.
0
dqnetAuthor Commented:
The second backup isnt from Windows.. It is directly from SQL using maintenance plans though.
0
Haresh NikumbhSr. Tech leadCommented:
so you should have.. make sure they dnt get conflict with each other
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.