Connecting Redshift SQL Server: Boost Data Performance

Data migration between different platforms is critical to consider when generating multiple organizational strategies. One such migration involves transferring data from a cloud-based data warehousing service environment to a relational database management system.

Amazon Redshift SQL Server Integration can give organizations an edge, allowing them to conduct enhanced analysis and reporting through custom report application development.

This article will guide you through easy-to-use methods that you can follow to migrate to SQL Server from Redshift.

An Overview of Amazon Redshift

Amazon Redshift, offered by Amazon Web Services (AWS), is a cloud data warehousing service that helps corporations manage large amounts of data and produce valuable insights.

Amazon Redshift provides you with a wide variety of features that can assist you meet the needs of your business requirements. Some popular features include its parallel processing abilities, automated data compression to reduce storage space requirements, columnar storage that makes the data easy to query by only considering specific columns, and many more.

An Overview of SQL Server

SQL Server is a popular Relational Database Management System (RDBMS) provided by Microsoft Corporation. It offers many features that you can use to create, manage, and analyze databases.

Some of the key features that SQL Server provides include storing, retrieving, and securely managing relational data; it supports Transact SQL (TSQL), which enables you to query and manipulate data in your database. SQL Server also includes integration, analysis, and reporting capabilities that help in data modeling and generate useful insights.

Methods of Integrating Amazon Redshift to SQL Server

Let’s look at the methods to help you achieve this SQL Server Redshift Migration.

Method 1: Using Hevo Data to Connect Redshift to SQL Server

Hevo Data is a no-code, real-time ELT data pipeline platform that can automate your data pipeline in a cost-effective way according to your requirements. Hevo Data provides many features, including instant data integration, loading data in real-time, and transforming data to make it analytics-ready.

With Hevo’s easy-to-use UI, you can integrate your data from Redshift to SQL Server in just a few clicks. This powerful SaaS tool also allows you to model your data without prior technical knowledge.

Here are some of Hevo’s key features.

• Data Transformation: Hevo provides simple Python-based and drag-and-drop data transformation techniques to help prepare your data for further analysis.

• Incremental Data Load: Hevo allows you to transfer modified data in real time; this results in effective bandwidth utilization on the source and the destination.

• Automated Schema Mapping: Hevo detects the format of the incoming data and maps the destination schema to complement it. You can choose the way to map your destination schema according to your requirements by selecting Full & Incremental Mappings.

• Multi-Region Support: Hevo supports maintaining a single account across all the present Hevo regions, with a maximum of five workspaces.

Let’s look into the different steps involved in using Hevo Data to connect the Redshift SQL Server.

1. Configuring Amazon Redshift as the Source

You can configure Amazon Redshift as your source by following the steps below. However, before getting started, you must ensure you satisfy the prerequisites.

Prerequisites

• An active AWS account.
• A running instance of Amazon Redshift.
• The database hostname and port number of the Source instance.
• You are assigned a Team Administrator, Team Collaborator, or Pipeline Administrator role in Hevo.

Step 1: Whitelisting Hevo IP Address for your Region

To enable Hevo to connect to the Redshift database, you need to whitelist Hevo’s IP Address for your region.

• First, you need to log in to Amazon Redshift Dashboard.

• Click Clusters on the left navigation pane.

• Click the Cluster you want to connect to Hevo.

• In the Properties tab, click the link under the VPC security group on the Network and security settings panel; a Security Groups page will open.

• On the Security Groups page, click Inbound rules > Edit inbound rules.

• On the Edit inbound rules page, click on Add Rule. In the Type column, you have to select Redshift from the drop-down. Enter the port of your Amazon Redshift cluster in the Port Range column.

• Select Custom from the drop-down in the Source column and enter the Hevo IP address of your region. Keep repeating these steps until all the IP addresses are whitelisted, and finally, click on Save.

Step 2: Creating and Granting Privileges to a Database User

• To create a database user, you can either log in to the Redshift database as a superuser or a user allotted with CREATE privileges. Enter the following command:

CREATE USER hevo WITH PASSWORD '<password>';

• To grant SELECT privileges to the user, you must be logged in to the Redshift database as a superuser. Enter the following command:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO hevo; 
GRANT SELECT ON TABLE <schema_name>.<table_name> TO hevo;

• You can also view the list of tables that are available in a schema by following these commands:

SELECT distinct(<table_name>) FROM pg_table_def WHERE <schema_name> = 'pg_catalog';

Step 3: Setting up Amazon Redshift as the Source

• In the Navigation Bar, click on PIPELINES.

• In the Pipelines List View, select the + CREATE option.

• Click on Amazon Redshift on the Select Source Type page.

• Specify the mandatory details on the Configure your Amazon Redshift Source page. This includes Pipeline Name, Database Cluster Identifier, Database Port, Database User, Database Password, Database Name that you want to replicate, Select an Ingestion Mode that you want to use to ingest data, and enable the Connect through SSH option.

• You can enable Include New Tables in the Pipeline; this is applicable for ingestion modes apart from Custom SQL. If enabled, Hevo will automatically ingest data from the source tables created after the pipeline creation.

• Finally, click on TEST CONNECTION > TEST & CONTINUE, and proceed to set up the destination.

Step 4: Query Mode Settings

Once the source has been set up, you can define any one of the given steps according to the selected data ingestion mode:

• For Table mode Pipelines

• Select the objects that you want to copy from the Select Objects Page, and click on CONTINUE; each object represents a table in your database.

Select the query mode that you want to use for each object on the Configure Objects page.

• For Custom SQL mode Pipelines:

• On the Provide Query Settings page, fetch data from the source using the custom SQL query.

Select query mode from the Query Mode drop-down, and click on CONTINUE.

For more information on configuring Amazon Redshift as a source, you can refer to the Hevo Data Redshift Documentation.

2. Configuring SQL Server as the Destination

To configure SQL Server as a destination, follow the steps in this section. But first, ensure the prerequisites are satisfied.

Prerequisites

• A running SQL Server instance.
• SQL Server’s database host’s DNS or IP and port must be available. If you are not aware of these credentials, you can easily obtain them from your SQL Server administrator.
• You are assigned as a Team Collaborator, or some other administrative role apart from Billing Administrator role in Hevo.

Step 1: Creating a Database User and Granting Privileges

• Sign in to your SQL Server instance as a master user using an SQL client tool, such as sqlcmd. For instance, sqlcmd -U hevouser.

• Access your preferred database where you want to add the user using the following command:

USE <database_name>
GO

You can use an existing user, but if you want to create a new user, you can use this command:

CREATE LOGIN [<login_name>] WITH PASSWORD=N'<strong_password>', DEFAULT_DATABASE=[<database_name>]
GO

To create a database user for the given login user, edit the placeholder space with the original credentials:

CREATE USER [<username>] FOR LOGIN [<login_name>]
GO

Now, in order to grant the privileges to the database user to create a database object so that data can be loaded and stored in the objects, you can follow these steps.

• Log in to your SQL Server instance using the SQL client tool, such as sqlcmd, as a login user.

• Use the following command to access the database in which you created the database user:

USE <database_name>
GO

To grant the necessary privileges to the database user, enter the following command and replace the placeholder values with your own credentials:

GRANT ALTER, SELECT, INSERT, UPDATE ON SCHEMA::<schema_name> TO <username>
GO
GRANT CREATE TABLE, INSERT, SELECT, UPDATE ON DATABASE::<database_name> to <username>
GO

Step 2: Configuring SQL Server Connection Settings

• On the Navigation Bar, click on DESTINATIONS.

• Select + CREATE in the Destinations List View.

• Select SQL Server on the Add Destination page.

• Fill out the necessary fields on the Configure your SQL Server Destination page. Enter the Destination Name, Database Host, Database Port, Database User, Database Password, and Database Name.

• Additionally, enable the Connect through SSH option to connect to Hevo using SSH tunnel; for more information, read Connecting Through SSH.

• Enable Sanitize Table/Column Names to remove all non-alphanumeric characters and spaces from the table or column names.

Finally, click on TEST CONNECTION and select SAVE & CONTINUE.

Disable the foreign keys defined in the target table; they don’t allow data loading until the reference table has an associated key defined. You can only replicate data of 1018 columns in this SQL Server table; for more information, go through the Limits on the Number of Columns. By following these steps, you can access Hevo Data’s capabilities to integrate Redshift SQL Server.

If you want to transfer data from SQL Server to Redshift using Hevo Data you can refer to SQL Server to Redshift blog post. Hevo Data provides 150+ connector options; it can easily help you load data from SQL Server to Redshift. For more information on configuring SQL Server as a destination, you can refer to the Hevo Data SQL Server Documentation.

Method 2: Accessing Amazon Redshift Data Using Linked Servers

In the second method, you will use a linked server to access Amazon Redshift data with the help of SQL Server. Linked servers allow you to read data from external sources and perform instructions outside the SQL Server instance. In this method, you will connect Redshift using SQL Server Management Studio.

Prerequisites

Before connecting the Redshift SQL Server through a linked server, ensure you satisfy the following prerequisites:

• Install and configure the Amazon Redshift ODBC Driver. You can refer to AWS Documentation to learn more.
• You must have Administrator rights for your system.
• Available disk space of 100 MB.

Step 1: Configure a SQL Server Linked Server to Amazon Redshift

• Select your SQL Server instance in Management Studio and navigate to the Object Explorer.

• Right-click on Linked Servers in Server Objects, then select New Linked Server.

• Fill out the name of the SQL Server instance you want to link to on the General Page.

• Choose any OLE DB server type apart from SQL Server, and select the desired OLE DB data source, for instance, ODBC Drivers.

• Enter the Product name, and the Data source will be the ODBC data source that you created before.

The Remote Procedure Call (RPC) OUT setting is important for linked servers in SQL Server. An RPC is a stored procedure that runs from one server to another linked server.

You can also use these T-SQL commands to create a linked server by providing the placeholder credential; in the code given below, @datasrc is the placeholder.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’REDSHIFT’
, @srvproduct=N’Redshift’
, @provider=N’MSDASQL’
, @datasrc=N’demo’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’REDSHIFT’
,@useself=N’False’
,@locallogin=NULL
,@rmtuser=NULL
,@rmtpassword=NULL
GO

Step 2: Access Amazon Redshift Data with SQL Server

After the linked server connection has been established, you will be able to see the remote data under the SSMS Object Explorer.

Step 3: Querying the Amazon Redshift Data with SQL Server

You can join and merge data into the Redshift environment, insert data into Redshift, or load data on the SQL server by following these commands:

USE demo
GO

CREATE TABLE tb01 (
id INT IDENTITY
, first_name VARCHAR(50)
, last_name VARCHAR(50)
, age SMALLINT
)
GO

TRUNCATE TABLE dbo.tb01
GO

INSERT INTO dbo.tb01 (first_name, last_name, age) VALUES ('douglas', 'correa', 36)
GO

EXEC('create table demodb.public.tbdemo(
id integer not null
,name varchar(100)
,birth timestamp ) ') AT DEMO 

EXEC('insert into demodb.public.tbdemo values(1, ''douglas correa'', getdate() )') AT DEMO
EXEC('insert into demodb.public.tbdemo values(2, ''renato silvestre'', getdate() )') AT DEMO
GO

EXEC('delete from demodb.public.tbdemo') AT DEMO
GO

SELECT T.id, T.first_name, T.last_name, T.age
FROM OPENQUERY(DEMO,'select id, name, birth from demodb.public.tbdemo') AS OQ
INNER JOIN dbo.tb01 AS T ON OQ.id = T.id
GO

MERGE dbo.tb01 AS target
USING (SELECT * 
FROM OPENQUERY(DEMO,'select id, name, birth from demodb.public.tbdemo') AS OQ
) AS source (id, name, birth) 
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.age = DATEDIFF(yyyy, GETDATE(), source.birth)
WHEN NOT MATCHED THEN
INSERT (first_name, age) 
VALUES (SUBSTRING(source.name, 0, CHARINDEX(' ',source.name)), DATEDIFF(yyyy, GETDATE(), source.birth))
OUTPUT Inserted.id, Inserted.first_name, Inserted.age;
GO

After running this script, you will see the option on your Results Console.

The MERGE code that is mentioned above produces the output:

Finally, the SELECT with INNER JOIN produces the result:

The results of these operations will depend upon the compatibility of the data type and the type conversion rules. Similar data types can be implicitly converted, whereas if different data types are present, you will see some differences in the results. By following the given steps, you can connect the Redshift SQL Server with a linked server.

If you notice any issues with the performance of the ODBC drivers or some large disk activities for simple query tasks, you can disable ODBC Tracing, or you can use the Tracing Panel of ODBC Data Source Administration to disable tracing within your system.

Limitations of Using Linked Servers

There are certain limitations associated with the use of linked servers to connect SQL Server to Amazon Redshift.

• Data Mapping Issues: If the data types are incompatible in Redshift and SQL Server, this might result in data conversion issues.

• Time Consumption: Following this method can be beneficial if you have all the prerequisites set up beforehand; as this method involves manual efforts, it can be time-consuming to follow the steps thoroughly.

• Lack of Automation: This method lacks automation; it requires effort and technical expertise. Performing the tasks also requires you to code the T-SQL commands.

Use Cases of Connecting Redshift to SQL Server

There are multiple advantages of connecting Amazon Redshift to an SQL Server; let’s look at some of the main positives of this integration.

• Moving data from Redshift to SQL Server allows a data backup facility, which can provide an extra security layer for disaster recovery. By moving your data to a SQL Server, you can minimize the risk of data loss.

• By transferring data from Redshift to SQL Server, you can leverage the capabilities of tools like SQL Server Reporting Services that can help you generate essential reports and create dashboards.

• Integrating SQL Server to Amazon Redshift can help you save historical data and optimize storage while reducing the cost of storage.

Conclusion

A Redshift SQL Server connection can provide various advantages to assist your business with analytics and reporting. It also introduces you to data backup opportunities that can be useful during any disaster that leads to data loss.

In this article, you have seen two effective methods for establishing a Redshift SQL Server migration. One method is to use linked servers. However, this method has certain limitations, such as a lack of automation, time-consuming, and data mapping issues.

Leveraging Hevo Data’s impressive capabilities for your Redshift SQL Server integration can help overcome the limitations associated with this method. It provides an intuitive UI that requires no coding expertise and 150+ sources for hassle-free integration.

Want to connect your Redshift data to some other destination? Here are some of the interesting integrations:

• Connect Amazon Redshift to DynamoDB
• Redshift to Snowflake Data Migration Made Easy
• Connect Redshift to PowerBI: A Comprehensive Guide

Frequently Asked Questions (FAQs)

Q. What is the risk of using Redshift as the only database?

• As an online analytical processing (OLAP) database, Redshift is a good choice, especially for companies working in the AWS ecosystem. It can help save overall costs. However, relying on Redshift as a database to conduct every operation is not a good consideration, specifically for online transaction processing (OLTP) workloads. Redshift is not optimized as an OLTP database.

Q. What is the main difference between Redshift and SQL Server?

• Redshift is a large-scale data warehousing service built for OLAP-style queries, while SQL Server, like any other relational database, is really efficient in performing OLTP workloads. Redshift can perform parallel processing on large amounts of data to perform analytical tasks. In contrast, SQL Server is incredibly powerful in storing, updating, and deleting data, as the data is stored in row-based format.

Want to take Hevo for a spin? 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 Hevo pricing that will help you choose the right plan for your business needs.

Visit our Website to Explore Hevo