June 12, 2013 at 9:12 am
Hi All,
Someone asked the question today "Just how many of our servers have AS installed?" and I didn't know.
Out of 300 servers (mainly SQL 2008) I reckon maybe 10 have AS installed.
I was hoping I could just fire some t-sql at our central management server and get a result back but I can't find anywhere within SQL that this info is stored?
At the moment it's looking to me like it'll be easier for me to do it with a powershell script and just feed that an input file that's lists all our servers and look for either a partial Service or AMO name.
Has anyone else had to do this and found a neat way to do it?
Many thanks.
June 12, 2013 at 9:40 am
Here is a powershell script that will query your CMS and check for the SSAS service on all servers in your CMS as well as whether the service is running or not.
$CMSServer = "<CMS Server name>"
$CMSquery = "SELECT name
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal"
$targets = invoke-sqlcmd -query $CMSquery -ServerInstance $CMSServer
foreach($target in $targets)
{
$machineName = $Target[0]
$agent= Get-Service -ComputerName $machineName -Name MSSQLServerOLAPService -ErrorAction SilentlyContinue
if ($agent -ne $null)
{
$status = $agent.status
Write-Host "the Server $machineName has SSAS installed and service is currently $status"
}
}
If you wanted to query CMS you could do the following
declare @ssas varchar(50)
exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\MSSQLServerOLAPService', N'DisplayName', @ssas OUTPUT
select @ssas
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply