SQL Server database on an external hard disk drive

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:

detach the database with exec sp_detach_db '<db_name>'
copy the files to the new location on the external drive
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).

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.

SQL Server database on an external hard disk drive

Related

Recent Posts