Monday, June 22, 2015

Can you run queries on multiple SQL Server Instances at the same time?

It is possible to query multiple SQL Servers using the SQLCMD mode in SQL Server Management Studio. You start getting connected to atleast on instance.

In the following example, there are two instances of SQL Server:
1. SQL Server 2012 with named instance 'RegencyPark'
2. SQL Server 2016 CTP2 with named instance 'May2015'

I start with SSMS 2012 and run the following statements after enabling SQLCMD Mode in SSMS 2012.
You will get connected to the two named instances when you query them as shown here (in a query window in SSMS 2012):

:Connect .\May2015
Select @@version
Select * from dbo.msdb_version

:Connect .\RegencyPark
Select @@version
Use AdventureWorks2012
Select * from Person.person

You get the following response from the server.


No comments: