THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Using SQL Client Configuration Alias to Create Linked Server

If you have a named instance of SQL Server and you need to create a Linked Server to it, using the SQL Native Client will result in a Linked Server like SQLDEMO\SQLEXPRESS.  This isn't ideal for coding against.  One thing that you can do is create a Client Configuration Alias to provide a common name for the instance that you want to connect to, for example EXPRESS.  To do this, first logon to the SQL Server through remote desktop and open the Client Configuration Tool by clicking Start and then Run:

image 

Then click the Alias tab:

image

Then Click Add, and then click the TCP/IP button.  Type in the Alias Name, and then type in the Instance path:

image

Click OK twice, and then open SSMS and connect to the SQL Server instance that will have the linked server created.  Create the new linked server, and Specify SQL Server and use the Alias name instead of the actual instance name:

image

If all went well, then the Linked server will connect and be available for querying:

image

Other uses for this could be to create a loopback linked server in SQL Server 2005.  You can use TCP/IP and specify to use the loopback IP 127.0.0.1 for the ServerName.  This will allow you to create a linked server to itself.

What other scenarios can you think of that Client Configuration Aliases will make life easier?

Published Tuesday, October 14, 2008 8:07 PM by Jonathan Kehayias

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement