June 16, 2010 at 8:51 am
Greetz!
I mentioned to our IT admin that all of our backups and our transaction logs reside on the same physical drive as our main database files. If the drive were to go all would probably be lost. So he plugged in a 1 Tb drive USB drive.
I'm wondering if these usb drives can operate at sufficient speeds to place our LDF files on them. I'm concerned that it would deteriorate the speed of our transactions. It is plugged into a standard USB port and not via firewire.
If it is too slow would it be sufficient for using as a place to store our hourly and nightly backups?
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2010 at 8:56 am
Transaction logs on a USB drive? That's got to be one of the worst ideas ever.
If you really have to use one, use it for your backups.
June 16, 2010 at 9:34 am
LOL. I thought it sounded off. But that is what was delivered when I told our IT guy that we might have better performance if we moved our transaction logs onto a seperate disk.
If someone could provide some technical information, or even an article, that supports this I can speak to my IT guy about it. I don't think it would be good for morale to just tell him its the worst idea ever 😉
BTW: I haven't seen the physical drive but am told it is not a thumb drive, if that makes any difference. It is a full size drive just connected via USB.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2010 at 9:41 am
This sounds like a really really bad idea, and i am not even sure that it is possible as the transaction log has to be on a local disk.
June 16, 2010 at 9:51 am
LOL... I had to check my calendar to make sure it wasn't April 1st.
Very bad idea, I'd question everything this IT Admin does in the future. Does he use a lot of duct tape in his datacenter?
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
June 16, 2010 at 10:00 am
I'm working on getting the make and model of the drive to see if it is even usb 2.0. For now I'm just using it for backups that are like 4 years old. My boss doesn't want to delete them for some reason. I can't imagine they are of any use now but...everyone here seems to be afraid of stuff I suggest..lol.
I'll post the drive details when (or if) I get them.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2010 at 10:42 am
The drive being used is an Iomega LDHD-UP (Prestige). It is 1Tb in capacity. It uses 'Hi-Speed USB' to connect which has a 480Mbps speed and it has an 8Mb buffer.
Apparently Firewire is faster but my IT guy said they are harder to find and more expensive.
The reason he is using a USB is because the database server doesn't have room for an additional drive.
So does the idea sound any better now? 🙂
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2010 at 10:43 am
The t-log is write intensive. You'd have to test the perfromance of the drive, but I suspect that the benefits of separate drives would be lost with the latency of accessing through USB.
June 16, 2010 at 10:51 am
When I've tested ideas like this in the past I've used sqlio.exe. Here's a blog on it:
Run it on the same drive as the database during a maitenance window then run it on the usb drive to compare the results.
I don't see a problem with the backups on the usb drive.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
June 16, 2010 at 10:52 am
I'd like to reiterate how bad of an idea that sounds. Even if it is possible (which I hope it isn't), it's just... bad.
Really, your transaction logs (and database files) should be on a RAID system. RAID 10 throughout will get you the best results, but it's also the most expensive. Failing that, the next best is RAID 10 for the transaction files, and RAID 5 for the database and index files.
Any benefits you get from having two parallel drives for the data and transactions, you loose by the abysmally slow bus speed of USB. Your database's performance will suffer greatly.
And then, imagine this USB drive comes unplugged... or the USB port fails. Now, imagine this happens during the day while people are making transactions.
Now, consider the impact your database has on your company. Chances are, it is huge. For a lot of businesses, enough data loss can cause a business to shut its door for good. This needs to be given the respect it is due. You are not going to arrive at a robust disaster recovery solution by throwing a $200 drive at the problem. It's going to cost some money.
The cost of a good disaster recovery solution could in the tens of thousands, but the cost of data loss could be in the millions. It's a real no-brainer. Make a business case to your boss. Then, make a plan and implement it.
--J
June 16, 2010 at 10:53 am
MothInTheMachine (6/16/2010)
So does the idea sound any better now? 🙂
No, for one simple reason - redundancy. You've got no RAID there, you've got a single point of failure, if that USB drive fails, your databases become unavailable. Especially if the USB drive is intended for desktop machines. There's a world of difference between desktop hard drives and server hard drives. Server hard drives are designed for heavier usage, higher duty cycles, higher RPM, often higher MTF.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 10:55 am
I would be worried that the drive gets pulled out or falls out at some stage/.
June 16, 2010 at 11:00 am
The reason he is using a USB is because the database server doesn't have room for an additional drive.
How many drives does the server have now? What are they being used for? Is you SQL Server a dedicated machine? If so, there should be plenty of drive space for the server. Has he considered adding a RAID card? How about a SAN?
--J
June 16, 2010 at 11:07 am
Thanks for all the feedback. I think I have enough information to make a good case for upgrading our database server. I'm just a developer with the company and don't want to cause any trouble for other people. I should probably make sure my resume is up to date before I say anything though.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply