List of SQL Servers in a company

Question

List of SQL Servers in a company

Henrik Schütze

Ten Centuries

Points: 1004
More actions
September 20, 2016 at 1:21 am

#330210

Hello,
my Boss has a total horrible Excel list to list all Servers.
This List collect things like:
Name,
Virtual/physic Server,
Production/test,
ServiceLevel,
Cores,
CPU Type,
needed licenses ,
OS Version
and lot of other things. This List is not easy to manage. Is there a better solution than this Excel and a plain SQL Server table?
How does you do this Job?
greetings
Henrik

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

First, use Central Management Servers or Registered Servers; that will be where you add new servers as you discover them.

That functionality is built into SQL Server Management Studio, so there's no excuse not to use it!

after that, add this procedure to all servers(via the very same CMS...one command goes to all servers.)

then simply execute the proc.(Again do this in Central Manamgent Servers , so you get ALL the servers at the same time)

it asks the server how much it has for memory etc, so there's no accidental transpositon or mistakes, or "thinking"

IF OBJECT_ID('[dbo].[sp_serverinventory]') IS NOT NULL

DROP PROCEDURE [dbo].[sp_serverinventory]

GO

CREATE PROCEDURE sp_serverinventory

AS

DECLARE @version NVARCHAR(32)

DECLARE @edition INT

DECLARE @SerialNumber VARCHAR(30);

DECLARE @cpu_count int;

DECLARE @hyperthreadratio int;

DECLARE @physicialCPUSockets int;

DECLARE @PhysicalMemoryMB int;

DECLARE @VirtualMachine varchar(50)

DECLARE @ServerRestartedDate varchar(30)

SELECT @ServerRestartedDate = CONVERT(varchar(30),dbz.create_date,120) from sys.databases dbz where name='tempdb'

IF OBJECT_ID('tempdb.[dbo].[#CPUData]') IS NOT NULL

DROP TABLE [dbo].[#CPUData]

CREATE TABLE #CPUData (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB int,VirtualMachineName varchar(50),VirtualMachine varchar(50))

--SQL2014 and above, column is different

--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

IF Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

IN('11.0','12.0','13.0')

BEGIN

INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachineName],[VirtualMachine])

EXEC(

'SELECT

s.cpu_count ,

s.hyperthread_ratio ,

s.cpu_count / s.hyperthread_ratio As NumberofCores,

s.physical_memory_kb/1024 As MemoryinMb,

s.virtual_machine_type_desc,

CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END As IsVirtual--1 = hypervisor, 2 = other type of virtual

FROM sys.dm_os_sys_info s');

END

ELSE

BEGIN

INSERT INTO #CPUData([cpu_count],[hyperthread_ratio],[physicialCPUSockets],[PhysicalMemoryMB],[VirtualMachineName],[VirtualMachine])

EXEC(

'SELECT

s.cpu_count ,

s.hyperthread_ratio ,

s.cpu_count / s.hyperthread_ratio As NumberofCores,

s.physical_memory_in_bytes/1048576 As MemoryinMb,

'''' AS virtual_machine_type_desc,

'''' As IsVirtual

FROM sys.dm_os_sys_info s');

END

IF EXISTS (SELECT * FROM sys.all_columns WHERE object_name(object_id) ='dm_os_sys_info' AND name = 'virtual_machine_type')

BEGIN

EXEC(

'UPDATE MyTarget

SET VirtualMachine = CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END

--SELECT CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END --1 = hypervisor, 2 = other type of virtual

FROM sys.dm_os_sys_info s

CROSS JOIN #CPUData MyTarget');

END

SELECT --@@SERVERNAME

@cpu_count = s.cpu_count ,

@hyperthreadratio = s.hyperthread_ratio ,

@physicialCPUSockets = s.physicialCPUSockets ,

@PhysicalMemoryMB = s.PhysicalMemoryMB,

@VirtualMachine = s.VirtualMachine

FROM #CPUData s ;

IF EXISTS(SELECT * from master.sys.objects where name = 'sqbutility' and type_desc = 'EXTENDED_STORED_PROCEDURE')

BEGIN

EXEC master..sqbutility

21,

@edition OUTPUT,

@version OUTPUT,

@SerialNumber OUTPUT;

END

ELSE

BEGIN

SELECT @version = '' ,

@edition = 0,

@SerialNumber = ''

END

SELECT @ServerRestartedDate As ServerRestartedDate,

--@@version,

Serverproperty('BuildClrVersion') AS BuildClrVersion,

Serverproperty('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,

Serverproperty('Edition') AS Edition,

CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

WHEN '9.00' THEN 'SQL 2005'

WHEN '10.0' THEN 'SQL 2008'

WHEN '10.50' THEN 'SQL 2008R2'

WHEN '11.0' THEN 'SQL 2012'

WHEN '12.0' THEN 'SQL 2014'

WHEN '13.0' THEN 'SQL 2016'

ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)

+ '.'

+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)

END As SQLVersion,

Serverproperty('productversion') As ProductVersion,

Serverproperty('EditionID') AS EditionID,

Serverproperty('EngineEdition') AS EngineEdition,

Serverproperty('MachineName') AS MachineName,

Serverproperty('ProductLevel') AS ProductLevel,

Serverproperty('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

Serverproperty('ResourceVersion') AS ResourceVersion,

Serverproperty('ServerName') AS ServerName,

ISNULL(Serverproperty('InstanceName'),'') AS InstanceName ,

CASE

WHEN CHARINDEX('(Hypervisor)',CONVERT(varchar(8000),@@version)) > 0

THEN '(Hypervisor)'

ELSE ''

END As VM,

@VirtualMachine AS VirtualMachine,

@version AS RedGateVersion,

@edition AS RedGateEdition,

@SerialNumber AS RedGateSerialNumber,

@cpu_count AS CPUCount,

@hyperthreadratio AS HyperThreadRatio,

@physicialCPUSockets AS PhysicalCPUSockets,

@PhysicalMemoryMB As [PhysicalMemory(MB)],

Getdate() AS DWCreatedDate

GO

and it gives you some great results.

ServerRestartedDate BuildClrVersion ComputerNamePhysicalNetBIOS Edition SQLVersion ProductVersion EditionID EngineEdition MachineName ProductLevel ResourceLastUpdateDateTime ResourceVersion ServerName InstanceName VM VirtualMachine RedGateVersion RedGateEdition RedGateSerialNumber CPUCount HyperThreadRatio PhysicalCPUSockets PhysicalMemory(MB) DWCreatedDate

2016-09-12 18:31:54 v4.0.30319 LOWELLDEV Developer Edition (64-bit) SQL 2012 11.0.3128.0 -2117995310 3 LOWELLDEV SP1 2012-10-19 15:30:00.377 11.00.3000 LOWELLDEV (Hypervisor) Virtual 0 2 1 2 8191 2016-09-20 09:13:44.717

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jasona.work SSC Guru Points: 50083 More actions · Answer 2

My question to you would be, is this a list of all the servers (SQL, Exchange, file share, etc?) If so, then it's going to be tough, regardless, to keep a list up-to-date.

You can look into third party tools (Microsoft System Center comes to mind,) but even there, it won't be perfect. It may not track information you want / need tracked, or it may not "find" all your servers.

Any such listing needs for people to make a concerted effort to maintain it and keep it up to date, or it just becomes so much more junk information.

With that being said, we keep track of our servers with an Excel file, so don't feel bad.