August 8, 2018 at 8:08 am
Hi,
Our production database set with compatibility 100 in sqlserver2016.
will there be any issues if we change the compatibility to 130 in current production?
Thank you !!
August 8, 2018 at 8:29 am
adisql - Wednesday, August 8, 2018 8:08 AMHi,Our production database set with compatibility 100 in sqlserver2016.
will there be any issues if we change the compatibility to 130 in current production?Thank you !!
I think should be fine to do but as always test it before making the changes in production.
Thanks
August 8, 2018 at 8:48 am
Just as NorthernSoul said, test...
In our case we migrated from 2008 Enterprise to 2016 Enterprise and the QA department said we were good to go.. but when we changed compatibility levels from 100 to 130 legacy 2008 queries would take longer in 2016 so we swapped back to 100, we've been working on improving queries and adapting them to 2016.
If something happens you could switch back.. also, we called support for an issue unrelated to it the issue above, I spoke about it.. if you want to 130 and you have issues with queries there's also an option in the Options tab in the database properties which is named "Legacy Cardinality Estimation" that we could try. it basically uses the cardinality estimator of older compatibility levels and applies the queries to the best one. we haven't tried and we are looking more into it to see, the advantages of it is that you can keep using your older queries and use the features of SQL Server 2016.
Greetings.
August 8, 2018 at 9:09 am
Thank you.
August 8, 2018 at 9:12 am
adisql - Wednesday, August 8, 2018 9:09 AMThank you.
Hi,
I have one more requirement.
Planning to migrate the database from sql2008 standard to sql2016 enterprise.
But the database is set with compatibility 90 in sql2008.
is this database will restore\attach sql2016?
Thnak you !!
August 8, 2018 at 9:18 am
adisql - Wednesday, August 8, 2018 9:12 AMadisql - Wednesday, August 8, 2018 9:09 AMThank you.Hi,
I have one more requirement.
Planning to migrate the database from sql2008 standard to sql2016 enterprise.
But the database is set with compatibility 90 in sql2008.
is this database will restore\attach sql2016?Thnak you !!
See the upgrade versions of SQL Server from Microsoft Books Online to see if you meet these requirements before starting.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-2017
Coming back to you,
Yeah, we actually did that with our largest database since a backup would take days (we had a window for 12 hours). we attached it and the version level changed from 655(SQL Server 2008) to 852 (SQL Server 2016).
This means that you can't go back!
If you have the chance to actually backup and restore that would be the recommended way!!, do what i said above as last resort.
And if you can keep your SQL Server 2008 in stand-by in case you need to go back..
August 8, 2018 at 11:38 am
adisql - Wednesday, August 8, 2018 8:08 AMHi,Our production database set with compatibility 100 in sqlserver2016.
will there be any issues if we change the compatibility to 130 in current production?Thank you !!
Not meaning to be disagreeing with others but the change in the cardinality estimation may or may not affect you. It may be something you would want to to be aware of:
ALTER DATABASE (Transact-SQL) Compatibility Level
SQL query times out or console slow on certain Configuration Manager database queries
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply