SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system
SQL database is a reliable and industry standard for websites of middle-level data-based applications.
Sprinkle supports a wide range of data sources. On clicking the “+sign”, a list of data sources pops up. In this case, SQL is selected. A new SQL data source is named and created.
After naming the data source, the configure tab would require the user to fill in the credentials such as Username, Password, JDBC URL and select between connecting via SSH Host or not before testing the connection and updating.
Optimising Incremental Ingestion in SQL Server
Also users can select Yes or No to Optimize Incremental Ingestion. If optimize is Yes, all the datasets will undergo full ingestion on every Sunday or every night. If optimize is No, data will be ingesting incrementally and it never goes under complete ingestion.
In Add Tables, the user can add tables either by table or query method, in table method the user is required to apply a table name and filter clause could also be applied whenever required.
On selecting Query, the user must provide a table name and apply SQL Query before creating a table.
In the Run and Schedule tab, the concurrency (number of tables that can run in parallel, a maximum of 7) can be set preferentially before running the job. The status of the job will be updated in the tab below once it’s complete. The jobs can also be set to run automatically by enabling autorun. By default, the frequency is set to every night. Frequency can be changed by clicking on More --> Autorun-->Change Frequency.
Setup for Change Data Capture
CREATE LOGIN sprinkle WITH PASSWORD = 'password';
CREATE USER sprinkle FOR LOGIN sprinkle;
Give select permission on database
GRANT SELECT on DATABASE::<database> to sprinkle;
Enable Change Data Capture
To turn on Change Data Capture, first you need to enable it at the database level:
Next, you need to enable it for each table you would like to integrate:
@source_schema = '<schema>',
@source_name = <table>,
@role_name = 'MyRole',
@supports_net_changes = 0
Test by listing tables enabled with CDC (the below command should be run with the same user as using to connect):
Start Sql server agent if not already running