November 28, 2019 at 5:15 pm
Hi Everyone,
I'd like to canvas some opinion around the migration of a set of databases from 2008 R2 to 2017. I'm not sure of the answer to the following questions myself but know enough to raise them.
The databases concerned have been migrated from the SQL Server 2008 R2 server to a different server, which is running SQL Server 2017 RTM. When I look at the restored databases in 2017, all looks good. No issues. However, the properties of the databases show that they are running in 2008 compatibility mode.
My questions are:
1) If the databases are migrated correctly between 2008 R2 and 2017, should the compatibility mode show as 2017, even if the database originally came from a 2008 R2 database?
2) Microsoft states that 2008 compatibility mode is deprecated from SQL Server 2016 onwards. (https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver15). I would be concerned if we have to move to 2019 in the future and the same issue occurs, meaning we are just lifting-and-shifting a 2008 database.
I was able to replicate the setup myself by copying a 2008 R2 .bak file over to the new server and restoring it into 2017 using SQL Server Data Tools. If a database migration to a newer version of SQL Server takes place properly, should the compatibility mode always show as 2017 or is the 2008 mode still going to appear as this was the database's original version? Is there any difference between that and just re-attaching a database?
As always, thanks in advance to the community for any help.
T.
November 28, 2019 at 6:58 pm
compatibility mode will stay as per original db - this is to allow companies to migrate to a new SQL Version but still keep their db's on the original "version".
By doing this certain things that could break on newer versions still work and give the devs more time to "fix" their code/issues.
My advise is to test (in DEV/TST!!!!) changing the compatibility mode to newer one and test both code and performance (performance may get a hit in some cases due to Query Engine changes - workarounds exist for this issue but only required if some queries misbehave)
Also do run the Database Migration Advisor to see if there are any issues https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
December 10, 2019 at 9:36 am
Hi Frederico,
I'm sorry for the late reply but thank you very much for your comments. I looked into things a bit further and discussed the issue. It's a stability issue for an older version of software so we're happy it's legitimate.
Thanks again,
Tom.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply