November 8, 2017 at 12:34 pm
I have a few question about migrating databases from 2008 RTto 2016.
The databases were originally created in 2005 and then migrated to 2008 R2 a few years ago. I saw online that I could take the databases and restore them from a back-up in 2016.
According to Microsoft, “When a SQL Server 2005 database is upgraded to SQL Server 2016, the database compatibility level will be changed from 90 to 100. (In SQL Server 2016, valid values for the database compatibility level are 100, 110, 120, and 130.)”
So, my first question is, would it still run ok as a (100)?
Second, I read that if I edit the properties/Options I could change it to a 2016 database. Would this work?
Next, I downloaded and ran the Data Migration assistant and connected it to one of the databases and checked the target for compatibility and new features recommendations. Most of the compatibility issues are the same for each database.
There are 130 compatibility flags. There are 0 breaking changes. I’m not quite sure exactly what the means. 27 Behavior changes which are primarily unqualified joins. 2 depreciated features – Ntext and Image fields that need tobe changed.
So, my third question there is, if I just restored a copy of the db to 2016 with these 27 behavioral changes and 2 depreciated features as they are now and sql server changes the compatibility level to 100, should the db work as is? The tables that are impacted are primarily asp.net_membership tables and procedures which were created by visual studio back in 2005.
See attached report that Exported out of Data MigrationAssistant.
I thank you in advance for any advice.
November 8, 2017 at 4:10 pm
Short answer, it should just work, but test it.
While a bunch of stuff is deprecated, there's very little that is outright gone. While you absolutely should move off of ntext and image, they're still supported within the product. The real issue actually isn't within the database, it's external. All the connectivity apps & drivers have been radically updated since 2005. I think you're more likely to see issues there.
As to the compatibility level, yeah, leave it at 10 for the time being. However, long term you absolutely do not want to do that. It keeps you from getting tons and tons of the new functionality within SQL Server 2016. At that point, why upgrade at all. However, don't switch to 13 right away. Leave it where it is, but enable Query Store. Let it run for a while a few days, a few weeks, it's your system, figure out what's right. Then, change the compatibility mode to 13.
The reason we're doing this is two-fold. First, and most importantly, there's a new cardinality estimation engine introduced in 2014 that will, in some cases, usually extreme edge cases, cause poor performance. Second, starting with SQL Server 2016, all optimizer enhancements are enabled out of the gate. The old way was to never enable them. Now that Microsoft has a way to deal with regressions, Query Store, they enable them by default. Again, in some cases, this causes performance to degrade. With Query Store, you can easily and quickly identify any queries that have regressed and just tell them to use the old plan (which you'll have because you enabled Query Store before you switched compatibility mode) through Plan Forcing. It's the greatest thing for upgrades.
Do very thorough tests.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 8, 2017 at 7:40 pm
Thank you sir! This is a huge help! I'll follow the path you laid out here. Again, I appreciate it!
November 9, 2017 at 12:14 am
As a starter I always use the target versions Data Migration Assistant (formerly known as Upgrade Advisor ).
Have a look at https://docs.microsoft.com/en-us/sql/dma/dma-overview
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 9, 2017 at 7:48 am
Yes, Johan, that's what I was using.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply