Enable Microsoft SQL Server Business Intelligence features (SSAS, SSRS, SSIS)

on Amazon RDS for SQL Server

SQL Server is a relational database management system developed by Microsoft. Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the cloud.

In this tutorial, you learn how to enable Microsoft SQL Server Business Intelligence features such as SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS) on Amazon RDS for SQL Server, at no additional cost, using the Amazon RDS for SQL Server Parameter Groups and Option Groups

Amazon RDS for SQL Server frees you up to focus on application development by managing time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling. If you are currently running SSAS, SSIS, or SSRS on Amazon Elastic Compute Cloud (Amazon EC2), you can now save costs by running any or all of these features directly on the same Amazon RDS database instance as your SQL Server database.

Prerequisites

This tutorial requires that you to have the following resources in place:

  • Amazon RDS for SQL Server database instance - Create an instance with SQL Server 2016 Standard or Enterprise edition or SQL Server 2017 Standard or Enterprise edition. For instructions, see Create and connect to a Microsoft SQL Database with Amazon RDS.
  • AWS Managed Microsoft Active Directory - Join the Amazon RDS for SQL Server databaase instance to AWS Managed Microsoft Active Directory to enable Windows Authentication. For instructions, see Setting Up Windows Authentication for SQL Server DB Instances.
  • SQL Client Application - you can use any standard SQL Client Application running on an Amazon EC2 instance to connect to the database instance.

The default parameter group and option group are not modifiable. In this tutorial, you will create new parameter group and option group to enable all three individual features – SSAS, SSRS, and SSIS. This tutorial configures resources for SQL Server Enterprise Edition and major version 14.00. The same steps can be followed for the Standard Edition.

About this Tutorial
Time 10 minutes      
Cost Less than $1
Use Case Databases
Products Amazon RDS for SQL Server
Audience Database administrators, Developers
Level Intermediate
Last Updated December 21, 2020

Step 1: Create security group and configure inbound rules

In this step, you configure the inbound rules of your security groups, that allows traffic from EC2 instance to the RDS database instance. You can use the default security group which allows all the traffic. However, as a security best practice it is always recommended to limit the security groups to allow only relevant traffic. For this tutorial, you create three rules to allow inbound traffic to the RDS instance.

1.1 — Open the Amazon VPC dashboard and sign in with your AWS account credentials. If you do not have an AWS account, create a new one to get started.

Already have an account? Log in to your account


1.2 — Choose the Region drop-down and select the AWS Region where your want to create your resources. This tutorial uses the US East (N. Virginia) Region.


1.3 — In the left navigation pane, choose Security Groups and then choose Create security group. On the Create security group page, fill the fields as follows:

  • Security group name: RDS SQL Server Tutorial
  • Description: Server to DB
  • VPC: Choose the VPC containing your Amazon RDS for SQL Server instance.

Then, choose Create security group.


1.4 – Choose Edit inbound rules section, choose Add rule and add the following inbound rules:

  • Rule 1: MSSQL
    • Type: MSSQL
    • Port range: 1433
    • Source: Search for RDS SQL Server Tutorial security group
    • Description: MSSQL Traffic
  • Rule 2: SSRS Traffic
    • Type: Custom TCP
    • Port range: 11443
    • Source: Search for RDS SQL Server Tutorial security group
    • Description: SSRS Traffic
  • Rule 3: SSAS Traffic
    • Type: Custom TCP
    • Port range: 2383
    • Source: Search for RDS SQL Server Tutorial security group
    • Description: SSAS Traffic
Then, choose Save rules.

Step 2: Configure Amazon RDS option group for business intelligence features

Some database engines offer additional features that make it easier to manage data and databases, and to provide additional security for your database. Amazon RDS uses option groups to enable and configure these features, and provides a default option group for each new database instance. You cannot modify this default option group, but any new option group that you create derives its settings from the default option group. For more information, see Working with Option Groups.

For this step, you create a new option group for the business intelligence features, then add three options to the option group for SSAS, SSRS, and SSIS.

2.1 — In the left navigation pane of the RDS console, choose Option groups, then Create group.


2.2 – On the Create option group page, fill the following fields.

  • Name: sql-server-bi-features
  • Description: To enable SSAS, SSRS, SSIS features.
  • Engine: sqlserver-ee
  • Major Engine Version: 14.00

Then, choose Create.


2.3 – In the Option groups list, choose your sql-server-bi-features option group and choose Add option.


2.4 — On the Add option page, make the following selections to enable SSAS:

  • For Option name, choose SSAS.
  • For Max memory, specify 15.
    Note: This parameter specifies the upper memory percentage threshold that your SSAS occupies on Microsoft SQL Server instance. When setting the max memory, consider the amount of memory available on the Amazon RDS DB instance. SSAS Tabular is a memory-intensive application. If you choose a higher max memory allocated to SSAS, you potentially impact SQL Server operations residing on the same instance.
  • For Security groups, choose the RDS SQL Server Tutorial security group you created in Step 1.
  • For Scheduling, choose Immediately.

Then, choose Add option.


2.5 — In the Option groups list, choose your sql-server-bi-features option group and choose Add option. On the Add option page, make the following selections to enable SSRS:

  • For Option name, choose SSRS.
  • For Port, specify 11433.
  • For Max memory, specify 15.
    Note: This parameter specifies the upper memory percentage threshold that your SSRS occupies on Microsoft SQL Server instance. If the SSRS service reaches the max memory configured, it attempts to free up memory or refuse requests so it doesn’t exceed the limit. Depending on your usage of SSRS, consider a value that isn’t intrusive to other applications on the same instance, such as the SQL Server database engine.
  • For Security groups, choose the RDS SQL Server Tutorial security group you created in Step 1.
  • For Scheduling, choose Immediately.

Then, choose Add option.


2.6 — In the Option groups list, choose your sql-server-bi-features option group and choose Add option. On the Add option page, make the following selections to enable SSIS:

  • For Option name, choose SSIS.
  • For Scheduling, choose Immediately.

Then, choose Add option.


In the Option groups list, your sql-server-bi-features option group now displays three options: SSAS, SSRS, SSIS.

Step 3: Configure Amazon RDS parameter group for SSIS

A DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances. If you create a DB instance without specifying a DB parameter group, the DB instance uses a default DB parameter group. Each default DB parameter group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You can't modify the parameter settings of a default parameter group. You can define your own parameter groups with customized settings. Then you can configure or modify your DB instances to use your own parameter groups. For more information, see Working with DB Parameter Groups.

In this step, you create a parameter group for the SSIS feature.

3.1 — In the left navigation pane of the RDS console choose Parameter groups, then Create parameter group.


3.2 — On the Create parameter group page, make the following selections:

  • For Parameter group family, choose sqlserver-ee-14.0.
  • For Group name, specify ssis-sqlserver-ee-14.
  • For Description, type clr enabled parameter group.

Then, choose Create.


3.3 – In the Parameter groups list, choose the ssis-sqlserver-ee-14 parameter group you created, and then choose Parameter group actions, Edit


3.4 – On the Parameters page, in the search box, type clr. Choose clr enabled, select the Values drop-down box, and choose 1. Then, choose Save changes.

Step 4: Modify the Amazon RDS for SQL Server DB instance

You are now ready to use Microsoft SQL Server Business Intelligence features SSAS, SSRS, SSIS on Amazon RDS for SQL Server. To do so, you must modify your Amazon RDS for SQL Server DB instance.

For this step, you modify your existing Amazon RDS for SQL Server DB instance to use the option group you created in Step 2 and the parameter group you created in Step 3.

4.1 — In the left pane of the RDS console, choose Databases. Select your RDS for SQL Server DB instance and choose Modify.

  • In the Microsoft SQL Server Windows Authentication section, confirm the check box for Enable Microsoft SQL Server Windows authentication is selected. 
  • In the Additonal configuration section, make these changes:
    • For DB parameter group, choose ssis-sqlserver-ee-14.
    • For Option group, choose sql-server-bi-features.

4.2 — Choose Continue.

4.3 — On the Summary of modifications page, for Scheduling of modifications, choose Apply immediately.

Note: Existing production SQL Server DB instances should always be modified during the next maintenance window. For this tutorial non-production DB instance, you can choose Apply immediately.

4.4 — Choose Modify DB instance.

Step 5: Integrate the Amazon RDS for SQL Server DB instance with Amazon S3

For this step, you integrate your existing Amazon RDS for SQL Server DB instance with Amazon S3.

To use the SSAS and SSIS features, you need to integrate your RDS DB instance with Amazon S3. Amazon S3 integration provides:

  • SSAS models transfers and backups between the DB instance and an S3 bucket.
  • SSIS packages transfers and output files transfers between the DB instance and S3 bucket.

This step is not covered in this tutorial. For instructions, see Integrating an Amazon RDS for SQL Server DB Instance with Amazon S3.

Step 6: Clean up

In the following steps, you clean up the resources you created in this tutorial.

It is a best practice to delete instances and resources that you are no longer using so that you are not continually charged for them.

Delete option group

6.1 — Navigate to the RDS console, and in the left pane, choose Option groups.

6.2 — Choose the sql-server-bi-features group.

6.3 — Choose Delete group.

6.4 — In the dialog box, type choose Delete.

Delete parameter group

6.5 — Navigate to the RDS console, and in the left pane, choose Parameter groups.

6.6 — Choose the ssis-sqlserver-ee-14 group.

6.7 — Choose Actions, then Delete.

6.8 — In the dialog box, type choose Delete.

Delete other resources

If you created a new EC2 instance, new RDS instance, AWS Managed Microsoft Active Directory, S3 integration, and corresponding security groups for this tutorial, delete those resources also.

Congratulations

You learned how to enable the Microsoft SQL Server Business intelligence features SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS) with just few clicks using the options group and parameter groups on Amazon RDS for SQL Server.

Was this tutorial helpful?

Thank you
Please let us know what you liked.
Close
Sorry to disappoint you
Is something out-of-date, confusing or inaccurate? Please help us improve this tutorial by providing feedback.
Close

Learn more about Amazon RDS features

Find out more about the features of Amazon RDS with the Amazon RDS User Guide.

Best practices with Amazon RDS

Learn about general best practices and options for working with Amazon RDS.

Learn more about Amazon RDS for SQL Server options

If you want to learn more, read the see Options for the Microsoft SQL Server database engine.