Microsoft SQL Server as Source

UnifyApps enables seamless integration with Microsoft SQL Server databases as a source for your data pipelines. This article covers essential configuration elements and best practices for connecting to MSSQL sources.

Overview

Microsoft SQL Server is widely used for enterprise applications including business intelligence, transaction processing, and analytics platforms. UnifyApps provides native connectivity to extract data from these MSSQL environments efficiently and securely.

Connection Configuration

To set up a MSSQL source, navigate to the Connections section, click New Connection, and select Microsoft SQL Server. Fill in the parameters above based on your MSSQL environment details.

For SSH tunnel connections, additional parameters are required:

• SSH Host: Hostname or IP address of the SSH server

• SSH Port: Port for SSH connections (default 22)

• SSH User: Username for SSH authentication

• SSH Authentication: Password or key-based authentication

Server Timezone Configuration

When adding objects from a MSSQL source, you'll need to specify the database server's timezone. This setting is crucial for proper handling of date and time values.

• In the Add Objects dialog, find the Server Time Zone setting

• Select your MSSQL server's timezone

This ensures all timestamp data is normalized to UTC during processing, maintaining consistency across your data pipeline.

Ingestion Modes

Choose the mode that aligns with your business requirements during pipeline configuration.

Change Data Capture (CDC) Requirements

For Historical and Live or Live Only modes that track ongoing changes, CDC must be properly configured:

1. Database-Level Configuration:

   USE YourDatabaseName;
   GO
   EXEC sys.sp_cdc_enable_db;
   

2. Table-Level Configuration:

   EXEC sys.sp_cdc_enable_table  
       @source_schema = N'YourSchemaName',
       @source_name   = N'YourTableName',
       @role_name     = NULL;  -- Or specify a role, e.g., N'cdc_role'
   

3. Verification queries:

   • To check database CDC status:

     SELECT 
         name, 
         is_cdc_enabled
     FROM 
         sys.databases
     WHERE 
         name = 'YourDatabaseName';
     

   • To check table CDC status:

     SELECT 
         name, 
         is_tracked_by_cdc
     FROM 
         sys.tables
     WHERE 
         is_tracked_by_cdc = 1;
     

4. SQL Server Agent: Ensure SQL Server Agent is running as it's required for CDC operations.

CRUD Operations Tracking

All database operations from MSSQL sources are identified and logged as unique actions:

This comprehensive logging supports audit requirements and troubleshooting efforts.

Supported Data Types

All common MSSQL data types are supported, including specialized types for transactional systems and modern applications.

Prerequisites and Permissions

To establish a successful MSSQL source connection, ensure:

• Access to an active MSSQL Server instance

• Minimum required role: "db_datareader" on the target database

• Recommended permission: SELECT on relevant tables or views

• Proper network connectivity and firewall rules

Common Business Scenarios

Customer Data Integration

• Extract customer records from MSSQL CRM databases

• Ensure proper handling of nullable fields and relationships

• Map customer hierarchies and related entities

Financial Reporting

• Connect to MSSQL financial systems to extract transaction data

• Configure date/time handling for fiscal period accuracy

• Implement data quality checks for financial integrity

Product Inventory Management

• Extract product catalog and inventory levels from MSSQL

• Set up incremental sync for high-volume inventory changes

• Ensure proper handling of product hierarchies and attributes

Best Practices

By properly configuring your MSSQL source connections and following these guidelines, you can ensure reliable, efficient data extraction while meeting your business requirements for data timeliness, completeness, and compliance.