July 1, 2008 at 1:20 am
Hi all,
I am currently considering using mount points with an SQL Server 2005 cluster installation. My incentive for doing this, is that we (The SQL Server team in my company) are using three drive letters per instance; one for data, one for log and one for backup. This consumes a lot of drive letters and effectively limits the possible number of instances on a cluster to about six.
To some extent I think mount points seems like a good idea to implement, offering great scalability for the cluster, but I have never made use of the technology, so your thoughts on the subject would be greatly appreciated.
It is worth mentioning that we are using a SAN to store all SQL Server data. We are offered no guarantee that the LUNs are placed on seperate physical disks, which would be the case for mount points as well. Both mount points and LUNs can be moved though if the storage guys detects a hotspot.
Knowing what you know, what do you think the advantages and disadvantages of using mount points with SQL Server in our environment are? Let alone SQL Server clustering?
Will the performance decrease? Will there be a single point of failure with three mount points as opposed to now, where we have three LUNs?
Thanks in advance.
Cheers, Lars Mikkelsen.
July 2, 2008 at 2:44 pm
Hey Ism,
There is no "performance" penalty for using mount points and you should definitely still use three separate LUN's for each mount point. There is however a flexibility issue in that you cannot have dynamic disks on a Windows cluster with the mount points (nor is it supported with drive letters either in a clustered environment either ;)).
The only other thing to make sure that you configure when you set up the mount points is that you have a drive letter for each instance, and that drive letter needs to be part of the clustered resource dependency tree. For example, if you have a root disk called D: and mount points called D:\SQLData, D:\SQLLogs, and D:\SQLTEMPDB; the mount points need to depend on the D: drive (the root disk). Otherwise, whenever you have a resource failure, those mount points won't start correctly and you won't see you SQL Server data as the Cluster service will not know how to manage them.
Oh yeah, if you are still running SP1 of Windows Server 2003, make sure you grab this hotfix (or just go to SP2 😀 http://support.microsoft.com/?id=898790).
Good luck.
July 3, 2008 at 2:36 am
We had no problems on a cluster with using mount points but we found monitoring disk space was a problem. We have a drive letter for a small disk (2Gb) on a SAN, this drive has folders in it that are mount points to other disks (10 or so) on the SAN. Some of our monitoring tools couldn't cope with the mount points at all, they
only reported the size/free space of the 2Gb drive, not the size/free space of the mount points underneath. We had to write scripts to monitor the free space of the mount points.
August 28, 2008 at 10:09 am
Are you able to post the monitoring scripts?
Thanks.
August 29, 2008 at 2:33 pm
If you are feeling adventurous and want to turn on CLR within your SQL instance, the procedure at the sqlteam weblogs is very nice and will give you information about mount points. Well, with a few minor changes that have been documented in the comments.
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
September 2, 2008 at 2:48 pm
thanks
October 7, 2008 at 8:52 am
We're considering using mount points with our SQL clusters as well, but with a twist. We'd like to be able to add mount points on a per-database basis, creating new mount points whenever we need to create a new database (which we do fairly often). The problem that arises is that you run the risk of the database not being available if the SQL resource comes online before the mount point.
When you add a mount point you can create the physical disk resource, specifying a dependency on the existing disk resource that contains the mount point directory. You can then create databases on the mount point since the SQL resource is already dependent on the disk that contains the mount point folder; however, if a failover occurs, the SQL resource can come online as soon as the disk resource comes online, even if the mount point resource is still offline. If that happens, any databases with files on the mount point will be unavailable.
In order to avoid this problem, the SQL Server resource must be dependent on the mount point resource directly, but to define the dependency you have to bring the SQL resource offline. We create several new databases per week, and we can't take downtime for every new database, so we're trying to find a workaround to the problem.
One thing we're considering is using the Generic Script resource to add dependencies for any new mount points before bringing the SQL resource online after a failover. I've read some of the documentation for the Generic Script resource, but it seems a bit cryptic. If anyone has experience using this resource and can provide examples of how to put it to use, it would be much appreciated. I'm also open to any alternative ideas anyone else may have on how to make this work.
Thanks!
~ Ron
October 9, 2008 at 3:42 pm
Thanks D: for the link.
Even I had a similar situation in our production environment we were using mount points and the disk monitoring was not showing the actual space used:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
I created the DLL and CLR stored proc it works very well for me.
Thanks,
Razi, M.
http://questivity.com/it-training.html
November 24, 2008 at 3:47 pm
More important mount point information
http://support.microsoft.com/kb/819546
SQL Server 2005 failover clustered instances fully support mounted drives if the mounted drive is hosted by a cluster drive with a drive letter assigned.
Note Because of the number of available drive letters, the number of the virtual instances on a cluster is limited to 25. SQL Server 2005 has the same limitation.
The SQL Server 2005 resource depends on the SQL network name resource and the physical disk resources that hold its data. 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.
February 2, 2009 at 2:26 am
I am using SQL Server 2005 with mount points for a while
One of the things I learned is to leave a space of, at least, 2 GB for the root drive
The setup program needs between 300MB and 1 GB and does not see the subfolder drives
It uses this space from only the root disk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply