1

I need to run a set of Powershell commands from my Central Management Server onto all of the SQL Server Instances that it contains. Can someone let me know how this could be achieved?

I need to loop each server instance from the central management and calculate/get the free space. I am using SQL Server 2012. I need to try out the following things:

  1. Search particular extension files on Server Instances
  2. Check the space occupied by them
  3. Check Free Space
  4. Store the above info. in a file on the current system

I am simply aiming for getting the size of backup files (.bak files) only. I have a solution already where we query Invoke-Command to find the files for us on remote machine. I wanted to know whether there exist a better solution to get the task done.

  • 2
    Use Get-DbaDiskSpace. Dbatools.io has lot of functions that you can use – Kin Shah Oct 6 '16 at 2:14
  • You can probably just expand on what you're already using but loop it with different server names of the host servers that have the SQL Server instances installed.You should edit your question and add the syntax you're using with the PowerShell Invoke-Command when you get a moment... this will likely clarify a lot too for me and others potentially. – Pimp Juice IT Oct 6 '16 at 14:27
  • "I wanted to know whether there exist a better solution to get the task done." -- We can't answer that unless you provide your current code. – Shawn Melton Oct 9 '16 at 0:37
  • Since you are searching for OS-specific statistics, you could eliminate searching the CMS just to get the host names. In other words, if your SQL hosts are stored in an Active Directory computer group, then you could directly search that group via Powershell to obtain the host names. Then, you check free space for each machine and search a certain path for .BAK files. – Gary Oct 12 '16 at 21:48
  • @Gary: +1 mate. That is exactly how I bought about the task. – genius_monkey Oct 13 '16 at 10:59
3

First, get your server list query returning what you want, bellow is an example query to run against your CMS.

SELECT DISTINCT [server_name] 
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]

Next the PowerShell, we use a loop that pings the server first to verify it is available then checks the SQL connection. If everything is good it will execute the requested code. The below executes a SQL query but anything could be put in that block. This script runs under the current windows user when connecting to the SQL Instances.

#Import the SQLPS module to use the Invoke-SQLCmd
Import-Module -Name SQLPS -DisableNameChecking -Force

#Create a variable to hold your SQL query for the server list
$ServerListQuery = @"
SELECT DISTINCT [server_name] 
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]
"@

#Create a variable to hold the query or queries you want to execute against each SQL Server
$SQLServerVersionQuery=@"
SELECT GetDate() AS DateTimeStamp, 
CAST(SERVERPROPERTY('ServerName')AS sysname)  AS ServerName, 
@@version AS SQLServerVersion
"@

#Create your list and place it in a variable using Invoke-Sqlcmd
#Invoke-Sqlcmd -ServerInstance <TheNameOfYourCMSServer> -Database msdb (because that is where CMS is stored) -Query $ServerListQuery
$list = Invoke-Sqlcmd -ServerInstance MyCMSServer -Database msdb -Query $ServerListQuery

#Loop through the list and run your query
ForEach ($databaseinstance in $list){

#remove named instance if it exists for the ping test
$servername = $databaseinstance[0].split("\") | Select-Object -Index 0
IF(Test-Connection -ComputerName $servername -quiet) {
TRY{

#Run the query variable created at the top $SQLServerVersionQuery
Invoke-Sqlcmd -ServerInstance $databaseinstance[0] -Query $SQLServerVersionQuery -QueryTimeout 300 | 
Select-Object -Property DateTimeStamp ,ServerName, SQLServerVersion

} 
#Catch the error if SQL connectino doesn't work
CATCH {Write-Output "SQL Connect Error on: $databaseinstance[0] >> $Error[0]"}
} 
#Catch the error if the ping test fails
ELSE {Write-Output "Server Ping Error: $servername >> $Error[0]"}
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.