articles

Articles of SQLschool.gr Team

The sys.dm_server_registry DMV

Antonios Chatzipavlis

Tuesday 16 April 2019

Δεν είναι λίγες οι φορές που θέλουμε για ένα SQL Server instance να δούμε πληροφορίες που αφορούν τα services, network configuration, startup trace flags, version και πολλά ακόμα και καταφεύγουμε στον SQL Configuration Manager.

Παρόλα αυτά όμως τις περισσότερες από αυτές δουλεύουμε από το pc μας χρησιμοποιώντας τον SSMS και για χρησιμοποιήσουμε τον SQL Server Configuration Manager από αυτό είναι μεγάλος πόνος καθώς θα πρέπει να ανοιχτούν πολλές πόρτες αλλά και να αποδοθούν άσκοπα δικαιώματα.

Δεν χρειάζεται να κάνουμε τίποτα από όλα αυτά εφόσον απλά θέλουμε να δούμε τις πληροφορίες καθώς υπάρχει από την έκδοση του SQL Server 2008 η sys.dm_server_registry με την οποία μπορώ να αντλήσω εύκολα με ένα απλό SELECT query. Απλά για την εκτέλεση του χρειάζεται να έχουμε VIEW SERVER STATE permissions.

select * from sys.dm_server_registry

registry_key	value_name	value_data
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DB01	ObjectName	sqlschool\sqlsrvacc
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DB01	ImagePath	"D:\MSSQL14.DB01\MSSQL\Binn\sqlservr.exe" -sDB01
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DB01	Start	2
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$DB01	ObjectName	sqlschool\sqlsrvacc
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$DB01	ImagePath	"D:\MSSQL14.DB01\MSSQL\Binn\SQLAGENT.EXE" -i DB01
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$DB01	Start	2
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$DB01	DependOnService	MSSQL$DB01
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\CurrentVersion	CurrentVersion	14.0.1000.169
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\Parameters	SQLArg0	-dD:\MSSQL14.DB01\MSSQL\DATA\master.mdf
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\Parameters	SQLArg1	-eD:\MSSQL14.DB01\MSSQL\Log\ERRORLOG
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\Parameters	SQLArg2	-lD:\MSSQL14.DB01\MSSQL\DATA\mastlog.ldf
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp	TcpDynamicPorts	50198
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp	DisplayName	TCP/IP
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Np	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Np	PipeName	\\.\pipe\MSSQL$DB01\sql\query
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Np	DisplayName	Named Pipes
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Sm	Enabled	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Sm	DisplayName	Shared Memory
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Via	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Via	DefaultServerPort	0:1433
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Via	ListenInfo	0:1433
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Via	DisplayName	VIA
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp	Enabled	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp	ListenOnAllIPs	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp	KeepAlive	30000
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp	DisplayName	TCP/IP
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	Enabled	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP1	IpAddress	fe80::8011:45f6:19be:1953%8
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	Enabled	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP2	IpAddress	172.0.1.10
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	Enabled	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP3	IpAddress	2a02:587:2106:600:6125:92dd:2677:998b
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP4	IpAddress	fe80::6125:92dd:2677:998b%3
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP5	IpAddress	192.168.1.17
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP6	IpAddress	::1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP7	IpAddress	127.0.0.1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP8	IpAddress	fe80::5efe:192.168.1.15%12
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	Enabled	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	Active	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	TcpPort
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	TcpDynamicPorts	0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	DisplayName	Specific IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IP9	IpAddress	fe80::200:5efe:172.0.1.10%2
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IPAll	TcpPort	50230
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IPAll	TcpDynamicPorts
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\MSSQLServer\SuperSocketNetLib\Tcp\IPAll	DisplayName	Any IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\SQLServerAgent	ErrorLoggingLevel	3
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\SQLServerAgent	JobHistoryMaxRows	1000
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\SQLServerAgent	JobHistoryMaxRowsPerJob	100
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\SQLServerAgent	WorkingDirectory	D:\MSSQL14.DB01\MSSQL\JOBS
HKLM\SOFTWARE\Microsoft\SQMClient	MachineId	{8F2F2888-8066-49C5-8F05-953026103E3F}
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\CPE	InstallID	1e8c2d4b-50c0-4979-83fa-22ebfbd97628
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	LogPath	D:\MSSQL14.DB01\MSSQL\Log\Polybase
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DiagTestMode	true
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	MaxDiagSessions	10
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	EngineTdsServerPort	17001
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DmsControlChannelHostname	SQL2017
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DmsControlChannelPort	16450
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DmsDataChannelPort	16451
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DmsDiagnosticsPort	16452
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	CtlDiagnosticsPort	16453
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	ScaleOutReady	true
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	PortRangeStart	16450
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	PortRangeEnd	16460
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	NodeRole	1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	EngSvcAcct	sqlschool\sqlsrvacc
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL14.DB01\Polybase\Configuration	DMSSvcAcct	sqlschool\sqlsrvacc

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.