none
SQL Server Migration from 2014 SP3 to SQL Server 2019 RRS feed

  • Question

  • Can I get some suggestion or help towards server migration from 2014 SP3 to 2019.

    Currently we have SQL Server 2014 SP3 PRODUCTION . As per companies infra policies we need to bring the server up to standard as per latest version which is SQL Server 2019. I am planning for a side by side installation of the set up.

    * Windows Server 2016  +  SQL Server 2019 latest version + AOAGs . What are the series of steps to be done ?

    I am reading : https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15

    Please guide me on suggestion for data migration from SQL Server 2014 SP3 --> SQL Server 2019 latest . What can be the possible cases of issues I might come across . Any documentation if anyone already upgraded to 2019 and date is production live . Please do suggest if anything to be considered as pre-requisites mandatory and various other sections. Many thanks .


    Best Regards,SQLBoy


    • Edited by Kumar GK Sunday, January 5, 2020 5:24 AM
    Sunday, January 5, 2020 5:23 AM

Answers

All replies

    • Marked as answer by Kumar GK Monday, January 6, 2020 5:23 AM
    Sunday, January 5, 2020 5:37 AM
    Moderator
  • Can I get some suggestion or help towards server migration from 2014 SP3 to 2019.

    Currently we have SQL Server 2014 SP3 PRODUCTION . As per companies infra policies we need to bring the server up to standard as per latest version which is SQL Server 2019. I am planning for a side by side installation of the set up.

    * Windows Server 2016  +  SQL Server 2019 latest version + AOAGs . What are the series of steps to be done ?

    I am reading : https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15

    Please guide me on suggestion for data migration from SQL Server 2014 SP3 --> SQL Server 2019 latest . What can be the possible cases of issues I might come across . Any documentation if anyone already upgraded to 2019 and date is production live . Please do suggest if anything to be considered as pre-requisites mandatory and various other sections. Many thanks .


    Best Regards,SQLBoy

    Good day Kumar,

    I will start from the obvious and must to say, and move to some tips and my golden rules. But as said I must start with: I will NOT advice you what to do and you should never get direct advice in the forum regarding such actions in the level of architecture - in order to advice what to do we have to FULLY FAMILIAR with the system, the business, and all around and not just focus on some points in the forum.

    With that being said here my 2 cents

    > Like always! BACKUP BACKUP BACKUP!

    (a) Before you start always test the behavior in developing environment

    (b) Always backup everything AND TEST THE BACKUP BY RESTORING IT! most people make the mistake of backup without checking that they actually succeed to restore the system.

    > Tasks like this are not done every day and upgrade is not good as clean page (assuming it is done in the right way). I usually do not recommend to upgrade and more against side-by-side of two instances on the same machine in production only for the sake of upgrade!

    (a) The hardware should not be the parameter in most cases, and building side-by-side another machine for the new product is usually best option!

    (b) You can use this opportunity to clean the system and make changes in design which you always complained that "you don't have time" or that "it is working like this since you got the system from someone else" or any other excuse which usually people in-charge present. THIS IS A HUGE OPPORTUNITY! use it right.

    >  What can be the possible cases of issues I might come across

    In most case you should see improvement in performance BUT not in all cases. There might be queries which will get different Execution Plan which in your specific case run poor.

    There are new feature which related to the new database compatibility level. You will need to choose (after testing) if you move the databases to use the new compatibility level or use the old one. Simple backup/restore keep the old compatibility.

    There are features which are not implemented by default like Memory-Optimized TempDB Metadata for example. You should make a deep tests and choose which feature to implement and how you configure your new instance.

    >> The first link which Uri gave is awesome starting point!


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, January 5, 2020 10:50 AM
    Moderator
  • Hello Friend,

    Usually you need to be aware of the connection of applications to this new instance.
    Is there approval?

    Passing these questions if you are installing the new Sql Server on the same machine, you will have a new instance that means it will have a different name than the current one. So be aware of application connections.

    Back up everything and if possible test to validate the migration time and make sure the settings are correct.

    Make a backup of logins and users.
    Back up the jobs (validate the scripts).

    These are some of the settings you need to validate before doing this migration.

    This migration is not complicated and you will succeed.

    Good luck

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Sunday, January 5, 2020 9:38 PM
  • Sure Uri , thank you very much .

    Best Regards,SQLBoy

    Monday, January 6, 2020 5:08 AM
  • Thank you Ronen for the inputs . I will read more and more on this . Thanks again.

    Best Regards,SQLBoy

    Monday, January 6, 2020 5:08 AM
  • Hi Kumar,

    AOAG exists in your sql server structure, you can consider rolling upgrade, the idea is that you can upgrade a secondary node, failover, and continue upgrading all nodes in this manner until you upgrade the primary node, and then fail back if needed.

    Of course, Backup is important.

    Hope this could help you.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 5:35 AM
  • You are most welcome :-) 

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, January 6, 2020 6:42 AM
    Moderator