Need to update changes from Microsoft SQL server to Kafka

1,990 views
Skip to first unread message

Jeetendra Gangele

unread,
Feb 28, 2017, 9:25:40 AM2/28/17
to Confluent Platform
Hi All I have transactional data stored in Microsoft SQL server. Any changes made to this database I need to push the changes to Kafka Topic.
debezium is one option but they did not support SQL server due to issue mentioned here https://issues.jboss.org/browse/DBZ-40?_sscc=t.

is there any alternative solution?

Gwen Shapira

unread,
Feb 28, 2017, 10:10:16 PM2/28/17
to confluent...@googlegroups.com
The issue you point at offers a solution:

---
See the SQL Server documentation
(https://msdn.microsoft.com/en-us/library/cc645937.aspx) about how to
capture changes from SQL Server. Also compare to change tracking
(https://msdn.microsoft.com/en-us/library/bb933994.aspx), which is
more limited and likely not as useful for Debezium.

It looks like SQL Server's change data capture simply exposes the
change log via a table, which is easier than requiring a separate
connection and protocol like PostgreSQL and MySQL.
---

So it sounds like enabling SQL Server's change capture and using JDBC
connector to get it should work. Did you try that?

Gwen
> --
> You received this message because you are subscribed to the Google Groups
> "Confluent Platform" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to confluent-platf...@googlegroups.com.
> To post to this group, send email to confluent...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/confluent-platform/545005f3-f62a-4086-b960-36976fa6222d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

Jeetendra Gangele

unread,
Mar 2, 2017, 2:35:50 AM3/2/17
to Confluent Platform
no I did not tried it, do you know any solution which does this?

Gwen Shapira

unread,
Mar 3, 2017, 9:45:05 PM3/3/17
to confluent...@googlegroups.com
You have to enable CDC on MSSQL. The first link has good background on the process (and it also links, on the left, to more details on how to enable and disable). I also found this cute tutorial: http://www.databasejournal.com/features/mssql/getting-starting-with-change-data-capture-cdc-in-sql-server-part-2.html

Once CDC in MSSQL works, you can use JDBC source connector to get updates from the CDC table. 

I actually don't know MSSQL and I haven't tried it, so let us know how it goes :)


> To post to this group, send email to confluent...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/confluent-platform/545005f3-f62a-4086-b960-36976fa6222d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent-platform@googlegroups.com.
 To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/d8f05f08-6d02-41df-ad87-63ca6b1a1868%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

dcma...@gmail.com

unread,
Jul 11, 2017, 3:26:41 PM7/11/17
to Confluent Platform
I'm also interested in reading the SQL Server changelog transactions and forwarding to kafka. I understand you must enable CDC and then read the data from the transaction table. How have you implemented this within the kafka producer? Are you executing "select *" queries using a JDBC connection? Does this impact performance on your SQL Server?

An approach I was thinking about was using SQL Server replication to replicate all databases to a second SQL Server that would be a "kafka integration hub"... This would ensure any processing being done on SQL Server to make the data streams available in kafka would not impact any transactional processes on the original SQL Server.

Thoughts on this approach? Has anyone implemented something similar?
> To post to this group, send email to confluent...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/confluent-platform/545005f3-f62a-4086-b960-36976fa6222d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.

Robin Moffatt

unread,
Jul 12, 2017, 6:08:06 PM7/12/17
to confluent...@googlegroups.com
Have you looked at https://github.com/jcustenborder/kafka-connect-cdc/tree/initial/kafka-connect-cdc-mssql ?
This provides CDC against MS SQL into Kafka, using Kafka Connect (i.e. no coding needed!)




> To post to this group, send email to confluent...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/confluent-platform/545005f3-f62a-4086-b960-36976fa6222d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsubscribe@googlegroups.com.

To post to this group, send email to confluent...@googlegroups.com.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.

dcma...@gmail.com

unread,
Jul 26, 2017, 12:25:14 PM7/26/17
to Confluent Platform
Looks like it moved repos, and the link is wrong on the initial page. Here's the direct link: https://github.com/jcustenborder/kafka-connect-cdc-mssql

Is anyone successfully using this? Any issues?
> To post to this group, send email to confluent...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/confluent-platform/545005f3-f62a-4086-b960-36976fa6222d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.



--
Gwen Shapira
Product Manager | Confluent
650.450.2760 | @gwenshap
Follow us: Twitter | blog

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages