How to Query Multiple SQL Server with a Single Query? – Interview Question of the Week #106

Question: How to Query Multiple SQL Server with a Single Query?

Answer: One of the toughest jobs in the world is being a DBA. Trust me, I have high regards to this job because it might look easy, but it has the most stressful times when things go as per plan. I personally see people underestimating what a typical DBA does. It is not about writing some automation, doing backups, upgrading the server, installing databases, etc. Their function goes beyond the unsaid.

I saw one of the use case that a senior DBA was using in his environment. It intrigued me on how effectively he was using the same. Thought of sharing the same with you. The senior DBA had given the task of installing SQL Server 2016 SP1 on their servers (Dev, test, QA and other environments) to a junior who had joined a few months back. He wanted an easy way to find if the servers were installed with the same. He didn’t want to go ahead and query each of the servers using the @@VERSION command.

The way he achieved the same was using the Registered Server pane. He had close to 5-6 servers registered based on the environment. From here, you can right click “Server Group” and do a “New Query”.

Now when you are in this window, any command that is fired will be executed against the different servers. In the above example, I have 2 servers registered and the query has been executed against both the server. You will see one integrated output and an additional column “Server name” is added to the result set.

This is a nifty trick and I am sure most of you are already aware. Thought of sharing the same for your reference. Do let me know if you have something like share with others via comments. Would be more than happy to blog about it.

Reference: Pinal Dave (https://blog.sqlauthority.com)