By:Daniel Calbimonte || Related Tips: > Analysis Services Administration

Problem

I would like to know if there are new PowerShell features in SQL Server 2016 related to Tabular Databases.

Solution

There are new PowerShell features for Tabular Databases in SQL Server 2016 and in this tip we will cover the following:

How to use the Invoke-ProcessASDatabase cmdlet How to execute TMSL with Invoke-ASCmd How to automate Tabular Database tasks with PowerShell using SQL Server Agent How to use Invoke-ProcessTable Requirements SQL Server 2016 SSDT installed How to use the Invoke-ProcessASDatabase cmdlet

We will start with the Invoke-ProcessASDatabase cmdlet. This cmdlet is for Tabular and Multidimensional Databases. We will see how to use it in Tabular Databases.

Start PowerShell.


New features for SQL Server Analysis Services Tabular Databases in PowerShell

In PowerShell, go to sqlserver and change directories to the tabular databases:

sqlserver:
cd '\sqlas\YourInstanceName\tabular\databases'

The following command will list the name of the Tabular databases, the compatibility level and the last time the database was processed:

ls | select name, compatibilitylevel, lastprocessed
New features for SQL Server Analysis Services Tabular Databases in PowerShell

If the compatibility level is 1200 there is a new parameter we can use named RefreshType. This is used in Tabular databases to show the Process Type for this new level. Some possible values for this parameter are:

Full ClearValues Automatic Defragment

Use the following command to process the Tabular Database:

Invoke-ProcessASDatabase "TabularProject7" -RefreshType "Full""

This is the result if everything is OK:


New features for SQL Server Analysis Services Tabular Databases in PowerShell
How to execute TMSL with Invoke-ASCmd

Invoke-ASCmd now supports the Tabular Model Scripting Language (TMSL) . This is a new language introduced and supported in SQL Server 2016. It is simpler than XMLA (the old DDL language used) and it is based onJSON.

We will show how to execute a JSON file using Invoke-ASCmd.

First, we will create a TMSL script named sample.json in the c:\sql folder:

{
"alter": {
"object": {
"database": "TabularProject7"
},
"database": {
"name": "mssqlTIPS",
}
}
}

The script changes the tabular database name from TabularProject7 to MSSQLTIPS. With TMSL, you can process, backup, alter, create and drop Tabular Objects.

In order to execute the JSON script, run the following commands in PowerShell:

Invoke-ASCmd InputFile "C:\sql\sample.json" -Server "YourInstanceName\TABULAR"

Invoke-ASCmdis used to run a command for Analysis Services and InputFile is used to specify the JSON script and Server is used to specify the instance.

The results displayed will be similar to this:


New features for SQL Server Analysis Services Tabular Databases in PowerShell

If everything is OK, the Tabular Database name will be changed to MSSQLTIPS:


New features for SQL Server Analysis Services Tabular Databases in PowerShell
How to automate a Tabular Database task with PowerShell using SQL Server Agent

We will useSQL Server Agent to automate tasks in PowerShell for Tabular Databases. In this example, we will create a database backup in SQL Server Agent using PowerShell.

In SQL Server Management Studio (SSMS), select the database to backup, right click and select Back Up...:


New features for SQL Server Analysis Services Tabular Databases in PowerShell

Unselect the Encrypt backup file option to simplify the backup process ( encrypting the backup is a best practice, but it is not part of this tip):


New features for SQL Server Analysis Services Tabular Databases in PowerShell

Press Script to generate a script and select Script Action to New Query Window :


New features for SQL Server Analysis Services Tabular Databases in PowerShell

The script generated will be in JSON format:

{
"backup": {
"database": "MSSQLTIPS",
"file": "MSSQLTIPS.abf",
"allowOverwrite": false,
"applyCompression": true
}
}

We will modify the path to store in our SQL folder. By default, the tabular backups are stored in a path similar to this: C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Backup, but we changed to c:\\sql . Notice the use of double backslashes.

Save the script in a file named backupTabular.json :

{
"backup": {
"database": "MSSQLTIPS",
"file": "c:\\sql\\MSSQLTIPS.abf",
"allowOverwrite": false,
"applyCompression": true
}
}

In SSMS, connect to your SQL Server instance and under SQL Server Agent > Jobs, right click and select New Job... :


New features for SQL Server Analysis Services Tabular Databases in PowerShell

Specify a name for the job and optionally enter a description:


New features for SQL Server Analysis Services Tabular Databases in PowerShell

Go to Steps and press New... to create a new step:


New features for SQL Server Analysis Services Tabular Databases in PowerShell

You could run a TMSL script using the SQL Server Analysis Services Command in SQL Server 2016, which is a new feature:


New features for SQL Server Analysis Services Tabular Databases in PowerShell

However, in this tip we will run PowerShell. Select PowerShell as the Type and enter the following commands in the Command section of the interface:

sqlserver:
Invoke-ASCmd InputFile "C:\sql\backupTabular.json" -Server "YourServer\YourInstance"

The script will backup the tabular database using the backup script generated before.


New features for SQL Server Analysis Services Tabular Databases in PowerShell

By default, the job will run with the SQL Server Agent account. This account may not have permission to create a file in the folder specified, so you may need to use a proxy account that has the necessary permissions. For more information about proxies and credentials refer to this tip about using proxies . To create a credential, in the Database Engine, go to Security > Credential and assign a windows user with permissions to the folder where the backup will be created:


New features for SQL Server Analysis Services Tabular Databases in PowerShell
To

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

主题: SQLPowerShellSQL ServerXMLWindowsTISAS
分页:12
转载请注明
本文标题:New features for SQL Server Analysis Services Tabular Databases in PowerShell
本站链接:http://www.codesec.net/view/481974.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(39)