2

I have multiple databases on different servers and one of the servers is across a WAN. The app needs to access data on all the servers and potentially join one tableA on ServerA (local) and TableB on ServerB (across WAN). What is the best design pattern for this? I was thinking that instead of using SQL Server Linked Servers I would have a service layer on the app server that would aggregate the data (make multiple calls, one to each DB).

Not that is should matter because we are talking about design patterns and best practices, but the app is a .NET app and the service would probably be a WCF service using TCP binding.

My DBA suggested we would write a sproc and used linked servers to join to the other table. My concern is that this solution isn't scalable.

Any advice?

uomolupo
  • 77
  • 4

1 Answers1

0

It really depends. If you have a pattern of your read requests you might be able to create a duplicate of remote data in server A (as a view) using some sort of data pipeline. That is subject to rules if remote data can be kept elsewhere. Your suggested solution works too but will not be as performant as it is expected for read requests.

sasanplus
  • 11
  • 2