Troubleshout SQL Server

Find the PID of the SQL server

$sqlpid=((netstat -ano | sls ":143[34] ") -replace ' $','' -replace '^. ','' | select -Unique); echo "PID of SQL is $sqlpid"

echo "PID of SQL is $sqlpid, ports that it listens to:"; netstat -ano | sls ".* $sqlpid *$"

About the Instance name

How to find the Instance name

Query-SQLServer -Computer COMPUTER_NAME_OR_IP

How to connect to the default or other instance

How to configure SQL Express 2012 to accept remote connections

Troubleshouting SQL server connectivity issues

Query-SQLServer -Computer 10.1.11.5341
ServerName;W10SRV;InstanceName;MSSQLSERVER;IsClustered;No;Version;15.0.2000.5;tcp;1433

Test-SQLServer -server SERVER_NAME

Test-SQLServer -server SERVER_NAME -user USER_NAME

Test-SQLServer -server SERVER_NAME -instance INSTANCE_NAME

Test-SQLServer -server SERVER_NAME -user USER_NAME -instance INSTANCE_NAME

Test-SQLServer ...  -sql SQL_TO_RUN -database DATABASE_NAME

function Query-SQLServer{
# From: # http://sqlserverio.com/2013/02/27/finding-sql-server-installs-using-powershell/
   [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = 'low'
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer
    )
    Begin {
        $ErrorActionPreference = 'SilentlyContinue'
        $Port = 1434
        $ConnectionTimeout = 1000
        $Responses  = @();
    }
    Process {

          $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        $ToASCII = new-object system.text.asciiencoding
      $UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
        $UDPPacket = 0x02,0x00,0x00

        $UDPClient = new-Object system.Net.Sockets.Udpclient
        $UDPClient.client.ReceiveTimeout = $ConnectionTimeout
        $UDPClient.Connect($IPAddress,$Port)
      $UDPClient.Client.Blocking = $True

      [void]$UDPClient.Send($UDPPacket, $UDPPacket.length)
      $BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
      [string]$Response = $ToASCII.GetString($BytesRecived)

      If ($Response) {
         $Response = $Response.Substring(3,$Response.Length-3).Replace(';;','~')
         $Response.Split('~') | ForEach {
            $Responses += $_
         }
         $UDPClient.close()
      }
    }
    End {
        return ,$Responses
    }
} 

function Test-SQLServer {
    param(
        [Parameter(Mandatory = $True)] [string] $SERVER, 
      [string] $instance, 
      [string] $user,
      [string] $pass,
      [string] $database,
      [string] $sql
      )
    
   if ($server -match '[0-9][0-9.]*') {
      echo "You specified the IP of the server. If nothing works you may try with the server name"
   } else {
      echo "You specified the name of the server. If nothing works you may try with the server IP"
   }
   If (!($user)) {
       $cred="Integrated Security=true"
      echo "No user specified so I will try to connect with Windows Integrated Security"
      echo "  (if this fails consider retrying with -user SOME_USERNAME)"
   } else {
      echo "A user was specified so I will try to connect with username/password"
      echo "  (if this fails consider retrying without -user and I will use Windows Integrated Security)"
      if (!($pass)) {
         $secret = Read-Host -assecurestring "Please enter password for $user"
         $pass = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($secret))
      } else {
         echo "Using supplied password"
      }
      $cred='User ID={0};Password={1};' -f $user,$pass 
   }
   if ($instance) {
      $server_instance='{0}\{1}' -f $Server,$INSTANCE
      echo "An instance was specified ($INSTANCE) so I will try to connect to it"
      echo "  (if this fails consider retrying without -instance and I will try to connect to the default one)"
   } else {
      $server_instance=$server
      echo "No instance specified so I will try to connect to the default one"
      echo "  (if this fails consider retrying with-instance SOME_INSTANCE and I will try to connect to that one)"
   }
   if (($sql) -and ($database)) {
      echo "You specified DB, SQL so I will try to execute it"
      echo "  (if this fails consider running me without -sql ... -db ... and I will try to just list available databases)"
      $connstring = 'Data Source={0};database={1};{2}' -f $Server_instance, $database, $cred
   } else {
       $sql='SELECT name FROM sys.databases;'   
      echo "You did not specify DB &  SQL so I will try to list all databases with $SQL"
      echo "  (you can also try -db ... -sql ... and I will try to run the specified SQL"
      $connstring = 'Data Source={0};{1}' -f $server_instance, $cred
   }

   $printable = ($connstring -replace 'password=[^;]*','password=****'  -replace ';',' ; ')
   write-host "Testing with:"
   write-host -NoNewline "  Connection String: "
   write-host -ForegroundColor cyan  $printable
   write-host -NoNewline "  SQL:               "
   write-host -ForegroundColor cyan  $sql
   $connectionString = $connstring 
   
   $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
   $command = new-object system.data.sqlclient.sqlcommand($sql,$connection)
   $connection.Open()
   if ($connection.State -eq "Open") {
      $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
      $dataset = New-Object System.Data.DataSet
      $adapter.Fill($dataSet) | Out-Null
      $connection.Close()
      $dataSet.Tables | select -First 10 |  ft   
   }

}