Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

Multi-Server Queries – Underappreciated features of Microsoft SQL Server

Feb 4 2011 12:00AM by Nakul Vachhrajani   

This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren's editorial on SQLServerCentral.com.

After Activity Monitor, this is one of the other features that I was surprised to see in the list of underappreciated features. This feature has been a dream come true for anybody who manages more than one server at a time. The need to be able to query a bunch of servers in one go and the fact that this feature has been around since Microsoft SQL Server 2005 had made me think that this should have been pretty common in usage by now.

Anyway, let’s quickly see how to use this wonderful feature that Microsoft SQL Server Management Studio provides.

Running Multi-Server Queries

One of our esteemed SQL Server MVPs is Mr. Pinal Dave (blog). He has, on June 14, 2009 written a very short, concise and precise blog post on how to run Multi-Server Queries. You can find that particular post here.

Keep in mind that the most important usability change that you need to make in order to use this feature is that all servers participating in a multi-server query must be registered servers.

What Pinal’s blog post does not cover are some navigational aids and query execution options. I will attempt to cover them here.

Changes to the Query Editor window

The Query editor window in a multi-server query is similar to the regular single-instance query editor, but there is one minor difference. The SSMS query status bar informs us about the number of servers that a particular query will run against. On my test environment, let’s say I have two registered instances. When I attempt to run a multi-server query, this is what I see:

image

Query Execution Options:

In Pinal’s post, you would have already seen how the results would appear in your results pane by default. Results from both instances are available in a single grid, and the SQL Server instance name helps us identify which server contributed to the result. However, we can change the way these results are displayed. You can navigate out to Tools->Options->Query Results to see a window similar to the following. Please note that the values in the screenshot below are default values.

image

  • Add login name to the results – Setting this to TRUE adds a column containing the login name which produces the result row
  • Add server name to the results – Adds a column containing the server instance name for a particular result row when set to TRUE
  • Merge Results – Shows results from different servers in the same grid when set to TRUE

Allow me to change the SQL Server registration of one of the servers to use SQL Server authentication. Also, I will set the “Merge Results” property to FALSE, and the “Add login name to the results” to TRUE. Below will be the output when I run the following query (borrowed from the example in Pinal’s blog):

SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
GO 

image

Important points to note

Limitation

As of now, it is not possible to select a subset of servers from within a server group (you can select a single instance, but not a subset). The recommendation would be to have a separate group if such a selection needs to be made often.

This is not a programmability enhancement!

Also, this feature is nothing more than an ability to execute the same query against multiple registered servers and obtain their results within a single window. It is not a programmability enhancement, and hence the following should not be expected:

  1. Cross-server support for JOIN or ORDER BY clauses
  2. Transaction support across multiple servers

Security considerations

Quoting MSDN:

Because the connections maintained by a Central Management Server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.

You may also want to read:

This post completes the first part of my series on “Underappreciated features of Microsoft SQL Server”. Do share your feedback on how the journey has been till now, and what else would you like me to write on.

We will resume our journey to explore T-SQL Enhancements in the next couple of weeks. In the meanwhile, I will share with you some other interesting incidents that happened last month.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, SSMS, Tools and Utilities, Series,


Nakul Vachhrajani
4 · 36% · 11635
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

9  Comments  

  • I am using Multi-Server queries quite often and found it very helpful. Thanks for bringing this topic up.

    commented on Jul 14 2011 8:13AM
    Jacob Sebastian
    1 · 100% · 32235
  • You made the comment "you can select a single instance". Can you explain how you would do that from a CMS query window?

    Thanks.

    commented on Nov 23 2011 5:06PM
    khemmerl
    2216 · 0% · 6
  • @khemmerl: In the screenshot referenced above, you can select the entire "Local Server Group" as I have done in the example or select the individually registered server instances. What you cannot do is choose a subset of instances, for example, select 2 instances out of 5 registered instances.

    commented on Nov 23 2011 8:29PM
    Nakul Vachhrajani
    4 · 36% · 11635
  • Can you post the code showing how to select against individually registered server instances? I'm not able to get that to work.

    Thanks

    Ken

    commented on Nov 23 2011 10:51PM
    khemmerl
    2216 · 0% · 6
  • Ken,

    There is no code as such. It's about selecting individual instances from the UI.

    I have uploaded a problem steps recorder session output at http://beyondrelational.com/media/p/14569.aspx.

    Let me know if it helped.

    commented on Nov 23 2011 11:32PM
    Nakul Vachhrajani
    4 · 36% · 11635
  • I understand. Thank you for the explaination.

    commented on Nov 24 2011 11:34AM
    khemmerl
    2216 · 0% · 6
  • @Ken: You are most welcome. Feel free to let me know in case you have any queries on any of my other posts.

    commented on Nov 24 2011 10:26PM
    Nakul Vachhrajani
    4 · 36% · 11635
  • Before this i was working with linked server for monitoring of version details and backup details, your post is very help full to me to get this monitoring in one line code.

    Thanks Nakul

    commented on Feb 12 2012 11:29PM
    Nirav
    37 · 5% · 1593
  • Glad to note that the post helped, Nirav!

    commented on Feb 12 2012 11:31PM
    Nakul Vachhrajani
    4 · 36% · 11635

Your Comment


Sign Up or Login to post a comment.

"Multi-Server Queries – Underappreciated features of Microsoft SQL Server" rated 5 out of 5 by 2 readers
Multi-Server Queries – Underappreciated features of Microsoft SQL Server , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]