SA0206 : The sp_configure store procedure executed with a deprecated option

Introduction

Using deprecated configuration options in SQL Server may lead to issues as these options will be removed in future versions, affecting system behavior and performance.

Description

Deprecated sp_configure options in SQL Server are a significant concern because they signal older feature usage that Microsoft no longer supports. Using such options can lead to issues in future upgrades or feature removals.

For example:

-- Example of problematic T-SQL code
EXEC sp_configure 'priority boost', 1;
GO

In this example, the 'priority boost' option is deprecated. Usage of deprecated options like this one can create compatibility issues in future SQL Server releases, as they are maintained only for backward compatibility.

Potential removal of the option in future SQL Server versions, which can break existing scripts and configurations.
Deprecated options are not receiving improvements or optimizations, making them less efficient or secure than newer alternatives.

How to fix

To mitigate the risks associated with deprecated configuration options in SQL Server, follow these actionable steps to identify and replace them with supported alternatives.

Follow these steps to address the issue:

1.Identify any deprecated configuration options by reviewing your T-SQL scripts and stored procedures for calls to sp_configure with outdated settings.

2.Consult the SQL Server documentation to determine the current equivalent or recommended settings for the deprecated options you identified. Refer to the SQL Server Documentation on sp_configure for guidance.

3.Update your scripts by replacing deprecated options with their supported equivalents. Ensure that you test your changes in a development environment before applying them to production.

4.Verify that no other part of your application relies on the deprecated configuration options, and make necessary adjustments if dependencies are found.

5.Monitor any related system behavior post-change to ensure that the new configurations meet your application’s performance and security requirements.

For example:

-- Example of corrected query replacing deprecated option
EXEC sp_configure 'max degree of parallelism', 0;
GO

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

allow updates Server Configuration Option

open objects Server Configuration Option

locks Server Configuration Option

priority boost Server Configuration Option

remote proc trans Server Configuration Option

c2 audit mode Server Configuration Option

default trace enabled Server Configuration Option

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

sys.sp_configure @configname ='allow updates'

USE AdventureWorks2012;

DECLARE  @result INT
EXEC @result = dbo.uspGetWhereUsedProductID  819, '20050225';

EXECUTE  dbo.uspGetWhereUsedProductID  819, '20050225';
EXEC sp_configure 'show advanced options', 1;
EXECUTE sp_configure @configname ='allow updates'
EXECUTE   sp_configure 'locks', 20000;
EXEC sp_configure 'open objects' , 20000

EXEC sp_configure 'set working set size', 123

EXEC sp_configure @configname ='priority boost', 1 ;
EXEC sp_configure 'remote proc trans', 1
EXEC sp_configure @configname = 'c2 audit mode',1

EXEC sys.sp_configure @configname = 'default trace enabled', 1

EXEC sp_configure @configname = 'some other option'

Analysis Results

	Message	Line	Column
1	SA0206 : This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect.	1	30
2	SA0206 : This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect.	10	34
3	SA0206 : The configudation option is now automatically configured. Setting has no effect.	11	23
4	SA0206 : This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server.	12	18
5	SA0206 : This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server.	14	18
6	SA0206 : The priority boost option is deprecated. Use the Windows start /high … program.exe option instead.	16	31
7	SA0206 : The remote proc trans option is deprecated.	17	18
8	SA0206 : The c2 audit mode option is deprecated. Use common criteria compliance enabled option instead.	18	32
9	SA0206 : The default trace enabled option is deprecated and may be removed in a future version of Microsoft SQL Server. Use Extended Events instead.	20	36

Analysis Rules