Microsoft SQL Server

About

The SQL Server plugin is in public preview on the Snowflake Marketplace with a 7-day free trial. Please contact us to discuss any extra requirements.

Omnata's integration service replicates data from your SQL Server source database and loads it into your destination at regular intervals as well as providing direct query capabilities to your SQL Server database.

Architecture

Omnata connects to your database through a SSH Tunnel which is provided by ngrok. Omnata establishes a secure TLS tunnel to the ngrok server and then connects to the SQL Server database through a tunnel established from your . This ensures that your database is not exposed to the internet and that your data is secure.

Supported Services

Generic SQL Server
Azure SQL Database
Azure SQL Managed Instance
Amazon SQL Server RDS

Authentication

SQL Server Authentication

Omnata supports SQL Server Authentication. When you create a connection, you will need to provide the username and password for the SQL Server user you want to use to connect to the database. If connecting to Azure SQL Database, you will need to provide the username@servername for the username instead of just the username.

Configuration

SQL Server Configuration

No additional configuration is required for SQL Server databases unless you are wanting to use Change Data Capture (CDC).

SQL Azure Database Configuration

Public Access - Ensure Public Network Access is set to Selected Networks and add a virtual network rule to allow access from the vnet where the ngrok agent is running
Private Access - Create a new private endpoint for the Azure SQL Database
Connectivity - Update the connection policy to Proxy to allow access from the private endpoint

Tunnels Configuration

The Omnata SQL Server plugin uses ngrok to establish a secure tunnel to your SQL Server database. This ensures that your database is not exposed to the internet and that your data is secure.

In order to establish a tunnel to your SQL Server database, you will need to deploy the ngrok agent on your network which has access to your SQL Server. The ngrok agent will establish a secure TLS tunnel to the ngrok server and then connect to your SQL Server database through a tunnel established from your SQL Server host machine to the ngrok server.

ngrok Agent Download

For MacOS, use Homebrew:

brew install ngrok/ngrok/ngrok

For Linux, use Apt:

curl -s https://ngrok-agent.s3.amazonaws.com/ngrok.asc | \
  sudo gpg --dearmor -o /etc/apt/keyrings/ngrok.gpg && \
  echo "deb [signed-by=/etc/apt/keyrings/ngrok.gpg] https://ngrok-agent.s3.amazonaws.com buster main" | \
  sudo tee /etc/apt/sources.list.d/ngrok.list && \
  sudo apt update && sudo apt install ngrok

For Windows, use Chocolatey (You'll need to run this in an Administrator Command Prompt):

choco install ngrok

If you don't have one of these package managers installed or prefer to install the ngrok agent yourself, you can download the ngrok agent from the Ngrok website and install it on your network.

ngrok Agent Configuration

You can run the ngrok agent either as a standalone process or as a service. To run the ngrok agent as a standalone process, follow the steps provided in the connection setup which will look like this:

ngrok tunnel --authtoken <<omnata authentication token>> --label edge=<<omnata label>> [address]:[port]

To configure the ngrok agent to run as a service, follow these steps:

Locate the configuration directory for the ngrok agent.
- For MacOS, the configuration directory is ~/.config/ngrok
- For Linux, the configuration directory is ~/Library/Application Support/ngrok
- For Windows, the configuration directory is %HOMEPATH%\AppData\Local\ngrok
In the directory, create a configuration file (called omnata.yml) which will be used by ngrok agent. The configuration file should contain the following information:

version: "2"
authtoken: <<omnata authentication token>>
tunnels:
  omnata:
    labels:
      - edge=<<omnata label>>
    addr: [address]:[port]

replacing the placeholders with the appropriate values. If you run the agent on the same server as the SQL Server, you can just the port number. If you run the agent on a different server, you will need to use the hostname of the server where the SQL Server is running as well as the port.

Install the ngrok agent as a service by running the following command:

ngrok service install --config=%HOMEPATH%\AppData\Local\ngrok\omnata.yml

Start the ngrok service by running the following command:

ngrok service start --omnata

Inbound Syncs

Omnata connects to your database using SQL Server's default transaction isolation level, Read Committed. This ensures that the data you are reading is not locked by other transactions. Omnata uses the following methods to replicate data from your SQL Server source database:

Change Data Capture (CDC)
Highwater Mark

Change Data Capture (CDC)

If you have a SQL Server database that supports Change Data Capture (CDC), Omnata can use CDC to replicate data from your source database. CDC is a feature that captures insert, update, and delete operations that are performed on a table and makes the details of the changes available in change tables. Omnata reads the change tables to replicate the changes to your destination database. Omnata uses the CDC instance to capture changes to the source database and replicate them to the destination database and then extracts any records not currently found in your CDC instance from the source table.

Using the APPEND strategy will run the get_all_changes function to get all changes from the CDC instance and then append them to the destination table. This is the default merge strategy for CDC syncs. Using the MERGE strategy will run the get_net_changes function to get net changes from the CDC instance and then merge them into the destination table. This merge strategy is useful when you only need to keep the latest version of a record in the destination table.

Full Refresh / Incremental (High Watermark)

If you have tables which are not CDC enabled or Views you need to sync, Omnata can use Highwater Marks to replicate your data from your source database. This method is less efficient than CDC and only captures current state each run, therefore the only history available is from the last sync.

NOTE: Omnata recommends the use of Highwater Mark method to sync only tables that do not have a CDC instance available. This method is less efficient than CDC and only captures current state each run, therefore the only history available is from the last sync.

If a table or view don't have only of the following field types available date, datetime, datetime2, smalldatetime, datetimeoffset or rowversion field available, then only a full refersh option will be available.

Outbound Syncs

Omanta currently does not support outbound syncs for SQL Server databases. If this is a feature you would like to see, please let us know.

Functions

To enable you to query your SQL Server database directly from your destination database, Omnata provides a set of UDFs that you can use to query your SQL Server database. These UDFs are available in the OMNATA_MICROSOFT_SQL_SERVER_PLUGIN database and the UDFS schema.

SQL_QUERY

Executes a SQL query on the source database and returns the result as a table.

Parameters:

CONNECTION_SLUG (VARCHAR): The slug of the connection to query
DATABASE_NAME (VARCHAR): The name of the database to query
QUERY (VARCHAR): The SQL query

Examples

select *
from table(OMNATA_MICROSOFT_SQL_SERVER_PLUGIN.UDFS.SQL_QUERY(
    'my-sql-server-connection',
    'AdventureWorks2022',
    'select * from [Sales].[Customer]'
))

SQL_QUERY_FILTER

Executes a SQL query (with a filter applied) on the source database and returns the result as a table. This has been designed for use with Lambda views.

Parameters:

CONNECTION_SLUG (VARCHAR): The slug of the connection to query
DATABASE_NAME (VARCHAR): The name of the database to query
QUERY (VARCHAR): The SQL query
COLUMN_NAME (VARCHAR): The name of the column to use in the where clause
OPERATOR (VARCHAR): The operator for the where clause
COLUMN_VALUE (VARCHAR): The value to use in the where clause (a string must be enclosed in quotes)

Examples

select *
from table(OMNATA_MICROSOFT_SQL_SERVER_PLUGIN.UDFS.SQL_QUERY(
    'my-sql-server-connection',
    'AdventureWorks2022',
    'select * from [Sales].[Customer]',
    'CustomerID',
    '=',
    '1'
))

PreviousMicrosoft Excel NextRelease History

Last updated 10 days ago