How to write multi-server queries in SQL Server Management Studio

This post will show you in few quick steps how to write multi-server queries and what are the pitfalls where you should pay more attention.

Multiserver queries are something introduced in SSMS since SQL Server 2008 and can be very useful in many cases, especially in large environments with more that 50 instances for example.

Before you start using multiserver queries you should make sure you have SSMS 2008 or SSMS 2008 R2 installed. Then you should register all servers you wish to manage (find a related post here). If you have already done that, then go to Local Server Group (for sake of easiness of my demonstration I will not use a registered management instance and you should consider everything written here to be valid for both cases – with management server and with locally registered servers), right click on it and click on New Query button. Then a new query window is open which is the same as the one used to query a single instance but with one difference – before being able to execute a query, the management studio will try to connect to all registered instances so be careful and patient.

image

After writing a query and execute it what happens is that the query is executed on every instance and when the result is returned, the studio is merging it, adding one additional column to the returned dataset – the one with the server name as registered in the studio (not @@SERVERNAME but the name in the Registered Server window of the studio)

Common pitfalls and points to be noted:

1. Write queries valid for all engine editions of the group of server against you are running the multiserver query. If it is not compatible and error is raised and result form this server is not included (the query is returning results after all but not from all servers)

2. If you are creating objects in your query/script, be careful and perform a clean-up because this object is created in all servers!

3. If the first server from the list to be queried returns error, then the studio will not show any results (this is addition to point 1. above)

 

There are some properties that can be edited in the studio concerning returned results. Go to Options –> Query Results –> SQL Server –> Multiserver Results

image

There are three options which basically speak for themselves so no need of additionally describe them.

So, after writing so much theory, it is time to simply provide you a quick query, which can quickly provide you an overview of the environment you are managing.

 
   1: SELECT     

   2:        SERVERPROPERTY('ProductVersion') as [Product Version]   

   3:       ,SERVERPROPERTY ('ProductLevel') as [Product Level]   

   4:       ,SERVERPROPERTY ('Edition') as [Edition]   

   5:       ,SERVERPROPERTY('EngineEdition') as [Engine Edition]   

   6:       ,SERVERPROPERTY('IsClustered') as [IsClustered]

Enjoy and stay tuned for my next posts where I will summarize my research on extracting information about database backups, integrity checks, maintenance plans and SQL Server configuration.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s