SQL 2016 application on SQL 2017

Question

Hi,<o:p></o:p>

We need to run a SQL 2016 application on the organisations SQL farm which is SQL 2017 running in compatibility mode 140.
This farm hosts multiple database belonging to other clinical systems. They are all using compatibility mode 140.<o:p></o:p>

The required application for SQL 2016 requires the compatibility mode to be set to 130. <o:p></o:p>

We are being told that even though SQL 2017 can be pulled down and run in compatibility mode 130. The issue with this is that all the other clinical system databases will have to be revalidated to work on compatibility mode 130 and will involve a lot of work for Trust ICT and administrators for the other systems.<o:p></o:p>

Is this true? Will the other systems have to be downgraded from 140 to 130? If so is there a way around this?<o:p></o:p>

Many Thanks<o:p></o:p>

Answer 1

On an SQL Server instances you can have databases with different compatibility level, and these databases would normally not affect each other. I say normally, because if there are cross-database queries there can be some interesting things happening. But if the databases are for different applications, it is not likely that the run queries to the other databases.

So if your IT department it would affect other databases on the instance if you set this particular database to compat level 130, they are wrong.

However, I am aware of that in healthcare there are many regulations etc, and not all of these make sense.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

So the database using SQL Server 2016 application will be new database or any one of the databases using compatibility level 140 ?. If it is new database like Erland said you can run it easily without actually disturbing other DB's but if you are going to change DB compatibility of one of the SQL Server 2017 database then you need to check but again it would no be that much of a work. if it does not works you can change compatibility level back to 140 its just one small change from SSMS GUI or TSQL

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 3

The compatibility level is a database level setting, not a server level setting.  So as Erland said, this only affects the single database, unless you have cross database queries.

However, setting the compatibility level does not make it 100% compatible with the previous version.  The engine is still SQL 2017 and only certain functionality is changed. 

Please see:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#differences-between-compatibility-levels

Answer 4

Hi w1cka,

The database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. Refer to Differences between Compatibility Level 130 and Level 140.

As others mentioned, SQL server compatibility is a database level setting, not a server level setting. It will not effects other databases on the instance, unless you have cross database queries. We can change it using T-SQL or SSMS UI. Please refer to View or Change the Compatibility Level of a Database.

If the response helped, please click "Mark as Answer" and up vote it.

Best regards,
Cathy 

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 5

Hi w1cka，

Any update? If the replies could help you, please kindly click "Mark as Answer".By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

Best regards,
Cathy

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.