SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Perry Whittle / Using and Creating Mount Points in SQL Server / Latest Posts

RE: Using and Creating Mount Points in SQL Server

kan kuang — Fri, 04 Jan 2013 14:35:07 GMT

When mount points are being used together with the physical disks, each mount point must appear as a cluster resource. Additionally, each mount point must also be added as a SQL Server dependency. If only the root physical disks dependency is added and the mount points are not added, database corruption will occur on failover. Database corruption may also occur when SQL Server is restarted without failing over (http://support.microsoft.com/kb/819546).

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 09 Feb 2012 08:11:39 GMT

[quote][b]kellithompson (2/7/2012)[/b][hr]Nevermind. I finally got it working. Because of the failover cluster, I want to use high safety w/o auto failover (synchronous), right?[/quote]Offhand i'm not entirely certain, what makes you think that? You would at least need to ensure that you adjust the mirroring timeout to take account of the cluster failover time. The default mirror timeout is 10 secs and i'm guessing your cluster failover takes longer than that.

RE: Using and Creating Mount Points in SQL Server

okbangas — Tue, 07 Feb 2012 16:04:55 GMT

If I don't recall something wrong: are the two instances running under the same domain account? Have you verified that this account has sufficient permissions?

RE: Using and Creating Mount Points in SQL Server

kellithompson — Tue, 07 Feb 2012 14:32:52 GMT

[quote][b]kellithompson (2/7/2012)[/b][hr][quote][b]Perry Whittle (1/20/2012)[/b][hr][quote][b]kellithompson (1/20/2012)[/b][hr]Yes it is. It's off-site at another location, but it's the same domain.[/quote]That makes things a whole lot easier. Just set mirroring up as defined in BOL. I have a script which you can use if you want it.[/quote]Thanks Perry, I would like a script. I just wanted to test if I could get a mirror to work between my clustered instance and an offsite instance. I followed the BOL in creating a new database on my primary. Created a table and added some rows. Backed up the DB and the log fille using the proper method. Restored both w/ NoRecovery. Made sure I could telnet to both primary and mirror server on my designated port. I cannot get past the famous 1418 error w/ severity 16, state 1, line 1. I have removed all the configurations and started over 3 times and I must be missing something simple because each time I get the same error. Let me know if you have any advice.Thanks in advance. KT[/quote]Nevermind. I finally got it working. Because of the failover cluster, I want to use high safety w/o auto failover (synchronous), right?

RE: Using and Creating Mount Points in SQL Server

kellithompson — Tue, 07 Feb 2012 13:51:29 GMT

[quote][b]Perry Whittle (1/20/2012)[/b][hr][quote][b]kellithompson (1/20/2012)[/b][hr]Yes it is. It's off-site at another location, but it's the same domain.[/quote]That makes things a whole lot easier. Just set mirroring up as defined in BOL. I have a script which you can use if you want it.[/quote]Thanks Perry, I would like a script. I just wanted to test if I could get a mirror to work between my clustered instance and an offsite instance. I followed the BOL in creating a new database on my primary. Created a table and added some rows. Backed up the DB and the log fille using the proper method. Restored both w/ NoRecovery. Made sure I could telnet to both primary and mirror server on my designated port. I cannot get past the famous 1418 error w/ severity 16, state 1, line 1. I have removed all the configurations and started over 3 times and I must be missing something simple because each time I get the same error. Let me know if you have any advice.Thanks in advance. KT

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Fri, 20 Jan 2012 12:17:07 GMT

[quote][b]kellithompson (1/20/2012)[/b][hr]Yes it is. It's off-site at another location, but it's the same domain.[/quote]That makes things a whole lot easier. Just set mirroring up as defined in BOL. I have a script which you can use if you want it.[quote][b]kellithompson (1/20/2012)[/b][hr]Can I add another node and install SQL 2008 R2 Std? I want to be able to fail the existing instances on ent over to std. Can my cluster be mixed in that way? I know there are some technical differences between ENT and STD like partitioning, but we're not utilizing that.[/quote]You know what never even tried it and can't think why you would want to. I'm sure it would fail and cause issues somewhere along the line so don't ;-)

RE: Using and Creating Mount Points in SQL Server

kellithompson — Fri, 20 Jan 2012 07:42:24 GMT

Yes it is. It's off-site at another location, but it's the same domain. I think I know how to do it, I will give it a whirl today and post back if I have a problem. I do have another question. Let's say my current failover cluster is on SQL 2008 R2 Ent. Can I add another node and install SQL 2008 R2 Std? I want to be able to fail the existing instances on ent over to std. Can my cluster be mixed in that way? I know there are some technical differences between ENT and STD like partitioning, but we're not utilizing that.

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Wed, 18 Jan 2012 16:17:49 GMT

Is the stand alone server part of the same domain as the cluster or is it in a workgroup.

RE: Using and Creating Mount Points in SQL Server

kellithompson — Wed, 18 Jan 2012 10:39:13 GMT

Perry,This is a little off topic, but I was wondering if you or someone else on this forum have a step-by-step article on how to add high-performance mirroring on a stand-alone server with a failover cluster as the primary?

RE: Using and Creating Mount Points in SQL Server

okbangas — Wed, 21 Dec 2011 05:57:25 GMT

I do definitely not want to be petty, I do however think that the 2TB partition limit is an important knowledge regarding GPT vs MBR, in most cases actually more imporant than then number of partitions you can create, which you do mention. But, if this is the kind of response which I can expect if I try to point out something which could have been better, then I'll better keep it for myself.That said, I do realize that I should have read the article more thoroughly, but I do still think that the 2TB limit should have been mentioned.

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Wed, 21 Dec 2011 02:02:03 GMT

[quote][b]okbangas (12/21/2011)[/b][hr]A little piece of missing information: You describe some of the differences between MBR and GPT, but in my opinion you lack the most important one: MBR can only host partitions up to 2TB. Obviously, if you are trying to create a partition bigger than this, you need to have a GPT disk. More importantly though, if you think your partition can possibly be resized to more than 2TB in the future, you should go for GPT to allow for this.[/quote]<sigh> please read the article. I make a recommendation that if your disk will be under 500GB and have 1 partition use MBR otherwise consider GPT. It's not meant to be a comprehensive guide on disk types that is why I include the link to that KB. I do however cover the important aspects regarding partition constraints. You're just being petty, good day ;-)

RE: Using and Creating Mount Points in SQL Server

okbangas — Wed, 21 Dec 2011 00:44:06 GMT

A little piece of missing information: You describe some of the differences between MBR and GPT, but in my opinion you lack the most important one: MBR can only host partitions up to 2TB. Obviously, if you are trying to create a partition bigger than this, you need to have a GPT disk. More importantly though, if you think your partition can possibly be resized to more than 2TB in the future, you should go for GPT to allow for this.

RE: Using and Creating Mount Points in SQL Server

kellithompson — Fri, 09 Dec 2011 10:56:06 GMT

Got it. Thanks...

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Fri, 09 Dec 2011 10:46:27 GMT

Hi sorry, my bad totally missed the cluster part. Yes for a new clustered instance you will need to add another root drive and letter to the new application group

RE: Using and Creating Mount Points in SQL Server

kellithompson — Fri, 09 Dec 2011 10:03:11 GMT

[quote][b]Perry Whittle (12/9/2011)[/b][hr][quote][b]kellithompson (12/9/2011)[/b][hr]My question is when I am ready to install a new instance (let's call it WebDev), can I use the existing WebProd drive letter and mount some additional volumes or do I have to have a dedicated empty application for each instance I install, thus another drive letter and mount the new volumes under it?[/quote]You can use the same root drive for all of them, however, for management purposes some would choose to create another root drive and letter. It's entirely up to you ;-)[/quote]Then I must be doing something wrong. I created 2 new luns, assigned them to WebProd Storage, created the dependencies for the root drive. When I get to the Cluster Resource Group screen in the SQL installation, the WebProd Storage is not available for me to select as a resource.

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Fri, 09 Dec 2011 08:20:13 GMT

[quote][b]kellithompson (12/9/2011)[/b][hr]My question is when I am ready to install a new instance (let's call it WebDev), can I use the existing WebProd drive letter and mount some additional volumes or do I have to have a dedicated empty application for each instance I install, thus another drive letter and mount the new volumes under it?[/quote]You can use the same root drive for all of them, however, for management purposes some would choose to create another root drive and letter. It's entirely up to you ;-)

RE: Using and Creating Mount Points in SQL Server

kellithompson — Fri, 09 Dec 2011 07:45:27 GMT

Great article Perry. I have a question about adding a new instance to a SQL failover cluster. I created a 2-node test environment using Srv 2008 R2, Windows Clustering and SQL 2008 R2 Ent. I followed your article closely and I was able to create the empty application (let's call it WebProd Storage), add the storage and dependencies, create mount points under one drive letter for the application. My question is when I am ready to install a new instance (let's call it WebDev), can I use the existing WebProd drive letter and mount some additional volumes or do I have to have a dedicated empty application for each instance I install, thus another drive letter and mount the new volumes under it?

RE: Using and Creating Mount Points in SQL Server

kwe477 — Tue, 29 Nov 2011 09:49:10 GMT

I currently have an issue with Multiple Mount Points and SCOM monitoring our SQL Servers.This is how our configuration looks like Possible values for VolumeName along with current mount points are: \\?\Volume{196adec4-2a1c-11e0-96b6-806e6f6e6963}\ C:\ \\?\Volume{cd7bb9b7-2dd0-11e0-9724-d485645a492c}\ E:\ \\?\Volume{cd7bb9c5-2dd0-11e0-9724-d485645a492c}\ E:\OLAPDATA\DATA1\ E:\SQLDATA\DATA1\ \\?\Volume{cd7bb9cc-2dd0-11e0-9724-d485645a492c}\ E:\OLAPDATA\DATA2\ E:\SQLDATA\DATA2\ \\?\Volume{cd7bb9d3-2dd0-11e0-9724-d485645a492c}\ E:\OLAPDATA\DATA3\ E:\SQLDATA\DATA3\ \\?\Volume{cd7bb9be-2dd0-11e0-9724-d485645a492c}\ E:\OLAPDATA\DATA4\ E:\SQLDATA\DATA4\ \\?\Volume{85ef6b9b-9add-4e88-bbfb-c970696c866f}\ E:\BDATA\ \\?\Volume{196adec5-2a1c-11e0-96b6-806e6f6e6963}\ D:\ Because multiple MP point to the same Volume, SCOM seems to have an issue. Can anyone help here?

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Tue, 22 Nov 2011 09:25:06 GMT

[quote][b]k-335975 (11/10/2011)[/b][hr]I just see the letter of mount point and not underlying disks in the instance list of above counters[/quote]you're looking for volumes not disks. They are not actual disks within Windows, they are formatted volumes[quote][b]CDA (11/10/2011)[/b][hr]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 500mbxp_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 [code="other"]gwmi win32_volume|where-object {$_.filesystem -match "ntfs"}|ft name,capacity,freespace[/code]Anyone know of a way (or i'd like sql to have something built-in) to figure this out within TSQL?[/quote]you can either use[code]wmic volume get name, capacity, "free space"[/code]or view through explorer but you'll need to add the extra columns in the explorer window first

RE: Using and Creating Mount Points in SQL Server

Perry Dyball — Wed, 16 Nov 2011 03:57:59 GMT

Hi PerryNote : 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 R2All data, log, backup drives hosted on SAN as drives F: , G: , H: respectivelyEach 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.RegardsPerry D.

RE: Using and Creating Mount Points in SQL Server

L-P Larsson — Thu, 10 Nov 2011 22:25:41 GMT

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.

RE: Using and Creating Mount Points in SQL Server

rmechaber — Thu, 10 Nov 2011 14:54:26 GMT

[quote][b]SAinCA (11/10/2011)[/b][hr]I'm no Windows Disk Management proficianado - [/quote]That's great, I'm gonna add that word to my list! :-DRich

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 10 Nov 2011 12:58:13 GMT

[quote][b]SAinCA (11/10/2011)[/b][hr]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.[/quote]Ok, just be sure to select the correct disk type from the start. For large disks GPT is more robust than MBR.

RE: Using and Creating Mount Points in SQL Server

SAinCA — Thu, 10 Nov 2011 12:26:34 GMT

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.

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 10 Nov 2011 12:21:14 GMT

[quote][b]k-335975 (11/10/2011)[/b][hr]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![/quote]I do not have this information to hand at present. I will endeavour to provide this when I am back in blighty ;-)

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 10 Nov 2011 12:16:56 GMT

[quote][b]SAinCA (11/10/2011)[/b][hr]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...:-))[/quote]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!

RE: Using and Creating Mount Points in SQL Server

SAinCA — Thu, 10 Nov 2011 11:39:02 GMT

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.

RE: Using and Creating Mount Points in SQL Server

k-335975 — Thu, 10 Nov 2011 11:33:20 GMT

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!

RE: Using and Creating Mount Points in SQL Server

L-P Larsson — Thu, 10 Nov 2011 09:28:53 GMT

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 EnterpriseSQL 2008 R2 StandardSome 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! :)

RE: Using and Creating Mount Points in SQL Server

CDA — Thu, 10 Nov 2011 09:25:17 GMT

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 500mbxp_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 [code="other"]gwmi win32_volume|where-object {$_.filesystem -match "ntfs"}|ft name,capacity,freespace[/code]Anyone know of a way (or i'd like sql to have something built-in) to figure this out within TSQL?

RE: Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 10 Nov 2011 07:05:39 GMT

[quote][b]rmechaber (11/10/2011)[/b][hr]For containing SQL system files, they seem like just another way to create a virtual disk/shortcut to space on an NTFS volume.[/Quote]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.

RE: Using and Creating Mount Points in SQL Server

rmechaber — Thu, 10 Nov 2011 06:18:27 GMT

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

RE: Using and Creating Mount Points in SQL Server

mohammed moinudheen — Thu, 10 Nov 2011 01:57:07 GMT

Hi Perry,Very nice to see your article. I will definetly try this out. Thank you for sharing your knowledge on these topics.

Using and Creating Mount Points in SQL Server

Perry Whittle — Thu, 10 Nov 2011 00:01:31 GMT

Comments posted to this topic are about the item [B]Using and Creating Mount Points in SQL Server[/B]