Have only one root drive or multiple root drives for Implementing Mount Points

Question

Have only one root drive or multiple root drives for Implementing Mount Points

gary1

SSC-Insane

Points: 21152
More actions
September 1, 2011 at 7:29 pm

#244110

Hi,
We are implementing Mount Point in Windows Server 2008 r2 for SQL Server 2008 R2.
When we use Mount Points which one of the following method is recommended and why?
Method1:
Have only one root Drive and have all Mounts Points under that drive
Example: Have D drive has root and have all mount points under the drive D
Method2:
Have one root drive for Data files, one root drive for Log files , one root drive for Tempdb and one root drive for Backups and then have multiple Mount points for Primary data files, Secondary data files, Tempdb data & log files Under those root drives
Example: Have root drive D for Data and have mount points for multiple secondary files under root drive D
Have root drive E for Log and have mount points for multiple Log files under root drive E
Have root drive T for TempDB and have mount points for multiple TempDb data files & TempDB log files
under root drive T
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

I did ours with one drive letter for data files and one drive letter for log files and then have the mount points for data/log files for each db under their respective drive letters. I liked it because of the logical breakup that the drive letters provided. From a performance standpoint though I do not think it would matter because any way you model it the data is physically structured the same, so performance shouldn't suffer either way.

Joie Andrew
"Since 1982"

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

gmamata7 (9/1/2011)
Method1:
Have only one root Drive and have all Mounts Points under that drive

sort of, but i think you are confused with how mount points work. The whole point of mount points is to save on drive letter assignments but utilising mulitple windows disks

gmamata7 (9/1/2011)
Method2:
Have one root drive for Data files, one root drive for Log files , one root drive for Tempdb and one root drive for Backups and then have multiple Mount points for Primary data files, Secondary data files, Tempdb data & log files Under those root drives

Yes you have multiple drives but all mounted under a folder on your chosen existing Windows disk drive. Mount points are designed so that when you add a new disk via Windows disk management you mount the disk drive under an NTFS folder rather than using a drive letter.

Lets give an example to try and clear any confusion. For example, you have the following in disk management

disk0 C: 36GB system

disk1 D: 36GB apps

disk2 no partition tempdb

disk3 no partition SQLData

disk4 no partition SQLLogs

disk5 no partition SQLBaks

On your D: drive you create the following empty folders

D:\SQLSys

D:\SQLData

D:\SQLLogs

D:\SQLBaks

when you set disks 2 - 5 online and initialise them you then create partitions. When creating the partitions you are offered the following options

Assign a drive letter

mount in the following empty NTFS folder

do not assign letter or path

So, for disk 2 you would select 'mount in the following empty NTFS folder' and select the path D:\SQLSys

For disk 3 you would select 'mount in the following empty NTFS folder' and select the path D:\SQLData

For disk 4 you would select 'mount in the following empty NTFS folder' and select the path D:\SQLLogs

etc

I hope this clears up mount points for you, shout back if you need further info

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Satya(SQLMaster) SSC Eights! Points: 872 More actions · Answer 3

I prefer Method 2 and as referred above the performance is big aspect here, depending upon the application type how the data is extracted and how frequently you should keep up the statistics for better optimization.

-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network

gary1 SSC-Insane Points: 21152 More actions · Answer 4

Thanks for your inputs...

1. Can we rename the Mount Points after installing SQL Server?

2. How to get the Free space, used space for Mount Points?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

gmamata7 (9/2/2011)
1. Can we rename the Mount Points after installing SQL Server?

already commented on this 😉

gmamata7 (9/2/2011)
2. How to get the Free space, used space for Mount Points?

go into explorer and edit display columns and add "space free" and "space used"

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Oliiii SSCertifiable Points: 5328 More actions · Answer 6

Satya(SQLMaster) (9/2/2011)
I prefer Method 2 and as referred above the performance is big aspect here, depending upon the application type how the data is extracted and how frequently you should keep up the statistics for better optimization.

Are you sure about that?

Having different mountpoint mounted on different folder on different disks is better than having different mountpoint mounted on different folder on the same disk :doze: ?

I would think it doesn't matter at all since SQL probably access the mountpoint directly without ever accessing the disk it's being mounted on.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Method2:
Have one root drive for Data files, one root drive for Log files , one root drive for Tempdb and one root drive for Backups and then have multiple Mount points for Primary data files, Secondary data files, Tempdb data & log files Under those root drives
Example: Have root drive D for Data and have mount points for multiple secondary files under root drive D
Have root drive E for Log and have mount points for multiple Log files under root drive E
Have root drive T for TempDB and have mount points for multiple TempDb data files & TempDB log files
under root drive T

Oliiii (9/6/2011)
Satya(SQLMaster) (9/2/2011)
I prefer Method 2 and as referred above the performance is big aspect here, depending upon the application type how the data is extracted and how frequently you should keep up the statistics for better optimization.
Are you sure about that?
Having different mountpoint mounted on different folder on different disks is better than having different mountpoint mounted on different folder on the same disk :doze: ?
I would think it doesn't matter at all since SQL probably access the mountpoint directly without ever accessing the disk it's being mounted on.

Oliii you're right, You wouldn't use method 2 as the whole idea on mount points is to save drive letters and method 2 does not achieve this!

You have one root drive and then disks mounted under folders on the root drive. All the I\O is directed to the mounted disk\volume not the root drive. The mount points underneath are actually separate disk devices, the mount points all use the same drive letter but it's just a passthrough.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 8

gmamata7 (9/2/2011)
Thanks for your inputs...
2. How to get the Free space, used space for Mount Points?

I saw this script using Powershell

http://www.mssqltips.com/tip.asp?tip=2444

I haven't had the chance to use it, but it seems nifty.