My co-authors and I recently wrapped up the book SQL Server 2019 Administration Inside Out, which should be hitting the shelves in the next week or two. At the end of Chapter 1 I have a small note about deprecated and discontinued features, and this post expands on that section.
The following statements are true:
- SQL Server 2019 does not have any deprecated features, but does have discontinued features
- SQL Server 2017 does not have any discontinued features, but does have deprecated features
- SQL Server 2016 has both deprecated and discontinued features
Let’s discuss the difference between deprecated and discontinued features, and explain how this affects database administrators looking to move to SQL Server 2016 or newer. My current advice is to target SQL Server 2019 with at least Cumulative Update 2 (CU2).
Deprecated features
A deprecated feature is no longer under active development, and it may be removed from a future version. If we are building something new, we should avoid using that feature on the expectation that it will be gone in the future.
Discontinued features
A discontinued feature is one level up from deprecation, in that it is no longer available. If we are using a discontinued feature when we upgrade to a new version of SQL Server, that feature is gone and our code will break.
Breaking and behaviour changes
Both breaking changes and behaviour changes result in different or unexpected results after upgrading to a new version of SQL Server, potentially causing code to break. This may be the result of implicit conversions between data types being calculated differently, or a column on an existing dynamic management view (DMV) returning a different data type. Either way, these underlying changes can affect our code.
Database compatibility level
Since at least SQL Server 2016, Microsoft wants us to think about features as being linked to compatibility levels, as opposed to major version numbers. For instance, SQL Server 2019 (version 15.x) has a compatibility level of 150, and we make use of the new features introduced in 2019 by setting our database compatibility level to 150. This means that if a feature is discontinued in compatibility level 150, it will still be available in compatibility level 140 and below.
This is very helpful to keep in mind when reviewing the list of discontinued features. It’s possible on a SQL Server 2019 instance to run a database at compatibility level 100, which was the database engine used in SQL Server 2008 and SQL Server 2008 R2.
As a reminder, these are the currently supported versions of SQL Server (as at February 21, 2020), and their respective compatibility levels:
- SQL Server 2012 (version 11.x): compatibility level 110
- SQL Server 2014 (version 12.x): compatibility level 120
- SQL Server 2016 (version 13.x): compatibility level 130
- SQL Server 2017 (version 14.x): compatibility level 140
- SQL Server 2019 (version 15.x): compatibility level 150
How does this affect me?
Compatibility levels are our friend. We might have a SQL Server 2008 R2 server that needs to be upgraded because it is no longer supported by Microsoft. We can migrate the databases on that server to a brand new SQL Server 2019 instance, and maintain compatibility with 2008 R2 by keeping the compatibility level at 100.
Even better, we can still benefit from a number of features in SQL Server 2019 including Query Store and Accelerated Database Recovery, while still maintaining compatibility with applications that expect Transact-SQL query language and other features from SQL Server 2008. Over the lifetime of that SQL Server 2019 instance we can gradually adopt newer features in our database, and increase the compatibility level until we get to 150.
Discontinued features for supported versions of SQL Server
While the official documentation makes a concerted effort to list deprecated and discontinued features as well as breaking changes, this post is my effort to show a list of features that have been discontinued for each supported compatibility level. The list of deprecated features between supported versions is much longer, so these are linked to in the following list:
- SQL Server 2012: breaking changes, and behaviour changes.
- SQL Server 2014: deprecated features, breaking changes, and behaviour changes.
- SQL Server 2016: deprecated features, and breaking changes.
- SQL Server 2017: deprecated features.
Note that this list is generated from Microsoft Docs. I do not claim ownership or copyright to this content.
| Level | Category | Discontinued feature | Replacement |
|---|---|---|---|
| All | Compatibility level | < 100 compatibility level | Databases must be set to at least compatibility level 100 |
| All | Memory Management | 32-bit Address Windowing Extensions (AWE) and Hot Add memory support | Use a 64-bit operating system |
| All | Setup | 32-bit installation | Use a 64-bit SQL Server installation |
| 130 | ActiveX | ActiveX subsystem | Use command line or PowerShell scripts instead |
| 130 | Security | Secure Sockets Layer (SSL) encryption | Use Transport Layer Security (TLS) instead |
| 130 | Startup parameters | Startup parameter -g |
None |
| 120 | Startup parameters | Startup parameter -h |
None |
| 110 | Backup and Restore | BACKUP { DATABASE | LOG } WITH PASSWORD |
None |
| 110 | Backup and Restore | BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD |
None |
| 110 | Backup and Restore | RESTORE { DATABASE | LOG } WITH DBO_ONLY |
RESTORE { DATABASE | LOG } WITH RESTRICTED_USER |
| 110 | Configuration options | sp_configure 'user instance timeout' and 'user instances enabled' |
Use the Local Database feature |
| 110 | Connection protocols | Support for the VIA protocol | Use TCP/IP instead |
| 110 | Database objects | WITH APPEND clause on triggers |
Re-create the whole trigger |
| 110 | Database options | sp_dboption |
ALTER DATABASE |
| 110 | Extended Events | databases_data_file_size_changed |
database_file_size_change |
| 110 | Extended Events | databases_log_file_size_changed |
database_file_size_change |
| 110 | Extended Events | eventdatabases_log_file_used_size_changed |
database_file_size_change |
| 110 | Extended Events | locks_lock_timeouts_greater_than_0 |
lock_timeout_greater_than_0 |
| 110 | Extended Events | locks_lock_timeouts |
lock_timeout |
| 110 | SQL Mail | Use Database Mail | |
| 110 | Metadata | DATABASEPROPERTY |
DATABASEPROPERTYEX |
| 110 | Programmability | SQL Server Distributed Management Objects (SQL-DMO) | SQL Server Management Objects (SMO) |
| 110 | Query hints | FASTFIRSTROW hint |
OPTION (FAST n) |
| 110 | Remote servers | Create new remote servers by using sp_addserver. Remote servers preserved during upgrade or created by replication can be used. |
Replace remote servers by using linked servers |
| 110 | Security | sp_dropalias |
Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases. |
| 110 | Security | Version parameter of PWDCOMPARE from a login earlier than SQL Server 2000 |
None |
| 110 | Service Broker / SMO | BrokerPriority no longer implements IObjectPermission interface |
None |
| 110 | SET options | SET DISABLE_DEF_CNST_CHK |
None |
| 110 | System tables | sys.database_principal_aliases |
Use roles instead of aliases |
| 110 | Transact-SQL | RAISERROR in the format RAISERROR integer 'string' |
Rewrite the statement using the current RAISERROR() syntax |
| 110 | Transact-SQL | COMPUTE / COMPUTE BY |
Use ROLLUP |
| 110 | Transact-SQL | Use of *= and =* |
Use ANSI join syntax |
Share your thoughts in the comments below.
Photo by Ajeet Mestry on Unsplash.
Do you thin I should fly from 110 to 150 or is it better to upgrade 120 -> 130 -> 140 -> 150 ?
I’m quit scared as I have old applications from the ’90 hitting that database 🙁
Francesco, I would follow the upgrade recommendations from Microsoft. The idea is that when you install SQL Server 2016 or higher, you turn on Query Store and run a workload to capture the plans on the old compatibility level, and then do a compatibility level update to 150. Query Store will be able to tell you what plans aren’t good, and you can force the old plans until you rewrite those bad queries. Alternatively you can stay on compatibility level 110 as long as you need for individual databases. You’re not obligated to upgrade that. That’s what the compatibility level guarantee is that Microsoft is providing.
Does features apply to previous compatibility level?
I mean, I’m on SQL Server 2019 but my databases are still on Compatibility Level 110 (SQL Server 2012). I want to rebuild a fragmented index with the options RESUMABLE = ON and OPTIMIZE_FOR_SEQUENTIAL_KEY = ON. Will these new feature apply even if the Compatibility Level is 110?
Some features apply to the instance level, while some apply only to databases in the correct compatibility level. Optimize for sequential key is a 150-level feature.
Hi,
good day!
i have a sql server 2005 which I want to upgrade to SQl 2019, a huge jump and bypassing many updates..how can I know list of deprecated/discontinued functionality between two, So I can have an idea about what would break on upgrade. before I start it.
Thanks,
Hi Himanshu
The good news is that you can take a backup from your 2005 database server, and restore the backup file to a new 2019 server, and it will “just work”. When it restores, the database will automatically change its compatibility level (the internal compatibility version of the database) to 100, which means it will enable the same features as SQL Server 2008 (10.0). You can then stay on that compatibility level for as long as you need. Maybe you will decide it’s good enough for the time being.
Then over the next while, you can test with upgrading the compatibility level from 100 to 110, which is the same as SQL Server 2012 (version 11.0), and see if your application still works. Chances are you’ll only start running into issues when you go to 120, which is the same as SQL Server 2014 (version 12.0). This is because Microsoft introduced a new cardinality estimator at that version.
The good news is, if something breaks at a higher compatibility level, you can always drop it down again. Microsoft guarantees compatibility with that level setting, on SQL Server 2016 and later, meaning that you can keep running older application software, and SQL Server will act like it’s at that older version.
I hope that helps!
Comments are closed.