|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 8:26 PM
Points: 23,166,
Visits: 6,925
|
|
Here's my take. All of the agent based tools have had issues at times. I've sen too many of them fail on restores, which is what is critical. I don't need backups, except that at some point I'll have to restore.
The SQL native backup, and the 3rd parties that use it (SQL Backup, Litespeed), have proven to be 100% reliable. If the file is written out, it can be restored.
I'm happy for the backup guy to own backups, but he owns the backup of my backup files. not the process. The SQL restore is too important to be trusted to someone that doesn't know how it works inside and out. and it's not that hard to manage and run. I'll happily be on call for my backups.
Run to a file, let the backup people own the backup after that.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 2,039,
Visits: 3,314
|
|
Steve Jones - Editor (9/16/2009)
Here's my take. All of the agent based tools have had issues at times. I've sen too many of them fail on restores, which is what is critical. I don't need backups, except that at some point I'll have to restore.
The SQL native backup, and the 3rd parties that use it (SQL Backup, Litespeed), have proven to be 100% reliable. If the file is written out, it can be restored.
I'm happy for the backup guy to own backups, but he owns the backup of my backup files. not the process. The SQL restore is too important to be trusted to someone that doesn't know how it works inside and out. and it's not that hard to manage and run. I'll happily be on call for my backups.
Run to a file, let the backup people own the backup after that.
Amen...!!!
-Roy
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 7:29 AM
Points: 2,019,
Visits: 1,653
|
|
It is important to move away from a turf war about who should do what, and focus on the business objectives.
Most businesses will have recovery objectives and SLAs. These may be to recover to the last available backup or to a point in time. If you have a backup tool that can deliver the recoveries you need, does it matter to the business who has ownership of the tool of the process?
What should be measured is the ability of the toolset to deliver what the business needs.
We know that SQL native backup (also backup using vendor tools such as Litespeed, SQL Safe, SQL Backup, etc) can meet any recovery objective. The only trick is to make sure the backups are scheduled in the appropriate way and checks aer made to ensure they have run successfully. Many DBAs will keep 2 routes to recovery, often by keeping backups on disk until at least 2 tapes have been sent offsite. Also, many DBAs will regularly run restores to a trial system to prove that what is stored offsite contains the data that is backed up.
If your management are keen to move to an alternative approach, the people who will have ownership of that approach should prove they can meet the recovery objectives. This means they should show they keep 2 ruotes to recovery, and also regularly recover individual databases to the required point in time to show their system really works.
At the end of the day, if both systems work equally well but one needs less staff time than the other, then we must expect management will choose the cheapest system. On the other hand, if one can do the recovery 100% of the time and the other can only recover 95% of the time but at lower cost, management will have to justify the business and audit risk that cutting cost is likely at some point to leave the business with an unrecoverable hole in its data.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, and 2008 R2. 24 February 2010: now over 9,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 889,
Visits: 1,381
|
|
EdVassie (9/25/2009)
...if one can do the recovery 100% of the time and the other can only recover 95% of the time...
 do elaborate, please.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 7:29 AM
Points: 2,019,
Visits: 1,653
|
|
| If testing restores in multiple scenarios shows if one can do the recovery 100% of the time and the other can only recover 95% of the time then you know that one approach has a real risk of unrecoverable data loss.
If both approaches work 100% of the time then the risks are the same so the focus moves to costs.
You need to test using backups taken of your production databases at the times they would normally be taken with the normal user activity expected at that time. You then restore to a spare server and see if SQL Server is happy with the result.
If you have to restore multiple databases, or databases and file system data, to get your application working correctly, you also need to prevent the 'jagged edge of recovery' where different data stores are restored to different points in time.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, and 2008 R2. 24 February 2010: now over 9,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 889,
Visits: 1,381
|
|
EdVassie (9/25/2009)
If both approaches work 100% of the time then the risks are the same so the focus moves to costs.
mmmhhh... There is not such a thing as a 95% effective restore procedure.
I'm sorry but I'm with Steve on this one when he says "The SQL native backup, and the 3rd parties that use it (SQL Backup, Litespeed), have proven to be 100% reliable. If the file is written out, it can be restored".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 7:29 AM
Points: 2,019,
Visits: 1,653
|
|
I agree that a restore process that does not work is not worth having.
My main pioint is that we should not assume that because method A has proven to be 100% successful, then method B must be less than 100% successful. The only way to prove if method B is safe to use is repeated testing.
You can be certain that when Litespeed first appeared there was a lot of uncertainty about how reliable it was. Time has proven that it works.
We have seen problems in the past where backup agents have produced a 'backup' that cannot be restored as a working SQL Server database. Eventually these problems will be fixed for SQL Server, as they have already been fixed on other platforms for other DBMSs.
The vendor involved should be asked to provide their testing results, showing how many restore tests they ran, what size databases were used, the level of query activity when the backup was taken, and how many restores were successful. Then the customer should do their own tests with their own databases and levels of user activity. The critical thing of course is not whether a file purporting to be a backup is created, but if you get a working SQL Server database when you do a restore from that file.
Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2005, 2008, and 2008 R2. 24 February 2010: now over 9,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:54 PM
Points: 1,200,
Visits: 2,080
|
|
Thanks all for the helpful discussion, keep it coming!
Another issue, which I think has not been brought up yet, is troubleshooting backup/restore errors when they occur.
Working with a 3rd party tool, IDERA SQLsafe, I have had to deal with obscure error messages at times, which even the vendor tech support was unable/unwilling to help me out with, other than suggesting that it is a SQL error and that I should talk to Microsoft!
Adding an extra layer of complexity - on top (or in place) of the native SQL-code - causes ambiguity when errors occur and increases the number of possible culprits.
I guess, at the end of the day, the question to ask is this:
Why replace the SQL native functionality for backups with a 3rd party tool, especially if you are already using SQL Server 2008 Enterprise Edition (which comes with a good compression utility)?
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 889,
Visits: 1,381
|
|
Marios Philippopoulos (10/2/2009)Why replace the SQL native functionality for backups with a 3rd party tool, especially if you are already using SQL Server 2008 Enterprise Edition (which comes with a good compression utility)?
Some vendors would argue they can do better that SS native backup and some CIO/CTO may buy it.
In my experience a SS native backup to disk then letting your backup tool of choice e.g. Netbackup, etc. take care of moving the dump files to tape is a reliable and easy to deploy/maintain solution.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:54 PM
Points: 1,200,
Visits: 2,080
|
|
|
|
|
