There are a lot of moving parts to any application system. One such moving part is the creation and dependence upon the use of linked servers inside of SQL Server. These linked servers give users the ability to write queries as if the data was local by referencing a four-part name. I’ve written before about the use of linked servers and the performance issues that may arise. Today I want to talk about something more fundamental about linked servers: connectivity.
Creating a linked server is fairly straightforward, you can read the reference here. You have a handful of ways to handle authentication between the instances. These methods include using the security context for the current login, for the current user, or passing along remote credentials. The one you choose will depend on your needs and requirements. The specific method chosen isn’t important for today’s post. Today is more about the failure to communicate between servers.
Connections between servers can fail for a variety of reasons. Permissions get changed, AD accounts get modified (or removed), passwords get reset. And sometimes the use of a linked server gets lost over time. It was not uncommon for me to migrate databases to a new server and find out weeks later that a linked server was needed. At some point in my career, I had been bitten enough times by linked servers failing to connect that I built a way to automate the checking of the linked server connections. I wrote about it here, and I even updated the script recently. And I would have put that script into GitHub by now if not for last February, while at SQL Konferenz in Darmstadt, Germany, I was struck with an idea.
While having some post-event German beverages I was talking with William Durkin (blog | @sql_williamd) regarding the DBAtools.io project. This project is wonderful for migrating data between servers, or even an entire instance. I noticed that there was no cmdlet for testing a linked server connection. I asked “hey, do you think that might be something useful?” William said yes, and off I went to email Chrissy LeMaire (blog | @cl).
A few emails later I found myself connecting to the dbatools.io GitHub repo and merging my cmdlet into the project. So that’s where my code now sits, for everyone to use.
You could download my specific cmdlet easily, but what you should do is download all the DBAtools.io goodness. DBAtools.io is in the Microsoft Powershell gallery, so installing DBAtools as easy as running this command:
And then you can run any of the commands easily. Ever want to safely remove a database? There’s a cmdlet for that: Remove-SqlDatabaseSafely. You can find a cmdlet for just about everything. And, if you don’t see one, you can contribute to the project and add the missing cmdlet to the project.
For a while now I have been meaning to take all the scripts I’ve used over the years and get them loaded to my GitHub repo for everyone to use and modify as they see fit. I like the idea of contributing to this project instead. I’m not going to spend time trying to market and pimp my scripts at my own repo, it’s easier for me to share what I can over at dbatools.io. I’d rather contribute to the larger project there than have a bunch of scripts here.
The dbatools.io project is awesome. I like it and I think you should, too. I’ve contributed and I think you should, too. Being a part of the dbatools.io team reminds me of what it was like when I was first starting out as a DBA and I exchanged ideas with a handful of folks I would meet at conferences. If you are just getting started in SQL Server administration, are looking for some tools, and want an easy way to learn some PowerShell, then dbatools.io is the place for you.