Will storing a SQL Server 2005 MDF (Database File) on USB External Device Slow Performance

Posted on 2008-11-17
Last Modified: 2016-06-26
I have a 500 GB Database File (MDF) & (LDF) SQL Server 2005 sitting on an External Device Drive. Western Digital. I've attached the database back to the SQL Server Management Studio; but the MDF and LDF reside on the External Device.

The data is only being used for archiving purposes. End Users will be viewing the data only

1. Will this slow down performance?
2. Good Idea?
3. Is it good to have the end user making changes on this drive? Updates and Adds ( or be too slow)

Any information that can be useful will help.

Question by:jseaman12
    LVL 18

    Accepted Solution

    My opinion is that this is going to be slow and probably not a very good idea. Hard drives are cheap
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    I take as premise that the data is really only read!

    >1. Will this slow down performance?
    once the db data is read, it will be in memory, so only the first users will have to "wait" for the reads of their records.
    so, overall, if the disk is somewhat quick, it will not be a problem

    >2. Good Idea?
    it is as good as the backups you perform, and a failover place in case the disk crashes.

    >3. Is it good to have the end user making changes on this drive? Updates and Adds ( or be too slow)
    it depends only on the disk write speed, and the fault tolerance you can grant...

    LVL 51

    Assisted Solution

    by:Mark Wills
    1) 500GB will be slow via USB port - if you can make use of caching, might not be disasterously slow, but will definitely be slow.

    2) Do not think it is a good idea unless there is no other alternative.

    3) No, not a good thing for users to add /change on that devise.

    the other problem you have is how cached writes behave via a usb device - you have to properly disconnet that device to make sure any caching has been flushed, and failure to do so, can corrupt the database.

    All in all, USB attached devices are really good to transport data, or backup data. Not good as an "online" data repository, and certainly not for updates. The speed will not be good as soon as it fills cache quotas, and have multiple people accessing it.

    Try getting one of those ginormous SATA drives and install in the server itself. If you have NO expansion capacity for another disk drive, then you will need to look for bigger drives and upgrades.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Let’s face it, any data that’s not backed up frequently is in danger. And when data loss happens to you, it can seem like the end of the world.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now