AWS RDS MSSQL to BigQuery Integration: Maximizing Analytical Potential

Easily move your data from AWS RDS MSSQL to BigQuery to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Data is one of the most valuable assets, allowing corporations to make insightful decisions to boost their business performance. By efficiently utilizing their on-premise data, companies are transitioning towards an advanced analytical environment to extract more profound insights.

AWS Relational Database Service (RDS) is an Amazon data management web service that can help you manage your data efficiently. Eventually, you can transfer this data into a data warehousing service such as BigQuery for improved analytics and scalability.

In this article, you will learn about the AWS RDS MSSQL to BigQuery data integration in two ways. These methods will help you transfer data from your SQL Server instance to an analytically optimized Google BigQuery environment.

An Overview of Amazon RDS

Amazon Relational Database Service (Amazon RDS) is a fully managed component of Amazon Web Services (AWS) that allows you to create, operate, and scale a relational database on the cloud. It offers a cost-effective, resizable capacity for an industry-standard relational database while handling everyday database management tasks.

Amazon RDS supports some of the most popular relational databases, including SQL Server, MySQL, and PostgreSQL, among others. With RDS Custom, you can utilize Amazon RDS’s managed features while managing the host and customizing the OS as in Amazon EC2. To know more, refer to Working with Amazon RDS Custom.

An Overview of Google BigQuery

Google BigQuery is a data warehousing service that helps you manage your data with its functionalities. Its serverless architecture allows you to perform complex SQL queries that can answer your business questions without infrastructure management.

With BigQuery’s distributed analysis engine, you can perform operations on terabytes of data in seconds and petabytes in minutes. It also contains built-in AI that provides features like code assist and intelligent recommendations to help you manage costs while enhancing productivity. Check out the Best Practices for BigQuery.

Methods to Integrate AWS RDS MSSQL to BigQuery

Let’s look into the steps of using Hevo Data for an AWS RDS MSSQL to BigQuery integration.

Step 1: Configuring Amazon RDS SQL Server as a Source

Prerequisites

• MS SQL Server 2008 version or higher.
• For Pipeline Mode of Change Tracking or Table and Change Tracking Query mode,
  • Enable Change Tracking
  • Grant the database user ALTER DATABASE and CHANGE TRACKING privileges.
• Whitelist Hevo’s IP Addresses.
• Grant SELECT and VIEW CHANGE TRACKING privileges to the database user.
• You must have the designation of Team Administrator, Pipeline Administrator, or Team Collaborator role in Hevo.

Following the steps mentioned, you can easily set Amazon RDS SQL Server as your data pipeline source. You can follow the official Hevo Documentation on Amazon RDS SQL Server to learn more about the steps.

Step 2: Setting up Google BigQuery as a Destination

Prerequisites

• An available GCP project. If you don’t have one, refer to Create a Google Cloud project.
• You must assign the essential roles to the connecting Google account for the GCP project.
• Your GCP project must have an active billing account.
• To create a Hevo Destination, you’re assigned a Team Collaborator or any administrative role apart from the Billing Administrator.

With just two simple steps, you can move your data from AWS RDS MSSQL to BigQuery. You can also refer to the Hevo BigQuery Destination Documentation for in-depth information about the steps involved.

Method 2: Integrating AWS RDS MSSQL to BigQuery Using Amazon S3 and Google Cloud Storage

In this method, you must transfer your AWS RDS SQL Server data to BigQuery using Amazon S3 and Google Cloud Storage as intermediate staging.

Step 1: Migrating Data from Amazon RDS MSSQL to Amazon S3

This step helps migrate data from Amazon RDS MSSQL to an Amazon S3 bucket using the AWS Management Console. But before proceeding, you must ensure that you meet the prerequisite conditions.

Prerequisites

Follow the steps below to generate an IAM policy:

• Choose Policies in the navigation pane from the IAM Management Console.
• You must create a new policy and use the Visual editor tab for the following steps.
• For Service, enter S3 and select the S3 service.
• For Actions, choose the following: ListAllMyBuckets, ListBucket, GetBucketACL, GetBucketLocation, GetObject, PutObject, ListMultipartUploadParts, and AbortMultipartUpload.
• The available options for Resources will depend on the actions you previously selected. For the bucket option, mention the desired bucket’s Amazon Resource Name (ARN). For objects, specify the bucket ARN, and then choose one of the following options:
  • Select Any for both Bucket name and Object name to grant permission to all the files in the bucket.
  • Specify the ARN for specific files or folders you want to grant SQL Server access to.
• Finally, you must follow the console instructions until you finish creating the policy.

After performing these steps, you can follow the steps given below to create an IAM role for the IAM policy from the previous process.

• Select Roles in the navigation pane on the IAM Management Console.
• Create a new IAM role, choose the AWS service, RDS, and RDS – Add Role to Database options. After completing this step, select Next:Permissions at the bottom.
• For the Attach permission policies, enter the name of the IAM policy previously created.
• Follow the instructions in the console to complete the role creation.

Transferring Data from RDS MSSQL to Amazon S3

After satisfying the prerequisites, you can integrate Amazon RDS SQL Server to S3 by following the steps below:

• Sign in to the AWS Management Console and open the RDS console.
• Select the RDS for SQL Server DB instance name.
• In the Manage IAM roles section, choose the IAM role on the Connectivity & security tab to Add IAM roles to this instance.
• Select S3_INTEGRATION for Feature.

• Select Add role.

To learn more, refer to AWS Documentation on Integrating AWS RDS MSSQL to S3.

Step 2: Moving Data from Amazon S3 to Google Cloud Storage

You can follow the steps in this section to move your data from Amazon S3 to GCS, where BigQuery can easily access the data.

• You can export all the data present in Amazon S3 into CSV format. To download the data to your system, follow the instructions for Downloading an Object Using an S3 Console.
• After downloading data from S3, you can follow the steps to Upload an object to a GCS bucket to move data to GCS.

Following these steps, you can quickly move data from Amazon S3 to GCS.

Step 3: Moving Data from Google Cloud Storage to Google BigQuery

In this step, you will move the data stored in GCS to Google BigQuery. You must utilize the BigQuery Console to upload the CSV data. You can refer to the Loading CSV data from Cloud Storage to learn more about this process, the permissions required, and the associated limitations.

Follow the steps given below to move data from a Google Cloud Storage to BigQuery:

• Visit the BigQuery page on the Google Cloud Console.
• In the Explorer pane, expand your project to select a dataset.
• Select + Create table in the Dataset info section.
• Specify the necessary details in the Create table panel to create a table.

Following these steps, you can move your data from GCS to Google BigQuery.

Limitations of Using Manually Migrating Data from AWS RDS MSSQL to BigQuery

The method to connect and load data from AWS RDS MSSQL to BigQuery using Amazon S3 and Google Cloud Storage is an effective way. However, there are certain limitations that you must be aware of while using this method of integration between AWS RDS MSSQL and BigQuerys table.

• Time consumption: Many steps associated with this method require manual effort, increasing the time needed for the integration process. It may not be the best choice for real-time integration requirements.

• Complexity: This method requires reviewing all the necessary documentation to achieve the integration, which can complicate the process. It also requires prior technical knowledge.

• Risk of Error Production: Following this method requires manual efforts. This might even introduce errors and generate inconsistencies in the data transfer process.

Use Cases of Moving Data from AWS RDS MSSQL to BigQuery

• Perform Advanced Analysis: AWS RDS MSSQL to BigQuery integration allows you to perform ad hoc analysis, business intelligence, geospatial analysis, and machine learning on your data to produce actionable insights.

• Warehouse Storage: Moving your data from AWS RDS MSSQL to BigQuery allows you to store it in an analytics-ready format. BigQuery provides full support for database transaction semantics (ACID).

• Manage Data Types: Moving data from AWS RDS MSSQL to BigQuery allows you to easily manage all data types with fine-grained access controls across Google Cloud. To know more, refer to BigQuery Omni.

Conclusion

This article describes two effective methods to convert AWS RDS MSSQL to BigQuery. Although both methods successfully load data from AWS RDS SQL Server to BigQuery, the custom method using S3 and GCS is associated with some drawbacks.

You can use Hevo Data to load data from AWS RDS MSSQL to BigQuery efficiently. Hevo provides 150+ source connector options to help you connect your data without interruptions. Sign up for Hevo’s 14-day free trial and experience seamless data migration today!

Interested in reading about more integrations from different databases to BigQuery? Here are some interesting reads:

• MySQL to BigQuery
• Aurora to BigQuery
• DynamoDB to BigQuery ETL

Frequently Asked Questions (FAQs)