Sprinkle Docs
  • What is Sprinkle?
  • Quick Start
  • Analysing your data
    • 🔭Analytics Overview
    • 💠Data Models
      • *️Variables
      • 🌲Hierarchies
      • 🤿Column Mask
    • 🎉Switch to New Reports & Dashboards
    • 🆕Reports
      • Overview
      • Build Using Tables
        • Create a new Report
        • Layout and options
        • Build and Format - Overview
        • Apply Row Limits
        • Identify Date Columns
        • Filter your data
        • Visualizations
          • Table
          • Pivot
          • Line Chart
          • Bar Chart
          • Column Chart
          • Area Chart
          • Combo Chart
          • Scatter & Bubble Plot
          • Pie Chart
          • Funnel Chart
          • Stat Card
          • Point Map
          • Heat Map
          • Radial gauge chart
        • Advanced Features
          • Custom Analysis
          • Variables
          • Table & Quick Calculations
          • Drill - Hierarchical & Date
          • Break Out
          • RLS in Table reports
          • Scheduled Exports
          • Embedding Table Reports
      • Build Using Models
        • Create a new report
        • Layout and options
        • Visualizations
        • Advanced Features
      • Build SQL Reports
        • Create a new Report
        • Layout and options
        • Writing a SQL Code on Editor
        • Visualizations
        • Variables in SQL Reports
    • 🆕Dashboards
      • 🌀Filters
      • 👆Click Behaviour
      • ⏰Data Alerts
      • 🗓️Date Drill
      • 📤Scheduled Exports
      • 🔗Embed link
      • 🖥️Dashboard layout
      • 📱Mobile Dashboards
  • Transforming your data
    • 🔰SQL Transform
    • 📓Python Notebooks
  • Integrating your data
    • ☁️Destination Warehouses
      • AWS Athena
        • Manage storage of Flow tables
      • AWS Redshift
      • Azure Synapse
      • Databricks
      • Google BigQuery
      • MySQL
      • Postgres
      • Snowflake
      • SQL Server
      • K8 Setup
        • AWS EKS
        • Google GKE
        • Azure AKS
    • ⚙️Warehouse & Storage Setup
  • Ingesting your data
    • ☄️Data Imports
      • Databases
        • Azure Cosmos DB
        • Azure Table Storage
        • Google BigQuery
        • Mongo DB
        • MySQL DB
        • Oracle DB
        • Postgres DB
        • SQL Server DB
        • Features
          • Ingestion Modes
          • Add Multiple Datasets
          • CDC Setup
            • CDC setup in Mysql
            • CDC setup in Postgres
            • CDC setup in Mongo
            • CDC setup in SQL Server
          • Destination Create Table Clause
          • SSH Tunnel Setup
      • Files
        • AWS S3
        • AWS S3 External
        • Azure Blob
        • FTP
        • Google Cloud Storage
        • Google Sheet
        • SFTP
      • Applications
        • Apple Search Ads
        • Appsflyer
        • Branch
        • Clevertap
        • Facebook Ads
        • Freshdesk
        • Freshsales
        • Google Ads
        • Google Ads V2
        • Google Analytics
        • Google Analytics 4
        • Google Analytics MCF
        • Google Search Console
        • Hubspot
        • Impact Ads
        • Intercom
        • Klaviyo
        • Leadsquared
        • LinkedIn Ads
        • Magento
        • Mailchimp
        • Marketo
        • Mixpanel
        • MoEngage
        • Rocketlane
        • Salesforce
        • SAP S4
        • Shopify
        • Snapchat Marketing
        • TikTok Ads
        • WooCommerce
        • Zendesk Chat
        • Zendesk Support
        • Zoho Analytics
        • Zoho Books
        • Zoho CRM
        • Zoho Desk
        • Zoho Invoice
        • Zoho Subscription
      • Events
        • Apache Kafka
        • AWS Kinesis
        • Azure EventHub
    • 📤File Uploads
    • 🤖API Pulls
    • 🕸️Webhooks
  • Collaborating on data
    • 📤Sharing
    • 💬Comments
    • ⚡Activity
    • 🏷️Labels
  • Managing Schedules and Data Refreshes
    • ⏱️Schedules
    • 🔔Notifications
  • User Management
    • 🔑Access Management
    • 🧑‍🤝‍🧑Groups
    • 📂Folders
    • 🔄Syncing users, groups and RLS
    • 📧Azure AD Integration
  • Data Security & Privacy
    • 🔐Security at Sprinkle
    • 📄GDPR
    • 📄Privacy Policy
  • Release Notes
    • 📢Release Notes
      • 🗒️Release Notes - v12.1 (New)
      • 🗒️Release Notes - v12.0
      • 🗒️Release Notes - v11.0
      • 🗒️Release Notes - v10.8
      • 🗒️Release Notes - v10.7
      • 🗒️Release Notes - v10.6
      • 🗒️Release Notes - v10.5
      • 🗒️Release Notes - v10.4
      • 🗒️Release Notes - v10.3
      • 🗒️Release Notes - v10.2
      • 🗒️Release Notes - v10.1
      • 🗒️Release Notes - v10.0
      • 🗒️Release Notes - v9.31
      • 🗒️Release Notes - v9.30
      • 🗒️Release Notes - v9.29
      • 🗒️Release Notes - v9.28
      • 🗒️Release Notes - v9.27
      • 🗒️Release Notes - v9.25
      • 🗒️Release Notes - v9.24
      • 🗒️Release Notes - v9.23
      • 🗒️Release Notes - v9.22
      • 🗒️Release Notes - v9.21
      • 🗒️Release Notes - v9.20
      • 🗒️Release Notes - v9.19
      • 🗒️Release Notes - v9.18
      • 🗒️Release Notes - v9.17
      • 🗒️Release Notes - v9.16
      • 🗒️Release Notes - v9.14
      • 🗒️Release Notes - v9.13
      • 🗒️Release Notes - v9.12
      • 🗒️Release Notes -v9.8
      • 🗒️Release Notes - v9.7
      • 🗒️Release Notes - v9.6
      • 🗒️Release Notes - v9.5
      • 🗒️Release Notes - v9.4
      • 🗒️Release Notes - v9.3
      • 🗒️Release Notes - v9.2
      • 🗒️Release Notes - v9.1
      • 🗒️Release Notes - v9.0 (Major)
      • 🗒️Release Notes - v7.23
      • 🗒️Release Notes - v7.21
      • 🗒️Release Notes - v7.20
      • 🗒️Release Notes - v7.15
      • 🗒️Release Notes - v7.14
      • 🗒️Release Notes - v7.13
Powered by GitBook
On this page
  • SQL Server CDC setup on Linux
  • AZURE SQL Server CDC Setup
  • Column updates with CDC enabled
  • Adding new column
  • Dropping a column
  • Updating column type
  1. Ingesting your data
  2. Data Imports
  3. Databases
  4. Features
  5. CDC Setup

CDC setup in SQL Server

SQL Server CDC setup on Linux

  • Create a User for Sprinkle using your own password

Use <databasename>
CREATE LOGIN sprinkle WITH PASSWORD = 'password';
CREATE USER sprinkle FOR LOGIN sprinkle; 
  • Give select permission on the 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.

USE [<database>];
EXEC sys.sp_cdc_enable_db
  • Next, you need to enable it for each table you would like to integrate

EXEC sys.sp_cdc_enable_table
@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 used to connect)

EXEC sys.sp_cdc_help_change_data_capture;
  • Start Sql server agent if not already running

Command definitions used above:

  • USE- Changes the database context to the specified database or database snapshot in SQL Server.

  • CREATE LOGIN - Creates a login for SQL Server, SQL Database, Azure Synapse Analytics, or Analytics Platform System databases.

  • CREATE USER - Adds a user to the current database.

  • EXEC - Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure.

  • GRANT - Grants permissions on a database in SQL Server.

AZURE SQL Server CDC Setup

Run the following commands to enable CDC in Azure:

  • Enable Change Data CaptureTo turn on Change Data Capture first, you need to enable it at the database level.

USE [<database>];
EXEC sys.sp_cdc_enable_db
  • Next, you need to enable it for each table you would like to integrate.

EXEC sys.sp_cdc_enable_table
@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 used to connect):

EXEC sys.sp_cdc_help_change_data_capture;

Column updates with CDC enabled

Adding new column

Adding a new column to table requires recreating cdc capture instance. Explaining the steps required with below example

We have a table named sample contains id and name as columns,

This block of code executes only when CDC is not enabled, if enabled then we don’t have to write this block(make sure capture_instance is there if it’s already enabled.)

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
 @source_name = N'sample',
  @role_name = NULL, @capture_instance = 'dbo_sample'

Query to add column

ALTER TABLE dbo.sample ADD TestColumn int

Code Block to create new capture instance for same table

EXEC sys.sp_cdc_enable_table 
     @source_schema = N'dbo',
     @source_name = N'sample', 
     @role_name = NULL,
     @capture_instance = 'dbo_sample2'

Shifting all data from older instance to newer instance

INSERT INTO cdc.dbo_sample2_CT 
($start_lsn, $end_lsn,$seqval,$operation,__$update_mask,id,name) 
   SELECT __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, id, name 
   FROM cdc.dbo_sample_CT

Disabling the older instance, as 1 table can have only 2 capture instance

EXEC sys.sp_cdc_disable_table 
     @source_schema = N'dbo', 
     @source_name = N'sample', 
     @capture_instance = 'dbo_sample'

Dropping a column

Follow the above steps and reset the ingested table and run ingestion again, it will drop the column.(No boundation to follow above steps).

OR

Users can simply drop the column, using simple sql query and user can reset the ingested table and run ingestion again, it will drop the column. If not reset, then the values for column will come as null.

Updating column type

Users can simply update the data-type and reset the table, on running ingestion again,it will update the data type.

PreviousCDC setup in MongoNextDestination Create Table Clause

Last updated 1 year ago

☄️