Rajendra Gupta
AWS RDS SQL Server dashboard

Deploy tabular databases in SSAS on AWS RDS SQL Server

June 1, 2020 by

In this article, we are going to explore Analysis Service for AWS RDS SQL Server in detail.

Amazon offers various database services to support completely managed database infrastructure. We can deploy RDS for SQL Server, but it supports only database service until now. If we look at the relational database Microsoft SQL Server, it offers the following useful services as well:

  • SQL Server Analysis Service – SSAS
  • SQL Server Reporting Service – SSRS
  • SQL Server Integration Service – SSIS

But RDS limitation to database service was stopping a few users from migrating on it. Recently AWS announced that we can now run the Analysis Service on AWS RDS SQL Server as well.

Prerequisites

Supported editions

RDS supports SQL Server Analysis Service on the following SQL Server editions:

  1. SQL Server 2016 Standard or Enterprise edition (minimum version:13.00.5426.0.v1)
  2. SQL Server 2017 Standard or Enterprise edition(minimum version:14.00.3223.3.v1)

Follow the article AWS RDS SQL Server – Launching a new database instance to launch a new AWS RDS SQL Server instance.

You can verify the SQL instance edition in the RDS dashboard, as shown below:

AWS RDS SQL Server dashboard

Domain Joined RDS instance

Join this RDS instance with an Amazon managed Active directory.

Configure EC2 instance as a member of Domain

Create an EC2 instance, and it should be part of the same active directory.

For requirement 2 and 3, follow the articles below:

Once you follow steps 2 and 3, you can use the Windows authentication to connect to AWS RDS SQL Server:

Connect using Windows authentcation

Integrate an Amazon S3 bucket with AWS RDS SQL Server

We require an S3 bucket to transfer tabular database models, backups between the AWS instance and S3 bucket. I have already associated the SQLshackdemo bucket with my RDS instance.

In the below image, we can see the sqlshackrole role associated with the RDS instance for the S3 integration feature. This role should have permissions to do tasks in the S3 bucket for RDS instance.

Integrate an Amazon S3 bucket

Refer to the article Integrating an AWS RDS SQL Server with Amazon S3 bucket to go through the integration process.

SSMS installation

Launch EC2 instance and install SQL Server Management Studio to connect with RDS DB and Analysis Service instance

Visual Studio with SQL Server Data Tools (SSDT)

We require Visual Studio 2019 along with SQL Server data tools for creating database projects. You can install Visual Studio in the EC2 instance and enable the extension for the Analysis Service project.

Refer to the article Download SQL Server Data Tools (SSDT) for Visual Studio for more details.

Note: Complete all prerequisites specified so far in this article to go further.

Enable SSAS on the AWS RDS SQL Server

SQL Server Analysis Service is an Online analytical processing (OLAP) application. In a traditional SQL Server, we get two options to choose, Tabular or Multi-dimensional. RDS supports the tabular model.

To enable the Analysis Service in RDS, we need to add an option group in the RDS instance. In the RDS dashboard, click on Options groups. You can see existing options groups as well on this page.

Click on Create group to configure a new option group:

Create a new Option group for RDS

Create a new Option group

On this Create option group page, enter the name and description. We also need to select the SQL engine (standard, enterprise, express) along with the SQL version:

Fill details

It creates the option group, but it is not configured for any services yet. We need to select the newly created option group and click on Add option:

Add SSAS in the option group

Add SQL Server Analysis Service in the option group

In the option details page, select the SSAS option from the drop-down. It gives you brief information that this option enables SQL Server Analysis Services on the DB instance.

Analysis Service runs on port 2383, so this port should be allowed in the security groups (AWS firewall) from the EC2 instance. Refer to article security groups for your VPC for more detail.

We also need to configure the Max memory for the Analysis Service. You might be aware that Analysis Service is a memory-intensive application, and you might do bulk data processing, so max memory should be configured appropriately. Analysis Service instance runs on the same RDS database, so if we specify a high memory, it might impact memory available for RDS instance. I allocated 50% memory for the SSAS instance.

Option SSAS for Analysis Service

The RDS instance and options group must remain in the same VPC. You can verify the RDS instance VPC in the connectivity & Security tab:

Check VPC

Select the RDS instance VPC (in my case vpc-9c710fe6) in the security groups as shown below:

RDS instance VPC

Scroll down, and you can schedule to add the option group immediately or on next scheduled maintenance window:

schedule changes

It adds the SSAS in the newly created option group, and you get a message for it as well:

adds the SSAS in the newly created option group

Modify RDS instance to use SSAS option group

Now, lunch SSMS in EC2 instance and try to connect the Analysis Services using the RDS endpoint. We get the error message that connection did not succeed:

Modify RDS instance for SSAS

The above error comes because we have not assigned an option group for the RDS instance. Click on modify on RDS instance and select the option group (in my case SSASforRDS) we created earlier:

Database option group

We can view the modification summary for the option group in the below image. Apply the changes immediately. It restarts the RDS instances and enables Analysis Services for the selected SQL instance:

Summary of modification

Now, connect to SSAS service again from the EC2 SSMS using Windows authentication. We do not see any database in this instance by default:

Connect to SSAS

Right-click on the connected instance and view the properties of the Analysis Server. You can see its hostname, Server mode, version, and supported compatibility level:

Analysis Server

Create an Analysis Service tabular project for RDS

We will create a sample project for this demonstration. We already installed Visual Studio for creating projects. Go to start and launch Visual Studio 2019 and create a new project for Analysis Service tabular database:

tabular project for RDS

Specify a project name, solution name, and the directory that holds all the required project files:

Configure project

Click on Create. In the next tabular model designer, select the option Integrated workspace:

Integrated workspace

It creates a solution and project. Right-click on the project solution and view its properties. In the processing option, select the value Do Not Process:

You can also view the database name TestProject in the below screenshot. We require this database name in the following SQL queries:

Project properties

In the Visual Studio, go to Build > Build Solution:

Build Solution

Now, go to the directory that we specified earlier while creating the project. Here, we see the project solution file and a folder holding project files:

View project files

Open the folder, and you can see two files highlighted below:

  • Model.asdatabase
  • Model.deploymentoptions

Deployment files

Open the S3 bucket that is integrated with AWS RDS SQL Server and upload the files into S3:

Upload to S3

We use the msdb.dbo.rds_download_from_s3 stored procedure to download the project files from the S3 bucket to the RDS instance. Connect to the AWS RDS SQL instance in the SSMS and execute the following queries. In these queries, we specified the project file name that we uploaded in the S3 bucket:

It starts the tasks to download the files from the S3 bucket to the RDS instance folder:

Upload file t o RDS instance

We can check the status of the tasks using the stored procedure msdb.dbo.rds_fn_task_status using the task ID we got earlier. The first parameter is always a NULL value:

We can see that both tasks completed successfully:

RDS Task status

Now, we are ready to deploy the tabular project in the RDS instance. It uses the msdb.dbo.rds_msbi_task stored procedure with the following arguments:

  • @task_type: SSAS_DEPLOY_PROJECT
  • @file_path: It is the RDS instance project path. It always uses directory D:\S3

It also starts a task for the RDS instance. Once we track it using the msdb.dbo.rds_fn_task_status, its status shows success after some time. It might take longer, depending on the project:

Task status

We also need to give permissions to the user that connects with the AWS RDS SQL Server Analysis Service. To do so, we use the stored procedure msdb.dbo.rds_msbi_task with the following arguments:

  • @task_type: We need to specify SSAS_ADD_DB_ADMIN_MEMBER in this argument
  • @Database_name: it is the database name that we specified while creating the Visual Studio project
  • @ssas_role_name: Specify a role name to create in the Analysis Service database
  • @ssas_role_member: Specify the Windows user that we use to connect RDS using Windows authentication

We can see this stored procedure successfully added specified user in the specified role:

Add users in the SSAS role

Now, refresh the Analysis Server connection in SSMS. We can see the TestProject database in the Analysis Service of AWS RDS SQL Server:

View tabular database

Conclusion

In this article, we explored that AWS RDS now supports SQL Server Analysis Service as well. We can configure it for RDS instance and deploy projects using Visual Studio, and RDS stored procedures. We deployed a primary tabular database in this demonstration.

Follow the article Creating your first SSAS tabular model database to create a tabular model database in RDS.

Rajendra Gupta
142 Views