go backarticles

Articles of SQLschool.gr Team

The sys.dm_server_registry DMV

Antonios Chatzipavlis

Δεν είναι λίγες οι φορές που θέλουμε για ένα 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

Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


PASS chapter logo

The Official PASS Local Group for Greece

1435 33 595 27 41 1333
sql school greece logo
© 2010-2019 All rights reserved