question

aminifdo avatar image
aminifdo asked

SQL Server Data Tools and MultiSubnetFailover

I need to create a package with "Transfer SQL Server Objects Task". the source is a external server with AlwaysOn multi subnet. Is there a way I can specify the AlwaysOn listener name on the SMOServer connection with "MultiSubnetFailover=TRUE"?
ssisalwaysonssdt
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The point of the AG listener is that clients don't need to know which replica (or which subnet) the live server is running on. You should be able to just use the AG listener name in your package connection and the listener name will get you to the right server.
0 Likes 0 ·

1 Answer

·
aminifdo avatar image
aminifdo answered
Hello KenJ, Thank You for your reply, Using the listener name will work for same subnet AlwayOn set up. This is a multi-subnet where the listener have two IPs associated with it, and its publishing both the IPs in a round-robin manner. I do not have access to those clusters to configure HostRecordTTL and RegisterAllProvidersIP settings. Having "MultiSubnetFailover=TRUE" will stop timing out the client connections in this scenario as it will try to connect both the IPs at once and proceed with the active IP. Anyway .NET 4.6.1 onward has this feature inbuilt, not sure that will help me or not. still there may be some issues like this https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/ Currently, the packages are set to use only the primary server, I'm busy with some other projects and will have a look at them later ;) ,
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sounds good. The only time I've set up a multi-subnet AG, the cluster actually took care of updating the DNS entries for me after failover so that the listener name always got me to the correct subnet (or so it appeared to me as an SSMS user). Perhaps, under the hood, it was using the technique you describe. Hope you'll stop by with an update one you have it proven out.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.