Upgrading from SQL Server 2012 to SQL Server 2017 - Any Tips or Gotchas

  • HookSQLDBA7

    SSChampion

    Points: 10552

    We are running SQL Server 2012 SP4 Standard Edition on Windows Server 2012 R2 Standard Operating System. We would like to do an in-place upgrade to SQL Server 2017 in a non-production environment using the Developer Edition. Are there any tips, pitfalls, or gotchas that we should be aware of. Please share anything that may be helpful.

    Thanks in advance.

  • Jo Pattyn

    SSC-Dedicated

    Points: 31401

    Is it purely the engine or also other ms components like SSAS, SSIS, ...?

    The major change is the new cardinality estimator introduced in 2014 which may effect the performance on some queries

  • HookSQLDBA7

    SSChampion

    Points: 10552

    The upgrade involves the DB Engine, SSIS, SSAS, and SSRS. Microsoft informed me to stop SSRS. I guess I will leave the other services running. With older SQL Server versions, I have had issues with running processes like fdhost.exe and fdlauncher.exe (both are for the MSSQLFDLauncher Full Text Service). I am going to leave them running and see if the 2017 upgrade complains.

    Thank you.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    My advice is to not do an in-place upgrade.  The benefits of an in-place upgrade are that you don't need to change any connection strings in your applications and it uses less disk space.  The downside is a rollback involves a lot more steps if something goes wrong and it is a lot harder to do a side-by-side comparison for performance.

    My preference, where possible, is to do a migration upgrade.  Involves more disk and application level changes, but makes rollback if something goes wrong very trivial.

    Either way, the upgrade should go fairly smoothly.  Might not hurt to do some analysis on the databases prior to upgrading to ensure you have no deprecated features in play or unexpected behavior changes (database migration assistant can do it for you but is a bit of a resource hog and can crash randomly).

    My biggest tips are ones you probably already know.  Once you are done the upgrade, don't forget to change the compatibility levels and update statistics and take a backup.  Upgrading SSIS, SSAS and SSRS should be mostly painless, but with SSRS I'd make sure your encryption key backups are up to date just to be on the safe side.  Don't want to upgrade and find out all of your reports are busted.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • lmarkum

    SSCertifiable

    Points: 7955

    My advice or tip would be to run the Database Migration assistant against your 2012 instance prior to the upgrade to SQL Server 2017.  It will examine the databases and code in them. The tool then provides a report of potential problems that will need to be overcome.  It breaks those issues down into a number of categories.  some you will have to fix and others are just warnings. We did this last year prior to migrating a SQL Server 2008 and two 2008 R2 servers to AlwaysOn AGs on SQL Server 2017.  The tool was very helpful.

    https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply