Tutorial

Use Amazon RDS for SQL Server for Storage

  • 14 April 2023
  • 0 replies
  • 584 views

Userlevel 3
Badge +5

Amazon RDS for SQL Server is a fully managed Relational Database Service for SQL Server on Amazon Web Services.  This guide shows how to create and configure an AWS RDS SQL server to access from TimeXtender as Storage for your Data Warehouse Instances.

AWS RDS SQL Reference Architecture

Note: It is possible to setup several data areas (e.g. DSA and MDW) in one Data Warehouse instance, which will then use the same AWS RDS SQL Database.

Configure an AWS RDS SQL Database

To create AWS virtual machine, select the Lightsail option from Console Home:

To create a database, click on the RDS option from Console Home:

The easiest way to create the database is by selecting the option “Easy create

Configuration = Microsoft SQL Server

Select the engine type and enter a name for the “DB instance identifier”.

Enter Master username and Password details.

Click on “Create database” button.

Configure settings to allow remote access

Open the created database and modify its settings.

Go to “Connectivity” settings > Additional Configuration >

Set Public Access to “Publicly accessible

Set database port to 1433

Open the VPC security group and set “Inbound rules” and “Outbound rules” to access the database.

 

 

Important:

The regular RDS SQL Server has some limitations: the 'sysadmin' role itself is reserved for the 'rdsa' internal SQL Server account, which is managed by the Amazon RDS service and not accessible to users. That's why it's not possible to create new databases in the regular RDS SQL Server.

The solution is to use RDS Custom for SQL Server which provides lots of features along with 'sysadmin' role. Use the following videos and guides to understand and configure RDS Custom:
Amazon RDS Custom for SQL Server Technical Overview
How to Create and Connect to RDS Custom for SQL Server Instance

AWS Docs   Setting up your environment for Amazon RDS Custom for SQL Server 

AWS Docs   Working with RDS Custom for SQL Server - Amazon Relational Database Service

 

Once your RDS Custom for SQL Server is fully configured, verify the following:

  • Connect via SQL Server Management Studio (SSMS) running on a remote machine
  • Login via SQL authentication and configure required permissions to create and manage databases. Give admin privileges to the user as needed.
  • Test creating a new database and/ or restoring a backup (or bacpac) of a sample SQL database

Note: Remote access to RDS Custom SQL Server and database creation permissions must be working, before using it in your TimeXtender solution.

 

Now you may implement the following scenarios with TimeXtender:

  • Use RDS SQL for Data Warehouse storage in TimeXtender Data Area

Configure SQL users & Permissions

Create users in AWS RDS SQL Database with the necessary permissions. 

Setup a Data Warehouse Instance with AWS RDS SQL Database as storage

Create a Data Warehouse instance using the guide below.

For the ServerName, copy the server name provided in AWS. 

Reference

AWS RDS SQL Reference Architecture

 


0 replies

Be the first to reply!

Reply