Search code examples
sqlexceloledb

Does Migrating DB from On-Prem to Cloud Break OLEDB Connections?

Had trouble querying Google for answers to this question.

My IT team informed me tonight that they're moving forward with a migration to the cloud; they temporarily turned off the on-prem db during this process which broke some reporting and caused instant panic (people working off hours).

After the migration though, the engineer informed IT that they would not have to reenable the on-prem database as everything should smoothly work as is. We're skeptical.

Already, I've tried connecting to the DB from various applications targeting the DB (not sure how these connection strings are established, most are C# apps). These connections failed. This also leads me to believe that OLEDB connections, of which various excel reports depend, will break.

During this migration, will we need to hunt down each of these reports and reconfigure all of the OLEDB connections?

Forgive me as this is likely a stupid question. I just keep getting hums and hahs that if the DNS work, other connection strings won't be broken, but I'm skeptical as I doubt many if any apps/reports connect over DNS.

EDIT: Also tested an Excel file that grabs data; no dice on that. So guessing that's my answer.

And there we have it: Checked the connection string on one such report: enter image description here

Solution

  • I'm going to assume this is IaaS (they just moved the DB to a VM in the cloud), not PaaS (actually using Azure SQL, the cloud native SQL Server). You don't supply enough info to be sure.

    You need two things to connect

    A. Network connectivity

    If the team has done this right, they have opened required ports, esnured there is network connectivity, and used DNS to ensure that whatever SQL Server name you use now points at the new SQL Server

    B. Authentication

    Again, then as long as they migrated all SQL and windows logins, and ensured the VM was connected to the domain, everything should work

    In summary

    It's possible you don't need to change anything in your connect string, since the same server name works, and the same login works. You need to test everything as soon as you can since there are a lot of things that can go wrong

    It's certainly strange that they would make this huge change and give you no notice