SQL Server Data Tools and MultiSubnetFailover

Question

question

aminifdo asked Apr 14, '16

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"?

ssis alwayson ssdt

1

10 |1200

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

KenJ commented · Aug 18, 2016 at 04:14 PM

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 ·

Answer 1 · 2016-08-18T23:08:04Z

aminifdo answered Aug 18, '16

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

10 |1200

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

KenJ commented · Aug 19, 2016 at 03:34 AM

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 ·

question