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