Creating a linked server for SQL Server Always On Listener

Here’s some examples for creating linked server connection to an Always On Availability Group listener

–IF USING INTEGRATED SECURITY
EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’

    ,@srvproduct = N’SQL’

    ,@provider = N’SQLNCLI11′

    ,@datasrc = N’MY_LISTENER’

    ,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;Integrated Security=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’

    ,@catalog = N’MY_DBNAME’

GO
EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’

GO
–IF USING SQL LOGIN SECURITY
EXEC master.dbo.sp_addlinkedserver @server = N’MY_LINKEDSERVER’

    ,@srvproduct = N’SQL’

    ,@provider = N’SQLNCLI11′

    ,@datasrc = N’MY_LISTENER’

    ,@provstr = N’Provider=SQLNCLI11;Server=MY_LISTENER;Database=MY_DBNAME;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;’

    ,@catalog = N’MY_DBNAME’

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MY_LINKEDSERVER’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’MY_SQLLOGIN’,@rmtpassword=’MY_SQLPASSWORD’

GO
EXEC master.dbo.sp_serveroption @server=N’MY_LINKEDSERVER’, @optname=N’connect timeout’, @optvalue=N’63’

GO