May 4, 2012 2 Comments
As a developer it’s common practice that you work on a server farm with 2 or more servers, and a clustered or redundant production environment. Sometimes it’s necessary to run certain queries on all the machines you’re developing on or that you’re maintaining. For example if you want to know if all your instances run the same version (and/or Service Pack) of SQL Server. One of the options you’ve got is to run the query in multiple tabs or windows; one for each server or instance. This seems okay for 2 or 3 instances, but not for an entire OLAP environment.
Luckily the SQL Server team has build in the opportunity to run a query against multiple instances simultaneously. This can be accomplished by running a Multiserver query. It will run your query on a complete group of SQL Server instances. In the example below, I retrieve the version of each instance I connect to. I’ve done this by using the @@VERSION function that is shipped with SQL Server.
First, I’ve created a group of servers in the Registered Servers window in SSMS:
This group contains our development instances. These instances run on 1 hardware-platform, which contains different virtual machines. Each development team has it’s own machine with dedicated SQL Server instance.
If you right-click on the server group, you choose “New Query”:
If the new query window opens, you will see a different status bar. With the default settings of SSMS set, the bar will change to a pink color:
Also in the left corner of the status bar, you can see how many instances are in the group, and how much of these instances are (still) running. In my case, all six instances in the group are running, and will return the result.
For this example I used the query:
SELECT @@VERSION AS VersionInfo
The @@VERSION function returns the version, processor architecture, build date and operating system for the current installation of SQL Server. For more info, see the MSDN article. Also, by default the servername you used to register the server will be shown:
As you see, all of our development machines run on the same version. That’s because we copied the instances from 1 base-image. As an example, I ran the same query on my local server group:
These are obviously different versions: I have a SQL Server 2008 R2 and 2012 Developer instance running on my laptop.
If you want to, you can change the options for Multiserver queries. For example, you can choose NOT to merge all results into 1 result set. You can do this via Tools -> Options -> Query Results -> SQL Server -> Multiserver Results. There you can change these options:
Only one remarkt is left. Writing this post I tried several things, but one thing I still don’t understand: why is the result ordered differently every time you run a Multiserver query? This might be because the results of the different instances are collected, and merged together to return a single result set. I’m not sure about this though! So if you know the answer to this question, please let me know!