Listando informações e conectividade de Linked Servers – Powershell


Listing Linked Servers information and connectivity – Powershell

Technet Scripting Guys link

Pessoal, um de meus checklist é verificar conectividade dos linked servers de todos os servidores.

Segue uma função que ajuda bastante :

  1: Function Save-SQLMsg () 
  2: <#
  3: ----------------------------------------------------------
  4: Save  log in file
  5: ----------------------------------------------------------
  6: Requires POWERSHELL 2.0
  7: 
  8: File Name     			= $NamePS1
  9: Server name   			= $Server 
 10: DatabaseName 			= $databasename
 11: Message To Log		    = $Message
 12: Path to generate file 	= $PathFileLog 
 13: Date to Log				= $TodayDate
 14: #>
 15: 
 16: 
 17: {
 18: 
 19: 	[CmdletBinding()]
 20: 	
 21: 	Param (
 22: 		[Parameter(position=1,Mandatory = $true )][String] $NamePS1,
 23: 		[Parameter(position=2,Mandatory = $true )][String] $Server,
 24: 		[Parameter(position=3,Mandatory = $false )][String] $DatabaseName = "",
 25: 		[Parameter(position=4,Mandatory = $false )][String] $Message = "" ,
 26: 		[Parameter(position=5,Mandatory = $false )][String] $PathFileLog = "C:\temp1",
 27: 		[Parameter(position=6,Mandatory = $false )][String] $TodayDate = (Get-Date -Format "yyyyMMddhhmmss")
 28: 		)
 29: 	process 
 30: 	{
 31: 	
 32: 		#test if path wich will contains the error file exists. if not create 
 33: 	
 34: 	if (!(Test-Path -path $PathFileLog))
 35: 	{
 36: 		try {
 37: 			New-Item $PathFileLog -itemtype directory -ErrorAction  Stop   | Out-Null
 38: 		}
 39: 		catch {
 40: 			Write-Host "Can not create log file path"
 41: 			break;
 42: 		}
 43: 	} 
 44: 	
 45: 	
 46: 	$NameFileFull = $PathFileLog + "\" + $NamePS1 + $TodayDate + ".log" 
 47: 	
 48: 	$TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2) + "-" + $TodayDate.Substring(6,2) 
 49: 	
 50: 	"Server : " + $Server + " Database : " + $DatabaseName + " Date : "  + $TDate + " Message: "  + $Message | Out-file  $NameFileFull -append 
 51: 	} 
 52: }
 53: 
 54: Function Get-SQLLinkedServer()
 55: <#
 56: ----------------------------------------------------------
 57: Test All Linked Servers
 58: ----------------------------------------------------------
 59: Requires POWERSHELL 2.0
 60: 
 61: $TXTServersList         = Txt with servers to be checked - Default server
 62: 
 63: 
 64: #>
 65: {
 66: 	[CmdletBinding()]
 67: 	
 68: 	PARAM	(
 69: 				[Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="File Servers List")]
 70: 				[Alias("FullNameTXT")]
 71: 				[String] $TXTServersList = $env:COMPUTERNAME
 72: 				
 73: 			)
 74: 
 75: 	begin 
 76: 	{
 77: 		[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
 78: 	}
 79: 	Process
 80: 	{
 81: 	
 82: 		$verbosePreference="continue" 
 83: 		[datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss"
 84: 		if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT")
 85: 		{
 86: 			try
 87: 			{
 88: 				$ServersList = get-content $TXTServersList	
 89: 			} catch {
 90: 						$msg = $error[0]
 91: 						Write-Warning $msg
 92: 						break;
 93: 			}
 94: 		}	
 95: 		else
 96: 		{
 97: 			$ServersList = $TXTServersList
 98: 		}	
 99: 		
100: 		
101: 		$LineNumber = 1
102: 		$FinalResult = @()
103: 	
104: 		foreach ($svr in  $ServersList )
105: 		{
106: 			try 
107: 			{
108: 				$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
109: 				$Data = $Server.linkedservers| where-object {$_.State -eq "Existing"} | foreach {
110: 				
111: 				$Object = New-Object PSObject
112: 				
113: 				[datetime]$DateLastModified = "{0:yyyy-MM-dd hh:mm:ss}" -f [datetime] $_.DateLastModified  
114: 
115: 				$Object | add-member Noteproperty LineNumber  					$LineNumber 	
116: 				$Object | add-member Noteproperty Date  						$TodayDate 	
117: 				$Object | add-member Noteproperty ServerName  					$svr
118: 				$Object | add-member Noteproperty LinkedServerName 				$_.Name
119: 				$Object | add-member Noteproperty DataSource 					$_.DataSource 					
120: 				$Object | add-member Noteproperty DateLastModified 				$DateLastModified
121: 				$Object | add-member Noteproperty CollationCompatible 			$_.CollationCompatible
122: 				$Object | add-member Noteproperty DataAccess 					$_.DataAccess
123: 				$Object | add-member Noteproperty RPC		 					$_.RPC
124: 				$Object | add-member Noteproperty RpcOut		 				$_.RPCOut
125: 				$Object | add-member Noteproperty UseRemoteCollation			$_.UseRemoteCollation
126: 				$Object | add-member Noteproperty CollationName					$_.CollationName					
127: 				$Object | add-member Noteproperty ConnectionTimeOut				$_.ConnectTimeOut
128: 				$Object | add-member Noteproperty QueryTimeOut					$_.QueryTimeOut					
129: 				$Object | add-member Noteproperty Distributor	 				$_.Distributor					
130: 				$Object | add-member Noteproperty Publisher 					$_.Publisher					
131: 				$Object | add-member Noteproperty Subscriber 					$_.Subscriber										
132: 				$Object | add-member Noteproperty LazySchemaValidation 			$_.LazySchemaValidation
133: 				$Object | add-member Noteproperty EnablePromotionofDistributedTransactionsForRPC		$_.IsPromotionofDistributedTransactionsForRPCEnable					
134: 				$Object | add-member Noteproperty ProviderName 					$_.ProviderName					
135: 				$Object | add-member Noteproperty ProductName					$_.ProductName
136: 				try
137: 				{
138: 					$_.testconnection() 
139: 					$Object | add-member Noteproperty Connectivity				$True
140: 				} catch {
141: 					$Object | add-member Noteproperty Connectivity				$False
142: 				}
143: 				
144: 				$FinalResult += $Object
145: 				$LineNumber ++ 
146: 			} 
147: 
148: 			Write-Output $FinalResult												
149: 			
150: 		}	catch {		
151: 					$msg = $error[0]
152: 					Write-Warning $msg
153: 					Save-SQLMsg "Get-SQLLinkedServer" "$svr" "" "$msg" 
154: 					continue
155: 		} 
156: 		
157: 	}
158: }	
159: 
160: }	

 

 

 

 

 

 

Esta função retorna :

This function returns :

  1: LineNumber 					int,
  2: Date 					datetime,
  3: ServerName 					string,
  4: LinkedServerName 				string,
  5: DataSource 					String,
  6: DateLastModified 				datetime,
  7: CollationCompatible 				boolean,
  8: DataAccess 					boolean,
  9: RPC 						boolean,
 10: RpcOut 					boolean,
 11: UseRemoteCollation 				boolean,
 12: CollationName 				string,
 13: ConnectionTimeOut 				int,
 14: QueryTimeOut 					int,
 15: Distributor 					boolean,
 16: Publisher 					boolean,
 17: Subscriber 					boolean,
 18: LazySchemaValidation 				boolean,
 19: EnablePromotionofDistributedTransactionsForRPC 	boolean,
 20: ProviderName 					string,
 21: ProductName 					string,
 22: Connectivity 					Boolean 

Donwload i

 

Exemplos

List All LinkedServers Server Default
Get-SQLLinkedServer

List All LinkedServers All servers (by txt)
Get-SQLLinkedServer "c:\temp\servers.txt"

List All LinkedServers All servers (by txt) (name and connectivity test )
Get-SQLLinkedServer "c:\temp\servers.txt" | select LineNumber,Date,ServerName,LinkedServerName,Connectivity |Format-Table

List All LinkedServers All servers (by txt) (name and connectivity test = false )
Get-SQLLinkedServer "c:\temp\servers.txt" | where-object {$_.Connectivity -eq $false} | select LineNumber,Date,ServerName,LinkedServerName,Connectivity |Format-Table

List all LinkedServers All servers (by txt) (name and connectivity test = false ) and stored SQL Server Table
TSQL create table (LinkedServers XML)

(Get-SQLLinkedServer "c:\temp\servers.txt" | where-object {$_.Connectivity -eq $false} | select LineNumber,Date,ServerName,LinkedServerName,Connectivity | ConvertTo-Xml -NoTypeInformation).save("c:\temp\LinkedServers.xml")
$XML = (Get-Content "C:\Temp\LinkedServers.xml" ) -replace "’", "”"
$SQL = "insert into LinkedServers (XMLl) values (‘$XML’)"
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql

TSQL to list with servername  condition = "Jupiter"
Select t2.Col1.value(‘(Property[@Name="LineNumber"]/text())[1]’, ‘int’) LineNumber,
    t2.Col1.value(‘(Property[@Name="Date"]/text())[1]’, ‘nvarchar(255)’) Date,
    t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) ServerName,
    t2.Col1.value(‘(Property[@Name="LinkedServerName"]/text())[1]’, ‘nvarchar(255)’) LinkedServerName,
    t2.Col1.value(‘(Property[@Name="Connectivity"]/text())[1]’, ‘nvarchar(255)’) Connectivity
FROM dbo.LinkedServers
CROSS APPLY xml.nodes(‘/Objects/Object’) As t2(Col1)
where t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) = ‘Jupiter’

POWERSHELL ROCKS !!!!

“Times have changed and times are strange
Here I come, but I ain’t the same
Mama, I’m coming home
Times gone by seems to be
You could have been a better friend to me
Mama, I’m coming home

You took me in and you drove me out
Yeah, you had me hypnotized
Lost and found and turned around
By the fire in your eyes

You made me cry, you told me lies
But I can’t stand to say goodbye
Mama, I’m coming home
I could be right, I could be wrong
Hurts so bad, it’s been so long
Mama, I’m coming home”

Mama, I’m Coming Home

Ozzy Osbourne

 

Follow me on Twitter

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

2 Responses to Listando informações e conectividade de Linked Servers – Powershell

  1. Thiago says:

    Grande Laerte!Cara, a cada post seu eu vejo que preciso me aprofundar (e começar a mexer primeiro, hehe) com PowerShell!Muito bom! Continue assim!Aquele abraço!

  2. Laerte says:

    Thiago Man… (man from Etec girls) kkkkk. Brigadão cara..é igual chips..impossivel parar depois que começa !!!Abráço meu amigo !!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s