Linked Servers

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 1 Comment
Published on: March 1, 2012

Do you have linked servers that have been created in your environment?  Do you know what these linked servers are?

Finding Linked Servers

SQL Server has provided us means to be able to find the linked servers on each installed instance.  I will show two simple means (that don’t involve traversing the SSMS GUI) that will list the linked servers.

Both methods are very similar in nature.  I will leave it up to you to determine which you prefer to use.

 

Stored Procedure

Microsoft has shipped a stored procedure called sp_linkedservers.  Execute this stored procedure and you will get a list of linked servers and the details related to those objects.  One problem with this method is that not all the results returned by this proc truly represent linked servers.  This procedure will also return the name of the instance to which you are also connected.

Catalog Query

This is really what the stored procedure is doing.  But here is a modified query to return only those servers that are linked servers in the catalog.

The main difference being the where condition.  Querying the sys.servers catalog with a condition of is_linked = 1 will return only those servers that are linked servers.  Getting this information is a pre-requisite for an upcoming post.  I hope you find this information useful.

 

1 Comment - Leave a comment
  1. […] This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here. […]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp

  • @richardslim: Is it possible to have traditional fail-over Clustered instances with locally installed instances in an AG on the same 2 servers? #SQLHelp
  • @gm_praveenk: Hi all Can any one help out in documentations of migration of physical sql server to cloud (hp cloud). #sqlhelp Thanks
  • @SQLSoldier: @DBArgenis @SportsNut70 More specifically, look for updates to heaps. #sqlhelp
  • @DBArgenis: @SportsNut70 that is orthogonal to the storage you use. Look for changes in database schema and workload. #sqlhelp
  • @SportsNut70: We migrated to Flash storage and now see Forwarded Records/sec avg about 5000. On SAN, we had 0. Should I be concerned? Is it Flash?#sqlhelp
  • @yenho213: @DBArgenis interesting idea. I will try to use that solution. thanks. #sqlhelp
  • @DBArgenis: @yenho213 you don't _have_ to configure log shipping again upon failover - write your own scripts and voila. #sqlhelp
  • @yenho213: @DBArgenis, primary and sec node are AG, 3rd server using Log shipping due to application can't handle multisubnet=true just yet #sqlhelp
  • @DBArgenis: @yenho213 depending on what you're actually trying to achieve...#sqlhelp
  • @yenho213: @DBArgenis that means I have to configure Log shipping again every time my AG fail over between the primary and secondary correct? #sqlhelp

Welcome , today is Tuesday, August 4, 2015