September 30, 2009 at 9:29 am
Hello everyone.
I need either some help or some clarification.
We have a new client who is using a 3rd party vendor to host our database. The database is 2005 enterprise edition, with clustering, on a SAN. The company hosting the database has told the client, and my manager, that they are going to take care of the backups with SAN Backups, and not SQL Server backups. I have always heard that this is a bad idea, but as we do not have SAN's in the office, I have never done research into the matter.
Now I am in the situation where I need to determine if doing SAN backups are an acceptable form to replace the sql server backups, or if I should whip my manager with a wet noodle to get him to talk the client about why it is a bad idea.
Can anyone provide links or information on either method. Maybe a pro vs con type of thing.
Thanks,
Fraggle.
September 30, 2009 at 9:46 am
We run *almost* everything off of an Enterprise SAN, and we still do SQL backups.
Since this is an ASP/hosted deal I would make SURE that if you can't get SQL backups you have a very clearly defined SLA, etc. Yikes that scares me.
MJM
September 30, 2009 at 10:22 am
Ask the vendor to write up in detail exactly how they would do a database recovery to a specific point in time to a new database without disturbing the old database or any other database so that you can recover data that was accidentally deleted.
If they can't explain that, then their solution is worthless. If they can explain it, insist that you want to test the recovery before commiting to it.
I have had a similar suggestion from in-house SAN admins, and rejected it. There is no way I would trust this in a hosted environment without actual proof they could do the recovery I described above.
I would just go with database backups and transaction log backups to disk with the vendor doing backups to tape of the backup files.
September 30, 2009 at 10:23 am
We run *almost* everything off of an Enterprise SAN, and we still do SQL backups.
Since this is an ASP/hosted deal I would make SURE that if you can't get SQL backups you have a very clearly defined SLA, etc. Yikes that scares me.
MJM
Ok, but why do you still do SQL Backups is my question. What is the reason for them vs doing the SAN backups.
Fraggle
September 30, 2009 at 10:30 am
Ask the vendor to write up in detail exactly how they would do a database recovery to a specific point in time to a new database without disturbing the old database or any other database so that you can recover data that was accidentally deleted.
If they can't explain that, then their solution is worthless. If they can explain it, insist that you want to test the recovery before commiting to it.
There will only be one database on the system, so the aspect of affecting other databases isn't really relavent. I will have to ask about the point in time aspect as that was the only thing that I could think of as a down side ot the deal.
Fraggle
September 30, 2009 at 12:29 pm
Fraggle-805517 (9/30/2009)
Ask the vendor to write up in detail exactly how they would do a database recovery to a specific point in time to a new database without disturbing the old database or any other database so that you can recover data that was accidentally deleted.
If they can't explain that, then their solution is worthless. If they can explain it, insist that you want to test the recovery before commiting to it.
There will only be one database on the system, so the aspect of affecting other databases isn't really relavent. I will have to ask about the point in time aspect as that was the only thing that I could think of as a down side ot the deal.
Fraggle
Not affecting other databases is relavent; don't forget master, model, and msdb. You could see entries for jobs, job and backup history, DTS and SSIS packages, and logins disappear if you roll them all back.
September 30, 2009 at 12:37 pm
Fraggle-805517 (9/30/2009)[hr
Ok, but why do you still do SQL Backups is my question. What is the reason for them vs doing the SAN backups.
Fraggle
For me personally, the honest answer to your question is trust, control, and accountability. I am sorry but if I cannot ascertain with 100% certainty that my company's data (arguably one of the most valuable assets we have) cannot be restored OR I am unfamiliar/uncomfortable with the solution provided I will voice concern.
As far as the technical aspect goes I still say that having true SQL database backups is the way to go. That is what I am comfortable with restoring (especially under pressure or in a DR situation). I know that may sound reactive and even lazy but when the chips are down, trust me it's all about "Show me how you did your job as a DBA ensuring the security and recoverability of the organization's assets". Anything less than that and even if I am not out of a job, my credibility is damaged.
My $0.02.
September 30, 2009 at 3:18 pm
Michael Valentine Jones (9/30/2009)
Fraggle-805517 (9/30/2009)
Ask the vendor to write up in detail exactly how they would do a database recovery to a specific point in time to a new database without disturbing the old database or any other database so that you can recover data that was accidentally deleted.
If they can't explain that, then their solution is worthless. If they can explain it, insist that you want to test the recovery before commiting to it.
There will only be one database on the system, so the aspect of affecting other databases isn't really relavent. I will have to ask about the point in time aspect as that was the only thing that I could think of as a down side ot the deal.
Fraggle
Not affecting other databases is relavent; don't forget master, model, and msdb. You could see entries for jobs, job and backup history, DTS and SSIS packages, and logins disappear if you roll them all back.
Forgot all about those, which I am not sure how, but I did.
Thanks,
Fraggle
September 30, 2009 at 3:18 pm
Mark Marinovic (9/30/2009)
Fraggle-805517 (9/30/2009)[hr
Ok, but why do you still do SQL Backups is my question. What is the reason for them vs doing the SAN backups.
Fraggle
For me personally, the honest answer to your question is trust, control, and accountability. I am sorry but if I cannot ascertain with 100% certainty that my company's data (arguably one of the most valuable assets we have) cannot be restored OR I am unfamiliar/uncomfortable with the solution provided I will voice concern.
As far as the technical aspect goes I still say that having true SQL database backups is the way to go. That is what I am comfortable with restoring (especially under pressure or in a DR situation). I know that may sound reactive and even lazy but when the chips are down, trust me it's all about "Show me how you did your job as a DBA ensuring the security and recoverability of the organization's assets". Anything less than that and even if I am not out of a job, my credibility is damaged.
My $0.02.
I won't disagree with you at all on the matter. Just a matter of proving it to the MAN.
Fraggle
September 30, 2009 at 3:34 pm
Fraggle-805517 (9/30/2009)
Mark Marinovic (9/30/2009)
Fraggle-805517 (9/30/2009)[hr
Ok, but why do you still do SQL Backups is my question. What is the reason for them vs doing the SAN backups.
Fraggle
For me personally, the honest answer to your question is trust, control, and accountability. I am sorry but if I cannot ascertain with 100% certainty that my company's data (arguably one of the most valuable assets we have) cannot be restored OR I am unfamiliar/uncomfortable with the solution provided I will voice concern.
As far as the technical aspect goes I still say that having true SQL database backups is the way to go. That is what I am comfortable with restoring (especially under pressure or in a DR situation). I know that may sound reactive and even lazy but when the chips are down, trust me it's all about "Show me how you did your job as a DBA ensuring the security and recoverability of the organization's assets". Anything less than that and even if I am not out of a job, my credibility is damaged.
My $0.02.
I won't disagree with you at all on the matter. Just a matter of proving it to the MAN.
Fraggle
The easiest way to prove it is to just have a disaster where you lose all your data. :crying:
Seriously, I think the burden of proof should be on the vendor to prove that what they are proposing will somehow be a better way to do backups than established SQL Server best practices. When you have a major problem, it is not the time to start hoping that those backups really work.
You don’t want to end up as another entry on this thread:
It's dead, Jim
September 30, 2009 at 3:40 pm
Michael Valentine Jones (9/30/2009)
Fraggle-805517 (9/30/2009)
Mark Marinovic (9/30/2009)
Fraggle-805517 (9/30/2009)[hr
Ok, but why do you still do SQL Backups is my question. What is the reason for them vs doing the SAN backups.
Fraggle
For me personally, the honest answer to your question is trust, control, and accountability. I am sorry but if I cannot ascertain with 100% certainty that my company's data (arguably one of the most valuable assets we have) cannot be restored OR I am unfamiliar/uncomfortable with the solution provided I will voice concern.
As far as the technical aspect goes I still say that having true SQL database backups is the way to go. That is what I am comfortable with restoring (especially under pressure or in a DR situation). I know that may sound reactive and even lazy but when the chips are down, trust me it's all about "Show me how you did your job as a DBA ensuring the security and recoverability of the organization's assets". Anything less than that and even if I am not out of a job, my credibility is damaged.
My $0.02.
I won't disagree with you at all on the matter. Just a matter of proving it to the MAN.
Fraggle
The easiest way to prove it is to just have a disaster where you lose all your data. :crying:
Seriously, I think the burden of proof should be on the vendor to prove that what they are proposing will somehow be a better way to do backups than established SQL Server best practices. When you have a major problem, it is not the time to start hoping that those backups really work.
You don’t want to end up as another entry on this thread:
It's dead, Jim
The crux of the problem is that the company hosting the database has sold the client on it. Techincally we are at no legal responsibility to do anything or manage their backups. However, we still get a black eye if the system goes down, no matter who is really at fault. And strickly from a customer service standpoint I personally think it is a good idea to tell them they should do it.
Fraggle
September 30, 2009 at 3:55 pm
Well, it is up to them to make the decision. Just tell them your opinion, give them the facts, and let them make it.
If it all goes wrong, they wouldn't be the first company that made a poor choice for their backup strategy, and paid a high price for it.
http://thedailywtf.com/Articles/Death-by-Delete.aspx
http://hardware.slashdot.org/article.pl?sid=09%2F01%2F02%2F1546214&from=rss
October 2, 2009 at 7:41 am
The crux of the problem is that the company hosting the database has sold the client on it. Technically we are at no legal responsibility to do anything or manage their backups. However, we still get a black eye if the system goes down, no matter who is really at fault. And strictly from a customer service standpoint I personally think it is a good idea to tell them they should do it.
Fraggle
I know exactly what you are talking about. We have had Clients select a Hosting company for everything, all of their Servers for our application. So when there were performance problems, we got blamed. When there was a disaster that required the database be recovered and the backups apparently had been failing, we got blamed for that too.
Since the client is already "married" to the hosting company your best bet is to do two things. One, as someone has suggested, tell your client that you have some concerns about only completing a file level (read as SAN backup) on a transactional database. Especially since the ability to recover to a point in time is in question. For me, that is HUGE! :w00t:
Second, take the high road and tell them that even though they are on a hosted solution, it is still their data and they should be able to dictate exactly how backup and recovery operations are to be completed. From that standpoint you have done your due diligence and suggested that they create SQL backups as an extra security blanket should something go wrong with the SAN.
My guess is that this hosting service is betting on the fact that their SQL Cluster and SAN combo are not going to fail. That is, they are already redundant on SQL Servers being connected to a ultra redundant disk array. However, as everyone who reads this forum is abundantly aware, disks of every kind fail all the time and the amount of time it takes to recover a database is directly proportional to the amount of time you took to plan.
Plan wisely and sell your client on the idea that SAN backups are not enough. It will save them, and your company, lots of heartache in the future.
Regards, Irish
October 2, 2009 at 3:13 pm
The key is the ability to recover to a specific point in time (mentioned above), and not just they time they took the 'SAN backup' (only relevant if you are running in full recovery or bulk-logged modes).
A great line Jeff Moden posted a few weeks back that has stuck with me: A backup never saved anybody's job - A RESTORE did.
There are several types of SAN-level backups for databases, one of which is actually good, if they get it to work reliably.
The types (off the top of my head):
1. SAN-level snapshot
2. SAN-level Snapshot archived to tape/VTL/offsite something
3. SAN-Based SQL Server snapshot
4. SAN-based SQL Server snapshot with log backups
5. SAN-Based SQL Server snapshot archived to tape/VTL/offsite something
6. SAN-based SQL Server snapshot with log backups archived to tape/VTL/offsite something
Pros/cons
1. SAN-level snapshot
Captures the state of files as of a specific point in time.
* Pro:
+ It's cheap and easy, provides limited ability to roll back to the time of the snapshot
* Cons:
- Snapshots are not backups. Actually, I should word it like this: SNAPSHOTS ARE NOT BACKUPS!! Part of what you are protecting yourself from with backups is corruption/destruction/loss of the files, disks, or SAN. Snapshots protect you from none of this.
- It's not a 'crash-consistant' backup, just a picture of what the files looked like at the time of the snapshot. SQL Server is not designed to be backed up this way; there's no guarantee that when you recover a database from one of these snapshots that it won't be corrupt.
- There's no ability to roll forward. All changes made to the data after the last recoverable snapshot (which may not be the most recently taken snapshot) will be lost.
2. SAN-level Snapshot archived to tape/VTL/offsite something
Pros and cons similar to #1, but the snapshot is used as the basis for taking the backup to secondary storage. It then goes from being a snapshot to a backup, but it's still not crash-consistant. To restore the database, the files are pulled from tape, and attached to an instance of SQL Server.
3. SAN-Based (VSS) SQL Server snapshot
Several SAN vendors have implementations of VSS (Microsoft Volume Shadow Service) applications for SQL Server. The VSS-based snapshots ensure that the snapshot is crash-consistant. It's still a snapshot, but it has a higher likelihood of being recoverable. It is effectively a full database backup, albiet one residing on, and dependant on, the same disks and files as the database. (Snapshots are not backups!)
4. SAN-based (VSS) SQL Server snapshot with log backups
This method adds roll-forward ability to VSS snapshots (item #3, above). Full backups are performed using VSS backups, and transaction log backups are performed using native BACKUP LOG statements in SQL Agent jobs. When the database snapshot backup is restored through VSS, it is left in an uncovered state, to which native RESTORE LOG commands are executed to restore the log files. Provides the speed and low I/O of a snapshot with the ability to roll forward during recovery. Recovery still depends on the existence of the SAN, and the presence of the snapshot data in uncorrupted form. (Snapshots are not backups!)
5. SAN-Based (VSS) SQL Server snapshot archived to tape/VTL/offsite something
Same as #3 above, but as part of the process, the snapshot is mounted to a secondary SQL Server instance, and backed up to stable media. This is what converts a snapshot to a backup. No roll-forward capability, but at least it's a real backup stored off of the SAN.
6. SAN-based (VSS) SQL Server snapshots with log backups archived to tape/VTL/offsite something
Combines #4 with #5 - database backups and log files are stored off-SAN, and preferably offsite. This is the only option that provides roll-forward recovery in the event of SAN loss or corruption. Getting this method to work properly is not trivial.
As posed above, #6 is the only option that answers the goal of a restore with roll-forward capability. It's the answer to the question (that you can ask the vendor), "How do I restore my databases to any point in time in the event of SAN corruption (which happens, and don't let anybody tell you otherwise, unless they are willing to sign a 100% SLA with cash remediation).
Insist that there is a way for you to routinely test restores (and then actually do them). Niether backup processes, error-reporting systems, storage management, nor computer hardware are perfect.
-Eddie
Eddie Wuerch
MCM: SQL
October 2, 2009 at 10:01 pm
So we go live with the client, yesterday 10/1. After all of the issues were resolved at 3:30 a.m. on 10/2, I was poking around the system to make sure that everything else was going ok before finally going to bed. Not sure why I did it, but I ran sp_spaceused, and OH BOY! did I get a shock. It sits there and tells me that this brand new database with conversion data is now 170GB. STOP THE TRAIN!!!!!.
I go and take a look, and the mdf is the correct size for what I was expecting.....just under 50 GB. Which means of course that the ldf was........wait for it............ 120 GB. WOOOOOOO MOMMA!
Come to find out that they had the database in full recovery model, and with no backups, it just grew over the course of the day.
So I show my amazing :doze: manager this morning and he finally agrees to let me stick my nose into the issue. Call the hosting company, and ask them about it. "Don't worry about that. The full backup we will do once a week will make sure that the transaction log doesn't get to large." was the line I got. What? A log file almost 2.5 times larger than the datafile at 120GB is small for you guys??????
Come to find out even more that they are only doing their snapshots once a day.
Again, talk with manager, and he starts to worry. However, I think the favorite part of my day, was when the Log file autogrew by 10% and performance, even on the SAN slowed down while it allocated the 12GB of new space. (GEE GO FIGURE!) That was when my manager finally flipped out and called the client to inform them of the situation. Of course, the client didn't realize any of it, and proceeded to respond with a few choice words, after which they asked us to setup correct backup structures. I believe they said that they could afford to lose 10 minutes worth of data, but 24 hours wouldn't be a good then when they did $500k in our system today.
I greatly appreciate everyone's input as it helped me stay the course and gave me ammo to fight was I knew wasn't a good thing.
Good guys win again.
Fraggle
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply