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?

Anissa

Hi there. I was wondering if you could please change which answer is accepted. Not because I provided the other answer, but because the currently accepted answer is incredibly wrong, misleading, spreading misinformation, and is easily proven to be wrong (as shown in my answer). Thanks :) P.S. the official documentation is also incorrect in that this is not an advanced setting. That is also easy to prove and I will update my answer in a moment to show how. – Wheaten

Answer 1

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 )

Wheaten

Thank you for the detailed answer. Definitely deserves an upvote! – Ambidexterity

Answer 2

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

Persia

This is absolutely incorrect. Not only in terms of what WITH OVERRIDE does, but also that anything is even needed in the first place. RECONFIGURE by itself already updates the enabled/disabled state with no need for restart. – Wheaten