Connect Microsoft SQL Server to S3: 8 Easy Steps

on Data Aggregation, Data Driven, Data Extraction, Data Integration, Data Replication, Database Management Systems • May 21st, 2021 • Write for Hevo

Cloud-based data storage solutions have now become the preferred choice for most modern businesses. This is primarily because leveraging a Cloud-based database makes it easier for businesses to ensure that their databases grow along with their unique data requirements and scale up or down on-demand or automatically to accommodate all peak-workload periods. Cloud-based databases enable businesses to efficiently settle all Data Availability and Security concerns as they allow for seamless Database Replication across multiple geographical locations, in addition to numerous backup and data recovery options. One of the most popular Cloud-based solutions is Amazon S3.

Amazon S3 houses a wide variety of features that make it the preferred Cloud-based storage solution for most businesses. Organizations leverage it to handle a large number of use cases seamlessly. Some of the use cases for which businesses rely on Amazon S3 include creating Backups, Analytical purposes, Data Archiving, and enhancing Security. Hence, a lot of businesses have started replicating data from their databases to Amazon S3 for creating backup and security reasons. This article will provide you with an in-depth understanding of how you can connect Microsoft SQL Server to S3.

Table of Contents

Introduction to Microsoft SQL Server

SQL Server to S3: Microsoft SQL Server Logo
Image Source: deepinthecode

Microsoft SQL Server is a well-known Relational Database Management System (RDBMS). Considering it is an RDBMS, its primary purpose is to store and retrieve data as per the requirements of the users and the applications connected to it. Microsoft SQL Server houses an implementation for a wide range of functionalities including Transactional Processing, Business Intelligence, Data Analytics, etc. that are required by most businesses today. It is considered to be one of the top three leading Database Technologies along with Oracle Database and IBM DB2.

Along with support for ANSI SQL, which is the popular Structured Query Language (SQL), Microsoft SQL Server comes with its own implementation of SQL called Transact SQL (T-SQL). T-SQL supports additional capabilities that allow users to declare variables, handle Exceptions, etc. The main interface tool for Microsoft SQL Server is called SQL Server Management Studio (SSMS).

Understanding the Key Features of Microsoft SQL Server

The key features of Microsoft SQL Server are as follows:

  • Accelerated Data Recovery: There might be situations wherein a complex transaction being run on the Microsoft SQL Server fails or the Server crashes while performing an operation. In these situations, the database has to be recovered to its initial state by rolling back all incomplete transactions and operations. This process was considered to be time-consuming until Microsoft’s implementation of Accelerated Data Recovery in Microsoft SQL Server. The process that performs database recovery in Microsoft SQL Server has been redeveloped using Transaction Logs which has led to a drastic decrease in the time taken.
  • Advanced Encryption: Considering that databases store sensitive information for most businesses, it is essential for Database Providers to ensure that their databases are secure. Microsoft SQL Server 2016 introduced a new Encryption Technology called AlwaysEncrypted that allowed transparent Column Encryption without giving Database Administrators access to Decryption Keys.
  • Intelligent Query Processing: Microsoft SQL Server houses a robust in-built Query Optimizer that generates an execution plan for the queries being executed. Various improvements have been made to this Query Optimizer to ensure that the databases provide the best in class performance to their users. These improvements include Dynamic Memory Grants for Rowstore Tables, Batch Mode on Rowstore, Table Variable Deferred Compilation, etc.
  • Advanced Analytics: All data stored in Microsoft SQL Server can be easily leveraged to perform a comprehensive analysis as per requirements using Microsoft SQL Server Analysis Service (SSAS) which is a Data Mining and an Online Analytical Processing (OLAP) tool in Microsoft SQL Server.
  • Advanced Integrations: Microsoft SQL Server also allows users to perform a broad range of tasks such as Data Extraction, Transformation, Loading, Data Migration, etc. using the Microsoft SQL Server Integration Services (SSIS). SSIS can be used to extract and transform data from various sources such as Relational Databases, Flat Files, XML Files, etc. using its Graphical User Interface (GUI) without writing a single line of code.

Introduction to Amazon S3

SQL Server to S3: Amazon S3 Logo
Image Source: freecodecamp

Amazon S3, also known as Amazon Simple Storage Service, is Amazon’s Cloud-based data storage platform. Amazon S3 holds up the entirety of Amazon’s massive Cloud Computing Network along with a significant amount of the modern web including Amazon’s website, Netflix, Facebook, and more.

Since its introduction in 2006, it is seen as the new standard for storing data. Amazon S3 does not store data like the file system of a computer that uses a collection of data blocks. Instead, it stores data as independent objects along with complete metadata and a unique object identifier. Its object storage system can work with almost all platforms that make it incredibly flexible and hence, suitable for a wide variety of businesses. Data can be stored across various locations and retrieved much more quickly and seamlessly than any traditional file storage system. 

Amazon guarantees a 99.99% durability rate. This means that all the data stored on Amazon S3 is guaranteed to remain intact and available to the user whenever it’s needed. Amazon S3 has now been adopted by a wide variety of companies along with some of the world’s largest Enterprises. Two of the most well-known Social Media Platforms, i.e., Facebook and Twitter, rely on Amazon S3 to securely store user data and keep it accessible for all network analyses. Healthcare Enterprises like Illumina, Bristol-Myers Squibb, and Celgene rely on Amazon S3 to keep their patient data secure and easily accessible, enabling them to analyze patient’s health data as per requirements.

Understanding the Key Features of Amazon S3

The key features of Amazon S3 are as follows:

  • Storage Management: With Amazon S3 Bucket names, object tags, prefixes, and S3 Inventory, users have access to a wide range of functionalities such as S3 Batch Operations, S3 Replication, etc., that help them categorize and report their data.
  • Storage Monitoring: Amazon S3 houses various functionalities such as AWS Cost Allocation Reports, Amazon CloudWatch, AWS CloudTrail, AWS S3 Event Notifications that enable users to monitor and control how their Amazon S3 resources are being utilized.
  • Storage Analytics: Amazon S3 houses two services called S3 Storage Lens and S3 Storage Class Analysis that can provide users with insights on data being stored. Amazon S3 Storage Lens delivers organization-wide visibility into object storage usage, activity trends and makes actionable recommendations to improve cost-efficiency and implement the best practices for data protection. Amazon S3 Storage Class Analysis analyzes storage access patterns to help users decide when they should implement transitions for the data into the right storage class.
  • Security: Amazon S3 offers various flexible security features to ensure that only authorized users have access to the data. Amazon S3 provides support for both Client-side and Server-side encryption for data uploads.

Simplify ETL Using Hevo’s No-code Data Pipeline

Hevo is a No-code Data Pipeline that offers a fully managed solution to set up data integration to your Data Warehouse or a destination of choice from Microsoft SQL Server, Amazon S3, and 100+ data sources (including 30+ free data sources). It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully-automated solution to manage data in real-time and always have analysis-ready data.

Get Started with Hevo for free

Let’s look at Some Salient Features of Hevo:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-day Free Trial!

Steps to Connect Microsoft SQL Server to S3

You can connect Microsoft SQL Server to S3 by implementing the following steps:

SQL Server to S3 Step 1: Connecting to EC2 Instance

SQL Server to S3:  Open AWS Instances
Image Source: dms-immersionday.workshop.aws
  • Select an EC2 instance as per your requirements and click on Connect from the Actions dropdown in the upper right corner.
SQL Server to S3:  Connect to EC2 Instance
Image Source: dms-immersionday.workshop.aws
  • In the Connect to Instance menu under the RDP Client section, click on Get Password.
SQL Server to S3:  Get RDP Client Password
Image Source: dms-immersionday.workshop.aws
  • Make a copy of the password and save it in a secure location.
SQL Server to S3:  Copy RDP Client Password
Image Source: dms-immersionday.workshop.aws
  • Click on Download Remote Desktop File. This will download the RDP file on your system, which will help you connect with the EC2 instance. Connect to the EC2 instance using a suitable RDP client.
  • Open Microsoft SQL Server Management Studio.
  • In the Connect to Server popup, enter the following parameters:
SQL Server to S3:  Microsoft SQL Server Configurations
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 2: Configuring SQL Server Source Database

Download the Microsoft SQL Server Source Configuration file, copy its contents to the SQL Server Management Studio Query Editor and execute it.

SQL Server to S3:  Source Database Configuration
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 3: Configuring AWS S3 Target Database

SQL Server to S3: Create S3 Bucket
Image Source: dms-immersionday.workshop.aws
  • Pick a suitable unique name for your AWS S3 Bucket, select a region as per requirement, and click on Create.
SQL Server to S3: Name S3 Bucket
Image Source: dms-immersionday.workshop.aws
  • Open the AWS S3 Bucket that you just created, click on Create Folder, provide a suitable unique name for it, and click on Save.
SQL Server to S3: Create folder in S3 Bucket
Image Source: dms-immersionday.workshop.aws
  • Open AWS IAM Console, and click on Policies under the left navigation pane.
SQL Server to S3: Open IAM Policies
Image Source: dms-immersionday.workshop.aws
  • Select Create Policy and open the JSON section.
SQL Server to S3: JSON in AWS Policy
Image Source: dms-immersionday.workshop.aws
  • Paste the following code in the AWS IAM Policy Editor and make suitable changes in the Resource sections to add your AWS S3 Bucket name:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::REPLACE-WITH-YOUR-BUCKET-NAME*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::REPLACE-WITH-YOUR-BUCKET-NAME*"
            ]
        }
    ]
}
  • Click on Review Policy.
  • Enter a suitable name for the AWS IAM policy and click on Create Policy.
SQL Server to S3: IAM Policy Name
Image Source: dms-immersionday.workshop.aws
SQL Server to S3: Open IAM Roles
Image Source: dms-immersionday.workshop.aws
  • Click on Create Role, choose the configurations as shown in the image below, and click on Next: Permissions.
SQL Server to S3: Create IAM Role
Image Source: dms-immersionday.workshop.aws
  • Select the IAM Policy created previously and click on Next: Tags.
SQL Server to S3: Connect Role to Policy
Image Source: dms-immersionday.workshop.aws
  • Click on Next: Review.
  • Enter a suitable name for the new AWS Role along with a small Role description and click on Create Role.
SQL Server to S3: Enter IAM Role name
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 4: Creating a DMS Replication Instance

Open AWS Database Migration Service (DMS) Console, select Replication Instances in the left navigation pane, click on Create Replication Instance, and enter the configuration as shown in the following image: 

SQL Server to S3: Create DMS Replication Instance
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 5: Creating DMS Source and Target Endpoints

SQL Server to S3: DMS Console endpoints
Image Source: dms-immersionday.workshop.aws
  • Configure the Source DMS Endpoint as shown in the image below.
SQL Server to S3: Source DMS Endpoint
Image Source: dms-immersionday.workshop.aws
  • Click on Run Test, and then Create Endpoint if the status changes to Successful.
  • Configure the Target DMS Endpoint as shown in the image below.
SQL Server to S3: Target DMS Endpoint
Image Source: dms-immersionday.workshop.aws
  • Click on Run Test, and then Create Endpoint if the status changes to Successful.

SQL Server to S3 Step 6: Creating DMS Migration Task

SQL Server to S3: Create Data Migration Task
Image Source: dms-immersionday.workshop.aws
  • Configure the Data Migration task as shown in the following image:
SQL Server to S3: Create DMT
Image Source: dms-immersionday.workshop.aws
  • Click on Create Task.
  • The created task will now connect your Microsoft SQL Server to S3 and start replicating data in the AWS S3 Bucket that was created earlier.
SQL Server to S3: Running Data Migration Task
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 7: Inspecting Content in AWS S3 Bucket

Open the folder that was created in the AWS S3 Bucket previously. This folder should contain all the tables that have been replicated from Microsoft SQL Server.

SQL Server to S3: Inspect S3 Bucket
Image Source: dms-immersionday.workshop.aws

SQL Server to S3 Step 8: Replicating Data Changes

  • Open Microsoft SQL Server Management Studio and connect to the Source SQL Server on the AWS EC2 instance.
  • Open a New Query window and make the required changes to the data.
  • Open the folder corresponding to the table in which the changes were made in AWS S3 Bucket.
  • You will observe a new CSV file in the folder with the new timestamp at which the changes were made to the table in Microsoft SQL Server.
SQL Server to S3: Inspect changes in S3 Bucket
Image Source: dms-immersionday.workshop.aws

Conclusion

This article provided you with a step-by-step guide on how you can connect Microsoft SQL Server to S3 to enhance the security and availability of your Microsoft SQL Server database. However, this process is considered to be too complex for someone who does not have enough technical knowledge and expertise in the Amazon Web Services (AWS) environment.

Most database migration tasks, if done manually, require immense engineering bandwidth and resources for the development and maintenance of Data Pipelines. Hence, businesses can instead use existing automated No-code platforms like Hevo.

Visit our Website to Explore Hevo

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

No-code Data Pipeline For Your Data Warehouse