MultiServer Query

Querying Data from multi server simultaneously

    How nice if you can do the multi-server query. You can query from different database that are sitting on different server and get result together. For example if you want to know how many database are running under less compatibility level on my all the environment (development, QA, staging), or you want to know what all jobs are failing in my environments. Let’s have fun below J

·         How to do that?

 

1.       To do this first we need to register the server in “Central Management Server” in register server menu.  Open SSMS go to View menu and click on Register server option. Now new

2.       Now right click on the “Central Management Server” and select “Register Central Management Server” now give the credential for the base server where this configuration will store, and give the name of this central management server.

3.       Now right click on this newly register server and select option “New server registration” and give the all credential, similarly you can register many server that you need to query.

4.       Then right click on the central registered server and select new query. Now your query can get data from all register server in one result set.

  

·          I want to know how many of jobs are failing in my all the environment   :

; WITH CTE AS (
SELECT CASE @@servername WHEN 'EDQASSIS' THEN 'QA' WHEN 'EDFSSIS'
THEN 'Development' ELSE ''END [server name]
,j.name,js.step_Name
,CASE js.run_Status WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3
THEN 'Canceled' END AS RunStatus,
CAST(CONVERT(VARCHAR(8)
,CAST(js.run_Date AS VARCHAR(20))+cast(js.run_time AS VARCHAR(20)) )
AS DATETIME)JobRunDate
FROM MSDB..SYSJOBS J INNER JOIN MSDB..SYSJOBHISTORY JS ON j.job_id=js.job_id
)
SELECT * FROM cte WHERE runstatus='Failed'  

·         I want to know the compatibility level, recovery model of all the database of my all the environment.

SELECT @@servername,Name

,case Compatibility_level when 70 then 'SQL Server 2008 through SQL Server 2008 R2'

when 80 then 'SQL Server 2008 through SQL Server 2008 R2'

when 90 then 'SQL Server 2008 through SQL Server 2012'

when 100 then 'SQL Server 2008 through SQL Server 2016 and Azure SQL Database'

when 110 then 'SQL Server 2012 through SQL Server 2016 and Azure SQL Database'

when 120 then 'SQL Server 2014 through SQL Server 2016 and Azure SQL Database'

when 130 then 'SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016' end

as CompatibilityLevel,

 CASE  recovery_model WHEN 1 THEN 'FULL' WHEN 2 THEN 'BULK LOGGED' WHEN 3 THEN 'SIMPLE' END AS RecoveryModel FROM SYS.DATABASES

 

References : https://msdn.microsoft.com/en-us/library/bb964743.aspx