Script to check all your linked server connections for SQL Server

Problem

You need to quickly check all linked server connections for a given instance or list of instances.

Solution

Using Powershell and a Central Management Server you can quickly check your linked servers for connectivity.

Checking your connections

Every now and then you may have the need to check and verify that all of your defined linked servers are able to provide basic connectivity. You will most often find yourself needing to check these linked server definitions usually in between the time a server has been rebuilt after a disaster event and the time you turn it back over to your end users for them to test their systems. Having a script to quickly cursor through and check each definition comes in handy as it can save you headaches later when users complain about their systems not working.

It is not uncommon, especially as servers get older, to have more than a handful of linked servers defined. And these links could point to a variety of sources, and require a variety of specific drivers to be loaded onto your instance. As such, it is quite possible that as time passes, and systems pass from one person to another to administer, that the details of the installations can be lost. The last thing you want to do after a disaster is to hand over a server that is simply not ready.

Another use for this script would be for routine checks to make sure that your links are still valid, and to clean up ones that are no longer working. It is always better to have only those linked servers defined that are necessary, rather than to simply continue to carry forward a host of definitions that are no longer valid. This script works against SQL 2005 and SQL 2008 instances.

$OutputFile = “C:\LinkedServerFail.txt”

“Starting linked server connectivity test…” | Out-File $OutputFile

$CMSGroup = Set-Location “SQLSERVER:\SQLRegistration\Central Management Server Group\YourCMSInstance\YourCMSDirectory\”

$InstanceListFromCMS = dir $CMSGroup -recurse | where-object { $_.Mode.Equals(“-“) } | select-object Name -Unique

foreach ($InstanceName in $InstanceListFromCMS)

{

 $InstanceNameFriendly = (Encode-SqlName $InstanceName.Name)

 $InstanceNameFriendly = (Decode-SqlName $InstanceNameFriendly) 



  


 $s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $InstanceNameFriendly

 try

 {

 $LSName = $s.linkedservers | where-object {$_.State -eq “Existing”}

 }

 catch

 {

 “Cannot connect to server $InstanceNameFriendly.” | Out-File $OutputFile -Append

 }
 foreach ($Name in $LSName)

 {

  if ($Name -ne $null)

  {

   try

   {

    $Name.testconnection()

    $Connectivity = $true

   }

   catch

   {

    $Connectivity = $false

    “$InstanceNameFriendly $Name connection failure.” | Out-File $OutputFile -Append

   }

  }

 }

}

The above script will output to a text file located at “C:\LinkedServerFail.txt”, feel free to place the output file anywhere you want. You will also need to substitute the name of the instance where your CMS is registered (YourCMSInstance) as well as the directory for the servers you want to check. If you do not include a directory it will return all server instances resgitered in the CMS.

For example: $CMSGroup = Set-Location “SQLSERVER:\SQLRegistration\Central Management Server Group\SQL252\SQL2008\Test\”  

SQL252 is the name of my CMS instance. I have a directory named SQL2008 and a sub-directory named Test. The script will enumerate the servers registered and assigned to the directory and the script will connect to each server name and then attempt to test the connection for each linked server that is defined.

To execute the script, save it to a file with a .ps1 extension. For example, you could save it to c:\run.ps1. Open up a command prompt, navigate to the folder where the .ps1 files exists and run the following command:

C:> sqlps c:\run.ps1

The output will be written to a file that you can use to further investigate your linked server connections.

Next Steps

• Take the above code and execute.
• Read these related tips
  • Execute same SQL Server query across multiple servers at the same time using Central Management Servers
  •  Linked Server Tips

Thomas LaRock

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.

LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.

LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.