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
- The Adventureworks Multidimensional project.
- SQL Server 2008 or later (in this example I am using SQL Server 2012).
Getting started
- Let's start verifying the Windows Services Status with PowerShell:
The command get-service, obtains the list of windows Services and their status:
- In this case we are interested in the Analysis Services status, so the command will be:
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.
- Now we are going to send a message if the SSAS Service is stopped.
The command shows a message saying that the service is down if the Analysis Services Service is stopped.
- The following tip shows how to see the space available in the hard disks.
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.
- 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.
- In order to start, open SSMS.
- Open the SSAS Server and browse the Adventureworks cube.
- Now, drag and drop the Internet Gross Profit as shown below, so we can generate a MDX query.
- The query would be the following:
- Save the query in a file named Adventure.mdx in the scripts folder.
- 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.
- In order to run PowerShell commands on a schedule, in SSMS create a new job by right clicking in jobs:
- Give the job a name and click on the Step page. Click the New button in order to create a new step.
- Specify a step name. In the Type select PowerShell and in Command copy and paste the command from Step 11 and then press OK.
Now select the Schedules page and press the New button.
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.
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