How to Migrate Data from S3 to SQL Server?| 2 Easy Methods

Amazon S3 is a popular storage solution from the stable of Amazon. It is a reliable and stable solution when it comes to storing enterprise data. Microsoft SQL Server is a leading RDBMS platform in the market. It is efficient in querying data and performing operations through an end-to-end process.

By migrating data from Amazon S3 to SQL Server, a proper ETL process can be performed to gain the most from the data. It also enables a proper insight generation from the data.

This article details two comprehensive methods that can be used to migrate data from Amazon S3 to SQL Server.

What is Amazon S3?

Amazon S3 is a popular storage service from the house of Amazon Web Services. It is object-based storage that enables the users to store large amounts of data present in various formats like application files, blogs, codes, and many more with ease. S# stands for Simple Storage Service.

Amazon S3 is believed to be 99.9999% durable and ensures the high availability of data. It is a robust platform with the availability of various integrations, that enable it to be integrated with numerous sources to perform ETL. It also supports the integration of various programming languages like Python, Java, Scala, and more. Amazon S3 further has a provision for command-line interfacing that allows performing operations like adding, modifying, viewing, and manipulating data present in S3. 

Amazon S3 provides industry-leading scalability, security, accessibility, and performance. The data is stored in form of Buckets. It supports a single object size of 5 TB. It also has Data Management features built-in so that it is easy to handle data. 

What is Microsoft SQL Server?

SQL Server is a popular Relational Database Management System. It was developed by Microsoft and launched on April 24, 1989. It was written in programming languages C and C++. It follows a relational model architecture that was invented by E.F.Codd. 

The Relational Model stores the data in a structured format and generates relations between each table. The data is organized in the form of rows and columns where the column represents an attribute or feature and the row represents the value or record.

SQL Server comes in many versions, each with a slightly different feature set and query limits. The versions are Express, Enterprise, Standard, Web, and Developer

In case you want to install SQL Server, you may click here and download a version as per your system requirements.

Methods to Migrate Data from Amazon S3 to SQL Server

Data can be migrated from Amazon S3 to SQL Server via two different methods. 

• Method 1: Migrate Data from S3 to SQL Server via AWS Services
• Method 2: Automated S3 to SQL Server Migration using Hevo Data

Method 1: Migrate Data from S3 to SQL Server via AWS Services

• Step 1: Create an AWS S3 Bucket
• Step 2: Add Sample Data as CSV Files in S3 Buckets
• Step 3: Configure SQL Server Database Tables & Objects 
• Step 4: Create the IAM Policy for accessing S3
• Step 5: Push Data from S3 to SQL Server Instance

Step 1: Create an AWS S3 Bucket

• Log in to your Amazon Console. 
• Click on Find Services and search for S3.

• Now, click on the Create Bucket button.
• Enter the bucket name and select the region.

• Click on the Create Button. 
• Search for the Bucket, and check for access. It should not be public.

Step 2: Add Sample Data as CSV Files in S3 Buckets

• Create a file “employee_Hevo.csv.”
• Add the following components:

Employee_Id,Employee_First,Employee_Last,Employee_Title
1,Jane,Doe,Software Developer
2,Vikas,Sharma,Marketing
3,Rajesh,Kumar,Project Manager
4,Akshay,Ram,Customer Support

• In the S3 console, select the bucket name you just created. 
• Click on the upload option and follow the onscreen instructions.

Step 3: Configure SQL Server Database Tables & Objects

• Open SQL Server Management Studio and run the script below or create a similar table.

CREATE TABLE [dbo].[Employees](
[Employee_Id] [int] IDENTITY(1,1) NOT NULL,
[Employee_First] [varchar](25) NULL,
[Employee_Last] [varchar](25) NULL,
[Employee_Title] [varchar](50) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Employee_Id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 4: Create the IAM Policy for Accessing S3

• Before the data is migrated from S3 to SQL Server, you need to set up IAM policies so that the bucket you created earlier is accessible.
• Click on AWS search and search IAM.

• In the policies section, click on create policy

• Click on the choose a service button to search for S3.

• Complete and fill all the access levels and parameters.

• In the resources tab, select the bucket name and click on the Add ARN button. Enter the bucket name.

• Add the CSV file by specifying the ARN.

• Go to the review policy section and click on Create Policy.

• Create the IAM role to use the policy. Open the AWS Console, and go to IAM. Select the Roles tab. 
• Click on create the role. Follow the below mentioned order:
  • AWS service (at the top.)
  • RDS (in the middle list, “select a service to view use cases”.)
  • RDS — Add Role to Database (towards the bottom in the “Select your use case” section.)
• Click on the next: permission button. Attach the permission policies by entering the name of the policy.
• Follow the instructions on the screen. Click on review role, enter the values and then click on create the role. 

Step 5: Push Data from S3 to SQL Server Instance

• Open the AWS Console and click on RDS.
• Choose the SQL server instance name to load data from S3 to SQL Server.
• In the security and connectivity tab, select the IAM roles and click on Add IAM Roles.
• Choose S3_integration in the feature section.
• Click on Add Role button.

Limitations of a Manual Approach

• The manual method for loading data from S3 to SQL Server requires a lot more technical expertise.
• There are many steps involved in the manual method for S3 to SQL Server and in case a step gets mixed up, all the steps need to run from the start. 
• Creating IAM roles and access control is difficult.

Method 2: Automated S3 to SQL Server Migration using Hevo Data

Hevo can help you Integrate your data from 150+ data sources such as AWS S3 and load them into a destination like SQL Server to Analyze real-time data. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

With Hevo, S3 to SQL Server Migration becomes a simple two-step process:

• Step 1: Configure S3 Source in Hevo
• Step 2: Configure SQL Server Destination in Hevo

Step 1: Configure S3 Source in Hevo

A) Obtain your Access Key ID and Secret Access Key

You require a secret key to set S3 as a source. Perform the following steps to obtain your AWS Access Key ID and Secret Access Key:

• Log in to the AWS Console.
• In the drop-down menu in the profile section, click on security credentials. 

• On the Security Credential page, click on Access Keys.
• Click Create New Access Key.
• Click Show Access Key to display the generated Access Key ID and Secret Access Key. Copy the details or download the key file for later use.

B) Configuring Amazon S3 as a Source

To configure Amazon S3 as a Source in Hevo:

• In the Asset palette, click on PIPELINES. 
• In the pipeline List view, click on the Create button. 
• Select S3 in the Select Source Type.
• In the Configure your S3 Source page, specify the following:

• Pipeline Name: A unique name for the Pipeline.
• Access Key ID: The AWS access key ID that you retrieved in Step 1 above.
• Secret Access Key: The AWS Secret Access Key for the Access Key ID that you retrieved in Step 1 above.
• Bucket: The name of the bucket from which you want to ingest data.
• Bucket Region: Choose the AWS region where the bucket is located.
• Path Prefix: The prefix of the path for the directory which contains the data. By default, the files are listed from the root of the directory.
• File Format: The format of the data file in the Source. Hevo currently supports AVRO, CSV, JSON, and XML formats. Contact Hevo Support if your Source data is in another format.

For more information, refer the Hevo’s documentation for AWS S3.

5. Click TEST & CONTINUE to proceed with setting up the Destination.

Step 2: Configure SQL Server Destination in Hevo

A) Add Destination

Do one of the following:

• After the source is configured, click on Add Destination. 
• In the Asset Palette, click on Destinations.
• Click on the Create button on the Destination List View. 

B) Select Destination Type

• On the Add Destination page, select MS SQL Server.

C) Configure MS SQL Server Connection Settings

Specify the following settings in the Configure your MS SQL Server Destination page:

• Destination Name: A unique name for your Destination.
• Database Host: The SQL Server host’s IP address or DNS.
• Database Port: The port on which your SQL Server listens for connections. Default value: 1433
• Database User: A user with a non-administrative role in the SQL Server database.
• Database Password: The password for the user.
• Database Name: The name of the Destination database to which the data is loaded.
• Database Schema: The name of the Destination database schema (Default value: dbo)
• Additional Settings:
  • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your MS SQL Server database host to Hevo. This provides an additional level of security to your database by not exposing your MS SQL Server setup to the public. Read Connecting Through SSH.
  • Sanitize Table/Column Names: Enable this option to remove all non-alphanumeric characters and spaces in a table or column name, and replace them with an underscore (_). Read Name Sanitization.

D) Test Connection

• After filling in the details, click on TEST CONNECTION to test connectivity to the Destination SQL Server Database.

Organize data into ranges with SQL bucketing to simplify analysis, such as age groups or income brackets. Learn more at Bucket Data in SQL.

Conclusion

Amazon S3 is one of the best data storage solutions that support various different formats. It stores the data efficiently but getting insight from it is slightly difficult. Migrating data into a more traditional RDBMS allows us to gain insights better. This article provides two different methods that can be used to migrate data from S3 to SQL Server.

There are various trusted sources that companies use as it provides many benefits, but, transferring data from it into a data warehouse is a hectic task. The Automated data pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations such as AWS S3 & SQL Server that you can choose from.Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQ on Migrate Data from S3 to SQL Server