SQL Compatibility Level

We upgraded from Epicor 9 running SQL 2008 to Epicor 10 running SQL 2012. Inside of SSMS under the database properties, we noticed for ‘Compatibility Level’ ours is set to ‘SQL Server 2008’ can this be changed to ‘SQL 2012’ without causing any harm to the database?

Thanks for any input.

Every environment is different so only you can know for sure. Always have a backup, but normally yes you can without causing harm to Epicor.

I don’t think we have made any dependencies on anything past 2008 but have been considering - especially with the new rules in 2016 around all the features turned on by default. Backup, test, measure…

im running 10.1.500.17 on 2012 r2 SQL 2016 live, i know 2014 works as well

Is there any risk/reward in changing the compatibility level? Ours is set for SQL 2008, and we’re upgrading to Kinetic. Should I just leave this alone? Is our performance suffering because of this?

I would highly recommend you change this to something more modern there is a LOT of stuff you are missing out on. I believe (look at the docs) but the latest “verified” is SQL 2019

2 Likes

Can you provide some quick examples? I’m mostly concerned with just Epicor running. That being said, we have a product configurator that was written around 2011 that I don’t want to distrupt.

If its just bonus features for developers, im not too concerned.

What type of risks are there?
-Kyle

I can tell you that our Kinetic database conversion failed until I raised the compatibility level. Epicor uses a T-SQL command that didn’t exist in 2012. We choose 150 (SQL Server 2019) but you could set it to the lowest version approved.

2 Likes

I noticed that too while testing. Might be good enough reason to push forward.

1 Like

As long as you are choosing a compatibility level that is approved for the ERP version, I wouldn’t be too worried about something like a Configurator breaking.

In higher compatibility levels there are SQL features that have been deprecated / discontinued from the old versions, so maybe if you have a BAQ with some very fringe/obscure syntax in something like a calculated field, then I suppose that could break. I haven’t heard of that happening to anyone before but I guess it’s possible.

1 Like

There is close to nothing on the epicare site for trouble tickets on this so i’m assuming most have not have issue with it as well. I put in a ticket to try and get a bit more info.

cough FOR XML vs. STRING_AGG cough

Technically I wasn’t out of compliance when we got bit with that, but it was close.

And I think TRIM() is what @Mark_Wonsil is probably referring to, new for 2017.

2 Likes

I believe it was STRING_SPLIT, which made it’s first appearance in 2016. I do remember TRIM() as well.

Good to know, that’s a great example!