How to deploy SQL Server Analysis Services on RDS Custom in a new VPC environment

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service designed to support custom or packaged applications that require access to the underlying operating system and database (DB) environment. Amazon RDS Custom for SQL Server also automates setup, operation, and scaling of databases in the cloud, while granting access to the database engine and underlying operating system. This allows customers to configure specific settings, install drivers, and activate native features to meet your application’s requirements.

A common use case for Amazon RDS Custom for SQL Server is to offload the undifferentiated heavy lifting of managing the underlying infrastructure of the cluster when running SQL Server Analysis Services (SSAS). SSAS is an analytical data engine, based on the VertiPaq technology which is used in decision support and business analytics.

In this post, we explain how to launch and setup Amazon RDS Custom for SQL Server and enable SSAS to run either Tabular or Multi-Dimensional model modes.

Solution overview

Here’s what we are going to perform at a high level:

1. Using Remote Desktop Protocol (RDP), connect to the Amazon Elastic Compute Cloud (Amazon EC2) instance created at the prerequisites step above.
2. Use SQL Server Management Studio (SSMS) to connect to Amazon RDS Custom for SQL Server from a Windows Amazon EC2 instance.
3. Configure a Remote Desktop Protocol (RDP) connection to the RDS Custom for SQL Server instance from the Windows Amazon EC2 instance.
4. RDP into the RDS Custom instance, start the SQL Server Analysis Services (SSAS) service, and set it to be automatically enabled.
5. (Optional) Alter configuration settings in msmdsrv.ini, if you need to enable SSAS to be set into the multi-dimensional model.

Prerequisites

Before configuring SSAS, you must setup and prepare your Amazon RDS Custom environment by following the steps listed in this post: Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template.

Also, we assume that you have basic knowledge of the following prerequisites:

• Previous experience in administration of SSAS and SQL Server.
• You can find more information on how to administer SQL Server Analysis Services overview.

For more details, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.

Walkthrough

RDP into Amazon EC2 instance

The following steps explain how to RDP into the Amazon EC2 instance. We use that instance as a bastion host or jump box to allow us to access the Amazon RDS Custom instance, which runs in a private subnet.

1. On the Amazon EC2 console, navigate to the Amazon EC2 instance created through the steps described in the Prerequisites section. The instance should be named ec2-windows-test-node
2. Select the ec2-windows-test-node instance again and choose the Security tab
3. Select the Security Group assigned to the instance. On the Inbound rules tab choose Edit inbound rules
4. To gain access to the instance, configure the rule to allow RDP access with your own IP as a source. Choose My IP in the Source field and select Save rules.
   
   
5. Also, make sure you configure the Network Access Control List (NACL) associated with the public subnet to allow your source IP address in the inbound and outbound rules.
6. Go back to the Amazon EC2 console and select instance ec2-windows-test-node again.
7. Choose Connect and choose RDP client.
8. Select Connect using RDP client and select Get password.
9. Specify the key pair you used to create the instance and select the Decrypt password.
10. On the RDS client tab, choose Download remote desktop file, and select Cancel.
    
    
11. Open the file and use the password acquired on the previous step to access the instance
12. Once on the instance, search for SSMS and open the SQL Server Management Studio application

Connect to RDS Custom

To connect to RDS Custom from SSMS, enter the RDS Custom for SQL Server endpoint, login, and password details that were specified when you created the RDS Custom for SQL Server instance and choose Connect

Configure RDP for RDS Custom

Complete the following steps to set up Windows Firewall to allow the RDP connection:

1. On the Amazon EC2 console, search for the underlying Amazon EC2 instance of the RDS Custom for SQL Server instance. This Amazon EC2 instance is created with the naming standard do-not-delete-<rds-custom-sqlserver-instance-name>.
2. Select this instance and choose Connect.
3. On the Session Manager tab, choose Connect. A separate Session Manager window pops up.
   
4. Run the following command to set up a firewall rule to allow the RDP connection. (Optional Multi-AZ) If Multi-AZ is enabled, then ensure to run on both “do-not-delete” provisioned instances.

Set-NetFirewallRule -DisplayName "Remote Desktop - User Mode (TCP-In)" -Direction Inbound -LocalAddress Any -Profile Any

To connect through RDP, we need to retrieve a remote desktop file and with the login credentials. Similar to what we did for the Amazon EC2 instance, we use the key pair to retrieve the credentials. Complete the following steps to decrypt the password:

5. Go back to the Amazon EC2 tab and select the RDP client tab.
6. Choose Get password and Upload private key file.
7. Choose the key pair associated with this instance and choose Decrypt password.
8. To get the key, open Secrets Manager in a separated browser tab, select the secret starting with do-not-delete-rds-custom and select Retrieve secret value.
   
   
9. Copy the private key content and paste it into the Private key contents – optional field.
   
10. Save password, Username, and Private IP.
11. From the Amazon EC2 instance, search for RDP, open it, and use the above information to connect to the Amazon RDS Custom instance

You are now connected to the RDS Custom instance and can access the Operating System files, as shown in the previous diagram. You may now proceed with the activities related to SQL Server Analysis Services.

Setup and start SSAS service

RDS Custom for SQL Server comes installed with the database engine and all the other Business Intelligence (BI) services, where the database engine and the full-text service are running by default:

The following are steps to setup and start the SSAS service:

1. After connecting through RDP to the RDS Custom server OS, select the Start button, then search and select SQL Server Configuration Manager.
   
   
2. Once opened, right click on SQL Server Analysis Services (MSSQLSERVER) and select Properties.
   
3. Ensure that you choose any Built-in account and click Start.
   
   
4. Open SSMS and select server type as Analysis Services.
   
5. Ensure to change your DataDir, BackupDir, LogDir and TempDir values to any folder in the D:\rdsdbdata directory. For example, I’ve created a top folder called D:\rdsdbdata\SSAS for all sub-folders needed.
   
   
6. (Optional Multi-AZ) If Multi-AZ is enabled, then initiate a failover by rebooting instance and repeat steps 1-5 from “Setup and start SSAS service”.
   
7. (OPTIONAL Host Replacement) Ensure to repeat steps 1-6 of “Setup and start SSAS service” in case of host replacement.
   

You should now be able to connect using SSMS and choose SSAS as its desired service

Optional – Configure multi-dimensional model

RDS Custom comes installed with SQL Server and all the other necessary services, where the database engine is the only one running by default. In addition, SQL Server Analysis Services comes setup as a Tabular model. To determine which service is used, connect to SSAS using SSMS, right click at the server level to see the SSAS server properties. Choose Information and you’ll see that its mode is set to Tabular.

Depending on your requirements, you may need to deploy your database as a Multidimensional Online Analytical Processing (MOLAP) workload.

NOTE 1: This is a one-way change, and cannot be reverted.

The following are the necessary steps to switch SSAS from a tabular model to a multi-dimensional (MOLAP) model:

1. Select the Start button, then search and select SQL Server Configuration Manager. Once opened, right click on SQL Server Analysis Services (MSSQLSERVER) and choose Stop.
   
   
2. Navigate to C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config. Right click on file msmdsrv.ini and select Copy and then Paste. This creates a file called msmdsrv - Copy.ini.
   
3. Choose the Start button, then search for Notepad. Right click and ensure that you Run as administrator.
   
4. Once opened, choose File and Open and make sure that you have the file extension set to All Files. Navigate to C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config and select msmdsrv.ini.
5. Change the DeploymentMode property from 2 (tabular) to 0 (multidimensional) as shown in the following diagram. Save and close the file.
   
6. Back on SQL Server Configuration Manager, right click on SQL Server Analysis Services (MSSQLSERVER) and choose Start.
7. (OPTIONAL Multi-AZ) If Multi-AZ is enabled, then ensure to failover by rebooting instance and repeat steps 1-6 from “Optional – Configure multi-dimensional model”.
8. (OPTIONAL Host Replacement) Ensure to repeat steps 1-7 of “Optional – Configure multi-dimensional model” in case of host replacement.

After SSAS is up-and-running, connect using SSMS and you’ll now see that it is running in Multi-dimensional mode.

Cleaning up

If the resources provisioned during this setup are no longer required, then you can remove them by deleting the AWS CloudFormation stack. To do that, go to the AWS CloudFormation console, select the stack name, and choose Delete. Click Delete again to confirm.

Conclusion

In this post, we showed you how to use Amazon RDS Custom for SQL Server to run SQL Server Analysis Services in a new Virtual Private Cloud (VPC) environment. RDS Custom for SQL Server allows businesses to run SSAS environments, while providing the time-savings, durability, and scalability benefits of a managed database service. RDS Custom provides access to the underlying operating system to perform the specific configurations required by SSAS.

Try out the solution and if you have any comments or questions, leave them in the comments section.

About the Authors

JP Santana is a Principal Startup Solutions Architect for AWS. He lives with his wife and three children in Minneola, FL. He has worked with IT for over 23 years and for AWS for over 8 years. At AWS, he is a generalist SA with a strong background in container technologies. In his spare time, he enjoys spending time with his family, studying Catholic theology, and rolling on Brazilian Jiu Jitsu mats.

Alvaro Costa-Neto is a Database Specialist Solutions Architect for AWS, where he helps customers design and implement database solutions on the cloud. He has a passion for database technologies and has been working with them for more than 19 years, mostly with Microsoft SQL Server. He resides in Clermont, FL with his wife and two children, who share his love for aviation and traveling. When he is not working, he likes to host cookouts with his family and friends and explore new places.