We have a spider-web of SQL queries and SQL Servers joined by Linked Servers. We want to get rid of these so that we can migrate to Azure SQL.
I need suggestions/ideas/opinions on the best practice nowadays? I believe that Linked Servers are no longer a widely used practice (correct me if I'm wrong) and I wonder how others got rid of it.
What we are thinking is to create an API layer and use APIs to call multiple DBs and aggregate the data. This is a lot cleaner than calling a stored procedure that is dependent on multiple DBs via Linked Servers.
However, this approach takes a lot of effort as one would imagine (changing the architecture, coding effort for both the API and SQL stored procedures, etc.). Therefore, I would just like to validate if this is the best approach to go forward with.
Opinions and suggestions please?