July 2, 2020 at 1:34 pm
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.
July 2, 2020 at 2:11 pm
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
July 2, 2020 at 2:46 pm
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.
July 7, 2020 at 3:00 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 8, 2020 at 12:00 pm
This was removed by the editor as SPAM
July 8, 2020 at 12:01 pm
This was removed by the editor as SPAM
July 18, 2020 at 11:27 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply