Thursday, 10 September 2009

SQL 2008 : Using MultiServer Queries

In SQL 2008 you can run a query against multiple servers at the same time by opening a query window against a server group rather than an individual server.

To demonstrate, a query that fetches data about a server -
SELECT
  TOP 1 BACKUP_START_DATE AS LAST_BACKUP
, @@VERSION AS SQL_VERSION
, CREATE_DATE AS LAST_STARTUP
, DATEDIFF(D,CREATE_DATE,GETDATE()) AS DaysUptime
FROM MSDB.DBO.BACKUPSET (NOLOCK)
CROSS JOIN SYS.DATABASES (NOLOCK)
WHERE SYS.DATABASES.NAME = 'TEMPDB'
ORDER BY BACKUP_SET_ID DESC

By executing against the group we get it executed against each server, hence a row for each >

No comments: