Guides

SQL Server

Suggest Edits

Connector Details

Connector AttributesDetails
NameMicrosoft SQL Server
DescriptionMicrosoft SQL Server is a comprehensive relational database management system (RDBMS) renowned for its performance, security, and scalability. Offering a range of editions tailored to various needs, SQL Server caters to small businesses, enterprises, and cloud-based applications alike. With features like Always On availability groups for high availability, Transparent Data Encryption (TDE) for data security, and column store indexes for analytical processing, SQL Server provides robust solutions for mission-critical workloads. Its integration with Azure services enables hybrid and cloud-native deployments, while tools like SQL Server Management Studio (SSMS) streamline database administration tasks. With continuous updates and advancements, Microsoft SQL Server remains a top choice for organizations seeking a reliable and feature-rich database solution.
Connector TypeClass A

Features

Feature NameFeature Details
Load StrategiesFull Load, Incremental Load
Metadata ExtractionSupported
Data AcquisitionSupported
Data PublishingNot Supported
Automated Schema Drift HandlingSupported

Source Connection Attributes

Connection ParametersData TypeExample
Connection NameStringSQLServerConnection
Server NameStringsqlserver.example.com
Database NameStringmydatabase
User NameStringmyuser
PasswordStringmypassword
Always EncryptedBooleanTrue or False
Trust Server CertificateBooleanTrue or False
Connection TimeoutNumeric30
EncryptBooleanTrue or False
Persist Security InfoBooleanTrue or False
Bronze Schema (Optional)String
Silver Schema (Optional)String

Connector Specific Configuration Details

  1. Trust Server Certificate = False is the default value, if Trust Server Certificate is not defined
  2. Persist Security Info=False is the default value, if Persist Security Info is not defined
  3. Connection Timeout=30 is the default value, if Connection Timeout is not defined
  4. Encrypt = True is the default value, if Encrypt is not defined
  5. SQL Server connector has optional values such as Bronze Schema and Silver Schema

Screenshot To Use Connector

Supported load strategies

Load strategies can be configured in the Metadata Catalog View of the DataSource through the User Interface (UI), or manually in the Object List state DB table for features that are not accessible via the UI.

  1. Full Load: select Full as value for Load Strategy in metadata catalog of Data source.

  2. Incremental Load: Load Strategy and Watermark Type defines the various types of incremental loads.
    Only TIMESTAMP is supported as Merge Strategy from UI. For other type of Merge strategies updating the WaterMarkType of ObjectList table is required manually.

    a.Timestamp based extraction:
    Select the following fields in metadata catalog
    1. LoadIncremental = 1
    2. WaterMarkType = TIMESTAMP/ DATE
    3. Watermark Column
    The Watermark Date will be updated in Object Extract table during the extraction process.


    b.Change Tracking extraction:
    Pre-requisite is to enable change tracking on the required entitiy on source system side.
    In [state].[ObjectList] table set the following in order to use the change tracking
    1. LoadIncremental = 1
    2. WaterMarkType = CHANGE_TRACKING
    The following fields will be updated in Object Extract during the extraction process for change tracking
    1. WatermarkId
    2. Watermark Date


    c.Identity Tracking extraction:
    In [state].[ObjectList] table set the following in order to use the Identity tracking
    1. LoadIncremental = 1
    2. WaterMarkType = 'IDENTITY'
    3. Watermark Column
    The WatermarkId (Object Extract) will be updated during extraction process.

Updated about 1 month ago