I had a customer recently that was asking about Linked Servers and some development advice. I was going to show them a few things and realized I hadn’t created a linked server in my demo environment, so I did it and decided to create a quick post on this.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
I have a few demo instances of SQL Server in my local environment: Aristotle and Aristotle\SQL2022. In this case I was connected to the named instance, and decided to create a connection to Aristotle. As you can see, I don’t have any linked servers in the named instance.
To create a linked server, I can use this simple code:
EXEC master.dbo.sp_addlinkedserver
@server = N'Aristotle',
@srvproduct=N'SQL Server';
GO
This creates the linked server (as you can see below), with a number of defaults. In this case, the security is made with whatever login queries the linked server.
You can see the security properties here:
This might be OK in your enviroment, or it might not be. Perhaps you need to ensure everyone querying the remote server uses the same login. In which case, the sp_addlinkedserver procedure doesn’t do this. You would need to use sp_addlinkedsrvlogin to do that. That’s for another post.
NOTE: Be sure you understand what a linked server does, how to use it, and the downsides. There are many and this can slow down your application or overload servers
I can test this connection by right clicking the Linked Server in SSMS:
This works, as expected.
I can also run a query through the linked server, using 4-part naming with the linked server, then the database, schema, and table. This also works:
That’s it to get started. I recommend you be careful when using linked servers as this creates a bit of a tight coupling and makes development harder. I might recommend you get away from querying database server to database server when possible and let an application do this work if it’s possible.
SQL New Blogger
Linked Servers aren’t that common, but they aren’t rare. This is a skill that SQL Server people should have and understand a bit about. This post is very basic, but it provides a jumping off point where I could write a number of other posts related to linked servers and perhaps guide an interviewer along a path of asking me about them. I certainly showcase some knowledge here if someone asks me if I’ve ever created one.
This post took me about 10 minutes to test and write, and you could probably do this in your environment. You don’t even need to servers, as you could create a loopback linked server.
Voice of the DBA 