June 19, 2015 at 5:15 am
I have a customer with an odd request and try as I might, I just can't talk him out of what he wants to do. For security reasons he wants to put a SQL database on an encrypted thumb drive (IronKey). Not a great idea, but it can be done. Here's the rub though. He wants to be able to work with the data on a workstation. Then, if he takes his laptop out of the office he wants to be able to simply plug that thumb drive into the laptop and fire up SQL on the laptop and use that same database. Procedurally this would work in that the database can be created so that the location is the same from both machine viewpoints, however will the two different SQL instances allow moving the database back and forth like this?
I cannot find anything related to this scenario.
June 19, 2015 at 5:29 am
Really, really bad idea.
Unless he makes sure that the drive is only removed after the first machine is shut down and plugged in before the second machine is started up, he's going to have repeated cases where the databases aren't accessible.
SQL Server is not MS Access (where this kind of thing works well enough)
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 19, 2015 at 6:03 am
I absolutely agree it's a bad idea. I've spent considerable time explaining why this shouldn't be done, yet he just won't give up the idea. He does understand that he will have to at least shut down the services before removing the thumb drive and this doesn't bother him. My contention is that sooner or later he's going to forget and that's when things are going to go south.
June 22, 2015 at 3:12 am
ratherbeflying (6/19/2015)
My contention is that sooner or later he's going to forget and that's when things are going to go south.
Yup.
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 22, 2015 at 6:56 am
Well, we are forging ahead with this idiotic plan, as he tells me it's his money and he should get what he wants for his money. Let's not even get into pitting me against his 'friend' that knows all about SQL and says there is no problem with doing any of this. So I'll go ahead and take his money and we'll see how long this lasts.
June 22, 2015 at 7:15 am
The first problem likely to occur is that he boots the laptop and then plugs the drive in. SQL tries to access the database files when it first opens the database, if it can't, it marks the DB as recovery_pending and won't automatically try again.
Make sure that you're not on 24/7 call for this.
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 22, 2015 at 8:12 am
Will it work? Yep.
Will it eventually fail in a spectacular manner? Yep.
I tried this once just to see what would happen; on my play laptop, with a bogus database... it worked, but I wouldn't even attempt it with data I actually cared about.
You already know about having to stop SQL before removing the drive, and keeping SQL in manual mode for startup... but he also needs to eject the drive, or at least wait until ALL activity has stopped, before actually pulling the drive. If he pulls it early, there is a good chance he loses his data.
If he absolutely insists on doing this, I would put something down in writing that says (in more politically correct terms) that you've advised him this is a horrible idea, that it carries a real and quite frankly likely chance of data loss, and that you waive responsibility for the inevitable disaster that occurs.
June 23, 2015 at 6:19 am
Not to fear, there is substantial documentation and a string of emails back and forth speaking of how dumb this is and what can happen, including the fact that there is ZERO liability on my part when this goes to hell and that I get paid regardless. At this point I'm looking forward to the whole thing.
June 23, 2015 at 8:44 am
Well, we have as of 9am Eastern embarked on this long strange trip. I'll let you know how it goes and I suspect down the road tell you how it didn't go :w00t:
June 23, 2015 at 9:21 pm
Heh... I can't wait until something corrupts and he needs to do a restore. 😉 Since it's his money, you know that'll be your fault. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2015 at 9:52 pm
CYA and make a backup and keep it in a safe place...
then offer to sell it to him later... No, just kidding about the last part.
June 24, 2015 at 11:50 am
Depending on the size of the db in question, here is something you could try.
On the desktop, setup the db on the local drive.
Every time before wanting to leave to office with the laptop, backup the db to the usb.
Fire up the laptop, restore db from usb to local drive on laptop.
Now each time he want switch machines, he needs to backup the most current machine db to the usb, and restore on the other one.
This should work, assuming the db is small in size (i.e. 1 or 2 gig), as any really large db would take a lot of time to backup and restore.
Also, he will have a copy of the db on each machine.
The trick and most important part to remember, is which machine db is the most current. If he forgets to backup the most current version, he could end up restoring an old version and going back in time.
Now, I do not recommend this for a production db or anything important. I mainly use this method when testing new tables and procedures. That way I can have the most current copy of the db on a the test machine, and in case I fubared my code or table structure I won't bring the production db down.
Also, I want to ask...If your client's db is ONLY to reside on said usb drive, what happens when he looses it someplace? I know those little buggers are easy to misplace. Then he has two machines with zero db's.
M.
June 24, 2015 at 2:32 pm
The story on this guy is that he wants the data secure in case his machines are stolen, so the thumb drive is one of those IronKey encrypted things that he guards with his life. I did the install and things are working... for now. How long it lasts is anyone's guess. I do have to admit that the IronKey is a fast little bugger as thumb drives go. I didn't see any performance issues at all and it operated as well as any I've ever seen on the local drive. Then again, it's not a huge database.
The odd thing though with all this 'analism' about security and not wanting the DB to live on the machines themselves is that he backs everything up to a NAS that as far as I know doesn't live in a safe or something and isn't encrypted. Go figure.
June 25, 2015 at 5:29 am
How about a Windows shutdown script which backs up the database to the USB drive then drops the database and a startup script to restore the database from the USB drive.
Apply it by GPO to both machines.
Alternatively encrypt the laptop and buy a docking station to use it in the office.
June 26, 2015 at 8:44 am
ratherbeflying (6/24/2015)
The story on this guy is that he wants the data secure in case his machines are stolen,
...and he's never heard of hdd encryption?
so the thumb drive is one of those IronKey encrypted things that he guards with his life.
Still much easier to lose or have stolen than a laptop; and definitely more likely to lose than a server. Not to mention that, in terms of process, he's vastly increasing his odds that his data will be corrupted.
I did the install and things are working... for now. How long it lasts is anyone's guess. I do have to admit that the IronKey is a fast little bugger as thumb drives go. I didn't see any performance issues at all and it operated as well as any I've ever seen on the local drive. Then again, it's not a huge database.
I honestly wouldn't expect performance to be an issue for a smaller database; USB drives are fast enough, and you've basically got exclusive access. The problem is the process. Merely bumping the thing at the wrong moment could result in corruption. Pulling the drive too soon after shutting down SQL could result in corruption.
The odd thing though with all this 'analism' about security and not wanting the DB to live on the machines themselves is that he backs everything up to a NAS that as far as I know doesn't live in a safe or something and isn't encrypted. Go figure.
He knows nothing about security; even less about reliability; and yet he's forced your hand into a solution that provides neither.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply