Integrate data from Microsoft SQL Server to Amazon Redshift using Matillion

Creating a User in Microsoft SQL Server

Creating a user in SQL Server is a two-step process: first, you must have a login at the server level, and then you create a database user mapped to that login. Below are instructions and example scripts.

1. Create a SQL Server Login

A login is required to allow authentication at the SQL Server instance level. You can create either a SQL Server authenticated login or a Windows authenticated login.

a) SQL Server Authenticated Login:

CREATE LOGIN [sample_login] WITH PASSWORD = 'StrongPassword123!';

b) Windows Authenticated Login:

CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

2. Create a Database User

After creating a login, you need to create a corresponding user in a specific database. Connect to the desired database and execute:

``` USE [YourDatabaseName]; GO

CREATE USER [sample_user] FOR LOGIN [sample_login]; ```

For a Windows user: ``` USE [YourDatabaseName]; GO

CREATE USER [DOMAIN\username] FOR LOGIN [DOMAIN\username]; ```

3. Assign Database Roles (Optional)

To give the user privileges, assign them to a database role, such as

db_datareader

db_datawriter

db_owner

ALTER ROLE [db_datareader] ADD MEMBER [sample_user];
ALTER ROLE [db_datawriter] ADD MEMBER [sample_user];

Notes

• Remember to use strong passwords, especially in production environments.
• Logins are server-level; users are database-level.
• Appropriate permissions can be granted using roles or direct GRANT statements.

Checking the JDBC driver

The JDBC driver for Microsoft SQL Server is already installed within Matillion Data Productivity Cloud. You do not need to download or install this driver separately.

To confirm that the JDBC driver is present in your environment, follow these steps:

1. Create an Orchestration Pipeline:
   In Matillion Data Productivity Cloud, create a new Orchestration pipeline within a project of your choice.

2. Add the Database Query Component:
   Drag and drop the "Database Query" component into your pipeline workspace.

3. Check the Database Types Dropdown:
   Open the settings panel for the Database Query component. In the "Database Type" dropdown menu, look for Microsoft SQL Server. The presence of this option confirms that the required JDBC driver is installed.

If you want to check the precise version of the installed driver, please refer to the instructions at the following URL:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/#database-driver-versions

Checking network connectivity

Before connecting Matillion Data Productivity Cloud to your Microsoft SQL Server database, you must ensure that the database allows incoming network connections based on your deployment configuration:

• Full SaaS Agent Configuration:
  If you are using a Full SaaS agent, the Microsoft SQL Server database must accept incoming connections from all IP addresses listed in the official documentation: Allowing IP addresses. Be sure to update any firewall rules or network access controls to permit these addresses.

• Hybrid SaaS Deployment:
  For Hybrid SaaS deployments, configure the Microsoft SQL Server to allow inbound connections from your own Virtual Private Cloud (VPC), where the Matillion Data Productivity Cloud components are hosted. To assist in verifying network access and connectivity from your VPC, you can use the utilities available at Check Network Access.

Note:
If you are configuring your Microsoft SQL Server connection using a DNS hostname rather than a static IP address, ensure that the Full SaaS or Hybrid SaaS agent is able to resolve the DNS name to the correct IP address. This may require appropriate DNS configuration or permissions within your deployment environment.

Querying Data from Microsoft SQL Server

This guide provides instructions and examples for querying data from a Microsoft SQL Server database, with considerations for data loading patterns and interoperability with Redshift.

1. Example SELECT Statements

To retrieve data from a SQL Server database, use the

SELECT

a. Select All Columns

SELECT * FROM dbo.Employees;

b. Select Specific Columns

SELECT EmployeeID, FirstName, LastName, HireDate
FROM dbo.Employees;

c. Select with Filter

SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees
WHERE Department = 'Sales';

d. Select with Aggregation

SELECT Department, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY Department;

e. Using Date Filter (for incremental loads)

SELECT EmployeeID, FirstName, LastName, LastModified
FROM dbo.Employees
WHERE LastModified > '2024-06-18 00:00:00';

2. Datatype Conversion Between SQL Server and Redshift

When transferring data from SQL Server to Amazon Redshift, you might encounter datatype differences. Here are a few common datatype mappings:

INT

INTEGER

VARCHAR(n)

VARCHAR(n)

DATETIME

TIMESTAMP

BIT

BOOLEAN

DECIMAL(p,s)

DECIMAL(p,s)

Be aware of potential conversion issues, such as: - SQL Server's

DATETIME

TIMESTAMP

UNIQUEIDENTIFIER

VARCHAR(36)

3. Recommended Data Loading Pattern

The best practice for loading data from SQL Server (e.g., using Matillion ETL) is an initial full load followed by incremental loads, both leveraging the same Database Query Component.

a. Initial Load

• Description: All data is loaded for the first time.
• Query Pattern: No filter clause.

SELECT * FROM dbo.Employees;

b. Incremental Load

• Description: Only new or changed data are loaded based on a filter (e.g., last modification date or incrementing key).
• Query Pattern: Add a

  WHERE

  clause to filter for new/updated records.

SELECT * FROM dbo.Employees
    WHERE LastModified > @LastLoadTimestamp;

Note: Replace

@LastLoadTimestamp

with the actual timestamp value that marks the end of the previous successful load.

• Reference: More details on incremental loading and data replication strategy are available on the Matillion Exchange.

By following this pattern—an initial full extract, then efficient incremental updates—you ensure robust, scalable, and reliable data replication between SQL Server and your destination database (such as Redshift).

Data Integration Architecture

Loading data in advance of integration is a hallmark of the Extract, Load, and Transform (ELT) architecture, offering a "divide and conquer" approach by handling ingestion and transformation as two discrete steps. Once raw data is ingested into Amazon Redshift, data integration activities, such as cleaning, joining, and reformatting, are performed as subsequent transformation pipelines within the database itself. These pipelines are essential for producing high-quality, analytics-ready data. A key advantage of the ELT approach is that it allows all transformation and integration operations to happen inside the target Redshift environment, providing speed, scalability, and the ability to run transformations on demand. This native processing model also eliminates the need to provision and maintain separate data processing infrastructure, reducing both operational overhead and cost.