Introduction:
Deprecated Features are SQL Server Database Engine features will not be supported in the next version of SQL Server or a future version of SQL Server. Deprecated features should not be used in new applications. The three main categories are: Breaking Change, Behavior Change or Warning.
Methods:
1. Data Migration Assistant:
Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server.
Microsoft® Data Migration Assistant v3.4
How to run from command line:
Assess databases at scale in an un-attended mode using dmacmd.exe utility.
With DMA v2.1 and above when you install DMA, it will also install dmacmd.exe in %ProgramFiles%\Microsoft Data Migration Assistant\. Use dmacmd.exe to assess your databases in an unattended mode. This is especially useful when assessing several databases or huge databases. Output the result to JSON or CSV file.
• Single-database assessment or Multiple-database assessment
2. Extended Events:
The Deprecation Final Support event class occurs when you use a feature that will be removed from the next major release of SQL Server. For greatest longevity of your applications, do not use features that cause the Deprecation Final Support event class or the Deprecation Announcement event class. Modify applications that use final deprecation features as soon as possible.
Deprecation Final Support Event Class
The Deprecation Announcement event class occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release. For greatest longevity of your applications, avoid using features that cause the Deprecation Announcement event class or the Deprecation Final Support event class.
Deprecation Announcement Event Class
Deprecated Database Engine Features in SQL Server 2016
T-SQL:
Create Extended Events Session:
CREATE EVENT SESSION EEDeprecatedFeatures ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(
SET filename=N'C:\temp\EEDeprecatedFeatures.xel',
METADATAFILE = N'C:\temp\EEDeprecatedFeatures.xem',
max_file_size=(1024),
max_rollover_files=(10)
)
WITH (STARTUP_STATE = ON)
GO
Start Extended Events Session:
ALTER EVENT SESSION EEDeprecatedFeatures
ON SERVER
STATE = start;
GO
Stop Extended Events Session:
ALTER EVENT SESSION EEDeprecatedFeatures
ON SERVER
STATE = stop;
GO
Query Extended Events Data:
Declare @Debug bit = 0
If @Debug = 1 Begin
SELECT [Files] = CONVERT(XML, file_name), file_offset, *
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EEDeprecatedFeatures*.xel',
'C:\temp\EEDeprecatedFeatures*.xem',
NULL, NULL
);
SELECT [Event Data] = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EEDeprecatedFeatures*.xel',
'C:\temp\EEDeprecatedFeatures*.xem',
NULL, NULL
);
End
;WITH ee_event_data AS
(
SELECT data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EEDeprecatedFeatures*.xel',
'C:\temp\EEDeprecatedFeatures*.xem',
NULL, NULL
)
),
cte AS
(
SELECT
[HostName] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
[ClientName] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
[DatabaseName] = data.value('(event/action[@name="database_name"]/value)[1]','nvarchar(4000)'),
[LoginName] = data.value('(event/action[@name="nt_username"]/value)[1]','nvarchar(4000)'),
[error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
[message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)'),
[Timestamp] = data.value('(event/@timestamp)[1]','datetime2'),
[SQLText] = data.value('(event/action[@name="sql_text"]/value)[1]','nvarchar(4000)')
FROM ee_event_data
)
SELECT [Timestamp],[HostName],[ClientName],UPPER([DatabaseName]) AS 'DatabaseName',[LoginName],[Message],[SQLText]
FROM cte
ORDER BY [Timestamp] DESC;
|
|
|
|
|
|