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.
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
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:
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 on JSON.
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-ASCmd is 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:
If everything is OK, the Tabular Database name will be changed to MSSQLTIPS:
How to automate a Tabular Database task with PowerShell using SQL Server Agent
We will use SQL 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…:
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):
Press Script to generate a script and select Script Action to New Query Window:
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…:
Specify a name for the job and optionally enter a description:
Go to Steps and press New… to create a new step:
You could run a TMSL script using the SQL Server Analysis Services Command in SQL Server 2016, which is a new feature:
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.
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:
To assign a credential to a proxy, go to SQL Server Agent > Proxies and assign the credential created above for the proxy:
Return to the SQL Server Agent Job and in the SQL Job Steps, in the Run as, use the proxy that you just created and save the job.
To run the job, right click the job and select Start Job at Step…:
If everything is OK, the job will create a backup named MSSQLTIPS.abf file in the specified folder:
SQL Server Agent does not show the detail errors when running PowerShell scripts. If you have errors when running the job, use PowerShell to run the job to see if it works. If it works, but fails in SQL Server Agent the problem might be the job permissions.
How to use Invoke-ProcessTable
To process Tabular Tables, there is a new cmdlet named Invoke-ProcessTable. In the past, to process tables we used the Invoke-ProcessDimension. Now there is an exclusive cmdlet for Tabular Tables.
The following example shows how to process a Tabular Table:
Invoke-ProcessTable -TableName "DimCustomer" -Database "TabularProject8" -RefreshType "Full"
Where DimCustomer is the Tabular Table and TabularProject8 is the database. We are doing a Process Full.
Conclusion
In this tip, we learned how to use the Invoke-ProcessASDatabase cmdlet, how to run TMSL using Invoke-ASCmd, how to automate Tabular Tasks using SQL Server Agent and PowerShell and how to process a Tabular table.
Next Steps
- For more information, refer to the following links:
