Wednesday, 22 September 2010

Making SQL Queries statement act on the same database across multiple servers

Here’s a useful way to apply queries across a number of sql 2008 servers

1. Open Microsoft SQL Server Management Studio
2. Under Database Engine select Central Management Servers
   (It is on the tab "Registered Servers" next to the tab "Object Explorer"
3. Right Mouse click "Central Management Servers" and select Register Central Management Server...
4. Choose the Server that you want to add in the "New Server Registration dialog"
   This has the effect of adding a node directly under "Central Management Server". This is used to host the Server groups
5. Right Mouse click on the new node and select "New Server Group..."
6. Give a group name
7. Right mouse click on the new group and select "New Server Registration..."
8. Select the servers that you want to be part of the group

This completes the setup.

To create queries that act across the registered servers
1. Right mouse click on a server group
2. "New Query"

When you execute the query you will receive the results with an additional "Server Name" column giving the result of that query on that server