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?
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
Moving as simple database to an external drive should be easy:
exec sp_detach_db '<db_name>'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).
An update for more modern hardware
Since the above was written USB3 has become far more ubiquitous which alters the performance aspect somewhat. A good 2.5" or mSATA SSD in an appropriate USB3 enclosure should actually perform quite nicely (not as well as an internal drive of course, and with a CPU impact, but still well). The other considerations remain the same though.
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.
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
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.
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.
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
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).
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.
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.
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).
@david-spillet sp_attachdb and sp_detach_db are going to be deprecated after SQL 2008. So you cannot use that command going forward
