Connection to On Premise SQL Server

[Davi Alefe]

 We have a SQL Server 2014 Edition on premise database for an application internal to the company  and would like to access its data from Bigquery, so we can run BI tasks using it.

 I've been struggling with this problem for some time and would like some input from other people - I come from an Analytics background and never had to deal with this sort of thing prior to leading this project. Also, I'd like to point out that we would prefer not to use any pre-built data connectors third-party services, if possible.

 Given the context, I basically have two questions:

•  I know that Bigquery can access Cloud SQL data using federated queries, so creating a SQL Server instance in Cloud SQL and somehow connecting it to our on premise db is an option - although I might be completely out of range here, since I'm not sure if that's the best option. If this is a good option, how exactly should I build this connection (which kind of services should I use and documentation should I read)? Any network configurations necessary  in the on premise db server  can be carried out by our internal specialists.
• If the previous item seems absurd to you or if you simply see an easier/cheaper option, how would that go?

Thanks in advance

[cristianrm]

This documentation from Google Cloud could help you to achieve what you want to do:

"This tutorial shows you how to create and deploy a pipeline that continuously replicates changed data from a Microsoft SQL Server database to a BigQuery table."

This other page shows another alternative to connect SQL Server with BigQuery.

But consider that the tutorial from Google Cloud relies on the CDC (Change Data Capture) feature, which

"is supported in the Developer and Enterprise Editions of SQL Server up to and including SQL Server 2016 RTM."

As stated on Microsoft’s site; MS SQL Server 2014, which is the one that you’re using, has reached its End of Life, and is beyond support.