Use Amazon Relational Database Service (RDS) for SQL Server for Storage
Note: The user must have Amazon Web Services (AWS) admin privileges to use Amazon Relational Database Service (RDS) for SQL Server as storage.
Amazon Relational Database Service (RDS) for SQL Server is a fully managed Relational Database Service for SQL Server on AWS. This guide covers how to create and configure an AWS RDS SQL Server as storage for Prepare instances in Data Integration.
Note: It is possible to configure several data areas (such as a staging area and a data warehouse) within a single Prepare instance, which will then share the same AWS RDS SQL database.
Configure an AWS RDS SQL database
To create an AWS virtual machine, select the Lightsail option from the AWS Console Home.
To create a database, select the RDS option from Console Home.
The easiest way to create the database is to select Easy create with Microsoft SQL Server as the configuration. Select the engine type and enter a name for the DB instance identifier. Enter Master username and password details, then click Create database.
Configure settings to allow remote access
Open the created database and modify its settings. Under Connectivity settings, go to Additional Configuration and configure the following:
- Set Public Access to Publicly accessible.
- Set the database port to 1433.
Open the VPC security group and configure the Inbound rules and Outbound rules to allow access to the database.
Standard RDS for SQL Server does not support the sysadmin role for user accounts — it is reserved for the internal rdsa account managed by Amazon RDS. This means creating new databases is not possible on standard RDS SQL Server.
The solution is to use RDS Custom for SQL Server, which provides full sysadmin access. Use the following resources to understand and configure RDS Custom:
Once RDS Custom for SQL Server is fully configured, verify the following before using it in your Jet Analytics Data Integration solution:
- Connect via SQL Server Management Studio (SSMS) from a remote machine.
- Log in using SQL authentication and confirm that the required permissions are in place to create and manage databases. Assign admin privileges as needed.
- Test creating a new database and/or restoring a backup or .bacpac file from a sample SQL database.
Note: Remote access to RDS Custom SQL Server and database creation permissions must be confirmed as working before using it in your Jet Analytics Data Integration solution.
Configure SQL users and permissions
Create users in the AWS RDS SQL database with the necessary permissions for Jet Analytics Data Integration to access and manage the database.
Set up a Prepare instance with AWS RDS SQL database as storage
Create a Prepare instance in the Jet Analytics Portal following the standard Prepare instance setup. For the Server Name, copy the endpoint provided in the AWS RDS console.