–By Lori Brown @SQLSupahStah
The dynamic management view sys.dm_server_registry has been around since SQL 2008 R2 SP1 so it is not new but it’s relatively new to me. Since I have a ton of SQL Servers to monitor, it is always good to have some way of easily knowing if there are any start up parameters configured for the instance. I found that this and more can be pulled from the registry using sys.dm_server_registry. (https://technet.microsoft.com/en-us/library/hh204561(v=sql.105).aspx )
If you look at all values returned by the dmv, you will see something like this:
In my local instance there are 191 rows returned so this can get to be a little difficult to read through. I wanted to just report on certain things so I have narrowed what is returned. Most of the time I am not interested in the actual registry key, so here is what I did:
— Startup Parameters
SELECT value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Which returns this as the results:
Notice SQLArg3 which shows that I have added the T1118 trace flag to the startup parameters. Of course I can get some of the same info by opening up the configuration manager and checking the Startup Parameters tab of the SQL Server service but I wanted my results sent to me periodically in a report so that I can know if someone had added or changed anything. I’ll show my report later.
I also have found that it is good to know what protocols are enabled and what port SQL is listening on. Since I have a lot of servers to manage, with some on the default port and others not, I thought that it would be good to report on this. My clients also have different protocols enabled for different servers. So, since nothing is standardized, it’s good that this info be put into a report so that I can easily refer to it should I ever need it.
— List protocols enabled
SELECT sr.value_data AS EnabledProtocol
FROM sys.dm_server_registry sr
WHERE sr.registry_key IN (SELECT k.registry_key
FROM sys.dm_server_registry k
WHERE k.value_name = ‘Enabled’ AND k.value_data = 1)
AND sr.value_name = ‘DisplayName’
— List TCP port
SELECT value_name AS PortName, value_data AS PortValue
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%SuperSocketNetLib\Tcp\IPAll’
AND value_name NOT LIKE N’DisplayName’
And, here is what my report looks like. I like to make small HTML formatted reports that I know I can generate on pretty much any server.
With a little bit of research and creativity, you can come up with ways to get at information in SQL easily.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!