Blog Post by Ryan Goad, Controls Engineer
If you’re working on a system that accesses data on multiple servers, did you know that you can have one access point that allows you to see data hosted on completely separate servers by creating links between the individual servers? Implementing this practice will help your company reduce processing time and connection issues that can be associated with accessing multiple unlinked servers.
You are interacting with one server (server 1), but also need to get data from another server (server 2) that is on the same network. Although this can be done by having a separate connection to both servers, you do not want to go through the headache of connecting your system to the second server when you are already set up to connect to one. Doing this would require writing more new code than is necessary for the application to have both connections.
You can link the two servers together so that you can query server 1 and get to the data in server 2 as well, while your specific application is only connected to server 1.
We have implemented this approach at Patti Engineering, and here is how we did it:
This technique is being used so our existing local server can talk to a new SQL Server that we have set up and also vice versa, as the new server is updating data that is stored on the local server.
The two servers were linked so that data in the master server can be accessed from the local server, and thus allow the system to receive the correct information needed. This link is only a one-way street, though. So if you want access in both directions, you will have to link 1 to 2 as well as link 2 to 1 on the respective servers. Linking the two servers together only requires knowing the computer name and the server instance name and placing these names within the linked server wizard.
Contrast this with the process of opening a connection to both of the servers individually, which may require an update to whatever application needs the access. Also, this link is done on the server itself and does not affect any apps that are hitting it.
Linking the two servers is a lot simpler than having your system query the databases within these servers separately, whether you are talking about 2 servers or 100. You can even write stored procedures on one server that will query multiple other servers at the same time, as long as all the naming is correct. This can be useful if you have any data on one server that has accompanying data on a different server, and need to view it all at one time. If you tried to do this with separate connections to each database, then you would have to complete two separate queries to each server. It would be a pain to save the returned data and combine it so that you can view it all as one.
Linking multiple servers can save a great deal of processing time and get rid of connection issues.