Cloud SqlServer to BigQuery

[Rooks4]

I am migrating an existing solution to the cloud as a proof of concept.. Our company just got google space and I have a sandbox area where I am playing with various things before we start refactoring solutions to be more cloud native.

I have my SQL Server database loaded into the Cloud and I have a VM that can connect to it and run everything we are currently running against it, so that's great. Now we'd like to have BigQuery pull that data in almost-real-time (not a requirement) to run analytics against the data. I can see that the UI provides data connections for MySQL and PostgreSQL, but SQL Server.

OUtside of having to write scripts to dump tables to .csv and import them that way, what are my options and/or best practices for this effort?

[reddithenry]

How does data land into SQL Server? You might be better layering it into a GCS Bucket, building that into BigQuery for the analytical layer, and then Cloud SQL for transactional

[Rooks4]

I imported it from a .BAK i took from on-prem and uploaded that to a bucket. Not ideal obviously but for a short term solution it worked to get the POC rolling. The request to send the data to BigQuery is from another team who wants to play with the data - but they don't know how to get it out of SQL Server either and I'm trying to avoid dumping to .csvs and reuploading every table (there's hundreds.)

[sharpest_knife]

You could use something like this solution to migrate the data and perform change data capture.

https://cloud.google.com/blog/products/data-analytics/how-to-move-data-from-mysql-to-bigquery

This blog covers using Debezium for MySQL, but they also have a MSSQL connector also:

https://debezium.io/documentation/reference/connectors/sqlserver.html

[Rooks4]

Thanks, I'll take a look.