Linked server vs integration

We have an application which needs to interact with 3 different databases (SQL Server) to fetch the user details and display them on a web page. Is it a good option to use a lin开发者_JS百科ked server or should we copy the user details (via some daily job) to the application database?

Using a linked server will give you a round trip delay every time you query the data. If you only query the data once per day or per session this might be acceptable. If however you are issuing many queries to these servers you may find that the performance is so poor that your application is unusable.

You could use SQL replication to push (or pull) the data from each of the servers into a local copy on the application server. This will provide you with much better query performance (no round trip delay) while also ensuring that you have the latest data. There are lots of options with SQL replication you should be able to find something that suits your needs.

For more information on SQL Replication see http://technet.microsoft.com/en-us/library/ms151198.aspx

A linked server is only going to allow your databases to talk to each other. If the application is interacting with three discrete databases, then you simply need discrete connections. I would not recommend heavily using the linked servers unless you are moving a lot of data (since picking it up into the application and putting it into another database may take even longer).