compatibility between sql versions and applications

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
I have 2 or 3 applications that are only officially compatible up to sql server 2014.  I would like to move them to a sql server 2016 sql instance.  I would test of course.  Is there a way to make a 2016 server behave exactly like a 2014 sql server at the database level.  ie... some databases on this instance would be at 2016, some at 2014, and some at 2012 but the instance itself is officially running 2016
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer
Commented:
Set compatibility level to 120 for SQL Server 2014.
David ToddSenior Database Administrator
Commented:
Hi,

The compatibility level is a per-database setting.

BUT! It only affects SOME query behaviour. You will still need to test and test extensively to be sure that this works for you. Fortunately this change appears to be completely reversible with no downsides, and only requires a restart of the client connection to take effect. It's almost completely unnoticed by users.

In SSMS in it is the options tab of the properties screen.

HTH
  David

Kind regards
  David
Doug BishopDatabase Developer
Commented:
See this page for more information.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
follow up question.
so lets say i move forward with moving a 2012 database to my sql server 2016 database and after using it for a week, it turns out it was a bad decision.  Now i have a weeks worth of data added while it was on the 2016 server.  am i allowed to backup the database at the 2012 level and then the 2012 sql server would be able to restore it?
David ToddSenior Database Administrator
Commented:
Hi,

I think this is a no from me.

Pretty much Backups can only move forward and not backwards.

Just tested by creating a new blank database on 2016 and it gave an error restoring on 2012.

Regards
  David

Author

Commented:
Thanks Gentlemen
i got what i was looking for

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial