SQL Server linked server's DATA ACCESS property changes to false

Question

I have a SQL Server instance that includes an agent job that accesses a second instance via a linked server. Occasionally, and seemingly randomly, the DATA ACCESS property of the linked server changes from true to false (and the agent job begins to fail).

That agent job runs every 10 minutes between 5am and 8pm, and the change to the linked server always happens sometime overnight, though I haven't yet been able to determine when exactly. I do have other jobs--some vendor supplied, and others such as backups, index and statistics maintenance, etc. (Ola Hallengren's scripts)--but nothing else that makes use of that linked server, and nothing that would explicitly touch it.

What would cause linked server DATA ACCESS to change? What troubleshooting or tracing can I do to determine what is causing this?

• J.D.: Do you happen to use Replication?
• pvallero: It is the current recommendation by the vendor for a reporting instance to share some of the load of the application. We are considering going to AAG, but haven't gotten there yet.
• J.D.: I think that could be the source of your issue because I use Transactional Replication and was running into the same problem, and I found a third person with the same situation. Will write up an answer with more details when I get a chance.
• pvallero: That's very interesting. I've implemented Zikato's extended event suggestion, and it will be interesting to catch this in the act, and I'll post here when I find the culprit.
• pvallero: The extended event I set back in May was finally triggered a couple of days ago, and I was able to determine what process was changing the DATA ACCESS of the linked server to false: it was our application vendor's own software deployment tool. Fortunately, it was not specific to replication, and it's something I can now account for in future deployments.

Answer 1

The only way to change the linked server properties I know of is via system procedure sp_serveroption.

This procedure is called in the background even if you change the properties via GUI.

So you can create an Extended Event session to track the execution of this proc.

CREATE EVENT SESSION [LinkedServerChange] ON SERVER 
ADD EVENT sqlserver.module_start
(
 SET collect_statement=(1)
 ACTION
 (
 sqlserver.client_app_name
 ,sqlserver.client_hostname
 ,sqlserver.database_name
 ,sqlserver.server_principal_name
 ,sqlserver.tsql_stack
 )
 WHERE [object_name]=N'sp_serveroption'
)
ADD TARGET package0.event_file
(
 SET filename=N'LinkedServerChange'
 , max_file_size=(2)
 , max_rollover_files=(2)
)
GO
ALTER EVENT SESSION [LinkedServerChange] ON SERVER STATE = START

The result looks like this enter image description here