October 2, 2009 at 7:53 am
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.October 2, 2009 at 8:16 am
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.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 2, 2009 at 9:25 am
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)?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 9, 2009 at 2:23 pm
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.October 27, 2009 at 9:37 am
Here is an interesting link on a COMMVAULT bug:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 4, 2009 at 8:41 pm
We're testing the latest Commvault sql backup product. So far I don't like the idea. The backup goes to the Commvault "nearline" device in a format that normal sql tools could not use. It will then go to tape. And again, you would have to have the Commvault GUI software to restore a database from those files.
One of the first things that happened is a fairly junior Systems member, working with the Commvault rep, proposed to backup one of my production databases. Fortunately I overheard them and cautioned about breaking the chain from native sql full backup to differential to transaction log backups. The commvault backup and restore operations do show up in the sql log as backup/restore.
So we have the likelihood of a non-sql person being asked to keep track of backups, performing restores -- they tend to think of restoring a file and then being done, rather than restoring a file to a database.
Undoubtedly there will be developers who are used to using management studio to restore backups in their environments, which they won't be able to do.
One of the arguments for this 3rd party tool is that it eliminates the need for backup space on our SAN.
November 5, 2009 at 8:14 am
I would add my support to the "native SQL Server backup to file" school, partly because this is clear and uncluttered by the need for extra tools, and partly because it separates my DBA role from the Ops role. I generate the backups - they secure the files; they restore the files from backup media to disk - I deal with the database restore/point-in-time, and so on. Our Ops people and System Engineers are great at hardware and system image recovery, but not hot on data consistency. They tend to equate recovery to having all the files back where they should be, which doesn't quite cover the issues that memory or file corruption, or a disaster scenario, might raise for the state of your databases.
Automated tools may eventually do it all - though the tool has to be intact and up-to-date (and where else is its media and backup catalogue other than in a database?!), and someone has to understand the consequences of the possible courses of action. If some DBAs jump to REPAIR_ALLOW_DATA_LOSS, what chance that a SysOp can make a sound decision, informed by knowledge of the options and possible outcomes for the data?
November 5, 2009 at 10:22 am
Marios Philippopoulos (10/2/2009)
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)?
Why not test your own application/database using different backup options? It really depends. A couple test metrics – backup & restore time, backup file size, CPU usage, I/O, memory.
Plus cost - recovery vs data lose (SLA), storage saving vs 3rd party tool fee, etc
Vendor products exist for some good reasons as there are gaps between SQL Server as a general DBMS and the unique customer infrastructure environment which means SQL native backup can not meet all the business ever-growing needs in some area.
November 6, 2009 at 2:40 am
As I said in a previous post, the business expect IT to provide the most cost-effective service, and is not interested in our turf wars. Backup technology is moving on, with continuous data protection and de-duplication now major technology areas and producing major cost savings.
Snapshot-based backup and restore is tried and trusted for some other major database systems, and eventually will probably become the standard way to do SQL backup. If we as SQL people just keep banging away about SQL native backups being the only safe way to do SQL backup we will be ignored. We need to engage with the new ideas, find their strong and weak points, and present these to our management.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 6, 2009 at 3:14 am
EdVassie (11/6/2009)
... the business expect IT to provide the most cost-effective service, and is not interested in our turf wars...
I assume "turf wars" means territorial conflicts between DBAs and SysOps/engineers, which must be problematic. We don't experience them - we usually collaborate pretty well - but we do find that people have different areas of specialist knowledge, and different priorities. And a great tool used by the wrong specialist can lead to avoidable problems (eg needless data loss) whilst imparting a false sense of security (everything's working fine).
But then I'm not a full-time DBA, I'm not working with huge or 24x7 databases, and we use no SQL Server add-ons or utilities. So far I am treating snapshots taken with the main overnight file backups as supplementing the established full and trans log routines. I expect our practices and my understanding and attitude will develop over time.
November 6, 2009 at 5:16 am
I am not so sure snapshot technologies will take over in the SQL sphere EXCEPT perhaps for VLDBs running on SQL, there a cost\suitability for purpose case could be made.
For the vast majority of SQL installations though the native backups are all that is required. As they are free, proven and robust no case can be made to use enterprise level backup tools to directly backup SQL databases. This is not being a stick in a the mud, it just remains a fact at this time.
The above is for enterprise level tools such as commvault and veritas, which this thread is about. I don't consider tools that provide backup compression\encryption to be enterprise level as they don't change the basic backup\recovery strategy.
As I have posted before the above covers database recovery, which is a separate area to true DR, in that area I can see SAN replication taking over from native technologies, its main advantage being the speed of failover AND failback and minimal data loss. Even that can be closely challenged by database mirroring, which is free!. Perhaps that is why the MSSQL world has not taken up these tools to the same degree, the free tools that ship with the product do a good job, whereas other large DBMS vendors have relied more on third parties to supply recovery options.
As for turf wars certainly the business does not care who recovers their data, as long as they can. But as this is the area where the DBA has the expertise it is beholden on them to ensure whoever takes it on understands what they have taken on and that the process works. In an ideal world they should remain involved.
---------------------------------------------------------------------
November 6, 2009 at 6:42 am
The real problem is that System types may either not understand enough to restore databases properly, taking into account on what drive the data and log files go, or not realize how often special, customized restores have to be done for various projects.
Our Systems team has found more than once that due to inattention, tapes filled up or scheduled backup jobs failed and weren't promptly attended to. So I don't have high confidence when very busy multitaskers are given responsibility for something that is not their first love.
If you do put sql types in charge of the 3rd party tool, then it definitely has to have advantages over native tools since we work with the native tools all day and generally would rather not be dependent on another GUI.
November 6, 2009 at 7:05 am
If you do put sql types in charge of the 3rd party tool, then it definitely has to have advantages over native tools since we work with the native tools all day and generally would rather not be dependent on another GUI.
I have to agree with that.
---------------------------------------------------------------------
November 6, 2009 at 7:39 am
There has to be a compelling reason for choosing a 3rd-party tool over SQL backups.
If a process isn't broken, what are we fixing?
The trouble is, oftentimes the decision to invest in such tools is made by "system" types (to borrow the term from recent postings) who have very little idea of the intrinsic complexities of database storage. Often these decisions are made in the absence of input from the DBA team. Once the tool is purchased and an untold amount of $$$ has been spent on it, it is too late to backtrack, even over the vehement objections of the DBAs.
Now those who made the original decision have to prove to the executives that the budgetted money was wisely spent.
In the end, the square peg will have to be pushed through the round hole, consequences be damned...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 6, 2009 at 12:00 pm
I completely agree with the compelling reason. Don't add SQL agents because you can. Don't choose compression/encryption, unless you have a valid reason to do so.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply