In an Environment where you have 100’s of SQL instances and you need to test the connectivity to each of the instance or you need to deploy a script on each of the instance after verifying the connectivity to the instance.You need to have some sort of script which will connect to each instance and verify the connectivity. I achieved this using powershell.
The script reads the server names fromĀ a text file and connect to each of the instance one at a time in a loop using SMO
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null $Servername=Get-Content "C:\ServerList.txt" foreach($Server in $Servername) { try{ $server = New-Object Microsoft.SqlServer.Management.Smo.Server($Server) $server.Version | Out-Null Write-Host $server - "SQL Server connection successful!!!" } Catch { Write-Host $Server "--Failed" -BackgroundColor Red } }
Execute a script or t-sql using Invoke-sqlcmd
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null $Servername=Get-Content "C:\ServerList.txt" foreach($Server in $Servername) { try{ $server = New-Object Microsoft.SqlServer.Management.Smo.Server($Server) $server.Version | Out-Null Write-Host $server - "SQL Server connection successful!!!" Invoke-Sqlcmd -InputFile "C:\CreateUser.sql" -ServerInstance $Server -ErrorAction SilentlyContinue -Verbose -QueryTimeout 0 -Database master } Catch { Write-Host $Server "--Failed" -BackgroundColor Red } }