Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Due to some security problems, My boss has asked me to store all sensitive data in external/removable storages like USB stick or external HDD and this specially includes the MDF/NDF/LDF files of SQL Server 2008 we're running.

I've been reading for these last three days with no luck to find a solution. Is there any solution at all? Has ever anybody done such thing?

share|improve this question
    
For those wondering why do this, here is a thought. What about securing the data drives into an vault at night. If its a database that is only used during the day by office I can see "some" logic in doing this. Somewhat akin to locking up sensitive paper files at night. –  SpaceManSpiff Aug 2 '09 at 10:26
2  
@SpaceManSpiff, I think you would be better off just running an Ethernet cable into the vault/secure space and storing your whole server there –  Element Aug 2 '09 at 22:34
    
@Element - that depends on what the heat transfer of the area is like. If the "secure area" is small and not intended for anything particularly temperature sensitive it might be too well insulated to be a healthy place to put a PC. –  David Spillett Aug 2 '09 at 23:08
1  
I didn't think someone would ask me why I'm gonna do this. The reason is: The company is in Iran and we don't trust our govt as a "Secret Keeper" since they WILL share our info with our competitors. We have our plans and stats in some MS-Office files and also on some remote databases. To avoid getting rubbed by the govt, we forced our personnel to keep their sensitive data (Excel, Word, Outlook, ...) on their USB sticks and keep it by themselves. Now I'm gonna develop some Database-driven apps and I need to keep the Database somewhere safe. I think I'd consider using SQLite instead... –  Achilles Aug 3 '09 at 10:32
1  
I imagine there might be some restrictions in Iran, but is encryption an option? It's very doable to setup encrypted access to the data (TrueCrypt, OS based encryption, etc), and is almost certainly more secure on a fixed drive than unencrypted data on a removable drive. –  Christopher Cashell Mar 26 '12 at 19:43
show 3 more comments

12 Answers

Moving as simple database to an external drive should be easy:

  1. detach the database with exec sp_detach_db '<db_name>'
  2. copy the files to the new location on the external drive
  3. reattach the database with exec sp_attach_db '<db_name>', '<full_path_to_new_location_of_mdf>', '<full_path_to_ldf>'

(you can do this through the GUI tools too, attach and detach are usually found under the all tasks" heading on relevant right-click menus). My experience of this is on SQL7, 2000 and 2005 only, using internal drives in USB enclosures, but I assume it is not something that will have changed in 2008 (and it should work with other USB mass-storage devices like flash sticks). The drive must be locally mounted - SQL Server will not allow you to attach to a database on network storage.

Before unplugging the drive ensure that either the database is detached, or SQL Server is shut down (or the machine is fully powered off, of course). If you release the disk for removable by powering off the machine or shutting down SQL server, the drive needs to be plugged in before SQL server next starts.

As other people have pointed out, you will get lower performance in most case. Most USB drives top out at around 25Mb/sec even if the drive within the enclosure is capable of far more due to the limitations of USB2 controllers. That being said, if you have a lot happening on your internal drives (other DB access and such) you might actually find moving the database to a separate spindle, even one connected by a slower interface, could improve responsiveness (as your DB access is not competing for time on the same spindle with other active IO and so causing latency through extra head movements). This is assuming you are using a SATA/PATA spinning-disk in a USB enclosure. If you are using a bog-standard flash-based USB stick then performance will be much lower still, especially for writes - despite the lower latency of the solid-state storage, which will help to a degree, many bog-standard sticks won't read much faster than 10MByte/sec and write speeds below 4Mbyte/sec are far from uncommon.

On the security point: having the data on removable media is only more secure if your work area is fully secure (no one can get in/out without keys and codes, and you vet who you let in) and if when you are not present all the external drives are unplugged and stored in a suitable safe. Otherwise the removable drive is actually a fair degree less secure.

All the above assumes that you are talking about your development environment. This goes from "not particularly recommended" to "strongly recommended against" if you are talking about anything close to a live service. And for development you should not be using sensitive data anyway. You should have either manufactured test data, or failing that anonymised real data (all identifying information such as names, addreses and identifying codes sufficiently randomised, if your sensitive data is personal information).

share|improve this answer
1  
+1 - Finally, someone who has answered the question, rather than telling them that what they're doing is wrong. Sure, it might not be very good, but that's not the question they've asked! –  Mark Henderson Aug 2 '09 at 22:13
    
<quote>The drive must be locally mounted - SQL Server will not allow you to attach to a database on network storage.</quote> Then how can people use NAS or SAN drives as SQL storage? What happens if your DB exceeds 1TB when you don't run a NAS? As per my experience in SQL 2k8, you can't run a DB from anything except a Local Physical HDD connected directly to mainboard in Normal; but you may use UNC paths like \\Server-Name\Path-to-Database.mdf when you use a special DBCC TraceOn command before you attach/use the DB... –  Achilles Aug 3 '09 at 10:51
    
I perhaps over-simplified there. Yes, there are ways to make SQL server use datafiles on no-local volumes though discussion seemed beyond the scope of the question. As for databases over 1Tb: having more then 1Tb in one volume in a machine is far from unheard of, though I'm guessing that the questioner isn't talking about data even close to this order of magnitude. –  David Spillett Aug 3 '09 at 13:06
    
Thanks :) I'm the questioner and now I know the answer to my question... –  Achilles Aug 5 '09 at 10:40
add comment
up vote 6 down vote accepted

Well, now I know the answer to my question!

According to http://support.microsoft.com/kb/304261, you may use SAN/NAS storage to store your databases' files using a TraceON flag. Something like:

DBCC TraceOn(1807);
GO

this command flags the 1807 TranceOn to let you use a UNC like "\Server-name\Path-to-Database-File.mdf" as the path to database files. Now you need to Create a folder on your removable USB HDD and use "Sharing and Security" to grant Full Control over that folder to "Network Service" or whatever user your SQL uses to interact with your Windows. Remember to remove everyone and Add Administrators too.

Now you're done; create the database and have fun!

DBCC TraceOn (1807);
Go

Use master;
GO

CREATE DATABASE [test001] 
    ON  PRIMARY (
    	NAME = N'test001', 
    	FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001.mdf' , 
    	SIZE = 2048KB , 
    	FILEGROWTH = 1024KB
    	)
    LOG ON ( 
    	NAME = N'test001_log', 
    	FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001_log.ldf' , 
    	SIZE = 1024KB , 
    	FILEGROWTH = 10%
    )
GO
share|improve this answer
add comment

Is the "security problem" you've got that it isn't hard enough to physically steal the data? Because I'm having a great deal of trouble imagining how a removable drive is going to improve data security.

share|improve this answer
    
Having sensitive data in removable storages helps a lot if one does tax evasion for example :-) That's a hell of a "security problem". –  Anonymous Aug 2 '09 at 9:45
    
We pay our taxes, but we don't want our govt or anyone except some of our staff to know about our plans and stats. The "security problem" is having a thief govt! --Iran! –  Achilles Aug 3 '09 at 10:37
add comment

Any USB connected device is going to perform so poorly that I'm sure it would be quite unbearable. You would be better off using encryption, even an encrypted drive

share|improve this answer
    
The speed of USB is irrelevant to the question. I'd say, keep up with technology. I'm using an external SSD on USB 3.0 and it screams. –  Dave Van den Eynde May 16 '12 at 7:01
1  
@Dave, perhaps you need to read the date the answer was posted before making such an asinine comment. –  John Gardeniers May 17 '12 at 2:11
1  
Perhaps you should consider that on a StackExchange site, questions and answers don't "age", and that over time, the answers can change due to changing technology/practices and so on. Besides that, I don't think that the question isn't about performance. Performance is totally irrelevant here, wether it would be an external drive on USB or one of FW800 or some future superbus that exceeds everything we know today. The question was on how to make databases work on removable storage. Perhaps you should consider that before calling my comment "asinine". –  Dave Van den Eynde May 18 '12 at 17:52
add comment

eSATA is your friend - do not use USB, Firewire or any non-10Gbps NAS solution, an FC SAN would be just lovely but they're not exactly synonymous with removability - so I'd go for an eSATA-attached disk array, there's lots about and they're not terribly expensive.

share|improve this answer
add comment

USB 2.0 is painfully slow but this may not be an issue. Especially if your database usage isn't particularly write-heavy and you have plenty of RAM -- ideally, SQL Server should be able to fit the entire database into RAM, at which point your disk speed won't matter much once SQL Server gets most/all of the database pages buffered into memory.

If you need more performance from a removable device, try Firewire or eSATA. FW800 will give you close to 80MB/sec if your drive is capable of it. Even FW400 is about twice as fast as USB2 in the real world. eSATA is even faster and you're not likely to saturate that interface without RAID.

I'd stay way from flash drives. They have slow write speeds and can't be written to very many times as they don't always have wear-levelling controllers like more expensive SSDs.

Are removable 3.5" drive enclosures an option? It'd be nice if you could just use a normal internal hard drive mounted in a removable sled, as is common with most servers. If the drive absolutely has to be physically removed and locked up this is the best solution, besides simply having the server in a secure location in the first place.

share|improve this answer
add comment

This is one of those requirements that makes absolutely no sense and you're not going to find any recommendations for it because nobody in their right mind would attempt it. Hate to be so pessimistic and outright defiant of your requirements, but that's the way it is.

Having said all that, your best bet, if it fits in line with your boss's intensions, would be to look into an iSCSI system like the QNAP

share|improve this answer
add comment

There is no reason it won't work. Unless you start pulling out the drive while SQL Server is running (you'll need to manually detach the database to be able to remove safely).

Performance will depend, a lightly used database will likely suffer little. A heavily used database needs multiple fast spindles and will perform very badly on a single spindle—whether USB or SAS.

But how can a removable, and more portable, medium be more secure? You need to understand the underlying requirement to be able to advise your boss usefully (whether he/she is interested in useful advice is another matter).

share|improve this answer
add comment

I wouldn't use USB, but would use normal drives in HotSwap cages.
Since you would be using a single USB drive your way, you can just use a single drive in a that hotswap cage and you'll get the same security as a USB drive (from a failure point). Make a backup of your files to a second drive that can also be removed using the HotSwap cage and you've got something that will get you good preformance & the removeable security the boss demands. Also they don't show up in Windows as removeable storage. If you use a good controller you might even be able to get away with removing them while the system is running, as said before, you should detach the files from the DB. You could do that as part of a batch script.

Typically you can get these in drive cages that also have keys which would add to your security while it's running so that no one can walk off with it.

For accessing it from another computer, say if you've pulled it out for some reason and need to access it from another computer (say a laptop) then get one of the drive toaster or cables where you can just drop the drive into that and it will be USB readable on the other computer.

share|improve this answer
    
Using "HotPlug" drives with Encryption at Database level looks like a solution... Thanks :) –  Achilles Aug 3 '09 at 10:53
    
Since you want your staff to carry them and knowing the purpose, consider using tghe 2.5 inch drives which are smaller. Also if you do it in a hardware RAID 5, and give one drive to each person (3 total), the lose of one drive will not compromise your data to the other company, but still allow you to recover it from the other 2. It also give you RAID while running. Then no single person can compromise you, you need atleast 2 of them. –  SpaceManSpiff Aug 3 '09 at 12:10
    
I'm sorry SpaceManSpiff but that is such a bad idea for a variety of reasons. This whole thread is a recipe for disaster. –  Dan Mar 15 '10 at 15:15
add comment

Putting the data onto removable USB drives so that you can lock up the drives at night seams even less secure to me than running the database on a computer under a desk.

If the data is sitting on a USB Drive, then someone could walk by, unplug the drive slip it into there pocket and they are gone. If the data is stored on internal drives in the server they have to take the entire computer to get the data, which is much more obvious.

If the data on the SQL Server is really that important pick a small office (or storage closet as long as it has good AC) and mark this as the server room. Put the SQL Server and other machines running server processes into this room and lock the door. Make sure that the cleaning crew knows that there is nothing in there for them to clean, better yet take back there key to that room.

The only people who should have access to that room (who should have the key to the room) are the person or people who manage the servers. The manager of those people will probably want a key as well, but he doesn't actually need one. There should be a spare key in a sealed envelope in a locked drawer the HR managers office in case something happens to the IT staff.

As for network protection to prevent people from accessing the data over the network, that should be done via normal best practices (minimum permissions, firewalls, etc).

As for performance of SQL Server on a USB drive, the performance will be horrible as USB is very slow, as are thumb drives and external hard drives.

In other words, this shouldn't be done.

share|improve this answer
add comment

If you will use SQL Server database on removable media such USB HDD - your database performance will be limited by USB bandswitch. If your database usage limited by 1-2 users periodically - such solution can be usable somehow, but if more users with (or) continuous acccess - I think it will be very slow or even not usable...

I use Truecrypt(free)/Bestcrypt (commertial) software for encrypting media with SQL Server database files (on RAID, not removable HDD). Yes it's add some complexity in SQL Server start procedure (manual [re]start or sp___attach_db every time) but provides a good protection for data storage (sure not data itself while it can be accessible via SQL Server, but it's another story).

share|improve this answer
    
We use trueCrypt too but on our USB sticks; but my problem is I want to be able to remove my data storage physically AT WILL and any moment... The best solution to me is using a HotPlug HDD on a NAS... –  Achilles Aug 3 '09 at 11:01
add comment

@david-spillet sp_attachdb and sp_detach_db are going to be deprecated after SQL 2008. So you cannot use that command going forward

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.