Rajendra Gupta
Create a custom parameter group

Explore SQL Server Integration Services (SSIS) on AWS RDS SQL Server

June 2, 2020 by

In the previous article, Deploy tabular databases in SSAS on AWS RDS SQL Server, we explored that AWS RDS SQL Server now supports SQL Server Analysis Service (SSAS). It enables you to implement tabular project models for RDS instance as well. In this article, we will explore the support of SQL Server integration service in the RDS environment.

The SSIS integration for AWS RDS SQL Server will be covered in two articles:

  1. In this, the first article, we will cover the following topics:
    • Overview of SSIS package on RDS instance
    • Prerequisites
    • Parameter group and Options group for SSIS feature
    • SSIS DB permissions for master user and Windows authenticated user

  2. In the second article, we will cover the following topics:
    • Create an integration service project in Visual Studio 2019
    • Deploy projects in SSIS RDS instance using S3 integration and stored procedures
    • Create credentials, proxy accounts for SQL Server agent
    • Create SQL Server agent jobs for SSIS package stored in the SSISDB catalog

Let’s get started with the SSIS feature in the AWS RDS SQL Server.

Introduction

Amazon gives you two options to implement SQL Server services:

  • Elastic Compute Cloud (EC2): We can deploy an EC2 instance and install SQL Server on it. An EC2 instance is similar to a virtual machine that we deploy in on-premise infrastructure. You have full control over the SQL Server, its services, version, edition, features in this
  • AWS RDS SQL Server: It is an AWS managed database platform for SQL Server. Here, the user does not worry about the infrastructure. They can select the supported SQL version and start using it. RDS instance comes with some feature restrictions

Previously, RDS supported only database service in SQL Server. Recently on May 19th, 2020, Amazon announced that now RDS supports the SSIS package using SQL Server Integration Service in the AWS RDS SQL Server as well.

In this article, we will explore the process to enable SSIS, create an SSIS package, and deploy it in the RDS instance.

Overview of SSIS on AWS RDS SQL Server

SQL Server Integration Service (SSIS) is an integral and useful component of SQL Server. It helps us to design various data flows and transformations. It provides many useful transformations for cleaning, aggregating, merging data, FTP, and database maintenance tasks. We can also take data from various sources such as a flat-file, relational, non-relational databases, and design work flow, logics to extract or merge them.

Let’s explore SSIS feature requirements for an AWS RDS SQL instance.

SSIS feature availability

SSIS feature is available on both Single-AZ and Multi-AZ SQL Server configurations.

We will use a single AZ RDS instance in this article. You can follow the above article and configure it to use multi-AZ RDS with SSIS functionality.

Supported SQL editions and versions

It supports SQL Server 2016 and SQL Server 2017 standard or Enterprise edition.

  • SQL Server 2016: Minimum version supported is 13.00.5426.0.v1
  • SQL Server 2017: Minimum version supported is14.00.3223.3.v1

In this article, I use the AWS RDS SQL Server instance SQL Server 2017 version 14.00.3281.6.v1 standard edition, as shown below.

AWS RDS SQL Server  version

AWS Managed Microsoft active directory

The RDS instance should be a member of the AWS managed active directory. It allows you to connect to SQL Server with Windows authentication.

We already covered this required in the following two series of articles:

In the following screenshot, we see SQLShackdemo.com active directory in joined status for my RDS instance:

RDS AWS managed directory

Amazon S3 bucket Integration for AWS RDS SQL Server

We need to integrate an Amazon S3 bucket with the RDS instance with an appropriate IAM role. RDS uses an S3 bucket to download and upload project-related files for the SSIS package.

You should refer to Integrating an AWS RDS SQL Server with Amazon S3 bucket to learn the integration process.

In the below screenshot, I have sqlshackrole in my RDS instance that is integrated with [sqlshackdemo] S3 bucket:

Integrate an Amazon S3 bucket

CLR integration on AWS RDS SQL Server

Enables Common Language Runtime(CLR) integration on the corresponding RDS instance. It allows RDS instance to use stored procedures, function, triggers written in any .Net language such as VB.net

To enable CLR integration on RDS SQL Server, first view the existing parameter group for RDS. The RDS instance should be in an available status to modify it. Click on modify and view its parameter group.

In my case, it is using the default parameter group i.e. default.sqlserver-se-14.0

Database Options

We cannot modify a default parameter group. We create a new parameter group with additional configuration. In the RDS dashboard, click on the Parameter groups taband create a new parameter group:

default parameter group

In the Create parameter group window, select the SQL Server version, specify a group name and description. You should choose the correct DB instance version similar to your RDS instance version else it might work correctly with associated AWS RDS server.

Create parameter group

Once you create it, select the new parameter group, open it, and click on edit parameters. Search for CLR enabled, change the value to 1, and save changes:

Create a custom parameter group

Now, go back to RDS instance and modify the option group from a default default.sqlserver-se-14.0 to new parameter group newparametergroup:

Apply new DB parameter group in RDS

Verify the changes and apply them immediately. In case you do not want to apply it immediately, select to apply the changes during the next scheduled maintenance window. It also shows you the current maintenance window:

Maintenance window

Option group in AWS RDS SQL Server to enable SSIS feature

Enable SSIS using the options group. In my RDS instance, it uses ssasforrds option group:

AWS RDS SQL Server to enable SSIS feature

Navigate to the Option groups tab in the RDS dashboard, select the option group ssasforrds and click on the Add option button:

Add Option

Select the SSIS option from the drop-down list and apply the changes immediately:

Save changes

It will add SSIS to the existing option group:

Verify SSIS feature

We do not need to change the option group in the RDS instance because we modified the existing option group. In case you create a new option group, modify the RDS instance to utilize the new option group having an SSIS feature.

Now, connect to AWS RDS SQL Server using endpoint in SSMS. You can see a new database SSISDB is available in the Databases node:

SSIS catalog database

You can expand the Integration Service Catalog node, and it will show the SSISDB catalog after we enabled the SSIS feature in the option group:

SSISDB catalog

SSIS DB permissions for master and Windows authenticated user

As you know, RDS creates a master user once we create an AWS RDS instance. We cannot use the master user to execute the SSIS package in the RDS environment. The master user has the following permissions in the SSISDB:

  • alter on ssis_admin role
  • alter on ssis_logreader role
  • alter any user

This master user can provide the SSIS_ADMIN, SSIS_LOGREADER permissions to other users. We need permissions for Windows users so that Windows authentication can be used.

Expand the Security node in the Object Explorer panel, then the Logins node, and open the properties of the login for a Windows user. Click on the User Mapping tab, select the SSISDB database, and give SSIS_ADMIN, SSIS_LOGREADER permissions for the user:

SSIDB permissions

We also need few additional permissions for the Windows user to run the SSIS package with the SQL Server agent job. In the below query, we provided these permissions to the user sqlshackdemo\rajendra.gupta. You can replace it with your windows user and execute the script. This script should run from the admin user security context in SSMS:

Conclusion

In the first article on the SSIS feature in AWS RDS SQL Server, we enabled SSIS services and provided permissions to the Windows user to connect using Windows authentication. In the second article, we will create an SSIS package, schedule it to run through SQL Server agent jobs.

Rajendra Gupta
233 Views