Connect Microsoft SQL Server to S3: 8 Easy Steps

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.

What is Microsoft SQL Server?

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).

What is Amazon S3?

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 Step 2: Configuring SQL Server Source Database
• SQL Server to S3 Step 3: Configuring AWS S3 Target Database
• SQL Server to S3 Step 4: Creating a DMS Replication Instance
• SQL Server to S3 Step 5: Creating DMS Source and Target Endpoints
• SQL Server to S3 Step 6: Creating DMS Migration Task
• SQL Server to S3 Step 7: Inspecting Content in AWS S3 Bucket
• SQL Server to S3 Step 8: Replicating Data Changes

SQL Server to S3 Step 1: Connecting to EC2 Instance

• Open the AWS EC2 Management Console and click on Instances in the left pane.

• Select an EC2 instance as per your requirements and click on Connect from the Actions dropdown in the upper right corner.

• In the Connect to Instance menu under the RDP Client section, click on Get Password.

• Make a copy of the password and save it in a secure location.

• 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 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 Step 3: Configuring AWS S3 Target Database

• Open the AWS S3 Console and click on Create Bucket.

• Pick a suitable unique name for your AWS S3 Bucket, select a region as per requirement, and click on Create.

• Open the AWS S3 Bucket that you just created, click on Create Folder, provide a suitable unique name for it, and click on Save.

• Open AWS IAM Console, and click on Policies under the left navigation pane.

• Select Create Policy and open the JSON section.

• 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.

• Open AWS IAM Console, and click on Roles in the left navigation pane.

• Click on Create Role, choose the configurations as shown in the image below, and click on Next: Permissions.

• Select the IAM Policy created previously and click on Next: Tags.

• 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 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 Step 5: Creating DMS Source and Target Endpoints

• Open AWS Database Migration Service (DMS) Console, and select Endpoints from the left navigation pane.

• Configure the Source DMS Endpoint as shown in the image below.

• 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.

• 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.

• Configure the Data Migration task as shown in the following image:

• 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 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 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.

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!