Sql-server – Accessing file share from ETL package in SQL Always-On configuration

availability-groupsazure-vmsql serversql-server-2012

How can I access the file share from ETL?

Here are my environment details:

  • I have 2 nodes in a windows cluster. node 01 and node 02
  • SQL Server 2014 is installed on both the nodes.
  • Both are Windows Server 2012 R2 machines
  • I have file share created in Azure portal and mapped as G: in both the nodes (01 and 02)

My ETL package is using C# code. It wants to read and write files on one shared folder for processing. I want it to use shared drive G.

Problem Statement: One of the nodes can be down at any time, so I cannot hard code as below:

//node01/G

Can I use file share feature (role) in a failover cluster? if yes, where will that new drive be stored?

I saw the post Accessing an Azure Storage Account File Share from an ASP.NET Application on Stack Overflow, but that requires code changes in all the ETL packages. Is that is the only way?

Best Answer

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your Availability Group's Windows Cluster since really that is only in place to provide Quorum and meta-data synchronisation/ mediation of AG Cluster HA objects -and I would personally advice against complicating that deployment -Clusters should be kept as simple and easy to troubleshoot as possible. Instead I would advise to use a remote fileshare - which you can either make highly available via another windows cluster or just use a standalone server (depending upon requirements).

Related Question