September 8, 2020 at 7:52 am
Hi,
Were planning a migration from 2014 to 2019 soon, and were wondering if anyone has experienced any particular issues we should watch out for.
At the moment our databases are in a mix of compat 100 and 120, and will initially remain so after the upgrade, after which we'll be testing for a compat upgrade. The compat 100 databases had a lot of plan issues on compat 120 with the 2014 CE, so it was decided to leave them at 100 until a future upgrade.
Are there any breaking changes / behaviour changes/ subtle issues etc anyone has experienced doing this kind of migration?
Thanks for the help
September 8, 2020 at 12:03 pm
I haven't encountered issues yet.
In any case, best is to start your preparation using SQL Server Data Migration Assistant
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
September 8, 2020 at 1:43 pm
I haven't encountered issues yet.
In any case, best is to start your preparation using SQL Server Data Migration Assistant
As a bit of a sidebar, I won't use the DMA anymore. The damned thing reports way too much information to MS for my liking. Seems like MS does whatever the hell they want. Between the automatic reboots of desktops in Windows 10, DMA, etc, etc, MS products have become incredibly invasive. There should be a law against it that specifies a guaranteed "Opt Out" setting.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2020 at 1:17 pm
A day late, but testing, as always, is going to be your bestest buddy. I'm not aware of any fundamental issues in the move from 2014 to 2019. You're already over the big hurdle of the new cardinality estimation engine (assuming your databases are not running in compatibility mode). However, as with any upgrade, while there aren't huge issues, there may be tiny issues that can cause you pain. The optimizer and more has changed since 2014. Mostly, I'd argue, for the better. That's not to say you won't find an edge case.
"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
September 15, 2020 at 9:59 pm
Hi T.C.,
I haven't ran into any issue as well. You'll actually see a performance boost by migrating to SQL Server 2019. The first boost I saw were in the inserts. Our great developers have a process to import 1B+ records into a single hash table using INSERT INTO...SELECT. it usually takes 10+ hours but we cut it in half by 5+ hours.
SQL Server 2019 now allows parallel inserts (mainly raw tables without PKs and Indexes) if you specify the TABLOCK hint
INSERT INTO [ParallelInsert_Destination] WITH(TABLOCK)
SELECT * FROM [DummySource_Table]
Limitations
*** Please network with me and feel free to IM if you have any more DBA questions and concerns
https://www.linkedin.com/in/james-rossi-9094651b7/
james rossi
Senior SQL Server DBA
Senior Software Developer
Alpha One Data
Brain Box SQL Mobile DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply