Linked Servers and Entity Framework. Any alternative?
I need to rewrite my ASP.NET Web Forms viewer application. The new version is based on ASP.NET MVC.
Architecture:
There is one web interface, but there are many databases spread all over the world (for each jurisdiction). The current solution has to do with servers from the central DB to all other instances. Several jurisdictions are protected by firewalls, but there is an exclusion rule on the firewall that allows connections from the central DB server to the jurisdictional SQL Server. No other connections allowed.
What they have done in the current solution is that they have created a separate database on a central server containing views that access remote database tables through linked servers. As mentioned, there is one instance of the user interface. The user can select a database instance to check the logs.
I want to port this structure to an entity and it will work if I use the same architecture.
Now the question is, is there any other way to achieve this without creating / maintaining these link databases in order to achieve the same result?
source to share
No, nothing is easier than with EF 6.1.
If synonyms were supported in EF, then you could create in your base DB syntaxes all tables in linked databases (in different schemas, of course). This will involve some overhead because synonyms are easier to maintain than views, and you can easily do this with the DataTools project for the central database.
Synonym support is currently a proposal for the next version of EF .
Like now why don't you create and maintain views in the central database? One schema for each linked server / database:
[jurisdiction1].[vTableA]
[jurisdiction1].[vTableB]
...
[jurisdictionN].[vTableA]
Similar to synonyms, with the downside that you need to write more code to view. You can still use DataTools to version control, track changes, and provide incremental upgrade scripts.
If you find it difficult to manage your databases right now, I recommend looking into DataTools . They simplify schema management.
source to share