A quick and dirty scan of a list of instances using a dynamic linked server.

3

November 12, 2020 by Kenneth Fisher

Note: This is not exactly a dyanmic linked server. It just gets dropped and recreated in a loop.

I recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few hundred instance names and do the following

  • Check if the instance is one we have access to or even exists? If not make a note of the error so we can tell the difference.
  • Collect information like instance size (total size of all databases), CPU count, memory count etc.
  • Collect a list of database names on the instance, their status, size, etc.

So the first thing I did was throw the list of instance names into a cursor and then put the code from my last post inside the loop.

-- Create MyLinkedServer using the current server so that it exists and
-- the code will compile.
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; 
GO

-- Create temp table to hold instance names. 
-- You'll probably want a permanent table.
CREATE TABLE #InstanceList (InstanceName NVARCHAR(256));
INSERT INTO #InstanceList VALUES 
	('InstanceA'), 
	('InstanceB'), 
	('InstanceC');

-- Declare vars
DECLARE @sql NVARCHAR(max);
DECLARE @InstanceName NVARCHAR(256);

-- setup cursor to loop through servers
DECLARE InstList CURSOR FOR 
 SELECT InstanceName
 FROM #InstanceList;

OPEN InstList
FETCH NEXT FROM InstList INTO @InstanceName
WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY  
		EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; 
		-- Test the linked server.
		EXEC sp_testlinkedserver @server = N'MyLinkedServer'  

		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
	END TRY
	BEGIN CATCH

		INSERT INTO dbo.[LinkedServerLog] VALUES (
			@InstanceName   
			,ERROR_NUMBER()  
			,ERROR_SEVERITY()  
			,ERROR_STATE()  
			,ERROR_PROCEDURE()  
			,ERROR_LINE()  
			,ERROR_MESSAGE());
	END CATCH
   
 FETCH NEXT FROM InstList into @InstanceName;
END
CLOSE InstList;
DEALLOCATE InstList;

-- Cleanup
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
 EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins';

I now have a piece of code that loops through a list of instances and creates a linked server for each one. It then tests that linked server to make sure I can connect and if I can’t store the error into an error table. From there I can see which instances I couldn’t connect to and which I could connect but couldn’t log into.

Now, all I have to do is add code into the try block that uses the linked server to collect information.

-- Create MyLinkedServer using the current server so that it exists and
-- the code will compile.
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; 
GO
-- Create temp table to hold instance names. 
-- You'll probably want a permanent table.
CREATE TABLE #InstanceList (InstanceName NVARCHAR(256));
INSERT INTO #InstanceList VALUES 
	('InstanceA'), 
	('InstanceB'), 
	('InstanceC');

-- Create temp table to hold database sizes.
CREATE TABLE #DBList (
	InstanceName NVARCHAR(256), 
	DatabaseName NVARCHAR(256), 
	DatabaseSize DECIMAL(17,5)
	);

-- Declare vars
DECLARE @sql NVARCHAR(max);
DECLARE @InstanceName NVARCHAR(256);

-- setup cursor to loop through servers
DECLARE InstList CURSOR FOR 
 SELECT InstanceName
 FROM #InstanceList;

OPEN InstList
FETCH NEXT FROM InstList INTO @InstanceName
WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY  
		IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
			EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' 

		EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; 
		-- Test the linked server.
		EXEC sp_testlinkedserver @server = N'MyLinkedServer'  

		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
		INSERT INTO #DBList
		SELECT @InstanceName, dbs.name, SUM(size)/128/1024.0
		FROM MyLinkedServer.master.sys.databases dbs
		JOIN MyLinkedServer.master.sys.master_files dbfiles
			ON dbs.database_id = dbfiles.database_id
		GROUP BY dbs.Name, dbs.database_id;
  
	END TRY
	BEGIN CATCH

		INSERT INTO dbo.[LinkedServerLog] VALUES (
			@InstanceName   
			,ERROR_NUMBER()  
			,ERROR_SEVERITY()  
			,ERROR_STATE()  
			,ERROR_PROCEDURE()  
			,ERROR_LINE()  
			,ERROR_MESSAGE());
	END CATCH
   
 FETCH NEXT FROM InstList into @InstanceName;
END
CLOSE InstList;
DEALLOCATE InstList;

-- Cleanup
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
 EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' ;

A couple of notes here. I have a piece of code at the top that adds MyLinkedServer to make sure it exists when the code starts. Otherwise it won’t compile. Also, because of the connection time for the test, particularly if you have a bunch of instances that you can’t log into/don’t exist, this script is going to take a while just to handle the loop. Make sure that the data collection code is hitting system tables and/or is as quick as you can make it.

Like I said in the title, this is pretty quick and dirty. This is the kind of thing you throw together because your manager wants some data collected from a bunch of sources ASAP and T-SQL is by far your best language. There are a lot of better ways to handle this.

3 thoughts on “A quick and dirty scan of a list of instances using a dynamic linked server.

  1. Chad Estes says:

    Impressive bit of T-SQL. How’s it run; pretty quickly?

    I’ve accomplished something similar using PowerShell – we often get blind-sided by a business unit purchasing software which installs a SQL Express instance on the app server, so hitting AD to find all the servers and all the services each was running seemed a better choice.

    It just takes about 4-5 hours to go through all the servers each day. 😦

    • Well, to a certain extent it depends on how many “bad” instance names you have. Remember it has to time out the connection on each of those and that can take a while. Even the ones it connects to add a bit of extra time. Over all though I was pretty pleased with the performance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,154 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: