February 9, 2016 at 5:41 am
Remember that the only reason to take a backup is so that you can do a restore.
Your organisation will have a Recovery Time Objective (RTO) which defines how long a restore should take, and a Recovery Point Objective (RPO) which defines how much data can be lost when a restore can be done. Whatever process you use to do your backups must meet these objectives.
The shorter your RPO is, the more your backup solution will cost. An RPO that allows up to 12 hours lost data means you need to take a full DB backup two times each day. An RPO that allows under 1 minute lost data means you need some form of DB mirroring and very frequent log backups.
Very few snapshot-style backup solutions can cope with short RPO times. Any discussions you have about about backup product need to focus on the required RTO and RPO, and not on 'ease of use' of the backup product. Many organisations can live with far longer RPO objectives for user files than for their key databases, and sometimes people loose sight that the RPO and backup methodology for files just is not good enough for databases.
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
February 9, 2016 at 6:54 am
Rich Mechaber (2/5/2016)
If you use a third party tool, you will have to guess, test, and verify that what you thought you told the tool to do actually happened.
Not in the case of Redgate SQLBackup or Dell Litespeed for SQL Server, they do exactly what it says on the tin
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2016 at 8:45 am
Perry Whittle (2/9/2016)
Rich Mechaber (2/5/2016)
If you use a third party tool, you will have to guess, test, and verify that what you thought you told the tool to do actually happened.Not in the case of Redgate SQLBackup or Dell Litespeed for SQL Server, they do exactly what it says on the tin
Thanks, but you should understand how they work, which involves testing and verifying. Make sure you know to get all the backup files (if you've striped) and ensure you have password properly tracked.
Also, make sure you can uncompress a backup back to native format. We give you a command line tool, but make sure you can use it.
Disclosure: I work for Redgate.
February 10, 2016 at 5:15 am
It's interesting reading other peoples experiences as I have native sql backups or RedGate's SQL Backup Pro in use on all but one server.
In addition our infrastructure team have a mixture of IBM Tivoli Storage Manager and Veritas Backup Exec doing server backups.
As mentioned in previous posts SQL sees both of these as a db backup. They wanted me to drop my SQL backups and rely totally on theirs but I stood my ground and said I don't trust what I don't know. I made it clear they would be totally responsible for all restores, whether for DR or not, which made them change their minds. I was proved right when we had a corruption issue and a failed drive. It was the SQL backups that got us out of the mess when we found BuExec hadn't run the night before!
Tivoli has also caused us no end of problems when backing up a SQL server hosting SharePoint databases. In the end they stopped doing it and switched to using Veeam.
Our Windows server guys see Veeam as the future for server backups but again I've made it clear its in addition to my own db backups.
A few years ago I inherited a load of servers using LiteSpeed when we bought another company and I found it wasn't being used correctly and the licences were about to expire. No-one knew why it was being used as it was put in by people who had left and it seemed it was only for backup compression reasons to save some space. So we dropped it and used native backups without any problems.
February 10, 2016 at 10:00 am
I work with Tivoli Storage Manager (TSM). Been working with it since it was called ADSM (V2.1). Antiquated? Maybe. But quite current in how it works and it does work very well. However TSM is NOT shrink-wrapped software but is an application (like an RDBMS) wherein you define how you want backups managed via a Policy Domain hierarchy (which we are now mapping to SAP Landscapes) and a Media hierarchy (which we use SAN disk for cache in front of the Data Domain for filesystem and transaction log backups as the Data Domain has two limiting factors in that it can't take unlimited write streams and doesn't 'like' lots of small files on its front-end). We also use one Data Domain MTree for each SAP Landscape -- very clean and an effective means of implementing replication for DR.
I distinctly prefer TSM. There is a separate client software for many applications including SQL Server. As with most mainline backup software the TSM client for SQL Servers binds into SQL Server (although it always seems oddly done -- at least the device names) such that SQL Server is using TSM as a storage location. We tend to backup all databases (at least production) full every day with either hourly or every 15-minute transaction log backups. We do use differential backups in non-production environments (SAP Landscapes).
My current employer's customer has a large SAP implementation and we support both backup & recovery as well as restoring databases from production to non-production.
The customer's environment is all 10G Ethernet with separate VLANs for backup (multiple VLANs for backup; each client has access to one of the four; each TSM server has all four) and we see excellent backup and restore performance. Backup storage is EMC Data Domain. We get very good deduplication rates for database backups
A 1.5TB database takes only 2-hours to perform the restore of the full (which took 4-hours to backup; [backups compete with each other for resources; restores tend to be done outside of the backup window]). On large databases using multiple streams we see performance of the restore from 290 to 375 MB/second. Most transaction log restores take only a few seconds.
The problem with TSM and SQL Server is that the TSM scheduler cannot handle more than one running activity at a time. If the full backup takes longer than the interval used for log backups the log backups are blocked during the full. So we are moving to the Windows Task Scheduler for scheduling and performing the transaction log backups.
The customer also has Tivoli Storage FlashCopy Manager for VMware (FCM4VE) to take SAN disk-based 'snapshots' before maintenance & release activity. The applications and databases are stopped before taking the snapshots (which internally take VMware snapshots of each VM before starting the SAN disk-based copy processes). These two backup products can be integrated but that isn't how we use them (at this customer's direction).
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply