Invoke-Sqlcmd

CLEAR
#Import-Module sqlserver -DisableNameChecking
#Import sql server assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMo")
$servernames=@("RAMESH",
#"RAMESH\TEST",
"RAMESH\UAT",
"RAMESH\MSSQL2016"
)
$servernames|
ForEach-Object{
    $computername=$_
    #Get a server object which corresponds to the default instance
    Invoke-Sqlcmd -Query "EXECUTE sp_configure 'show advanced options',1
RECONFIGURE
EXECUTE sp_configure 'Ad Hoc Distributed Queries',0
RECONFIGURE
GO
EXECUTE sp_configure 'show advanced options',0
RECONFIGURE
GO
select @@SERVERNAME,* from sys.configurations
where configuration_id=16391" `
-ServerInstance $computername
}




clear
$servernames="C:\ServerList\ServerList.txt"
Get-Content $servernames|
ForEach-Object{
            $ComputerName=$_
            Invoke-Command `
            -ComputerName $ComputerName `
            -ScriptBlock{
                         Get-Service -Name "*sql*"
        }|Format-Table -AutoSize
            $ComputerName=$_
            Invoke-Command `
            -ComputerName $ComputerName `
            -ScriptBlock{
                         Get-ClusterResource
        }|Select-Object Name,State,ResourceType,PSComputerName|Format-Table -AutoSize
------If we want to capture server information in a notepad and want to pass that as input parameter for a variable. Please provide server names in the notepad as mentioned below in. Dont give any comma,semicolon,singlequotes. Just provide names as below

To get Cluster and Standalone SQL Server instance services information. We can get it from the below code at a time,
clear
$servernames="C:\ServerList\ServerList.txt"
Get-Content $servernames|
ForEach-Object {
$ComputerName=$_
$S=Get-WmiObject `
-Class Win32_SystemServices `
-ComputerName $ComputerName
IF ($S|Select PartComponent | Where-Object {$_ -like "*ClusSvc*"})
{

Write-OutPut "$ComputerName is clustered"
Invoke-Command `
-ComputerName $ComputerName `
-ScriptBlock{
Get-Service -Name "*sql*"
}|Format-Table -AutoSize

Invoke-Command `
-ComputerName $ComputerName `
-ScriptBlock{
Get-ClusterResource

}|Select-Object Name,State,ResourceType,PSComputerName|Format-Table -AutoSize

}

else #If a server is not clustered then all the services should be up and running.
{

Write-OutPut "$ComputerName is not clustered"
Invoke-Command `
-ComputerName $ComputerName `
-ScriptBlock{

Get-Service -Name "*sql*"
}
}

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server