How to query data from 2 or more servers at a time without Linked Servers:

We can use Central Management Server(CMS) in SQL Server to achieve this.

Open SSMS and click on View and then select ‘Registered Servers’ as shown in below capture

Now it will connect to DB Engine and we can connect to Integration/Reporting/Analysis/Compact engines by clicking on the icons directly

Expand DB Engine and will notice 2 sections Local Groups or Central Management Server. We can use any of these options to connect servers. Now I am using CMS. By right clicking on CMS we need to select ‘Register CMS’ see below capture

Then a new window will where we need to select a CM server. After selecting a server save it.

Now I will create a group named ‘Test’ under this registered server as shown in below.

After creating Test group I will add 2 servers to this group. We can by right clicking on Test group we created as shown in below capture and select the ‘New Server Registration’.

I registered 2 servers one is MSSQLSERVERNEW and other is SQLEXPRESS servers under Test group.

Now by clicking on the Test group and select ‘New Query’ option a new query window will open and if I run any query it will fetch data from both servers that are registered under the Test group.

For example I will run “SELECT name FROM sysdatabases;”. If you look at the output it has databases names of both the servers as shown in below capture.

In the same way we can connect any number of servers and retrieve data from them.

Thanks