MORE ABOUT ME
Welcome! I’m Thomas…
Resources

SQL Server Linked Server Connection Test

Linked server connection test

SQL Server Linked Server Connection Test

Database servers have a lot of moving parts, as do the applications built on top of any database platform. That’s why when it comes time to rebuild a server, or migrate systems to use new servers, it can be difficult to get everything correctly configured the first time through.

As time passes and systems pass from one administer to another details of the installations can be lost. No matter how great your documentation and change control process might be there always seems to be something that gets forgotten. Things like specific trace flags, or a specific permissions granted, or a specific SSIS package.

And then there are linked servers, the equivalent of hotel wifi for SQL Server. You know they are there, and they should just work to bring you data as needed, but you don’t think about them until they stop working.

The last thing you want to do after a disaster is to hand over a server to your end users that is not ready.

How To Test Linked Server Connections

It’s easy enough to verify that a linked server connection is working after such events, you just need to navigate and right-click:

Linked server connection test

Of course this process is not optimal if you have dozens of linked servers defined. It is not uncommon as servers get older to have more than a handful of linked servers. And these linked server could point to a variety of sources, requiring a variety of specific drivers to be loaded onto your instance. Do you know all the drivers that are installed on your servers right now? Probably not.

Having a script to quickly check each definition would sure be nice, right?

Script To Test Linked Server Connections

Here’s a script for you to use to test your linked server connections. As always, the usual disclaimer:

Script disclaimer, for people who need to be told this sort of thing:

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code.

You can also download a copy of the script here.

<#
/*=============================================
  File: SQL_Server_linked_server_connection_check.ps1
 
  Authors: Thomas LaRock, http://thomaslarock.com/contact-me/ 
 
  Original blog post at http://thomaslarock.com/2016/03/sql-server-linked-server-connection-test
 
  Summary: 
 
    This script will test the connection for each linked server defined.
 
    This script will accept an input of a server name. If no name is passed,
    the script will default to the current computer name. 
 
    This script will output to a file, you should set the $OutputFile variable
    to whatever you want. 
 
  REMARKS: 
 
    None. 
 
  Date: February 29th, 2016
 
  SQL Server Versions: SQL2012, SQL2014, SQL2016
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/
#>
 
<# Input a server name. #>
param(
[Parameter(Mandatory = $false)]
[String]
$ServerName
)
 
<# Load the assemblies. #>
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")|Out-Null
 
<# If no server name is passed, we will set to the current server name. #>
if ($ServerName.Length -eq 0)
{
$ServerName = $env:COMPUTERNAME
}
 
<# Set the output file path, and initialize. #>
$OutputFile = "C:\Users\Administrator\Documents\linked_server_output.txt"
"$(Get-Date) Starting Linked Server connection test for server $ServerName." | Out-File $OutputFile 
 
<# Create new objects for managed computer and SQL server instances.
   We use ServerInstances to get friendly names for installed instances,
   and we get a list of services to skip those not currentoy running. #>
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName
$Instances = $mc.ServerInstances
$Services = $mc.Services
 
<# Begin outer loop for each SQL instance installed. #>
foreach($Instance in $Instances)
{
$Servername = $Instance.Parent.Name
 
<# Begin inner loop, for all services installed. #>
 foreach ($ServiceName in $Services)
 {
  <# We are filtering for services that are running. #>
  if ($ServiceName.ServiceState -eq "Running") 
  {
   <# Using wildcard search to find services that are like the instance name. #>
   if ($ServiceName.Name -like ("*" + $Instance.Name + "*"))
   {
   <# Build string for sql server name. If -ne, assume named instance. #>
    if ($Instance.Name -ne "MSSQLSERVER")
    {
    <# Build connection name. #>
    $ServerName = $ServerName + "\" + $Instance.name 
    }
 
    <# Build SQL Server object. #>
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
 
    <# Attempt to connect to this instance by returning the Version property. #>
    try   
    {
     $Server.Version | Out-Null
     "$(Get-Date) $ServerName connection attempt." | Out-File $OutputFile -Append
 
     <# If connection above is successful, get array of linked servers. #>
     $LSNames = $Server.LinkedServers
     "$(Get-Date) $ServerName connection success." | Out-File $OutputFile -Append
 
     <# If no linked servers defined. #>  
     if ($LSNames.Count -eq 0) 
     {
     "$(Get-Date) $ServerName no linked servers found." | Out-File $OutputFile -Append
     }
 
     <# For each linked server test the connection. #>
     foreach ($LSname in $LSnames)
     { 
      if ($LSname -ne $null)
      {
       try
       {
        $LSname.testconnection()                     
        $Connectivity = $true
        "$(Get-Date) $ServerName $LSname connection success." | Out-File $OutputFile -Append
       } 
       catch 
       {    
       $Connectivity = $false
       "$(Get-Date) $ServerName $LSname connection failure." | Out-File $OutputFile -Append
       }
      }
     }
    }
    catch [System.Exception] 
    {
    "$(Get-Date) $ServerName Server connection failed." | Out-File $OutputFile -Append
    }
   }
  }
 }
}

Another use for this script would be for routine checks to make sure that your linked servers are still valid, or remove any that are no longer working. I believe it is better to only have linked servers defined that are necessary, rather than continue to carry forward a host of linked server definitions that are no longer valid.

One Pingback/Trackback

  • Nancy Lytle

    What code is the script written in, Powershell?

    • ThomasLaRock

      Yes, PowerShell, sorry for the confusion.

      • Nancy Lytle

        Thanks for getting back so quickly.

  • Geoff Dawdy

    Any recommendations for testing a linked server on SQL Server 2008 R2?

    • ThomasLaRock

      I’d try the script above and see if it works. From what I can tell, it should, I just didn’t test it on SQL2008 R2. Let me know if it throws an error.

  • Pingback: Install-Module dbatools - Thomas LaRock()