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
    }
}