November 10, 2011 at 12:01 am
Comments posted to this topic are about the item Using and Creating Mount Points in SQL Server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2011 at 1:57 am
Hi Perry,
Very nice to see your article. I will definetly try this out. Thank you for sharing your knowledge on these topics.
M&M
November 10, 2011 at 6:18 am
Thanks for an informative article Perry, but I'm left wondering what the benefit is to creating/using mount points is. For containing SQL system files, they seem like just another way to create a virtual disk/shortcut to space on an NTFS volume. Or am I missing something?
Rich
November 10, 2011 at 7:05 am
rmechaber (11/10/2011)
They may be used for system files, database files, log files, tempdb files and backup files. As the article explains, you only have 26 drive letters total available to the operating system. Mounts points allow you to have multiple instances with multiple disk resources without the drive letter constraints.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2011 at 9:25 am
So, the one limitation, that I still see with mount points, is still doing any kind of logical detection of free-space.
Say I have a stub drive (drive letter) that's 500mb
xp_fixeddrives will ONLY see the stub drive freespace; it doesn't matter if I have mount points of 2 TB of space underneath it.
some vendor apps fail to install, saying there's not enough free space to complete installation based on this.
the only way I've figured out around this is to drop down into powershell and gwmi win32_volume|where-object {$_.filesystem -match "ntfs"}|ft name,capacity,freespace
Anyone know of a way (or i'd like sql to have something built-in) to figure this out within TSQL?
November 10, 2011 at 9:28 am
I have setup both our SQL test-environment and production environment, both are two node clusters.
I installed SQL Server first, and then mereley replaced the default installations folders with mount-point-folders via rename, xcopy /o <renamed-folder> <new-folder-with-default-name>
OS: WS 2008 R2 Enterprise
SQL 2008 R2 Standard
Some things I learned doing this are:
-there is a bug in Windows when setting permissions on the mounted folder - one has to do that first from disk management (right-click partition and set permissions). I had quite a lot of error messages doing that and it was impossible to just close the properties dialog by clicking Ok, I had to use Cancel. But afterwards the permissions are right if one checks.
-The amount of free space has to be checked for each "mount-point-folder" since the amount of free space in the root folder only show how much it is free of the root disk.
-When done and documented: Very nice to have one folder structure per sql instance and still have different write cache policies for data-folder, log-folder and temp-db-folder on each LUN that is used for mount point folder in the underlying storage system! 🙂
November 10, 2011 at 11:33 am
Perry,
I usually monitor Avg. Disk sec/Read and Avg. Disk sec/Write to check disk activity, without mount points adding this counters in perfmon is not an issue. But with mount points, I just see the letter of mount point and not underlying disks in the instance list of above counters. I tried to find the id's of the disks under mount points, but not able to relate those id's with actual disks.
Any ideas?
Thanks!
November 10, 2011 at 11:39 am
This may be a dumb question from a total mount-point novice...
The dialog boxes showed a max disk size of a smidge over 7GB. Where does that come from? Is it a limitation? What...? (As in, please elucidate 'cos I don't know enough to ask the "right" question...:-))
Thanks! Helpful article indeed on a topic I'd never heard of but had wondered for years how to get around the 23-user disk limitation.
November 10, 2011 at 12:16 pm
SAinCA (11/10/2011)
The dialog boxes showed a max disk size of a smidge over 7GB. Where does that come from? Is it a limitation? What...? (As in, please elucidate 'cos I don't know enough to ask the "right" question...:-))
Are you referring to the volume size of 7165MB shown in the wizard images? That is slightly under 7GB. The LUN sizes I used can be seen in the first image showing the Windows disk management console, there were a combination of 6GB and 7GB LUNs.
Remember the formatted size will always be less than the initial disk size presented!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2011 at 12:21 pm
k-335975 (11/10/2011)
Perry,I usually monitor Avg. Disk sec/Read and Avg. Disk sec/Write to check disk activity, without mount points adding this counters in perfmon is not an issue. But with mount points, I just see the letter of mount point and not underlying disks in the instance list of above counters. I tried to find the id's of the disks under mount points, but not able to relate those id's with actual disks.
Any ideas?
Thanks!
I do not have this information to hand at present. I will endeavour to provide this when I am back in blighty 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2011 at 12:26 pm
Thanks. Did a quick "truncate the zeros" to come up with 7GB, not using exact science...
Anyway, it's obvious I'm no Windows Disk Management proficianado - didn't see the 7GB in the background of the Unallocated Partition snapshot. It's clear to me, now, that pre-allocation of partitions occurs prior to mount-point definition and I'll be sure to make mine around 250GB to 500GB for the tables and partitions I have to deal with.
Cheers. Appreciate the detail you went to in the article.
November 10, 2011 at 12:58 pm
SAinCA (11/10/2011)
I'll be sure to make mine around 250GB to 500GB for the tables and partitions I have to deal with.Cheers. Appreciate the detail you went to in the article.
Ok, just be sure to select the correct disk type from the start. For large disks GPT is more robust than MBR.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 10, 2011 at 2:54 pm
SAinCA (11/10/2011)
I'm no Windows Disk Management proficianado -
That's great, I'm gonna add that word to my list! 😀
Rich
November 10, 2011 at 10:25 pm
Maybe it's better to check thoose numbers at the storage system level instead? In the storage system where I work there's also easy to see for example write latency, good way to get a rough idea about the load.
November 16, 2011 at 3:57 am
Hi Perry
Note : I hope this won't get confusing as my name is also Perry!
Take this scenario...
2 node active/passive cluster - SQL 2008 R2 on Windows 2008 R2
All data, log, backup drives hosted on SAN as drives F: , G: , H: respectively
Each node has 2 x SSD in RAID 1 configured as T:
Have you tried making a mount point on F: targeting the LOCAL SSD drive T: ?
We are looking at this technique to get tempdb and ASPState hosted in local SSD in a clustered solution. As tempdb and ASPState are db's that don't require persistence across a failover it appears to work as does not prevent the cluster from working or having SQL service packs / cumulative updates applied.
I am getting feedback on other forums that this is unsupported configuration by MS but http://support.microsoft.com/kb/819546 seems to state that mount points ARE supported on these versions. I cannot make out any statement that says that local drives cannot be the targets of the mount points.
Any opinions, good or bad, would be appeciated.
Regards
Perry D.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply