April 21, 2011 at 9:01 am
Our network folks are using Compellent Storage Center for their file backups and DR. They are pushing to have us abandon Redgate for SQL backups and go with a Compellent option. As a SQL DBA I'm uncomfortable with this change. Has anyone moved all SQL backups to this product that would be willing to comment on the process and outcome?
April 22, 2011 at 5:20 am
I'm not familiar with Compellent, but I went to their web site. It looks to me like it's a SAN management and backup software. Is that right?
If so, those things work really well. If you have giant databases on stand-alone systems, it's really one of the best ways to do backups and restores. However, if you have lots of small or mid-sized databases (< 1tb), getting snapshots of the drives, even if it is transaction aware snapshots (and that's the one thing you should validate) won't help you for restoring individual databases since multiple databases live on a drive. Personally, I'd see if you can't just do both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2011 at 9:46 am
Thank you for the reply, Grant. Compellent is SAN management and backup software. It is not transaction aware. I've been told by one of their customers that they set every database to simple recovery.
As we have many small databases (<= 2GB), on a single drive, you are correct that this technology doesn't help much with single database restores. An entire drive would be restored and I would have to detach and then attach the database that I need restored.
I'm voting to keep Redgate.
April 27, 2011 at 9:55 am
David Egnoski (4/27/2011)
Thank you for the reply, Grant. Compellent is SAN management and backup software. It is not transaction aware. I've been told by one of their customers that they set every database to simple recovery.As we have many small databases (<= 2GB), on a single drive, you are correct that this technology doesn't help much with single database restores. An entire drive would be restored and I would have to detach and then attach the database that I need restored.
I'm voting to keep Redgate.
Yeah, if it's not transaction aware then there's no way in the world I would support using it as a means of backing up my database systems. That has nothing to do with keeping Red Gate or not. You need to know that when you restore the drive that the databases on it won't be corrupt, that committed transactions are written to disk and uncommitted transactions are rolled back. That's also completely independent of whether or not your database is in simple recovery mode (you might want to to talk to them and their customers, they're operating in a dangerous manner).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2011 at 10:20 am
I'm not familiar with Compellent but others that do similar work allow you to take transaction log backups along with their backups and allow for restores through the same.
That all being said though there are reasons to keep the Red-Gate backups as well (or any type of SQL backups) some of which include the ability to restore without having to go to your probably already overworked SAN admin to do basic recoveries or to copy data to another environment, etc. Before throwing all other backups out I would sincerely look at all processes where backups might be used and then make sure that the SAN admins are going to be willing to support those activities.
One other thought too, but if you are doing any replication I would make sure that they support the KEEP_REPLICATION option as part of their restores.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 29, 2011 at 5:05 pm
David Egnoski (4/21/2011)
Our network folks are using Compellent Storage Center for their file backups and DR. They are pushing to have us abandon Redgate for SQL backups and go with a Compellent option. As a SQL DBA I'm uncomfortable with this change. Has anyone moved all SQL backups to this product that would be willing to comment on the process and outcome?
I am a certified Compellent SAN administrator as well as my company's DBA.
We were faced with a similar choice when we migrated from direct attached storage to our SAN. After considerable research, debate, and experimentation, we decided to:
1. Implement Compellent replays (their name for snapshots). Think of replays as a type of database backup but not like a SQL Server or RedGate backup. We also implemented them to facilitate efficient data migration between storage tiers, another subject.
2. Use Compellent's Replay Manager software package (which is fully transaction aware) for all database snapshots.
3. Continue to execute our regularly scheduled database backups using SQL Server in the simple recovery mode (our preference) as "insurance" and to retain the ability to store off SAN backups, which we regularly migrate to a file server.
4. Use Compellent's simple replays for efficient data migration of SQL Server produced database backups stored on the SAN.
At first we didn't trust the whole SAN replay/snapshot concept because we'd had no experience with it. But, we've had ample opportunities to use it for database restorations and it has never failed. It is also very fast, much faster than normal database restorations, especially if you have large databases.
Ask me any question. I'll be glad to respond.
LC
April 29, 2011 at 5:18 pm
You mention you use simple backups. If you have full backups and need point in time restoration capability, can compellent's snapshots deal with this? I'm assuming it can't but as long as we've got an expert in the house... 🙂
Also, are compellent's snapshots splittable, in particular does it file or drive snapshot? I don't want to be recovering 30-40 databases when I only need one off a logical array.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 29, 2011 at 6:37 pm
Craig Farrell (4/29/2011)
You mention you use simple backups. If you have full backups and need point in time restoration capability, can compellent's snapshots deal with this? I'm assuming it can't but as long as we've got an expert in the house... 🙂Also, are compellent's snapshots splittable, in particular does it file or drive snapshot? I don't want to be recovering 30-40 databases when I only need one off a logical array.
Great questions, Craig.
We only use Compellent's simple replays (snapshots/backups) for the database backup files produced by SQL Server's regular backup operations.
We use the Compellent Replay Manager generated replays for all of our databases. The difference is that the simple replays are not "transaction aware". The Replay Manager generated ones are and they ensure a consistent snapshot of a database's data and log files. Replay Manager uses Microsoft's VSS technology to accomplish this.
Either of the 2 types of Compellent replays can be used for point in time restoration to the degree of frequency the SAN administrator has set them up.
Compellent simple replays are file oriented. That is, they consist of a replay for a specific file.
Compellent Replay Manager replays are database oriented and are "splittable". We have several of these replays with more than one database included in them. I can restore a single database (both the data and log files) out of an aggregated replay.
The Compellent SAN is not drive/spindle oriented to an administrator. There is no way for an administrator to know exactly on which spindles his databases and/or other files are located. Not only is the information not available but the data is frequently in transit between defined "tiers", from 15K FC drives to SATA drives, or from RAID 10 to RAID 55, for instance.
The Compellent SAN is the closest thing to a black box I've ever seen. If I need to allocate disk space, I instruct it to allocate a certain amount on certain storage tier(s) that I've defined and the SAN just makes it happen.
LC
May 1, 2011 at 2:11 pm
crainlee2 (4/29/2011)
Great questions, Craig.We only use Compellent's simple replays (snapshots/backups) for the database backup files produced by SQL Server's regular backup operations.
Interesting. So you're not using the SAN to avoid standard backup procedure. You're snapshotting the .bak files.
We use the Compellent Replay Manager generated replays for all of our databases. The difference is that the simple replays are not "transaction aware". The Replay Manager generated ones are and they ensure a consistent snapshot of a database's data and log files. Replay Manager uses Microsoft's VSS technology to accomplish this.
Either of the 2 types of Compellent replays can be used for point in time restoration to the degree of frequency the SAN administrator has set them up.
So if they snapshot every 15 minutes, that's your point in time you can return to. However, if it's snapshotting the log AND DB constantly, that's got to be a space hog if you want 15 minute point in time ability. Also, you mention consistent snapshot of data and log file. I assume somehow you pair them in compellent as mdf/ldf combinations, but still, if you poke around there's any number of articles out there about how an unclosed/detached/offlined database that you do this with would end up performing and the data errors. What's the workaround here?
SAN snapshot backups of mdfs, particularly because of point in time requirements, have always worried me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 1, 2011 at 7:56 pm
Craig,
No, we don't use the SAN to avoid standard database backups using SQL Server. We do both SAN replays and SQL Server backups for the purpose of intentional redundancy, in case one fails. Plus, sometimes it's easier and quicker to restore the backup of a small database using SQL Server, so there's the issue of convenience, as well.
We take simple replays (snapshots, what Compellent calls Instant Replays) of the database backup files, the .bak files. The reason is that Compellent says Replays improve the performance of the automated data migration from Tier 1 storage (15K FC) to Tier 3 storage (SATA).
Currently, and this is soon to change:
1. We are taking a Replay Manager snapshot every 24 hours. We are soon going to experiment with increasing the frequency of these to see how much additional space they require. We will probably start out conservatively, say every 4 or 6 hours, and shrink that window until it reaches a point of diminishing returns by consuming too much disk space.
2. We take SQL Server generated full backups once every 24 hours.
3. We take SQL Server generated differential backups every 30 minutes, except when the full backups are in progress.
We decided not to implement point-in-time recovery. Business requirements dictated that being up and online was more important than some data loss, so we use the Simple Recovery model in SQL Server.
I was as skeptical as you about the synchronization of the .mdf and .ldf files during a SAN replay (snapshot). Compellent assures their customers that as long as they use the Replay Manager software (which utilizes the Microsoft VSS technology) to coalesce the 2 files, there will never be any data errors. We've experimented with it to the point that we believe Compellent and are comfortable with it.
LC
May 1, 2011 at 9:08 pm
crainlee2 (5/1/2011)
Craig,We decided not to implement point-in-time recovery. Business requirements dictated that being up and online was more important than some data loss, so we use the Simple Recovery model in SQL Server.
LC
How does FULL recovery model hinder being up & online ? What's the downside ?
"Stuff" happens, and full recovery model with trans-log backups gives you the ability to recover from a disaster (hardware, software, human error ...) in most situations.
May 2, 2011 at 3:07 pm
homebrew01 (5/1/2011)
crainlee2 (5/1/2011)
Craig,We decided not to implement point-in-time recovery. Business requirements dictated that being up and online was more important than some data loss, so we use the Simple Recovery model in SQL Server.
LC
How does FULL recovery model hinder being up & online ? What's the downside ?
"Stuff" happens, and full recovery model with trans-log backups gives you the ability to recover from a disaster (hardware, software, human error ...) in most situations.
Sorry I took so long to respond, HB. Mondays are busy days for me. Now, responding to your question:
We have 16 application databases on 2 servers. Of those databases, 2 are large (1 over a TByte, the other over 700 MBytes). If either database were to be corrupted or lost, I would be under extreme time pressure to restore it.
The larger of the 2 databases takes about 6 hours to have a full SQL Server backup performed. The other requires about 5 hours to have a full SQL Server backup performed.
The company used to use the full recovery model, performing a once per day full backup and then log file backups every half hour. I considered the time for restoring them under a worst case scenario and pointed out that if a database were to fail immediately before a full backup, it would require the restoration of the last full backup and the succeeding 37 log file backups to get us back online, management's principal concern.
I then pointed out that if the same failure scenario took place using the simple recovery model with differential backups, the time to recover would be only for the restoration of one full backup and the most current differential backup. (We take differential backups every 30 minutes.)
Management opted for the simple recovery model simply because we could get the application software back online more quickly using differential backups then log file backups in a worst, or close to worst, case scenario.
In a few months, the company will be releasing a totally new system. It will include a different database organization, different servers, and a slew of other differences. At that time, everything that I've implemented in the last 2 years will have to be reimplemented, and every way of maintaining and backing up the databases will be subject to review and reconsideration.
LC
May 2, 2011 at 3:19 pm
You could take full backups, & frequent differentials, and more frequent logs. Then you would restore the full, most recent diff, and just the logs since the diff. And still keep the option of not restoring the logs if you don't need to.
Also, let's say some data gets corrupted or deleted, that you need to recover, but maybe not super-urgently. If you have the space, you can restore to a temporary database, get the data using point in time recovery, then copy that data back to production.
Some 3d party backup tools allow object level recovery.
(I realize that my not-well-thought-out ideas sometimes don't work well for others.)
May 2, 2011 at 3:50 pm
HB,
I appreciate your response and ideas. I am not keen on losing data either.
We could take 1 full backup per day, then differential backups every 2 hours, and in between them, log file backups every 15 minutes. There are a lot of ways to make our situation better. We could take more frequent replays (snapshots) with the SAN. Those restore VERY quickly.
You should keep everything I've told you in perspective. Our backup and recovery plan is not the best solution but it is the best solution I could deliver with the time I had available. When I joined the company, they had never had a DBA, they did not know best practices, their database servers were "on fire", they did not know what was wrong with them, and it took me over 6 weeks of very hard work to get them under control. On one of them, the CPU utilization never went under 98% and the direct attached disk drives were accessed relentlessly. Read queue length was always in the triple digits and occasionally went over 1000. The company was on the verge of going out of business due to poor response times.
I just recently completed an almost 2 year backlog of work. I'm currently mining data for management, their highest priority. But, when the new application software is released, every way we do things will be subject to reevaluation and I am certain we are going to do some things differently because I know best practices, I continue to study best practices (especially on SQL Server Central), and there will always better ways to do things.
LC
May 5, 2011 at 10:34 am
Just a quick follow up question:-
How would it handle a mirrored database which is obviosuly FULL recovery model?
We have a requirement for faster backups / restores but still need our database mirrored for DR.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply