MORE ABOUT ME
Welcome! I’m Thomas…
Resources

Install-Module dbatools

Install-Module dbatools

Install-Module dbatools

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:

Install-Module dbatools

Install-Module dbatools

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.

Summary

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.

  • Jim peabbles

    A word of caution, replication uses linked servers. The original powershell and version in dbatools report the linked server connectivity as failing. Failed connectivity may not mean “drop this linked server” in your environment. A linked server that is not configured for data access may still be in use. “Server ‘MYSERVER’ is not configured for DATA ACCESS”

    Great script, Thank you.

    • ThomasLaRock

      Sorry, I didn’t mean to imply that you should be dropping linked servers. Not at all. Just that you should verify why the connection has failed. Thanks for the comment, I did not think about replication in my testing. Here’s hoping someone takes the cmdlet and enhances it to consider that scenario!

      • Jim peabbles

        You didn’t imply to drop linked servers with a failed connection. I think I just had a vision of spring cleaning. 😉 As you said, verify why the connection failed. It’s a great addition to dbatools. Thanks again!

        • ThomasLaRock

          Welcome!