Forem: Arockia Nirmal Amala Doss

From On-Prem Microsoft SQL Server to AWS Serverless - Migrating Business Intelligence, Application data Landscape

Arockia Nirmal Amala Doss — Mon, 10 Apr 2023 01:09:46 +0000

Introduction

In today's fast-paced business world, companies are constantly seeking ways to streamline their operations, increase efficiency, and reduce costs. One way to achieve these goals is by leveraging the power of cloud computing and serverless technologies. This was the case for a company that I was working for and had been using Microsoft SQL Server, SSIS ETL tool and Reporting Services for their business intelligence/internal application needs.
In this blog post, I will summarize how we successfully migrated the complete business intelligence (OLAP) and application (OLTP) databases setup to AWS serverless services, including Amazon Aurora RDS, Lambda, Event Bridge, Glue, S3, Redshift and Quick Sight. In this post I summarise high level details about the architecture and the process.

Old Architecture (On-Premises)

In the company we mainly had two categories of data, the first set is the worldwide sales/product data which is refreshed every morning and available as flat files in the company intranet which is available to the subsidiaries worldwide. The second set is the local sales data from the European subsidiary
Part 1 : A python script is scheduled using windows task scheduler on a on-prem windows server. Every morning this script downloads the worldwide sales/product data flat files from the intranet and places in the same on-prem windows server of our European subsidiary. Once the flat files are arrived in the folder, the SSIS ETL packages will get triggered.
Part 2 : The SSIS ETL packages will now read the flat files, transform and load the product metadata/worldwide sales data into the European SQL Server staging database. The product metadata is used by the European sales website as well as by the CRM. The sales data is used for analysis purposes
Part 3 : The data in the staging database is further transformed into Facts and dimensions and are available as base tables for reporting. Once the ETL jobs are completed the internal employees are notified with an email and they can access the business intelligence/sales SSRS reports.

Drawbacks of the above architecture:

Though we enjoyed full ownership of the data infrastructure on-premises, over the period of time we noticed performance degradation since the data we process everyday increased exponentially. Scalability and performance became major concern.
We always relied on an expert freelancer consultant who set up the data infrastructure to analyze and fix the infrastructure issues.
Security patches and upgrades must be done manually which further complicated the maintenance
Increasing costs for the hardware and licenses were also an additional issue

New Architecture (AWS Serverless)

Considering the above drawbacks, we decided to move the complete data landscape to AWS. Given the knowledge bases and documentation available online we decided to first build a PoC. We could able to successfully negotiate with the management with the PoC. Below is the rough illustration of the new architecture we developed over the time.

Part 1: We created and scheduled a Lambda function using Eventbridge which would download the required flat files every morning and put into the Raw bucket in our S3 Datalake.

Part 2: Once the files are in the Raw bucket, a Glue ETL job will be triggered which will refine the data in the flat files. For example, here we copy only the required columns from Raw to Refined stage.

Part 3: Once the files are in the Refined bucket, another set of Glue ETL jobs will be triggered which will transform the data and copy the required data rows into Aurora Serverless RDS, Redshift serverless and Reporting S3 bucket. To reduce the costs, we periodically unload cold data defined by our business departments into Refined S3 bucket.

Part 4: Aurora Serverless RDS is our OLTP database which is used by our Sales Apps and CRM which now contains the European sales and customer data. For our datawarehousing and analysis/reporting purposes we move the necessary data from the OLTP database to Redshift on a hourly basis using another set of Glue ETL jobs.

Part 5: We have a set of reports in Quicksight that connects with Redshift datawarehouse and S3 reporting bucket. To query the data from S3 we leverage the features of Glue crawler, data catalog and athena.

Initial Database Migration: We performed our initial database migration with AWS Database Migration Service and Schema Conversion Tool. DMS features like data validation and setting up ongoing replication from source target enabled smooth transition. Earlier we used SQL Server both as OLTP and OLAP databases but now we have Aurora as OLTP and Redshift as OLAP.

Best Practices & Things we gained after moving to AWS

The main advantage for us is the flexibility in terms of cost and scalability. We are now aware of our exact expenses for each AWS service, which inturn helps us fine tune our use cases. We have also set up database auto scaling policies based on usage
We always offloaded the read traffic from the main writer database instance by having a separate read only instances which boosted the performance further
Database backups & replications are easier to setup
We started using Infrastructure as Code with Terraform to provision AWS resources and version controlling for our data infrastructure
Since we opted for mostly serverless services, now we worry no more about managing the hardware

Migrating to AWS was the best decision made by the company and they are now exploring many new AWS services like EMR, Sagemaker etc., to dive deep into the data to extract insights from the data. Thanks for reading the article and feel free to add comments or write me about your thoughts!

Guide to Creating a Secure and Efficient Personal AWS Account in 2023 - Best Practices for First-Time Users

Arockia Nirmal Amala Doss — Tue, 04 Apr 2023 09:06:25 +0000

Amazon Web Services (AWS) is a cloud computing platform that offers a wide range of services, including compute power, storage, and databases, as well as tools for machine learning, security, and more. Opening an AWS account is a simple process, but it's important to follow best practices to ensure that your account is secure and your resources are optimized for cost and performance. In this blog, I'll walk you through the steps of opening an AWS account and provide you with some key best practices to help you get started on the right foot.

Content of this Article

Sign up for an AWS account
Login as a root user & secure(MFA) root user
Create an Admin user with required permissions
Setup account alias
Change payment currency preference
Update security challenge questions
Setup default region/language
Set up the AWS CLI
Billing alerts/alarm
Best practices & Recommendations

Sign up for an AWS account

Signing up for an AWS account is a 5 step process. The process can be started with the following link

In the initial step, you will be asked for a root user email address and an account name. Root user must be strictly used only for administrative functions like account recovery, billing etc. For developing/using services in AWS, it is always recommended to create separate users with minimal or required privileges. We will create an Admin user later in this article. After the above step you need to verify your email address with a one time password which will be sent to your root email address.

Step 1: After the email verification, you are asked to create a password for a root user. AWS requires that your password meet the following conditions: It must have a minimum of 8 characters and a maximum of 128 characters. Make sure to create a strong password with special characters.
Step 2: Here you will asked for your contact information and your usage type (choose Personal- for your own projects).
Step 3: Now its time for entering your billing information. Your credit/debit card will be validated in this step.
Step 4: Your identity will be confirmed in this step. You could opt to receive either a text message or a voice call.
Step 5: You will be asked to select a support plan. It is recommended to opt for 'Basic support - Free' plan for new users who are just getting started with AWS.
Congratulations you are now successfully created your personal AWS account! Now we will perform some essential setups and also secure your AWS account.

Login as a root user & secure(MFA) root user

Once the account has been created, you might be redirected to the login page where you can login again as a root user. In order to secure the root user we need to add MFA(Multifactor Authentication) for the root user. Search for IAM in the home page of the AWS management console and open IAM dashboard.

Under 'security recommendations' click 'Add MFA' and follow the instructions. For example you could use Google/Microsoft authenticator in your smartphone for this purpose. Also make sure that the root user have no active access keys since root user will not be used to perform daily tasks.

Create an Admin user with required permissions

When setting up a new AWS account, you create a root user account. The root user is a special entity that has full access to the account, and can perform all actions, including changing the payment methods or closing the account. Due to this level of permissions, set up additional users to perform daily tasks related to your account. It is recommended that you create separate users for specific roles and functions.

Again, we'll use the IAM service to create users and assign them permissions. Before setting up a new user, we'll create a user group. User groups let you specify permissions for multiple users, which can make it easier to manage the permissions for those users. For example, you could have a user group called Admins and give that user group typical administrator permissions. Any user in that user group automatically has Admins group permissions.

In the IAM console, choose User groups in the left-side navigation and then choose Create group. Enter the User group name (in this case, Admins), then scroll down to the Attach permissions policies section. Search for "AdministratorAccess", then select the box next to the policy with the name "AdministratorAccess", scroll down, and choose Create group. Once the user group has been created, select Users in the left-side navigation bar and then choose Add users and link it with the user group like below.

Once you review and create the new user, the console sign-in details will be shown which includes URL, username and password. Don't forget to secure this user too with MFA.

Setup account alias

Let's set an alias for your account, which should be easier to remember than the 12-digit account ID. To set it, navigate to the Identity and Access Management (IAM) dashboard. Find the account ID on the right-hand side and click Create or Change under the AWS account alias. This alias needs to be globally unique across all AWS accounts, so your first choice may not be available.

Change payment currency preference

This prevents our card issuer to charge a fee for transactions in other currencies. Click your Account Name (Top Right) -> Account to find the section Payment Currency Preference.

Update security challenge questions

Improve the security of your AWS account by adding security challenge questions. AWS use these to help identify you as the owner of your AWS account if you ever need to contact AWS customer service for help. Click your Account Name (Top Right) -> Account to find the section Configure Security Challenge Questions.

Setup default region/language

Click your Account Name (Top Right) -> Account to find the section Unified Settings where you set the default region and language. Setting default region isn't just an issue for newbies, it would save a lot of mini heart attacks when people log in to an account and think they're missing resources, only to find out they're in the wrong region!

Set up the AWS CLI

The AWS CLI is a unified tool to manage your AWS services. With just one tool to download and configure, you can control multiple AWS services from the command line and automate them through scripts.
Go to IAM -> Users -> (newly created user) -> Under 'Security Credentials' go to 'Access Keys' -> 'Create Access Key'. In the next window 'Access key best practices & alternatives' choose 'Command Line Interface (CLI) ' and then create the access key (adding Tags is optional). Now save the access key and the secret access key (download the .csv file). Follow the instructions below to install the CLI based on your OS.

AWS CLI

once the installation is complete, we could confirm the installation using the version command in the terminal/powershell (windows) like below and you would see a version number.

aws --version

To configure the credentials, use the following command in the CLI and add the access key ID and secret access key credentials of the user created earlier. You also need to mention the default region name. The default output format can be json.

aws configure

After this you could run the aws ec2 describe-vpcs command to check if the configuration is correct. Each new AWS account has default VPCs configured. In the output section, you can view the VPCs in your AWS account.

aws ec2 describe-vpcs

Billing alerts/alarm

Search for 'Billing' in AWS home. You would land in AWS Billing Dashboard. In the navigation pane, choose Billing Preferences under Preferences. Select Receive Billing Alerts And click save preferences. You could also select 'Receive PDF Invoice By Email' and 'Receive Free Tier Usage Alerts' if needed. Now the billing alerts are enabled.
Billing metrics are located in us-east-1 region. Make sure the Console is switched to that region by selecting US East (N. Virginia) us-east-1 in the region selector (top right corner, next to Support button).
Follow the instructions below to create/delete an alarm. In my case I set an alarm when the overall costs goes more than 10 Dollars.

Billing Alarm

Best practices & Recommendations

Rotate all your access key pairs / root user passwords regularly
Limit the tasks you perform with the root user (creating an administrative user, change account settings and closing AWS account etc.) More details are in the link below. Never create access keys for the root user. If you need to have one then rotate access keys regularly
When creating IAM users make sure that the IAM users have the most restrictive policies possible, with only enough permissions to allow them to complete their intended tasks
Frequently audit your IAM roles/Policies
To protect secrets in AWS, configure them in AWS Secrets Manager, then insert a descriptive reference to them in the application code. For example, the password for a production database can be stored in Secrets Manager and named my_db_password_production. Use AWS Cost Explorer which enables you to view and analyze your costs and usage. Using the Cost Explorer user interface is free of charge
Make use of AWS Free Tier
Use Tags to organize resources and to track you AWS costs on a detailed level