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
Use MSDB
go
Select * from dbo.msdb_version
GO

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


You get the following response from the server.


 

No comments: