SQL Server :: How to query a whole Data Center

Few days ago I had the time to update one of my too called SQL_Spring_Cleaning.

This made me figure out that there was space for another tool: I called it Wheke-Wheke , to honour a Māori song that I still like to sing with my daughter.

Problem

Sometimes you find yourself alone in a Data Center with a serious task: the Data Center has been growing in size in the last two decades and you need to discover what databases are in use and what not.

This is when you might need the help of Wheke-Wheke which tentacles will allows you to discover unused SQL Server databases across the whole Data Center.

How it works

I know what you are thinking:

“oh, well, you need to use Linked Servers for that, right?”

No. Linked Servers are not needed. 

“Well, so you are creating a Central Management Server?”

No. Who the hell uses that…

“So you are deploying a script on each server?”

Naaa…, your Project Manager will come to your desk with a stick.

“You are using PowerShell?”

Nope.

“So what are we using here… ?”

We are using OPENDATASOURCE one of the most underrated T-SQL arguments that will silently allows you to connect to a remote SQL Server without need to create a permanent connection to it. We will then use FETCH to iterate through a list of SQL Server instances.

The most important part of the code is this:

DECLARE @ServerName VARCHAR(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName VARCHAR(50))
INSERT INTO @myTableVariable VALUES (1, 'Server_A'), (2, 'Server_B') -- Add your SQL Server list here

DECLARE VarCursor CURSOR
FOR
SELECT ServerName
FROM @myTableVariable

OPEN VarCursor
FETCH NEXT
FROM VarCursor
INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @DynamicSQL = '
	--INSERT INTO [msdb].[dbo].[ConnectionCounts_Remote]
	SELECT ''' + @ServerName + ''' AS [ServerName],*
    FROM OPENDATASOURCE(''MSOLEDBSQL'', ''Server=' + @ServerName + ';TrustServerCertificate=Yes;Trusted_Connection=Yes;'').master.sys.databases'

	EXEC (@DynamicSQL)

	FETCH NEXT
	FROM VarCursor
	INTO @ServerName
END

CLOSE VarCursor
DEALLOCATE VarCursor

As you can see we are going to create a list of SQL Server instances and query each of them.

In this example I’m querying sys.databases but you can query any other table.

Query whole data center

Conclusions

The result is spectacularly fast, noiseless and needs zero code to be deployed.

I’m going to work more on it as this query is quite nice and might allow you to retrieve all sort of data. The fact that you can save the result in a #TempTable will allow to save the results with a right click to Excel and this is gold if you need to withdraw raw data.

So please, take the code and use it.

 

Related

Leave a Reply

You have to agree to the comment policy.