Posted on Leave a comment

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.