Microsoft SQL Server is Microsoft's offering of a relational database management system which is used by 98 of 100 Fortune 100 companies. Hevo supports replicating data from SQL Servers either hosted on-premises or hosted in the Azure cloud. The supported versions include SQL Server 2008 and later.
The replication can happen at any frequency that you would like. Following replication modes are supported:
- Full dump and load - Full tables are replicated at a set frequency.
- Incremental load for append-only data - Only new rows are replicated from tables. Suitable for append-only tables
- Incremental load for mutable data - Updated or new rows are replicates from tables. Suitable for mutable data.
1. Create a new Pipeline
Click on PIPELINES option in the left navigation bar and click on Create New Pipeline.
2. Select Source Type
Select SQL Server from the list on Select Source Type Screen.
3. Provide Connection Settings
Provide your SQL Server database connection details on SQL Server Connection Settings page. You will have following options in connection details block:
- Source Name - A unique name for this source
- SQL Server Host - SQL Server host's IP address or DNS
- SQL Server Port - The port on which your SQL Server is listening for connections (default is 1433)
- SQL Server User - The read-only user that can read the tables in your database.
- SQL Server Password - Password for the read-only user
- Database Name - The database that you wish to replicate
- Schema Name - The schema that holds the tables to be replicated (default is dbo)
- If you want to connect to Hevo using an SSH tunnel, check How to Connect through SSH. Else, you will have to whitelist Hevo's IP addresses as listed here.
Alternatively, you copy details from an existing source of SQL Server type. Please note that it will create an independent copy of the selected source.
Click TEST CONNECTION to test the credentials and click CONTINUE once test succeeds.
4. Select Ingestion Mode
On this page, you will have 2 options for Ingestion mode. This will define how you want Hevo to read your data from SQL Server source.
- Table: In this mode, your tables will be polled individually at a fixed frequency. You can use this mode when you are looking to fetch data from multiple tables in your database and you would like to have control over ingestion for every table individually.
- Custom SQL: If you are looking to fetch data in a different structure than how it is stored in your source tables, you can use this mode. It will allow you to write a custom query and data will be polled using that query at a fixed frequency.
Select the Ingestion Mode and hit CONTINUE.
5. Provide Job Settings
In the case of Table mode, you will be presented with the list of tables in your SQL Server database. You can deselect the tables that you don't want to replicate. For every table, you will have an option to provide replication mode.
In case of Query mode, you will be presented with the Query editor, where you can write the custom SQL query using which you want to load the data. Select the Replication mode after writing the query.
Replication mode defines how Hevo will read data from your tables or query in every run. You have the following options for replication:
- Full Load: Complete result set from the Query or Table will be replicated at a set frequency.
- Delta - Append-only: Specify an auto-increment column (usually the Primary key). Only new results from the Query result set or Table will be replicated on the basis of specified auto-increment id.
- Delta - Timestamp: Specify update timestamp column name in 'Timestamp column' field. New or updated data from Query or Table will be replicated.
- Change Data Capture: This mode will take care of both incrementing id and update timestamp for detecting delta change in data while replicating Query or Table.
- Change Tracking: This mode makes use of the Change Tracking feature provided by the Sql Server. You may follow the instructions in this guide to enable change tracking for the tables. The change retention period should be more than the replication frequency of your Hevo data pipeline.
After entering the details, click CONTINUE.
6. Select the Destination
Select the Destination where you want to replicate SQL Server Data or Click on NEW DESTINATION to create a new Destination. Check out How to add Destination tutorial for the detailed walkthrough on steps needed for adding new Destination.
7. Pipeline Created
Your Pipeline will be created when you enter this page and you will have an option to see Sample Data and Map Schema.
While Hevo tries to load your schemas, you can select CONTINUE IN BACKGROUND if it is taking too much time. Click on CREATE SCHEMA MAPPING to map Source and Destination Schemas, check out Introduction to Schema Mapper to learn about Schema Mapper or you can select DO IT LATER to directly head to Pipeline page. You can map schemas later on Schema Mapper page in your pipeline.
Please note that your data will not start replicating in Destination tables until you map source and Destination schemas.