Home / server / Questions / 86079
In Process
Ra Osolage
Asked: 2009-11-19 14:04:52 +0800 CST

SQL Server and mount points

  • 772

Does anybody know of any good information out there about mount points and SQL Server? I have like 80 databases to create on one SQL instance and what I'm trying to figure out is whether we'd benefit from having a mount point for each database. Or should I just do one mount point for data files, one for transaction logs and one for tempdb? Of course, all of this is backed by high-end SAN...

Thanks for any advice or links you can offer!

sql-server

5 Answers

  1. A mount point for each database is a complex way to handle it. I prefer the simple approach with volumes for

    • Data (RAID5)
    • Logs (RAID1+0)
    • TempDB and Backups (RAID1+0)

    This makes it easier to administer as the SQL DBA has a clear indication where the various files should go. they don't have to keep second guessing the disk design.

    If all of the volumes will be backed by the same disks on the SAN it probably won't make much difference whether you use separate mount points, volumes or folders to store the files in - the disks will be shared.

    I also find the ATTO Disk Benchmark tool great for getting a quick idea how a disk array performs.

    • 2

  2. You mean present a separate virtual LUN with for each data & tlog? for each of the 80 DB's? Thats 160 targets? this isn't the way to go. The admin overhead would be a nightmare, not to mention running out of drive letters in windows!

    How many real disks do you have to play with on your SAN? Present these to your SQL Server box. There are many documents out there on the convention.

    EDIT: Mount points are fine, i got confused with NTFS junction points

    If you are using mount points with clustering, you should read this KB.

    • 2

  3. One of our server admins proposed this but then I found some documentation that it didn't help with disk queuing in 2003 on a cluster.

    The best way for you to evaluate this might be to run some benchmarking tools on both configurations and choose the better performing congig. Check these tools out.

    http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en

    http://support.microsoft.com/kb/231619

    Whitepaper on what to do before deploying IO:

    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

    • 1

  4. So far the only reasons for mount points that I have encountered are if you are out of drive letters, or you need to give more space in a new directory to an existing drive letter.

    • 1

  5. Do you really need to isolate all of these databases? Could you use a more standard layout:

    • C: OS
    • D: SQL System files and binaries
    • E: TempDB(s)
    • F: Data Files
    • G: Log Files
    • H: Backups

    The F and the G luns holding all 80 database data / log files.

    The D and E LUNs hold the system files and tempdbs for each instance.

    Each LUN should have an appropriate amount of underlying disks to cope with required IO. Use a RAID 10 configuration for the TempDB and Log Files. If your databases are write heavy use RAID 10 for data files as well if you can afford it.

    Ensure you have sufficient memory in your server(s) to handle the required reads.

    Remember to format the LUNs with an appropriate partition offset. Consider using multiple HBA cards and active / active multipathing software. Skew your SAN cache towards writes.

    • 0