How to Build a SQL Server Inventory Using T-SQL Scripts

You need to create a SQL Server inventory. Without this information, you’ll be unaware of critical information about the environment your responsible to manage.

We’re on a journey to make sure that you as a DBA know what SQL Servers are in your environment so you know what you’re responsible for managing.  As your guide, I discussed in a previous post, how to get started with using the MAP Toolkit from Microsoft to get a list of SQL Servers in your environment and some key metadata about them.

Today, we will be looking at using T-SQL and a Central Management Server to create a SQL Server inventory. Let’s say that you’re  new at this company or in this role and all you have right now is the list of SQL Servers that people know about. you haven’t been able to run the MAP Toolkit or maybe you’ve been told that you can’t run it for some reason. the list of SQL Servers that people “just know about” probably isn’t anywhere near complete, but you have to start somewhere.

How To Create a Central Management Server

Open SQL Server management Studio. If you don’t see an area called Registered Servers, go to the View menu at the top and left click. Then left click on Registered Servers. Now Right click the “Central Management Servers” folder and choose “Create Central Management Server.” I recommend choosing a SQL Server in your environment that is on the newest SQL Server in your environment. The SQL Server must be on at least SQL Server 2008 in order to create a Central Management Server.

Once you do this, the fill in the details of the server registration dialog, then click Test and then Save. If you don’t get a successful test, then check your server registration information and try again.

Now, right click the name of the registered server you just created and select “New Server Group.” create groups that will organize your SQL Servers. That could be done by product name, SQL Server version, or by things like Production, Staging, Development. Now right click a folder and select New Server Registration and register the appropriate servers

Using a Central Management Server to Build a SQL Server Inventory

Right click your Central Server name and select “New Query.” Now copy the code below, paste it into the window. Let’s talk about what it does before you run it.

1. If it doesn’t exist already, it creates a database called DBAUtility on each of the servers it connects to.
2. The script creates a database view in the DBAUtility database. That view allows you to collect some basic information about the SQL Server instance. the view returns the machine name, SQL Server instance name, product version, Cumulative Update level, edition, and product version number. This will be enough information to help you identify critical things like, how many SQL Servers are on unsupported versions or old cumulative updates.
3. The script creates a table to store the data for each SQL Server and a procedure to insert that data.
4. There is a call to the database view to retrieve the data for each SQL Server that your Central management Server connects to. You could also query the table in each DBAUtility database as well.

/*Make the DBAUtility database if it doesn't exist.
This will put the database in the default data and 
log directories for the SQL instance.
*/
IF NOT EXISTS(SELECT * from sys.databases where [name] = 'DBAUtility')
  BEGIN
  CREATE DATABASE DBAUtility
  END;
GO

USE DBAUtility;
GO

CREATE OR ALTER VIEW GetSQLServerProperties
AS
SELECT  
  SERVERPROPERTY('MachineName') AS [ServerName],
    CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER'
    ELSE SERVERPROPERTY('InstanceName')
    END AS [SQLServerInstanceName], 
    CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' 
     ELSE 'unknown'
    END AS ProductVersion,
  SERVERPROPERTY('ProductUpdateLevel') AS CULevel,
  SERVERPROPERTY('Edition') AS [Edition],
  SERVERPROPERTY('ProductVersion') AS [ProductVersionNumber];

GO
DROP TABLE IF EXISTS dbo.SQLServerProperties
CREATE TABLE dbo. SQLServerProperties
(
Id INT IDENTITY (1,1) NOT NULL,
ServerName VARCHAR(20) NOT NULL,
SQLServerInstanceName VARCHAR(50) NOT NULL,
ProductVersion CHAR(7) NOT NULL,
CULevel VARCHAR(4) NOT NULL,
Edition VARCHAR(40) NOT NULL,
ProductVersionNumber VARCHAR(15) NOT NULL,
CONSTRAINT PK_SQLServerProperties PRIMARY KEY CLUSTERED (Id),

/*Unique index below prevents the same server name and instance 
name from being entered into the database twice
*/
INDEX IX_ServerName_InstanceName UNIQUE NONCLUSTERED (ServerName, SQLServerInstanceName)

);

GO

CREATE OR ALTER PROCEDURE dbo.SQLServerPropertiesInsert
AS
INSERT INTO SQLServerProperties

SELECT CONVERT(VARCHAR(20),[ServerName]), 
 CONVERT(VARCHAR(50),[SQLServerInstanceName]), 
 CONVERT(CHAR(7),[ProductVersion]), 
 CONVERT(VARCHAR(4),[CULevel]), 
 CONVERT(VARCHAR(40),[Edition]), 
 CONVERT(VARCHAR(15),[ProductVersionNumber])
 FROM dbo.GetSQLServerProperties;
 
 /*
 Example call to insert the data
 exec dbo.SQLServerPropertiesInsert

 Example call to retreive the data inserted
 SELECT *
 FROM GetSQLServerProperties;
 */

You can get started building a SQL Server inventory if you follow these steps. The inventory makes assessment of next steps  easy.

Next Steps To Take

1. If you have a lot of SQL Servers to register in a Central Management Server, consider this post which shows you how to do it with PowerShell.
2. Compare your collected data against a known list of currently supported SQL Server versions to find out of support SQL Servers. This is your migration project list.
3. Compare the SQL Server product version numbers to a known list of current patches and see what needs to be patched. This is your immediate patching list.
4. If you have questions or comments, leave me a comment on this post, or message me on Twitter