Ms SQL server to gcp bigquery using Datastream

[Vigneshanandara]

I have a table in Ms SQL server. I am replicating the table to gcp bigquery using datastream with write mode as merge.  Back file is successful. When a row is  inserted to the source table it gets replicated with out any issues. When I delete a row in source table I get a new record in bigquery, instead of deleting. When I update a record in source, I get two record inserted with the data stream to bigquery metadata as isdelete is true and the new one as false. The old record is not updated or removed it also has the metadata of the stream is deleted as false.

What is the issue here?

[mcbsalceda]

Hi @Vigneshanandara, 

Welcome to Google Cloud Community! 

Datastream doesn’t officially support MS SQL Server as a direct source. It works with MySQL, Oracle, PostgreSQL, and specific SQL Server setups like self-managed SQL Server (on-prem or cloud-hosted), Amazon RDS for SQL Server, Cloud SQL for SQL Server, and Azure SQL Database (only with the change tables CDC method for SQL Server versions S3 and later). Since MS SQL Server isn’t natively supported, you’re probably running into issues with how deletes and updates are handled—like deletes being treated as new records and updates causing both old and new rows to be inserted. It might be a good idea to contact Google Cloud support for advice on the best way to replicate MS SQL Server data to BigQuery.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.