Linked Server Set-Up

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:

  1. The ability to access data from outside of SQL Server
  2. The ability to issue distributed queries, updates, commands, and transactions on data sources
  3. 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):

Linked Server Database Engine

Creation of Linked Server

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s