Enabling CLR Integration on SQL Server 2008-r2

Question

Looking for Enabling CLR Integration I found this document: http://msdn.microsoft.com/en-us/library/ms131048.aspx that said to use the following code for setting to 1 the "crl enabled" variable.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

I want know If a reboot of SQL Server is required? Or, more generaly, what are the steps to follow in order to Enable CRL Integration?

Answer 1

If you use with override option, then restart is not required.

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE WITH OVERRIDE
GO

Answer 2

The accepted answer is incorrect. The WITH OVERRIDE option of RECONFIGURE has absolutely nothing to do with whether or not a restart of SQL Server is required. The MSDN documentation for RECONFIGURE states that WITH OVERRIDE:

Disables the configuration value checking (for values that are not valid or for nonrecommended values)...

The fact is, no restart of the SQL Server service is required when enabling, or disabling, the "CLR Integration" option in sp_configure. A simple test (run on SQL Server 2008 R2, but works the same across all versions that support SQLCLR) proves this:

EXEC sp_configure 'clr enabled'; -- show current value
EXEC sp_configure 'clr enabled', 0; RECONFIGURE;
EXEC sp_configure 'clr enabled'; -- show current value
GO
EXEC sp_configure 'clr enabled'; -- show current value
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
EXEC sp_configure 'clr enabled'; -- show current value
GO

Results:

Pay attention to the run_value field. It starts out as "1" since "CLR Integration" is already enabled on my system. But it switches with only calling RECONFIGURE.

name          minimum   maximum   config_value   run_value
clr enabled   0         1         1              1

clr enabled   0         1         0              0

clr enabled   0         1         0              0

clr enabled   0         1         1              1

Additionally, it should be stated with regards to the initial code shown in the Question, the statement for

sp_configure 'show advanced options', 1;

is unnecessary since clr enabled is not an advanced option.

To prove the point about clr enabled not being an advanced option, and even showing another way to prove that this option does not require a reboot, just execute the following simple query:

SELECT [name], [value], [value_in_use], [is_dynamic], [is_advanced]
FROM   sys.configurations
WHERE  [configuration_id] = 1562;
/*
name           value    value_in_use    is_dynamic    is_advanced
clr enabled    1        1               1             0
*/

As you can see in the result set shown above, is_advanced is 0, meaning "not an advanced option (yes, the official Microsoft documentation is currently incorrect; I will update it when I have time). Also, is_dynamic is 1, meaning that simply executing RECONFIGURE will enable the option immediately, not requiring a restart of the instance.

To summarize: The sum total of all steps required to enable "CLR Integration", and without needing to restart the SQL Server service, are as follows:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

That's it. ^**

---

^** WOW64 servers will require a restart of the server in order for this option to take effect. ( clr enabled Server Configuration Option )