Pages

Men

rh

7/09/2013

Using PowerShell for SSAS to check service status and database availability

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.


Source Collected from MSSQLTIPS.COM

No comments :

Post a Comment