At times I take for granted some of the features that SQL has to offer. One of the features that I was asked about from some fellow colleagues is setting up a Linked Server. First, what is a Linked Server? A linked server basically allows a SQL Server Database engine to execute commands against OLE DB data sources outside the instance of SQL Server. There are some advantages in having a
Linked Server a few just to mention are:
- The ability to access data from outside of SQL Server
- The ability to issue distributed queries, updates, commands, and transactions on data sources
- The ability to address diverse data sources
CONFIGURING A LINKED SERVER
One of the ways that you can set up a linked server is through SSMS; you can perform the following tasks:
- Connect to a server in SSMS
- Expand the Server Objects
- Right click Linked Servers
- Select a new linked server
The first box allows you to select a specific server type.
The security section will allow you to define the logins.
Under server options ensure the data access value is set to true
From there you can test the connection and ensure the validity of the setup has been completed correctly.
For further research you can review some of the documentation in the below links from Microsoft (some are from prior versions of SQL but the gist is still the same):