September 1, 2011 at 7:29 pm
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
September 1, 2011 at 8:19 pm
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"
September 2, 2011 at 2:33 am
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
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" 😉
September 2, 2011 at 3:36 am
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
September 2, 2011 at 9:13 am
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?
September 2, 2011 at 9:59 am
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" 😉
September 6, 2011 at 9:00 am
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.
September 6, 2011 at 9:16 am
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" 😉
September 6, 2011 at 3:49 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply