PowerShell SQL Connection Test

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *