SQL Server Analysis Service (SSAS) Virtual Machines in Azure — For MultiDimensional and Tabular Models
A few days back (Tuesday, Nov 10th, 2020) Microsoft announced new Analysis Services VM images for SQL Server 2019 (both Standard and Enterprise edition image types). The images are generally available with pay-as-you-go licensing only.
Standard Image:-This image contains the Standard edition of SQL Server 2019 Analysis Services engine only on Windows Server 2019 and provides core data analysis capabilities for medium-size workloads. It includes the Analysis Services engine with basic support for multidimensional and tabular modes. Includes Management Studio for integrated administration and development.
Enterprise Image:- This image contains the Enterprise edition of the SQL Server 2019 Analysis Services engine only on Windows Server 2019 and provides the richest set of data analysis capabilities for enterprise-size workloads. It includes the Analysis Services engine with full support for multidimensional and tabular modes, including query scale-out and high availability. Includes Management Studio for integrated administration and development.
Now let’s see how we can create an Analysis Service VM in Azure
Create SQL Server 2019 Analysis Service Virtual Machine
- Open the Azure portal and click on +Create Resource
- Search for Analysis Service and Select SQL Server 2019 Analysis Services
- Select the plan and click Create
- Availability options → Availability Zone and Availability Sets are available, choose the option based on your needs.
- Select the right server image
- Select the right size for your needs (below are different VM sizes that Azure offers)
- D-Series v4 →The latest generation D family sizes recommended for your general-purpose needs
- B-SeriesIdeal for workloads that do not need continuous full CPU performance
- A-Series v2 →Best suited for entry-level workloads (development or test)
- E-Series v4 →The latest generation E family sizes for your high memory needs
- F-Series v2 →Up to 2X performance boost for vector processing workloads
- L-Series v2 →High throughput, low latency, directly mapped to local NVMe storage
- D-Series v3 →The 3rd generation D family sizes for your general-purpose needs
- E-Series v3 →The 3rd generation E family sizes for your high memory needs
- D-Series v2 →The 2nd generation D family sizes for your general-purpose needs
- For Inbound Port, select RDP (and other ports based on your needs) if you want to remote into the machine and then click create
- The Analysis Service VM is now ready,
Connect to the Analysis Services from SQL Server Management Studio
- Hope the SQL Server Management Studio is already installed on the VM, let connect to it and find out
- Yes, SSMS is already installed and available,
- Now let’s open SSMS and connect to the SSAS services
- Select the default Server and click connect
- Great!!! the Analysis Service is already installed and ready for use, but the default Server mode is Multidimensional, which means it will support only Multidimensional cubes but not Tabular Models ..UGH!
- Don’t worry, we can install Tabular Mode using the SQL server installer
Install the Tabular Instance in the Analysis Service VM
- Open SQL Server Installer Center and click on New standalone installation or add features
- Select Analysis Service and Click Next
- Give the Instance a Name and click Next
- Select the Tabular Mode and Click Next
- Follow the instructions and install the Tabular Instance
- Now connect to the Tabular Instance from the SSMS
- Select the new Tabular Instance from the Server dropdown list and click Connect
- Now \the Analysis Server is ready to deploy both Multidimensional Cubes and Tabular Models
Next Article — How to deploy Multidimensional Cubes and Tabular Models to the SQL Server 2019 Analysis Service VM. until then happy learning.
