Support for SQL Server Analysis Services in Amazon RDS for SQL Server
Microsoft SQL Server Analysis Services (SSAS) is part of the Microsoft Business Intelligence
(MSBI) suite. SSAS is an online analytical processing (OLAP) and data mining tool
that
is installed within SQL Server. You use SSAS to analyze data to help make business
decisions. SSAS differs from the SQL Server relational database because SSAS is
optimized for queries and calculations common in a business intelligence environment.
For more information on SSAS, see the Microsoft Analysis services
documentation
Amazon RDS for SQL Server supports running SQL Server Analysis Services (SSAS) in Tabular mode. You can enable SSAS on existing or new DB instances. It's installed on the same DB instance as your database engine.
RDS supports SSAS for SQL Server Standard and Enterprise Editions on the following versions:
-
SQL Server 2019, version 15.00.4043.16.v1 and later
-
SQL Server 2017, version 14.00.3223.3.v1 and later
-
SQL Server 2016, version 13.00.5426.0.v1 and later
Limitations
The following limitations apply to running SSAS on RDS for SQL Server:
-
Tabular is the only supported mode for SSAS.
-
Multi-AZ instances aren't supported.
-
Instances must use AWS Directory Service for Microsoft Active Directory for SSAS authentication.
-
Users aren't given SSAS server administrator access, but they can be granted database-level administrator access.
-
The only supported port for accessing SSAS is 2383.
-
You can't deploy projects directly. We provide an RDS stored procedure to do this. For more information, see Deploying SSAS projects on Amazon RDS.
-
Processing during deployment isn't supported.
-
Using .xmla files for deployment isn't supported.
-
SSAS project input files and database backup output files can only be in the
D:\S3
folder on the DB instance.
Enabling SSAS
Use the following process to enable SSAS for your DB instance:
-
Create a new option group, or choose an existing option group.
-
Add the
SSAS
option to the option group. -
Associate the option group with the DB instance.
-
Allow inbound access to the VPC security group for the SSAS listener port.
-
Enable Amazon S3 integration.
Creating the option group for SSAS
Use the AWS Management Console or the AWS CLI to create an option group that corresponds to the SQL Server engine and version of the DB instance that you plan to use.
You can also use an existing option group if it's for the correct SQL Server engine and version.
The following console procedure creates an option group for SQL Server Standard Edition 2017.
To create the option group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose Create group.
-
In the Create option group pane, do the following:
-
For Name, enter a name for the option group that is unique within your AWS account, such as
ssas-se-2017
. The name can contain only letters, digits, and hyphens. -
For Description, enter a brief description of the option group, such as
SSAS option group for SQL Server SE 2017
. The description is used for display purposes. -
For Engine, choose sqlserver-se.
-
For Major engine version, choose 14.00.
-
-
Choose Create.
The following CLI example creates an option group for SQL Server Standard Edition 2017.
To create the option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds create-option-group \ --option-group-name
ssas-se-2017
\ --engine-namesqlserver-se
\ --major-engine-version14.00
\ --option-group-description "SSAS option group for SQL Server SE 2017
"For Windows:
aws rds create-option-group ^ --option-group-name
ssas-se-2017
^ --engine-namesqlserver-se
^ --major-engine-version14.00
^ --option-group-description "SSAS option group for SQL Server SE 2017
"
Adding the SSAS option to the option group
Next, use the AWS Management Console or the AWS CLI to add the SSAS
option to the option
group.
To add the SSAS option
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group that you just created.
-
Choose Add option.
-
Under Option details, choose SSAS for Option name.
-
Under Option settings, enter a value from 10–80 for Max memory.
Max memory specifies the upper threshold above which SSAS begins releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45.
Note The port for accessing SSAS, 2383, is prepopulated.
-
For Security groups, choose the VPC security group to associate with the option.
-
Under Scheduling, choose whether to add the option immediately or at the next maintenance window.
-
Choose Add option.
To add the SSAS option
-
Create a JSON file, for example
ssas-option.json
, with the following parameters:-
OptionGroupName
– The name of option group that you created or chose previously (ssas-se-2017
in the following example). -
Port
– The port that you use to access SSAS. The only supported port is 2383. -
VpcSecurityGroupMemberships
– VPC security group memberships for your RDS DB instance. -
MAX_MEMORY
– The upper threshold above which SSAS should begin releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45.
{ "OptionGroupName": "
ssas-se-2017
", "OptionsToInclude": [ { "OptionName": "SSAS", "Port": 2383, "VpcSecurityGroupMemberships" : ["sg-0abcdef123
"], "OptionSettings": [{"Name" : "MAX_MEMORY","Value" : "60
"}] }], "ApplyImmediately": true } -
-
Add the
SSAS
option to the option group.For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --cli-input-json file://
ssas-option.json
\ --apply-immediatelyFor Windows:
aws rds add-option-to-option-group ^ --cli-input-json file://
ssas-option.json
^ --apply-immediately
Associating the option group with your DB instance
You can use the AWS Management Console or the AWS CLI to associate the option group with your DB instance.
Associate your option group with a new or existing DB instance:
-
For a new DB instance, associate the option group with the DB instance when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
-
For an existing DB instance, modify the instance and associate the new option group with it. For more information, see Modifying an Amazon RDS DB instance.
Note If you use an existing instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see Using Windows Authentication with an Amazon RDS for SQL Server DB instance.
You can associate your option group with a new or existing DB instance.
If you use an existing instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see Using Windows Authentication with an Amazon RDS for SQL Server DB instance.
To create a DB instance that uses the option group
-
Specify the same DB engine type and major version that you used when creating the option group.
For Linux, macOS, or Unix:
aws rds create-db-instance \ --db-instance-identifier
myssasinstance
\ --db-instance-classdb.m5.2xlarge
\ --enginesqlserver-se
\ --engine-version14.00.3223.3.v1
\ --allocated-storage100
\ --master-user-passwordsecret123
\ --master-usernameadmin
\ --storage-typegp2
\ --license-modelli
\ --domain-iam-role-namemy-directory-iam-role
\ --domainmy-domain-id
\ --option-group-namessas-se-2017
For Windows:
aws rds create-db-instance ^ --db-instance-identifier
myssasinstance
^ --db-instance-classdb.m5.2xlarge
^ --enginesqlserver-se
^ --engine-version14.00.3223.3.v1
^ --allocated-storage100
^ --master-user-passwordsecret123
^ --master-usernameadmin
^ --storage-typegp2
^ --license-modelli
^ --domain-iam-role-namemy-directory-iam-role
^ --domainmy-domain-id
^ --option-group-namessas-se-2017
To modify a DB instance to associate the option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds modify-db-instance \ --db-instance-identifier
myssasinstance
\ --option-group-namessas-se-2017
\ --apply-immediatelyFor Windows:
aws rds modify-db-instance ^ --db-instance-identifier
myssasinstance
^ --option-group-namessas-se-2017
^ --apply-immediately
Allowing inbound access to your VPC security group
Create an inbound rule for the specified SSAS listener port in the VPC security group associated with your DB instance. For more information about setting up security groups, see Provide access to your DB instance in your VPC by creating a security group.
Enabling S3 integration
To download model configuration files to your host for deployment, use S3 integration. For more information, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.
Deploying SSAS projects on Amazon RDS
On RDS, you can't deploy SSAS projects directly by using SQL Server Management Studio (SSMS). To deploy projects, use an RDS stored procedure.
Using .xmla files for deployment isn't supported.
Before you deploy projects, make sure of the following:
-
S3 integration is enabled. For more information, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.
-
The
Processing Option
configuration setting is set toDo Not Process
. This setting means that no processing happens after deployment. -
You have both the
andmyssasproject
.asdatabase
files. They're automatically generated when you build the SSAS project.myssasproject
.deploymentoptions
To deploy an SSAS project on RDS
-
Download the
.asdatabase
(SSAS model) file from your S3 bucket to your DB instance, as shown in the following example. For more information on the download parameters, see Downloading files from an Amazon S3 bucket to a SQL Server DB instance.exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::
bucket_name
/myssasproject
.asdatabase', [@rds_file_path='D:\S3\myssasproject
.asdatabase'], [@overwrite_file=1]; -
Download the
.deploymentoptions
file from your S3 bucket to your DB instance.exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::
bucket_name
/myssasproject
.deploymentoptions', [@rds_file_path='D:\S3\myssasproject
.deploymentoptions'], [@overwrite_file=1]; -
Deploy the project.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_DEPLOY_PROJECT', @file_path='D:\S3\
myssasproject
.asdatabase';
Monitoring the status of a deployment task
To track the status of your deployment (or download) task, call the
rds_fn_task_status
function. It takes two parameters. The first
parameter should always be NULL
because it doesn't apply to SSAS. The
second parameter accepts a task ID.
To see a list of all tasks, set the first parameter to NULL
and the second
parameter to 0
, as shown in the following example.
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,
0
);
To get a specific task, set the first parameter to NULL
and the second
parameter to the task ID, as shown in the following example.
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,
42
);
The rds_fn_task_status
function returns the following information.
Output parameter |
Description |
---|---|
|
The ID of the task. |
|
For SSAS, tasks can have the following task types:
|
|
Not applicable to SSAS tasks. |
|
The progress of the task as a percentage. |
|
The amount of time spent on the task, in minutes. |
|
The status of the task. Possible statuses are the following:
|
|
Additional information about the task. If an error occurs during processing, this column contains information about the error. |
|
The date and time that the task status was last updated. |
|
The date and time that the task was created. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Metadata associated with the SSAS task. |
Using SSAS on Amazon RDS
After deploying the SSAS project, you can directly process the OLAP database on SSMS.
To use SSAS on RDS
-
In SSMS, connect to SSAS using the user name and password for the Active Directory domain.
-
Expand Databases. The newly deployed SSAS database appears.
-
Expand Connections, open the context (right-click) menu for the connection object, and then choose Properties.
-
In the connection string, update the user name and password to those for the source SQL database. Doing this is required for processing tables.
-
Open the context (right-click) menu for the SSAS database that you created and choose Process Database.
Depending on the size of the input data, the processing operation might take several minutes to complete.
Adding a domain user as a database administrator
You can add a domain user as an SSAS database administrator in the following ways:
-
A database administrator can use SSMS to create a role with
admin
privileges, then add users to that role. -
You can use the following stored procedure.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_ADD_DB_ADMIN_MEMBER', @database_name='
myssasdb
', @ssas_role_name='exampleRole
', @ssas_role_member='domain_name
\domain_user_name
';The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_ADD_DB_ADMIN_MEMBER
. -
@database_name
– The name of the SSAS database to which you're granting administrator privileges. -
@ssas_role_name
– The SSAS database administrator role name. If the role doesn't already exist, it's created. -
@ssas_role_member
– The SSAS database user that you're adding to the administrator role.
-
Backing up an SSAS database
You can create SSAS database backup files only in the D:\S3
folder on the DB
instance. To move the backup files to your S3 bucket, use Amazon S3.
You can back up an SSAS database as follows:
-
A domain user with the
admin
role for a particular database can use SSMS to back up the database to theD:\S3
folder.For more information, see Adding a domain user as a database administrator.
-
You can use the following stored procedure.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_BACKUP_DB', @database_name='
myssasdb
', @file_path='D:\S3\ssas_db_backup
.abf', [@ssas_apply_compression=1], [@ssas_overwrite_file=1];The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_BACKUP_DB
. -
@database_name
– The name of the SSAS database that you're backing up. -
@file_path
– The path for the SSAS backup file. The.abf
extension is required.
The following parameters are optional:
-
@ssas_apply_compression
– Whether to apply SSAS backup compression. Valid values are 1 (Yes) and 0 (No). -
@ssas_overwrite_file
– Whether to overwrite the SSAS backup file. Valid values are 1 (Yes) and 0 (No).
Note The stored procedure for backup doesn't support encryption.
-
Restoring an SSAS database
Use the following stored procedure to restore an SSAS database from a backup.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_RESTORE_DB', @database_name='
mynewssasdb
', @file_path='D:\S3\ssas_db_backup
.abf';
The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_RESTORE_DB
. -
@database_name
– The name of the new SSAS database that you're restoring to. -
@file_path
– The path to the SSAS backup file.
You can't restore a database if there is an existing SSAS database with the same name. The stored procedure for restoring doesn't support encrypted backup files.
Restoring a DB instance to a specified time
Point-in-time recovery (PITR) doesn't apply to SSAS databases. If you do PITR, only the SSAS data in the last snapshot before the requested time is available on the restored instance.
To have up-to-date SSAS databases on a restored DB instance
-
Back up your SSAS databases to the
D:\S3
folder on the source instance. -
Transfer the backup files to the S3 bucket.
-
Transfer the backup files from the S3 bucket to the
D:\S3
folder on the restored instance. -
Run the stored procedure to restore the SSAS databases onto the restored instance.
You can also reprocess the SSAS project to restore the databases.
Disabling SSAS
To disable SSAS, remove the SSAS
option from its option group. Before you
remove the SSAS
option, delete your SSAS databases.
We highly recommend that you back up your SSAS databases before deleting them and
removing
the SSAS
option.
To remove the SSAS option from its option group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group with the
SSAS
option (ssas-se-2017
in the previous examples). -
Choose Delete option.
-
Under Deletion options, choose SSAS for Options to delete.
-
Under Apply immediately, choose Yes to delete the option immediately, or No to delete it at the next maintenance window.
-
Choose Delete.
To remove the SSAS option from its option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds remove-option-from-option-group \ --option-group-name
ssas-se-2017
\ --options SSAS \ --apply-immediatelyFor Windows:
aws rds remove-option-from-option-group ^ --option-group-name
ssas-se-2017
^ --options SSAS ^ --apply-immediately