mssqltips logo

Using PowerShell for SSAS to check service status and database availability

By:   |   Updated: 2013-07-09   |   Comments (4)   |   Related: > Analysis Services Administration

Problem

In my first article about PowerShell we saw a brief introduction to PowerShell, learning how to use the basic commands, how to open PowerShell, how to Navigate in SSAS and how to process objects. In part 2, we learned how to work with variables and how to create SSAS backups using the current date and variables. Finally we learned to run MDX and XMLA scripts.

In part 3, there are tips to control the following:

  • PowerShell commands to verify the SSAS Service status
  • Send a message if the SSAS service is down
  • Verify the hard disk space for the SSAS database
  • Schedule a script to verify the database status every hour
Solution

In this article, we are going go run PowerShell commands to verify the SSAS status.

Requirements

  1. The Adventureworks Multidimensional project.
  2. SQL Server 2008 or later (in this example I am using SQL Server 2012).

Getting started

  1. Let's start verifying the Windows Services Status with PowerShell:
     
    get-service 

    The command get-service, obtains the list of windows Services and their status:

    powershell get-service

  2. In this case we are interested in the Analysis Services status, so the command will be:
     
    get-service | select status, name | Where-Object {$_.Name -like  "*MSSQLServerOLAP*"}

    powershell MOLAP status

    The command shows the status of the service, the name of the object with a name similar to MSSQLServerOLAP. It will show if the service status is started or stopped.

  3. Now we are going to send a message if the SSAS Service is stopped.
     
    $servicestatus=get-service | select status,name | Where-Object {$_.Name -like  "*MSSQLServerOLAP*"}
    $Message="The SSAS Service is down"
    if($servicestatus.status -eq "Stopped"){
    echo $Message
     }

    The command shows a message saying that the service is down if the Analysis Services Service is stopped.

  4. The following tip shows how to see the space available in the hard disks.
     
    $driveinformation=gwmi win32_volume -Filter 'drivetype = 3' | select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={$_.freespace/1GB} }

    powershell disk space

    This command shows the disk volumes and the space free in GB. It is useful to see if we have enough disk space for our SSAS databases.

  5. In order to verify the SSAS status of a cube you can call a mdx script every 5 minutes. In this example we are going to create a simple mdx script with a simple query. Then we are going to call the query every hour to verify that the cube is online.
  6. In order to start,  open SSMS.
  7. Open the SSAS Server and browse the Adventureworks cube.

    ssas browse cube

  8. Now, drag and drop the Internet Gross Profit as shown below, so we can generate a MDX query.

    ssas create query

  9. The query would be the following:
     
    SELECT NON EMPTY { [Measures].[Internet Gross Profit] } ON COLUMNS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    ssas see MDX

  10. Save the query in a file named Adventure.mdx in the scripts folder.
  11. Now we are going to call a PowerShell script to invoke the query. If there are no results or an error message related to the cube is not processed, a connection error message will be displayed.
     
    $result=Invoke-ASCmd  -Database "Adventureworks"  -InputFile:"c:\scripts\Adventure.mdx"
    $Message="Adventureworks had a connection error"
    if ((!$result) -or ($resultado -like  "*either does not exist or has not been processed*")){$message} 
  12. In order to run PowerShell commands on a schedule, in SSMS create a new job by right clicking in jobs:

    create job

  13. Give the job a name and click on the Step page. Click the New button in order to create a new step. 

    Click the New button in order to create a new step

  14. Specify a step name. In the Type select PowerShell and in Command copy and paste the command from Step 11 and then press OK.

    In the Type select PowerShell and in Command copy and paste the command of step 11

    Now select the Schedules page and press the New button.

    select the schedule page

    Enter the required schedule information. In this example we are creating a daily schedule that runs every hour to verify that the database is up.

    In this example we are creating a daily schedule every hour to verify that the database is up

That is all folks. You now have a script to verify the SSAS Database status every hour. I hope you enjoyed this article. If you have more questions about PowerShell, write in the comments below and we may create an article related to your question.

Next Steps


Last Updated: 2013-07-09


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, January 05, 2017 - 10:04:39 AM - Daniel Calbimonte Back To Top

 

What is your problem when you execute the sp_send_dbmail?


Thursday, January 05, 2017 - 12:08:59 AM - Vivek Singh Back To Top

 Hi,

Thank you so much for sharing such a useful article.

The script will help us to veirify the status of the cube but how do we use EXEC msdb.dbo.sp_send_dbmail to notifiy users over the email.

Please help me. 

 

Thanks,

Vivek Singh


Monday, March 14, 2016 - 2:42:36 PM - Scott Back To Top

Update your script to work correctly I would recomment this.

 

 

Import-module sqlascmdlets

$result=Invoke-ASCmd  -Database:"BSIGCubes"  -InputFile:"D:\CubeValidation\ValidationScripts\PolicyInfoCubeCheck.mdx"

$Message="PolicyInfoCube had a connection error"

if((!$result) -or ($result -like '*either does not exist or has not been processed.*')){Throw $Message}


Sunday, July 14, 2013 - 1:18:28 PM - John R Back To Top

THX for the tip!

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools