January 30, 2017 at 6:35 am
Hi all
I've been given the task of auditing our entire SQL estate as no-one is keeping track of the servers.
I've cobbled together what I need (I'll post my code at the bottom) and it seems to work on SQL2005 upwards.
We've got at least one server that's running 2000 and I can't see the changes I need to make to stop the code failing (I think I know where it is but I'm not 100% sure).
This is my working code:-
SET NOCOUNT ON;
-- Drop all temp tables
IF OBJECT_ID('tempdb..#TCPPorts') IS NOT NULL
BEGIN
DROP TABLE #TCPPorts;
END;
IF OBJECT_ID('tempdb..#SQLInstances') IS NOT NULL
BEGIN
DROP TABLE #SQLInstances;
END;
IF OBJECT_ID('tempdb..#Plat') IS NOT NULL
BEGIN
DROP TABLE #Plat;
END;
IF OBJECT_ID('tempdb..#Keyexist') IS NOT NULL
BEGIN
DROP TABLE #Keyexist;
END;
-- Create all temp tables
CREATE TABLE #TCPPorts
(
PortType NVARCHAR(180),
Port INT
);
CREATE TABLE #SQLInstances
(
InstanceID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
SQLVersion VARCHAR(180),
MajorVersionNumber INT,
InstName VARCHAR(180),
Folder VARCHAR(50),
StaticPort INT NULL,
DynamicPort INT NULL,
Platform INT NULL,
MinMemory INT NULL,
MaxMemory INT NULL,
PhysicalMemory INT NULL,
Processors INT NULL
);
CREATE TABLE #Plat
(
Id INT,
Name VARCHAR(180),
InternalValue VARCHAR(50),
Charactervalue VARCHAR(150)
);
CREATE TABLE #Keyexist
(
Keyexist INT
);
-- Declare all required variables
DECLARE
@CurrID INT,
@ExistValue INT,
@MaxID INT,
@SQL NVARCHAR(1000),
@Platform VARCHAR(100);
-- Get the platform data
INSERT INTO #Plat
EXEC xp_msver;
-- Check if the platform is 64-bit or not
SELECT
@Platform =
(
SELECT DISTINCT
1
FROM
#Plat
WHERE Charactervalue LIKE '%X86%'
);
-- Use this to get the install folder details of the install
IF @Platform IS NULL
BEGIN
INSERT INTO #SQLInstances
(
InstName
, Folder
)
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
UPDATE #SQLInstances
SET
Platform = 64;
END;
ELSE
BEGIN
INSERT INTO #SQLInstances
(
InstName
, Folder
)
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
UPDATE #SQLInstances
SET
Platform = 32;
END;
-- Check for keys and see if install is 64-bit or not
INSERT INTO #Keyexist
EXEC xp_regread
'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
SELECT
@ExistValue = Keyexist
FROM
#Keyexist;
IF @ExistValue = 1
BEGIN
INSERT INTO #SQLInstances
(
InstName
, Folder
)
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
END;
UPDATE #SQLInstances
SET
Platform = 32
WHERE
Platform IS NULL;
-- Loop through all installs to get data
SELECT
@MaxID = MAX(InstanceID)
, @CurrID = 1
FROM
#SQLInstances;
WHILE @CurrID <= @MaxID
BEGIN
DELETE FROM #TCPPorts;
SELECT
@SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
FROM
#SQLInstances
WHERE InstanceID = @CurrID;
INSERT INTO #TCPPorts
EXEC sp_executesql
@SQL;
SELECT
@SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
FROM
#SQLInstances
WHERE InstanceID = @CurrID;
INSERT INTO #TCPPorts
EXEC sp_executesql
@SQL;
SELECT
@SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
FROM
#SQLInstances
WHERE InstanceID = @CurrID;
INSERT INTO #TCPPorts
EXEC sp_executesql
@SQL;
SELECT
@SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\'+Folder+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
FROM
#SQLInstances
WHERE InstanceID = @CurrID;
INSERT INTO #TCPPorts
EXEC sp_executesql
@SQL;
UPDATE SI
SET
StaticPort = P.Port,
DynamicPort = DP.Port
FROM #SQLInstances SI
INNER JOIN #TCPPorts DP
ON DP.PortType = 'TCPDynamicPorts'
INNER JOIN #TCPPorts P
ON P.PortType = 'TCPPort'
WHERE
InstanceID = @CurrID;
SET @CurrID = @CurrID + 1;
END;
-- Get final bits of info for audit
UPDATE #SQLInstances
SET
MinMemory =
(
SELECT
value
FROM
sysconfigures
WHERE comment = 'Minimum size of server memory (MB)' -- Minimum SQL Usable memory
),
MaxMemory =
(
SELECT
value
FROM
sysconfigures
WHERE comment = 'Maximum size of server memory (MB)' -- Maximum SQL Usable memory
),
PhysicalMemory =
(
SELECT
InternalValue
FROM
#Plat
WHERE Name = 'PhysicalMemory' -- Total Usable memory on server (should be higher than Maximum SQL memory)
-- to allow the O/S some memory for normal tasks
),
Processors =
(
SELECT
InternalValue
FROM
#Plat
WHERE Name = 'ProcessorCount'
),
SQLVersion = (LEFT(@@version, CHARINDEX('Copyright', @@version)-2)),
MajorVersionNumber =
(
SELECT
CAST(@@MICROSOFTVERSION / 0x01000000 AS INT)
);
-- Display the data
SELECT
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName
, SQLVersion
, MajorVersionNumber
, InstName
, Folder
, StaticPort
, DynamicPort
, Platform
, MinMemory
, MaxMemory
, PhysicalMemory
, Processors
FROM
#SQLInstances;
SET NOCOUNT OFF;
Can anyone suggest any improvements and any ideas on why it's failing on SQL2000?
Anyhelp gratefully received.
January 30, 2017 at 7:02 am
John
January 30, 2017 at 7:10 am
John Mitchell-245523 - Monday, January 30, 2017 7:02 AMHave you tried making the change that you think you need to make? What happens if you run the code directly against a SQL Server 2000 server - you must get error messages?John
When I run this bit :-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
I get the following error:-
Msg 22001, Level 16, State 0, Line 0
RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'
I've been into regedit on the offending machine and changed the path to what I think is the right path (for the second part), I still get the same error.
January 30, 2017 at 7:22 am
John
January 30, 2017 at 8:43 am
Record 2
Value
SqlMdacRegRefCount
Data
1
If I then change the script to read:-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances - Item #1';
I get the same error.
I also get the error if I just use :-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances';
January 30, 2017 at 8:48 am
richardmgreen1 - Monday, January 30, 2017 8:43 AMYeah, if I change the script to read:-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\';
I get two records returned. These are:-
Record 1
Value
InstalledInstances - Item #1
Data
MSSQLSERVERRecord 2
Value
SqlMdacRegRefCount
Data
1If I then change the script to read:-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances - Item #1';I get the same error.
I also get the error if I just use :-
EXEC xp_regenumvalues
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances';
OK, and that path is definitely valid and doesn't have any special characters masquerading as normal characters? What about permissions - does it have the same access on it as other keys that you can see with the command?
John
January 30, 2017 at 8:51 am
It certainly looks that way.
I've got domain admin access and I can run this script on every other server (this is the only server I've come across so far that SQL2000 but I'm expecting more).
January 30, 2017 at 9:06 am
richardmgreen1 - Monday, January 30, 2017 8:51 AMIt certainly looks that way.
I've got domain admin access and I can run this script on every other server (this is the only server I've come across so far that SQL2000 but I'm expecting more).
Didn't you say the key was Instance Names, not Installed Instances? I'm not sure Domain Admins gives you access to every key in every registry. But in any case, xp_regenumvalues runs in the security context of the SQL Server service account, I think, so you'll need to check what permissions that has.
What happens if you do this? What happens if you run just the reg query command from a command prompt? xp_cmdshell 'reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names'
John
January 30, 2017 at 9:30 am
I've just tried the following (with outputs):-
xp_cmdshell 'reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names'
xp_cmdshell is not recognised as an internal or external command, operable program or batch file
reg query HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
Invalid syntax (probably because of the spaces)
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names"
ERROR: The system was unable to find the specified registry key or value
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server"
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
InstalledInstances REG_MUL;TIsz MSSQLSERVER
SqlMdacRegRefCount REG_DWORD 0x1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\8.00.000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\BKUPEXEC
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances"
ERROR: The system was unable to find the specified registry key or value
January 30, 2017 at 9:41 am
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /v InstalledInstancesJohn
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply