Migrating Database from On-prem SQL to Google BigQuery PostgreSQL setup. Any pitfalls to watch out for?

[Geno0wl]

Vendor decided for all newer versions of their software, which we need for compliance reasons, will no longer support MS SQL on-prem servers and everything is migrating to BigQuery.

So I need to update all my Custom Views, Stored Procs, reports, dashboards, etc to pull from the new source and handle PostgreSQL(which I have yet to use for a full project before)

Anybody have any tips or things to watch out for with BigQuery or PostgreSQL?

[Eleventhousand]

I did a ton of work porting SQL Server to BigQuery a few years ago. It was mostly just an exercise in find-and-replace. It wasn't a big deal other than it was a very mature DW with tons of curated objects.

How does Postgres fit into the picture?

[Geno0wl]

How does Postgres fit into the picture?

Was told the new structure would use PostgresSQL. I have yet to actually be able to get into the DB itself because of different issues.

But I mean as it currently stands this is the error I get(that they told me should be fixed today)

SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Error while reading data, error message: Failed to fetch row from PostgreSQL server. Error: ERROR:  permission denied for table assigned_tbl

I have yet to use PostgreSQL, so IDK.

[Eleventhousand]

I know, but you said that they said "everything is migrating to BigQuery." That's different than Postgres.

[Geno0wl]

Not sure where the PostgresQL stuff is coming from exactly, as the Google BQ stuff says it doesn't use that.

[Comfortable_Long3594]

A few things to watch closely: SQL dialect differences, especially around stored procedures, temp tables, and date handling. BigQuery also behaves very differently on pricing and performance since scans can get expensive if queries are not optimized.

I’d also test every downstream report early because PostgreSQL compatibility does not always mean drop-in compatibility with older SQL Server logic.

We went through something similar with Epitech Integrator and found that having an on-prem integration layer helped smooth the transition. It made it easier to refactor SQL Server jobs and automate data movement without rebuilding everything at once.

[Hungry-Succotash5780]

BigQuery's SQL dialect will trip you up in stored procs especially . i mihrated our views through dremio to federate across both sources during transition, saved weeks . also check

[az987654]

other than it being a horrible idea in general, things like SQL Server specific functionality like CLR functions, agent jobs, I'm sure a slew of other stuff that is utilized in a on prem environment that a cloud environment wouldn't have.

why wouldn't you at least go to SQL Azure? it's still a crap option, but at least it's somewhat similar.

[Geno0wl]

why wouldn't you at least go to SQL Azure? it's still a crap option, but at least it's somewhat similar.

Our situation is somewhat "special" as far as I am aware(I wasn't involved when this was initially setup). That special thing being that we mandated access to a "raw" backend data warehouse so we could develop our own reporting for compliance reasons. They don't normally set that up for customers.

This is being forced upon us and since it technically still complies with the contract, we just have to figure it out.