Sql-server – access the file stream shared folder using Listeners in case of AlwaysOn cluster environment

availability-groupsazure-vmfilestreamsql serversql-server-2012

One of the most effective ways to extend your SQL Server Integration Services (SSIS) control flow is to use a Script task to write custom code that perform tasks you cannot perform with the built-in components.But it's not straight forward in case of AlwaysOn configured nodes

Below is my current environment setup which helps you to understand the problem.

My environment

  • I am having NODE 1 and NODE 2 as cluster configured
  • SQL Server always on and availability group configured
  • SQL Server file stream is enabled on both node 1 and node 2 entitled as MYSHAREDNAME
  • Internal load balancing Listener configured and entitled as MYACTIVENODE

Problem Statement
One of the node among cluster (node 1 or node2) can be down any time. We will not sure which one will become PRIMARY. I am trying to access the shared folder as follow

\NODE1\MYSHAREDNAME – Works if NODE 1 is primary node
\NODE2\MYSHAREDNAME – Works if NODE 2 is primary node

But it's difficult to hard code above path as any node can go down. So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

  \\MYLISTENERNAME\MYSHAREDNAME

But I am not able to access it

How can I fix it? Ports 1433, 5022, 59999 are enabled.

Extract from https://msdn.microsoft.com/en-in/library/dn385720.aspx

AlwaysOn Availability Groups are supported as long as you do not add new database files to the primary database. If a database operation requires a new file to be created in the primary database, first disable AlwaysOn Availability Groups in the secondary node. Then, perform the database operation on the primary database and backup the database in the primary node. Next, restore the database to the secondary node, and enable AlwaysOn Availability Groups in the secondary node. Note that AlwaysOn Failover Cluster Instances is not supported when using the SQL Server Data Files in Windows Azure feature

But I did not understand what does above statement means?

Best Answer

I do not believe your extracted quote is relevant to your problem.

Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get the active IP and other times it will get the one that is offline.

This is the purpose behind the MultiSubnetFailover parameter that the listener acknowledges.

Related Question