[Translation] Play powershell in SSAs (3)

The first section briefly introduces powershell, including some basic commands, and how to open powershell and navigate to the SSAS object. The second article explains how to use variables to create SSAs backups based on the current date and how to run the MDX and xmla scripts.

 

Original article address:

Http://www.mssqltips.com/sqlservertip/2980/using-powershell-for-ssas-to-check-service-status-and-database-availability/

 

 

This article mainly introduces the following topics:

-Run the powershell command to verify the status of the SSAS service.

-If the SSAS service fails to send messages automatically

-Verify the hard disk usage of the SSAS Database

-Run scripts every hour to verify the database status

 

Environment:

Adventureworks Microsoft sample project.

Versions later than SQL Server 2008.

 

Start:

1. First, use powershell to get the Windows Services status.

Get-Service

This command returns the current Windows Services and its status:

2. In this article, we are concerned with the SSAS service, so the command will be as follows:

Get-service | select status, name | where-object {$ _. Name-like "* mssqlserverolap *"}

This command displays the service name and service status of "mssqlserverolap". You can see whether the service is enabled or stopped.

3. The message will be automatically sent if the 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

}

If the SSAS service is stopped, the message "The SSAS service is down" is displayed ".

4. The following command shows the remaining disk space.

$ Driveinformation = gwmi win32_volume-filter 'drivetype = 3' | select driveletter, label, @ {label = 'gbfreespace'; expression ={$ _. freespace/1 GB }}

This command shows the remaining space of each disk in GB. This command is useful when we need to know whether SSAs has enough space.

5. to verify the status of the cube in SSAs, an MDX statement can be sent every five minutes. In this article, we will create a simple MDX script and run it hourly to verify that the cube is online.

6. Open SSMs.

7. Connect to the SSAS service and browse the adentureworks cube.

8. Drag Internet gross profit to the pivot table area and switch to the MDX view.

9. The automatically generated query is as follows:

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

10. Save the script as the adventure. MDX file.

11. Use the powershell script to call this query. If no result is returned or an unprocessed error message is returned, the connection error message is automatically returned.

$ 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. To enable the powershell script to run periodically, create a job in SSMs:

13. Give the job a name and create a new step.

14. Specify a step name, select powershell under type, paste Step 1 script in the command area, and then click OK.

Select the schedules page and click New.

Enter the schedule information. Here we set it to Run hourly to verify the database status.

 

So far, a job that verifies the status of the SSAS database is created every hour. I hope you will like this article.

 

Related content:

Powershell is a good tool for creating automated tasks. For more information, see the following link:

Http://technet.microsoft.com/en-us/library/hh849804.aspx

Http://technet.microsoft.com/en-us/library/ee177028.aspx

Http://blogs.technet.com/ B /flaphead/archive/2006/09/12/455555.aspx

Read more about powershell on mssqltips.com.

Http://www.mssqltips.com/sql-server-tip-category/81/powershell/

[Translation] Play powershell in SSAs (3)