We are working on a system with several applications and we have several link servers. Every application has its DB on a server. Sometimes it's necessary for applications to use table of other DBs. These applications are written by ASP.net MVC and Entity Framework.
We have 3 situations:
1-We have some base tables that aren’t heavy and all of applications need to use them. In other words they are common tables. Now we put these tables as a view in every application's DB.
2-Sometimes apps need to communication with each other's via using each other's table.
3-We have some big tables that some applications need to use. They have 100 millions records.
If two DBs of two applications that want to use from other one table be in one server we create another context unless main context. But if two DBs of two applications that want to use from other one table be in one server we use from view.
We want to rewrite this system with MVC and Entity Framework code first. We don’t want to use view in that situation, I mentioned above. One way we suggested is to use from service in first Case above. In fact we want to Centralized all base tables in one DB. And if an app wants to use from this DB's table send necessary parameter to service and get the records of that table. For second Case above we want to do this too. But I don’t know if it is the correct way? And we don’t have a solution for third above situation.