Check programmatically if SQL Server needs restarting

Question

I have to check programmatically if the SQL Server service needs restarting after series of changes in configuration.

Is there any indicator (e.g. in the system registry) that allows to obtain this information?

Answer 1

You can check for differences between value_in_use and value in sys.configurations for non dynamic changes.

Select * from sys.configurations
where value <> value_in_use and is_dynamic=0

to check for a changed logginmode

declare @tab table (name varchar(max),value varchar(max))
insert into @tab EXEC master.sys.xp_loginconfig 'login mode'
insert into @tab
SELECT 'current',CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows NT Authentication'   
WHEN 0 THEN 'Mixed'   
END 

Select
CAST(
Case when (Select value from @tab where name='login mode')<>(Select value from @tab where name='current') 
then 1 else 0 end as bit) as loginconfig_changed