Questo script serve per restituire tutte le info rigardante ai servizi di SQL Server in un Server.
IMPORTANTE: C’è un bug che non ho ancora trovato una soluzione, dunque è meglio non fidarsi dalla informazione Current_Status dalla istanza default in un Cluster.
Questo è stato il unico bug che ho trovato fino a questo momento.
/*
****************************************************************************************************************************************
Script Name: info_tutti_servizi_mssql.sql
Client's Name: Service Desk
Create Date: 23/10/2018 - 16:00
Created By: Bruno Bragatto - Senior DBA
Objective: Script per verificare tutti i servizi di MSSQL in un Server (MSSQL, MSAS, Browser, Agent ecc...).
----------------------------------------------------------------------------------------------------------------------------------------
Modification Log
Date: 02/11/2018
Modified By: Bruno Bragatto
Modification Details: Sistemato le informazioni nel script.
Initial Created by: Bruno Bragatto
****************************************************************************************************************************************
IMPORTANTE: C'è un bug che non ho ancora trovato una soluzione, dunque è meglio non fidarsi dalla informazione Current_Status dalla istanza default in un Cluster.
Questo è stato il unico bug che ho trovato fino a questo momento.
*/
SET NOCOUNT ON
DECLARE @Keyexist Table (Keyexist int)
DECLARE @ServiceName AS VARCHAR(50),
@SQL VARCHAR(MAX),
@ExistValue INT,
@VERSION NVARCHAR(4),
@SSIS_DIR NVARCHAR(MAX)
IF OBJECT_ID('tempdb..#tempServicesInfo') IS NOT NULL
DROP TABLE #tempServicesInfo
CREATE TABLE #tempServicesInfo
(
InstanceName NVARCHAR(100),
ServiceReg NVARCHAR(100),
ServiceDesc NVARCHAR(100),
Instance_ID NVARCHAR(100),
LoginName NVARCHAR(100),
StartupMode NVARCHAR(100),
Current_Status NVARCHAR(100)
)
IF OBJECT_ID('tempdb..##tempServiceStatus') IS NOT NULL
DROP TABLE ##tempServiceStatus
CREATE TABLE ##tempServiceStatus
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus VARCHAR(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
IF OBJECT_ID('tempdb..#tempServicesNames') IS NOT NULL
DROP TABLE #tempServicesNames
CREATE TABLE #tempServicesNames
(
Service NVARCHAR(100),
Name NVARCHAR(100),
LName NVARCHAR(100),
)
--Populate Support Table
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\Microsoft\Microsoft SQL Server\Services';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
INSERT INTO #tempServicesNames (Service)
EXEC master..xp_regenumkeys
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Services'
--Update Name
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SOFTWARE\\Microsoft\\Microsoft SQL Server\\Services\\'+ Service + ''',
@value_name = N''Name'',
@value = @returnValue OUTPUT;
UPDATE #tempServicesNames SET Name = @returnValue
WHERE Service = ''' + Service + '''' + CHAR(13)
FROM #tempServicesNames
WHERE NAME IS NULL
EXEC (@SQL)
--UPDATE LNAME
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SOFTWARE\\Microsoft\\Microsoft SQL Server\\Services\\'+ Service + ''',
@value_name = N''LNAME'',
@value = @returnValue OUTPUT;
UPDATE #tempServicesNames SET LName = @returnValue
WHERE Service = ''' + Service + '''' + CHAR(13)
FROM #tempServicesNames
WHERE LName IS NULL
EXEC (@SQL)
--select * from #tempServicesNames
--IF --32Bits Instance on 64bits OS (Wow6432Node)
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
-- Get instance names from Windows registry Wow6432
INSERT INTO #tempServicesInfo (ServiceDesc, Instance_ID)
EXEC master..xp_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
--UPDATE ServiceDesc with Description and Instance Name for SQL Server Service
UPDATE #tempServicesInfo
SET ServiceDesc = 'SQL Server (' + InstanceName + ')'
where Instance_ID LIKE 'MSSQL%'
--Update ServiceReg with SQL Server Instance info
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='SQL Server')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='SQL Server')+InstanceName
ELSE
NULL
END
END
WHERE Instance_ID LIKE 'MSSQL%'
--SQL Server 64Bits Instance
-- Get instance names from Windows registry
INSERT INTO #tempServicesInfo (InstanceName,Instance_ID)
EXEC master..xp_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
--UPDATE ServiceDesc with Description and Instance Name for SQL Server Service
UPDATE #tempServicesInfo
SET ServiceDesc = 'SQL Server (' + InstanceName + ')'
where Instance_ID LIKE 'MSSQL%'
--Update ServiceReg with SQL Server Instance info
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='SQL Server')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='SQL Server')+InstanceName
ELSE
NULL
END
END
WHERE Instance_ID LIKE 'MSSQL%'
--IF Reporting Service
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\RS';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
-- Get instance names from Windows registry Reporting
INSERT INTO #tempServicesInfo (InstanceName,Instance_ID)
EXEC master..xp_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\RS'
--Update ServiceDesc Reporting Services
UPDATE #tempServicesInfo
SET ServiceDesc = 'SQL Server Reporting Services (' + InstanceName + ')'
where Instance_ID LIKE 'MSRS%';
--Update ServiceReg Reporting Services
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='ReportServer')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='ReportServer')+InstanceName
ELSE
NULL
END
END
WHERE ServiceDesc LIKE 'SQL Server Reporting Services%'
--IF Analysis Services
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\OLAP';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
-- Get instance names from Windows registry Analysis
INSERT INTO #tempServicesInfo (InstanceName,Instance_ID)
EXEC master..xp_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\OLAP'
UPDATE #tempServicesInfo
SET ServiceDesc = 'SQL Server Analysis Services (' + InstanceName + ')'
where Instance_ID LIKE 'MSAS%'
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='Analysis Server')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='Analysis Server')+InstanceName
ELSE
NULL
END
END
WHERE ServiceDesc LIKE 'SQL Server Analysis Services%'
--Update ServiceReg Reporting Services
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='ReportServer')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='ReportServer')+InstanceName
ELSE
NULL
END
END
WHERE ServiceDesc LIKE 'SQL Server Reporting Services%'
--INTEGRATION SERVICES
--SET @teste = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\RS';
SET @VERSION = convert(NVarchar(max),SERVERPROPERTY('productmajorversion'))*10
SET @SSIS_DIR = N'SYSTEM\\CurrentControlSet\\Services\\MsDtsServer'+ @VERSION +''
--EXEC master.dbo.xp_regread
--@rootkey = N'HKEY_LOCAL_MACHINE',
--@key = @SSIS_DIR;
--IF Integration Services (SSIS)
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = @SSIS_DIR;
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert INTO #tempServicesInfo (InstanceName, ServiceDesc)
VALUES (CONVERT(NVarchar(max),serverproperty('ComputerNamePhysicalNetBIOS')),'SQL Server Integration Services '+convert(NVarchar(max),cast(SERVERPROPERTY('productmajorversion') as decimal(3,1))))
Update #tempServicesInfo
SET ServiceReg = 'MsDtsServer'+@VERSION,
Instance_ID = 'MsDtsServer'+@VERSION
WHERE ServiceDesc LIKE '%Integration%'
--IF SQL Browser
Insert into @Keyexist
EXEC master.dbo.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\\CurrentControlSet\\Services\\SQLBrowser';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert INTO #tempServicesInfo (InstanceName, ServiceReg, ServiceDesc, Instance_ID)
VALUES (CONVERT(NVarchar(max),serverproperty('ComputerNamePhysicalNetBIOS')), 'SQLBrowser','SQL Server Browser', 'SQL Browser')
-- get SQL Server Agent Path
INSERT INTO #tempServicesInfo (InstanceName,ServiceDesc, Instance_ID)
SELECT InstanceName, 'SQL Server Agent ('+ InstanceName + ')', Instance_ID
FROM #tempServicesInfo
WHERE Instance_ID LIKE 'MSSQL%'
--Update ServiceReg with SQL Server Instance info
UPDATE #tempServicesInfo
SET ServiceReg =
CASE WHEN InstanceName = 'MSSQLSERVER'
THEN (Select Name from #tempServicesNames where Service='SQL Agent')
ELSE
CASE WHEN InstanceName <> 'MSSQLSERVER'
THEN (Select LName from #tempServicesNames where Service='SQL Agent')+InstanceName
ELSE
NULL
END
END
WHERE ServiceDesc LIKE 'SQL Server Agent%'
-- get account information
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SYSTEM\\CurrentControlSet\\Services\\' + ServiceReg + ''',
@value_name = N''ObjectName'',
@value = @returnValue OUTPUT;
UPDATE #tempServicesInfo SET LoginName =
@returnValue
WHERE ServiceReg = ''' + ServiceReg + '''' + CHAR(13)
FROM #tempServicesInfo
WHERE ServiceReg IS NOT NULL
EXEC (@SQL)
--get Service Status information
DECLARE EMP_CURSOR CURSOR FOR
SELECT ServiceReg FROM #tempServicesInfo
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
INTO @ServiceName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SET @SQL = + CHAR(13) +
'Insert Into ##tempServiceStatus (ServiceStatus)
EXEC xp_servicecontrol
@Action = N''QUERYSTATE'',
@ServiceName = N'''+ @ServiceName +'''
UPDATE ##tempServiceStatus
SET ServiceName = N'''+ @ServiceName +'''
WHERE ServiceName IS NULL
' + CHAR(13)
EXEC (@SQL)
FETCH NEXT FROM EMP_CURSOR
INTO @ServiceName
END
CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR
update #tempServicesInfo
set #tempServicesInfo.Current_Status = ServiceStatus
from ##tempServiceStatus sstatus
INNER JOIN #tempServicesInfo sinfo
ON sinfo.ServiceReg = sstatus.ServiceName
--get startupmode information
SET @SQL = 'DECLARE @returnValue int'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SYSTEM\\CurrentControlSet\\Services\\' + ServiceReg + ''',
@value_name = N''Start'',
@value = @returnValue OUTPUT;
UPDATE #tempServicesInfo SET StartupMode = CONVERT(int,@returnValue)
WHERE ServiceReg = ''' + ServiceReg + '''' + CHAR(13)
FROM #tempServicesInfo
WHERE ServiceReg IS NOT NULL
EXEC (@SQL)
UPDATE #tempServicesInfo
SET StartupMode =
CASE WHEN StartupMode = '2'
THEN ('Automatic')
ELSE
CASE WHEN #tempServicesInfo.StartupMode ='3'
THEN ('Manual')
ELSE
CASE WHEN #tempServicesInfo.StartupMode ='4'
THEN ('Disabled')
ELSE
'Other (Boot, System, Disabled or Unkown)'
END
END
END
select * from #tempServicesInfo
