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

SQL Server to S3- Featured Image

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

What is Microsoft SQL Server?

SQL Server to S3: Microsoft SQL Server Logo | Hevo Data
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).

Key Features of Microsoft SQL Server

The key features of Microsoft SQL Server are as follows:

  • Accelerated Data Recovery: Microsoft’s implementation of Accelerated Data Recovery in Microsoft SQL Server performs database recovery in Microsoft SQL Server efficiently and redeveloped the traditional recovery process using Transaction Logs which has led to a drastic decrease in the time taken.
  • Advanced Encryption: Microsoft SQL Server introduced a new Encryption Technology called AlwaysEncrypted that allowed transparent Column Encryption without giving Database Administrators access to Decryption Keys this ensures that sensitive information is secure.
  • Intelligent Query Processing: Microsoft SQL Server houses a robust in-built Query Optimizer that generates an execution plan for the queries being executed. It includes 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).

Simplify Data Integration With Hevo’s No Code Data Pipeline

Hevo Data, an Automated No-code Data Pipeline, helps you directly transfer data from sources like SQL Server & S3 among 100+ other sources (40+ free sources) to Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo’s fully managed pipeline supports data replication from SQL Server hosted either on-premises or on the Azure cloud. Hevo also enables you to load data from S3 buckets into your preferred Data Warehouse seamlessly. Moreover, S3 stores its files after compressing them into a Gzip format. Hevo’s Data pipeline automatically unzips any Gzipped files on ingestion and also performs file re-ingestion in case there is any data update.

Get Started with Hevo for Free

Hevo is fully managed and completely automates the process of not only loading data from 100+ data sources (including 40+ free sources) sources but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure and flexible manner with 

What is Amazon S3?

SQL Server to S3: Amazon S3 Logo | Hevo Data
Image Source: split

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 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 patients’ health data as per requirements.

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, and 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 visibility into object storage usage, and activity trends and makes actionable recommendations. 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.

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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
Image Source: dms-immersionday.workshop.aws

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

SQL Server to S3: Create S3 Bucket | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
Image Source: dms-immersionday.workshop.aws
  • Select Create Policy and open the JSON section.
SQL Server to S3: JSON in AWS Policy | Hevo Data
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 | Hevo Data
Image Source: dms-immersionday.workshop.aws
SQL Server to S3: Open IAM Roles | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
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 | Hevo Data
Image Source: dms-immersionday.workshop.aws

What Makes Your Data Integration Experience With Hevo Best-in-Class? 

Manually performing the Data Integration process is a tedious and time-consuming process as it involves building and maintaining Data Pipelines. To experience seamless Data Integration with your applications, you can use Hevo Data, an automated No Code Data Pipelining solution that not only helps in seamless Data Integration but also automate the ETL process without writing a single line of code.

These are some other benefits of having Hevo Data as your Data Replication Partner:

  • Exceptional Security: A Fault-tolerant Architecture that ensures Zero Data Loss.
  • Built to Scale: Exceptional Horizontal Scalability with Minimal Latency for Modern-data Needs.
  • Built-in Connectors: Support for SQL Server, S3, and 100+ Data Sources, including Databases, SaaS Platforms, Files & More. Native Webhooks & REST API Connector available for Custom Sources.
  • Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
  • Smooth Schema Mapping: Fully-managed Automated Schema Management for incoming data with the desired destination.
  • Blazing-fast Setup: Straightforward interface for new customers to work on, with minimal setup time.

With continuous real-time data movement, ETL your data seamlessly to your destination warehouse with Hevo’s easy-to-setup and No-code interface. Try our 14-day full access free trial.

Sign up here for a 14-Day Free Trial!

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

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

SQL Server to S3 Step 6: Creating DMS Migration Task

  • Open AWS Database Migration Service (DMS) Console, and select the Database Migration Tasks option from the left navigation pane under Conversion & Migration.
  • Click on Create Task in the upper right corner to create a new migration task for connecting SQL Server to S3.
SQL Server to S3: Create Data Migration Task | Hevo Data
Image Source: dms-immersionday.workshop.aws
  • Configure the Data Migration task as shown in the following image:
SQL Server to S3: Create DMT | Hevo Data
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 | Hevo Data
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 for connecting SQL Server to S3. This folder should contain all the tables that have been replicated from Microsoft SQL Server.

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

SQL Server to S3 Step 8: Replicating Data Changes

  • Open Microsoft SQL Server Management Studio and connect SQL Server to S3 as the Source on the AWS EC2 instance.
  • Open a New Query window and make the required changes to the data or you can choose Hevo to automate your Data Replication in a hassle-free manner. It offers auto-mapping for schema management to seamlessly replicate data changes from source to destination.
  • 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 | Hevo Data
Image Source: dms-immersionday.workshop.aws

Limitations of Connecting SQL Server to Amazon S3

The manual method mentioned in the previous section for connecting SQL Server to S3 suffers from the following limitations:

  • The process for connecting SQL Server to S3 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 such as transferring data from SQL Server to S3, if done manually, require immense engineering bandwidth and resources for the development and maintenance of Data Pipelines

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. Now, the manual approach of connecting SQL Server to S3 will add complex overheads in terms of time, and resources. Such a solution will require skilled engineers and regular data updates. Furthermore, you will have to build an in-house solution from scratch if you wish to transfer your data from SQL Server or S3 to a Data Warehouse for analysis.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly transfer your SQL Server and S3 data to the Data Warehouse of your choice in real-time. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure manner without having to write any code. It will make your life easier and make data migration hassle-free.

Learn more about Hevo

Share your experience of setting up SQL Server to S3 Integration in the comments section below!

No-code Data Pipeline For Your Data Warehouse