Mit diesem Script können Sie die Verbindung zu dem LinkedServer Objekten eines SQL Servers testen.
Aufruf Test-LinkedServers oder TestLinkedServers -MachineName Computer
Durchlaufen werden alle SQL-Instanzen auf der jeweiligen Maschine
Die Ausgabe wie folgt:
ERBOSE: Testing LinkedServer connetions for all SQL-Instances on SRPSDSQL008
VERBOSE: Result will be stored C:\Users\izeyl24\Documents\LinkedServer_test.txt
VERBOSE: [04.09.2020 13:37:02] INFO Testing SRPSDSQL008
VERBOSE: [04.09.2020 13:37:02] INFO Starting Linked Server connection test for server SRPSDSQL008.
VERBOSE: [04.09.2020 13:37:02] INFO Found SQL-Server Instances: 1
VERBOSE: [04.09.2020 13:37:03] INFO Try connection to SRPSDSQL008.
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 connection attempt.
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 Version is 13.0.5598.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL001] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL001
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL001] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL002] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL002
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL002] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL003] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL003
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL003] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL004] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL004
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL004] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL005] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL005
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL005] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL006] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL006
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL006] connection success.
VERBOSE: [04.09.2020 13:37:03] INFO [SRPSDSQL007] Provider: SQLNCLI ProductName: SQL Server DataSource: SRPSDSQL007
VERBOSE: [04.09.2020 13:37:03] INFO SRPSDSQL008 to LinkendServer [SRPSDSQL007] connection success.
Script:
Show/Hidden csharp code
param(
[Parameter(Mandatory = $false)]
[String]
$MachineName
)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
Clear-Host
$VerbosePreference = "continue"
if ($MachineName.Length -eq 0) {
$MachineName = $env:COMPUTERNAME
}
$myDocs = [environment]::getfolderpath("mydocuments")
$LogPath = "$myDocs\LinkedServer_test.txt"
Write-Verbose "Testing LinkedServer connetions for all SQL-Instances on $MachineName"
Write-Verbose "Result will be stored $LogPath`n`n"
function write-LogRecord {
param
(
[ValidateSet("INFO", "WARNING", "ERROR", "DEBUG")]
[String]$Typ = "INFO",
[ValidateNotNullOrEmpty()]
[String]$Text
)
# Generieren des Zeitstempels für die einzelnen LogZeilen
$TimeStamp = get-date -Format "[dd.MM.yyyy HH:mm:ss]"
# Inhalt entsprechend Formatieren und zusammensetzen
$LogInhalt = "{0,-25}{1,-12}{2}" -f $TimeStamp, $Typ, $Text
# Hinzufügen zum LogFile
Add-Content $Logfile $LogInhalt
Write-Verbose $LogInhalt
}
#Neues Log anlegen
$Logfile = (New-Item ($LogPath) -ItemType File -Force).FullName
$LogInhalt = "{0,-25}{1,-12}{2}" -f "Zeitstempel", "Typ", "Logtext"
Add-Content $Logfile $LogInhalt
write-LogRecord -Text "Testing $MachineName"
write-LogRecord -Text "Starting Linked Server connection test for server $ServerName."
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName
$Instances = $mc.ServerInstances
$AllServices = $mc.Services
write-LogRecord -text "Found SQL-Server Instances: $($Instances.Count)"
foreach ($Instance in $Instances) {
$Servername = $Instance.Parent.Name
foreach ($ServiceName in $AllServices) {
if ($ServiceName.ServiceState -eq "Running" -and $ServiceName.Type -eq "SqlServer") {
if ($Instance.Name -ne "MSSQLSERVER") {
$ServerName = $ServerName + "\" + $Instance.name
}
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
try {
write-LogRecord -Text "Try connection to $ServerName."
$Server.Version | Out-Null # Zaghafter Verbingugsversuch...
write-LogRecord -Text "$ServerName connection attempt."
write-LogRecord -Text "$ServerName Version
is $
($Server
.Version)."
$AllLinkedServers = $Server.LinkedServers
if ($AllLinkedServers.Count -eq 0) {
write-LogRecord -Typ WARNING -Text "$ServerName no linked servers found."
}
else {
foreach ($ThisLinkedServer in $AllLinkedServers) {
if ($null -ne $ThisLinkedServer) {
write-LogRecord -Text "$($ThisLinkedServer) Provider: $($ThisLinkedServer.ProviderName) ProductName: $( $ThisLinkedServer.ProductName) DataSource: $($ThisLinkedServer.DataSource)"
try {
$ThisLinkedServer.testconnection()
write-LogRecord -Text "$ServerName to LinkendServer $ThisLinkedServer connection success."
}
catch {
write-LogRecord -Typ WARNING -Text " $ServerName to LinkendServer $ThisLinkedServer connection failure."
}
}
} # $ThisLinkedServer
}
}
catch {
write-LogRecord -Typ WARNING -Text "$ServerName $ThisLinkedServer connection failure."
}
}#ServiceState
}#foreach $ServiceName
}#foreach $Instance
