Home Forums Discussions General Discussion Multiple backups, multiple places

These are good answers and good questions, and helps shift a little of the burder off me, lol.

I am researching this as well as one of my expert friends (who knows everything about networking and very little about SQL) was wondering how well it backed up SQL databases.

It is mostly a block by block comparison backup. My friend also mentioned a couple of other places, one of which is Iron Mountain, and I am looking into that.

One things is clear. It is not looking at the 1GB (for example) database and saying "a file changed there, but we can't back up that granular, so we will need to say the database file needs to be backed up." Reason being is during my tests, it only uploaded three files. It starts to get past me, but even though it is only one file, God knows it if it needs one bit of metadata or if one of my emails counted.

Another place recommended was sysform.com which is a lot like a P2P in that it takes your hard drive space and uses it offline. Don't ask me. I read through it once.

Another thing is certain. As I will talk about at the conference and, hopefully, Indy will correct me when I am way off base, is that more than one backup is good for redundancy and for the variability. If I have a Acronis or Windows SBS 2008 image backup or a file by file backup sitting on an external 1TB eSATA drive, I am going to grab that before one online. It's just good to know for $5.00 per month that all of my data is protected online as well. (Encrypted)

I do know that Iron Mountain supposedly does a bit by bit instead of file by file, which is getting pretty damned granular. I would say the bit (1/8th of a byte) is like talking about the atom.

Therefore, I gather Jungle Disk will work and will not take 18 hrs to do a typical backup at my 250Kb upload speed?

And, Indy, suppose you change something in record number 250 of 500 records in a table; is that change appended to the end of a block of data representing that table or halfway along it? Or something else entirely?

David,

Good question which I don't know the answer at this moment, but hope to ferret out with Bert's assistance next weekend.

If we are correct in our understanding of how a comparative[differential] backup occurs with JungleDisk, then whether the file end-appends, or blocks in the middle are changed, the changed and/or new blocks of the physical file are the only one actually uploaded.

This will require some additional information and testing to validate.

David,

You have started a good set of questions here. I have thrown it to my higher experts. I am having a hard time finding people with both sets of knowledge, e.g. de-dupe AND databases such as SQL.

I do know that if you back up daily using Jungle Disk, all the data you set to back up will be backed up. It will only upload the new or changed data (not in the traditional forms of incremental or differential backups). At this point, I wouldn't consider this your only backup nor feel comfortable simply restoring it in its entirety (although I have done multiple test restores).

The final answer is still out as I am waiting to hear back from Raja. I sort of sent him out a "feeler" email to see if he was aware of de-dupe. Apparently, he is as he sent back very correct information. But, I didn't ask him about how it related to SQL (that is is area of expertise). So, that answer will likely be the one.

The reason it is confusing and over my head at the moment, is the fact that within de-duplication, there is backing up at different levels such as bits, files and blocks.

Is it clearer now?

Thank you both. It is oddly reassuring to find it is a difficult question. At least JungleDisk seems to work for me, and adding a note or two did not mean that the next backup took very long, so it seems it is a decent solution. And, Bert, I consider this my backup for the event the office burns down.

I greatly appreciate the suggestions made here. You all have pointed me in directions I did not know existed, and have given some really elegant solutions to my deepest fear about converting from paper to electronic.

I am really sorry I won't be able to see you at the ACUC. Maybe next year.

I am doing the slides as we speak. Or type. And, it's evolving as I am looking at other backup sites -- Jungle Disk being the best so far. Still have to look at Iron Mountain.

Will post back when Raja gives me the scoop.

Fascinating. This is an intervew with Bob Wilson, vice-president of HP's Storage Platforms Division. While he doesn't mention SQL specifically, in the business world, SQL and Oracle reign king. He compares the advantages of deduplication technology (JD, Server Edition) with databases versus, are you ready: PACs. Don't know if your hospitals use Pacs, but it stands for Picture Archiving and Communication Systems. They don't change so de-dupe doesn't help much.

BM. Can you outline some of the common mistakes or pitfalls that customers should be aware of as they implement virtual tape libraries with deduplication?
BW. Probably the biggest one is comparing technologies based numbers being quoted for deduplication ratios delivered by products in the industry. It turns out that what ratio you will actually get is mostly dependent on a few things: the type of data, your backup policy, how frequently data changes and how you measure the ratio. For example, PACs are ‘picture archiving and communication systems,’ a type of data used in X-rays and medical imaging. These have very little duplicate data. At the other end of the spectrum, databases contain a lot of redundant data, their structure means that there will be many records with empty fields or the same data in the same fields.

Oh yeah, they froze Han Solo in that stuff. I got it confused with Corbomite.

The next generation answer, maybe...

Toshiba recently announced a technique that will shrink the size of Solid state digital Drives (SDD's) by 70%, allowing a marked reduction in price, and 1 TB of storage in a space the size of a half dollar. A prototype SDD was demonstrated with 128 GB of memory and a controller chip, and a data transfer rate of 2 GB/second.

You really could take everything home in your pocket every night.

Was that 2GB/s or 2Gb/s?

The article said 2 GB, but it is just a single source.

I guess I figure that if my server was trashed I could just download the complete AC (with SQL) onto my laptop, restore from the backup, and keep seeing patients. I love the simplicity of AC

Yeah, but when 4 providers plus support staff and scheduling all need it is is more of an issue. I have lost a mother board on one of of our occupational medicine servers, and it is really a goat rope... waiting for the computer store to open, begging for expedited service, clients backing up at the window, lying upside down under the server platform in the dust unplugging wires instead of seeing patients, etc. A second server always on line so all we do is point AC from "SERVER1" to SERVER2" while we leisurely get SERVER1 fixed.

Actually, the best is a complete mirror setup, with 2 independent systems always on line, but I'm told that would take a total of 4 servers, and that was too much even for me. But, that way, if side "A" fails, side "B" takes over and we don't even notice it except we get an e-mail alert that side "A" needs fixing.

That's cool. Computers really aren't that expensive, considering, and I could have a backup server ready just in case.

Just curious Boondoc..... are you WWAMI, and recently out of training? I used to teach ICM-1, and a buddy is starting in a month.

David,

So is Server 2 just sitting there with an empty database?

Thanks.

Bert

Bert,

Server 1 backs up to Server 2 every evening. If server 1 fails, we switch to server 2, and when server 1 is fixed we would synch if data had been entered into server 1 since the last backup.

See that is what would scare me. Even Jon at the conference stated that the sync is buggy and can even cause you to lose records. I have never trusted the sync feature.

Your plan is great. I like it. I just worry that after switching and fixing the other server and you now have 200 progress notes on the 2nd one, I would be scared to sync to the server with 20,000.

It is an imperfect world, and I am not totally comfortable either. I really want a mirror system, and that may come in time, but the extra two data servers I am told are required OD'd my budget.

I am hoping the Law of the Innate Perversity of Inanimate Objects, which states equipment always fails at night and on weekends will save me, so that server 2 will have been backed up just before server 1 crashes.

Have you looked at Double-Take? It was brought up to me at the conference.

And, I think your above rule changes in this instance, lol.

I am looking at it.. if Double-Take Availability is the program you reference, it looks very interesting.. it looks like this will create a continuous backup to a second server? Do I have this correct?

I don't know. I am still looking at several of the solutions. There is one specifically for SBS. It also states you can sync to remote, but I would think that your performance would take a huge hit if that happened, but that takes care of your offsite/disaster issues.

I guess from reading through it quickly, one would probably want to define what they wanted to achieve, then ask a rep. But, they have trials, so that is rather tempting. They strike me as the type of company that would follow up with any downloaded software, but one can't blame them for that.

I turned my IT guys loose on it. I will report the conclusions.

I contacted Double-Take. The great news is that their Availability software does exactly what I want; that is create a mirror machine so if one fails the other continues seamlessly on. The bad news is that it is extremely expensive; the "retail" cost for the required software for my two servers would be as much as adding the two additional hardware servers to make a hardware mirroring system. They offer it through resellers, and the salesman indicated that "substantial discounts" were available, but unless it is discounted about 90% it is not really in the running for a rational solution. He pointed with great pride that they had been in business since 1990-something, which always causes me great amusement when dealing with computer folks. For most businesses, a 15 year longevity is just getting started!

Their backup program, which does a continuous backup but does not automatically switch servers is about 1/4 the price, but still more than the cost of a basic laptop. He indicated he would send an evaluation package, and I'll have my IT guys look at it, but I was really floored by his quote; I thought he had misread by a decimal place. Frankly, with the cost of hardware being what it is, I don't know why anyone would go this route. I'll report further after I see what he sends.

Yikes! It still sounds pretty cool, though. I wonder what substantial discounts are.

I've got a busy week ahead but I'll drop an email to a distributor to see what the lic cost is for MS SQLServer clustering. [Admittedly that doesn't address real-time mirroring of the ImportedItems directory.]

A services cluster allows for seamless fail-over of the database service, [see http://msdn.microsoft.com/en-us/library/ms189134.aspx]

One of the side-effects of this would be going to a shared disk implementation that would provide hardware fault-tolerance.

Since we are talking about a significantly more complex architecture/implementation, I'll drop it there unless anyone wants to explore this path.

In honor of Bert's appreciation of tech p0rn, here is some of the latest in onboard SSD that you could use as your primary DB drive and mirror.

http://www.engadget.com/2010/06/28/revodrive-pcie-ssd-reviewed-deemed-awesome-cheap-and-awesome/

here is a sexy sample:
<----snip----->

Specifications:

* Capacity: 120GB / 240GB
* Flash type: MLC
* Interface: PCIe 4x (Gen. 1)
* Max read: 540 MB/sec
* Max write: 480 MB/sec
* Sustained write: 400 MB/sec
* 4KB random write (aligned): 75,000 IOPS
* Seek time: 0.1 ms
* Power consumption: Idle 3W / Active 8W

And now for the ringer:

* BOOTABLE (!)

Other PCIe SSD solutions out there are not bootable (most notable the Fusion-IO line of SSD's). Since the RevoDrive builds on a simple RAID controller, it sees the same benefits, such as a low footprint driver that can be executed by the Windows installation environment, enabling windows to be installed directly on the RevoDrive itself!.

It does look sweet. Although most of the comments deemed it anything but cheap.

Indy, where do you think RAID is headed?

As far as RAID, you can see the future in SAS (Serial Attached SCSI) technology. That paired with "self-healing" controller software is the blending of function, speed, and ease-of-use.

Part of the question is the hardware implementations - hot-swappable drives are de-rigueur in server builds these days, but those are always paired with enhanced controller technology.

I am seeing more enhanced controller technology showing up in retail builds.

When you talk about self-healing, is that part of DroboPro's configuration?

http://www.drobo.com/

Bert - in answer to your question about fail-over clustering, the rack-rate quote I got for SQL Server Enterprise & cals was ~ 11K per server. I did ask if the second machine is running shadow/standby could you just buy enough s/w for one server, but that is still a boat-load of money.

Just for the purposes of documenting the pricing, I'll update this when if I hear back from the rep.

So, here is my next dumb question..

It SEEMS like it should not be that hard to capture each keystroke or data bit coming in to server 1, and send a duplicate of that data bit to server 2. At least, it does not seem like it needs to be a $6,000 to $11,000 problem. Is there perhaps some simple application that does that?

My IT guys came up with the following backup plan:

The Amazing Charts backup program runs nightly, absent the Imported Items. It backs up to the primary server and the AC remote site. Jungle disk then backs up the AC backup folder on the server and the imported items folder to the Jungle Disk server nightly. This serves as the offsite backup in case of fire or natural disaster.

For the more timely local backups, Windows Server 2008 comes with its own backup utility that allows the entire primary drive to be incrementally backed up to the second server without shutting down the program on the primary server. This runs every 30 minutes.

Since my IT guys dreamed this up (and tested it) I can't offer any gory details, beyond the observation that it seems to fulfill my most paranoid desires.

Sounds good. Not to second guess your IT guys, 1) why not back up all your data to Jungle Disk? And, is Amazing Charts on your primary server? And, if so, are you backing up AC using its backup program to the primary server?

Well, actually all of our data is going to Jungle Disk. I was mostly indicating that they found it best to NOT have the AC backup file include imported items, but to back them up separately. (I'm pretty sure this is what they explained. I got a little lost here. Possibly to keep from overdosing the AC offsite backup as you have repeatedly warned.)

AC is on the primary server, and it backs up to the AC backup folder on that server using the AC backup program as you surmised.

David,

I guess even with that folder backed up, the default backup is to that AC folder on the primary server. I consider that the weakest of all places for a backup. Not your doing, though.

But, you have three other places to back up. Why not take advantage of that and let it back up to your second server or another computer?

David,

I don't know much about it, but your clients can only be connected to one database at a time. Now, you have data being loaded into SQL Server's Engine and the user databases. I don't see how that can be done at the same time without special software.

Maybe with terminal services or something. Throw it up on experts-exchange.com

Way I read the plan, running shadow copy to the second server.

I'm guessing that the reason they are backing to JungleDisk from the backup directory because the files are already compressed (and in theory verified).

I agree with Bert that I would use the other built-in AC backup locations to back to other shares on the network.

Can you clarify this for me? I need a picture in my head so I can compare it to the picture I have from IT.

David,

In the interface for AC Backup is a series of fields where you can specify (3 or 4 file paths) for the compressed backup file to be written locally.

Since it costs you nothing more, and the file is already compressed, it makes sense to write it to multiple locations on the network.

Another side tangent, but a way to protect you and your practice data is to encrypt those directories where you are storing backup data. Wendel mentioned Truecrypt at ACUC, and I know of other free and paid disk encryption tools out there. It is on my to-do list to eval the encryption solutions currently available.