If you’ve faced a situation like where you have data in different databases located in different servers then in case of SQL server there’s a solution to that problem- linked server. Linked server can be set up through SQL server to SQL server or other servers as well. Here I will show how I did connection among SQL servers to query in all server databases as per my reports’ requisition. Here I go.
Connect to your SQL server and under the object explorer tab there’s an option called ‘Server Objects’.
Expand it and under it ‘Linked Servers’ will be found. right click on it and choose ‘New Linked server’ and the pop-up window will look like:
Choose SQL server as Server type under the General page. In the linked server text box write server name that is to connect. Next step for SQL authentication select ‘Security’ from ‘Select a page’ tab. Choose ‘Be made using this security context’ and give login credentials here. User name is ‘Remote login’ box and password in ‘With password’ box. Click Ok and the linked server will be made. It can be found under the Linked servers.
Now to query in the linked server this is to follow:
select * from [server name].[database name].[schema].[table name]
Hope it helps 🙂