March 26, 2025 at 8:32 pm
Hello,
I'm tasked with setting up a 2 node server cluster that will host a MS SQL availability group. I have the servers setup and the AG looks to be working well. However, there is also a requirement to hang a share off of one of the drives. I'm not crazy about this requirement, but I have to deal with it.
Since this is a windows cluster, my understanding is that I need to add and configure the file server role and add the share there. I've done this and it's working, with one exception, which is the DNS name.
For example, let's say these are the various names involved:
Since the SQL Listener and the FS roles each need their own DNS names, I'm getting pushback from some people that the SQL instance and file share don’t answer from the same name. They want to be able to access both the SQL instance and the share from the same name, as if this was a singer server solution.
I've been looking for a way to accommodate this request, but have come up short. The GUI management does not list "File Share" as a resource option for the AG and I haven't found a way to do this in power shell.
Am I missing something or is this just not possible?
Thanks in advance,
March 27, 2025 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 28, 2025 at 1:08 pm
What?? 1. A File Server resource group has nothing to do on an AlwaysOn AG server. 2. The underlying wsfc is controlled by the sql server. So a failover has to be done in SSMS or by T-SQL.
Is the file share on the same disks as the SQL server located? If so, I hope it's not a production enviroment
March 31, 2025 at 6:34 am
I don't have an environment to test this, but I did have a fileshare on a SQL cluster in the past so I hope I can remember it all.
To have a clustered fileshare on the same box as a SQL cluster I used a separate listener name. I found that the cluster gizmos needed to support a clustered fileshare are not compatible with the AG gizmos on the same listener name. There were also some 'interesting' effects when enumerating shares from the windows cluster name compared to the fileshare listener name, and getting our users to use the right name as always was like hearding cats.
Howevern the whole thing ran successfully for many months on Win2016 and SQL2016 on a Hyper-v guest cluster but then we hit a problem. The best we could trouble shoot was it was caused by us using CSVs in the fileshare and a high IO load. It seemed IO had stalled on one of the CSVs and this eventually caused SQL to also stall. The active cluster node was no longer responsive, but Cluster Manager did not initiate a failover. So SQL was hung until the human noticed a problem and forced a failover (I think we had to reboot the active node).
We did see an error message and tried to automate something around this, but a couple of months later SQL stalled again in the early morning while the humans were sleeping.
At that stage we were starting the build process to move to SQL2017 on a new guest cluster. Our Windows people by this time had more experience in building Hyper-v fileshares so we separated these roles to their own servers. This solved all our problems.
It may be that Win2022 does not have the same IO issue as Win2016, but you could wait many months to find out. Also we were never that happy about how the fileshare access worked with having the fileshare and SQL roles on the same cluster - things were not as seemless as when we had SQL and a fileshare on the same non-clustered box.
So my advice is to push back to your users and say fileshare and SQL are two separate roles that need to be hosted on separate clusters. Hyper-v does fileshares much better than listeners on a SQL cluster.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply