up vote 2 down vote favorite
1
share [g+] share [fb]

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!

link|improve this question
25% accept rate
What's your OS? – Sam Nov 19 '09 at 21:32
Windows Server 2003 Enterprise Edition. So far, it's a 2-node cluster. Will probably add a 3rd node to serve up the 80 databases. – Ra Osolage Nov 20 '09 at 14:52
feedback

5 Answers

up vote 2 down vote

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.

link|improve this answer
Yep, setting it up as you mentioned is our team standard for each instance we put on the floor. We don't do the RAID configurations, however. Lately, our SAN administrators have been giving us one LUN and the Windows administrators break them up into the 3 mount points you listed above. But I've been told that the SAN controllers are backed by lots of memory, so the write I/O is almost instantateous as far as the operating system is concerned. Read I/O is governed by read-ahead cache on the SAN side, too. My concern, though is that we should be presenting more physical disks to the OS. – Ra Osolage Nov 20 '09 at 14:51
feedback
up vote 2 down vote

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.

link|improve this answer
Yes, I agree that 2 LUNs per db would be a headache (even though I don't have to manage it LOL). I have no idea how many disks are available. We have a very large SAN environment encompassing a number of disk racks/controllers. It's all managed by a team dedicated to SAN administration. If I had a good reason, though, I think they'd at least consider any requests I would make concerning LUNs. As far as mount points go, I haven't had any problems with Service Packs. I've been applying SPs to instances on mount points all year. – Ra Osolage Nov 20 '09 at 16:27
feedback
up vote 1 down vote

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.

link|improve this answer
Actually, our team has recently standardized on one drive letter for all of our SQL Server instances. We add a new LUN for each instance and break each LUN up for user databases, transaction logs, and tempdb. This way, we're not limited by the number of drive letters available. And in our clustered environments, we do one drive letter per cluster resource group. And then mount points on that drive letter for each instance in that cluster resource group. So far, we haven't installed more than one instance to a cluster resource group... – Ra Osolage Nov 20 '09 at 14:56
feedback
up vote 1 down vote

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

link|improve this answer
those tools measure performance of disk configurations that have been built. i suppose what i need is a tool to help me plan how to prepare the LUNs and mount points. – Ra Osolage Nov 20 '09 at 16:30
Well you have two scenarios right? Have them built and do the testing and the testing tools will tell you the better approach. – Sam Nov 23 '09 at 15:05
feedback
up vote 0 down vote

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.

link|improve this answer
What up, SuperCoolMoss? Yeah, we do something similar. Instead of the separate drive letters, we do mount points. However, we have to use separate drive letters in each cluster resource group. SAN team handles LUNs. Windows team manages mount points. Best I can do is to ask for a specific amount of LUNs and how they are concatenated into mount points. Prob starting off with 500 GB of space. So I was thinking of doing 7 LUNs and hence 7 mount points. Each db would have 1 data file per mount point. (2 quad-proc dual-core cluster nodes...) Does that sound crazy? – Ra Osolage Nov 24 '09 at 18:16
feedback

Your Answer

 
or
required, but never shown
discard

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