Change SQL Server settings without restarting services

Question

I can change SQL Server settings using Management Studio or programmatically as described e.g. here.

I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any trick to do so??

Answer 1

If you want to change the Authentication Mode, you can do this by running the following SQL

USE master
DECLARE @INSTANCEID VARCHAR(30), @STRVERSION VARCHAR(30), @SQLVERSION VARCHAR(30), @CMD VARCHAR(2000)
SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))

IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10 
    SET @STRVERSION = 'MSSQL10'
ELSE
 IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
  SET @STRVERSION = 'MSSQL'

IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
 SET @INSTANCEID = 1
ELSE
 IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
  SET @INSTANCEID = 'MSSQLSERVER'

SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth.

EXEC(@CMD)
--PRINT @CMD

You may also have to ensure that the sa login is enabled.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

You will however still have to restart the SQL Server service.

Answer 2

It depends on what settings you changed based on http://msdn.microsoft.com/en-GB/library/ms189631.aspx. Some require a restart (RR), other can be done via RECONFIGURE

There is no trick or workaround. At all.

Edit, now we know it is Authentication mode...

This is automatically deals with the instance name

 EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    1; -- or 2 for mixed

Restart is still needed