Execute Stored procedure with tables in multiple servers/clusters.

Question

Hi,

I have a scenario where my source tables are from multiple servers or synapse clusters that I need to use as part of my stored procedure and call it from the ADF.

My question is:

Can this be done? If yes, how do we establish the connections for multiple servers?

Ex: let say. I have table called product in cluster A and I want to call product from cluster B.

Answer 1

Hi @yogeshReddy
Welcome to Microsoft Q&A platform and thanks for posting your question here.

In SQL Server, it is possible to query multiple servers at once in a stored procedure by using a Central Management Server. To achieve this, you can create a CMS or a local server group, and then add one or more server groups and registered servers within the groups. Once you have set up the server groups, you can query the complete group and get the results in a single or separate results pane. Furthermore, the result set can include extra columns for the server's name and the login used by the query on each server.

Reference 
https://learn.microsoft.com/en-us/sql/ssms/register-servers/execute-statements-against-multiple-servers-simultaneously?view=sql-server-ver16

I hope this information helps you. Let me know if you have any further questions or concerns.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.