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??
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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