Tuesday, September 29, 2009

Linked server problem? No, connectivity issue.

In my new role as a Database Hardener, I also function as Level 2 Production Support.
 

If the Production Support Team can't resolve an issue, they pass it to me.
 

Recently, I was given the task to troubleshoot a linked server issue that had stumped other DBAs.

Both instances were SQL Server 2005. The DBAs tried multiple configurations but the linked server kept failing with either a timeout error or a network interface error.

I started from the beginning and tried to defined the linked server myself.  Same results.

The one thing I think I did different is that I clicked on the For Help: link at the bottom of the error dialog.

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3073&EvtSrc=MSSQLServer&EvtID=11001&LinkId=20476

After reading this entry, I took the recommended user action and pinged the target server via xp_cmdshell from the Query window on the originating server.   No response.


Tada! It was a network connectivity issue and had nothing to do with how the DBA was configuring the linked server.  I advised the DBA to call in a ticket for the network team.


The lesson here is try not to let your focus get too narrow when troubleshooting and use all the help that is offered.