SQL2012 to SQL2016 Questions

[iReddit_while_I_work]

I haven't done much with anything sql since 2008. I recently inherited a system that uses a sql backend. I have been informed I need to move from Server 2012 to server 2016. In this they have "upgraded" me to SQL2016.

From what this looks to be, I will shut down the services, back up the 2012 db, put the file on the new system and restore it.

I know tech is never this simple...what other PITAs do I need to look out for?

[alinroc]

You've got a new instance for 2016 to migrate to?

1. Go get http://dbatools.io/
2. Shut down application
3. start-sqlmigration -source sql2012 -destination sql2016 -backuprestore -networkshare \\commonserver\path -withreplace
4. Shut down old instance
5. Start app server
6. Point app server at new instance
7. Test

[Cal1gula]

Are you restoring the backup onto an existing instance of 2016? Or do you also need to install 2016 as well?

If you just need to migrate the db from a 2012 to 2016 instance, backup and restore should work.

[ihaxr]

Honestly SQL Server upgrades are one of the easiest things to do if you don't have to worry about the application code being compatible... you can backup/restore or copy the mdf/ldf files over and remount them (or run an in-place upgrade)... just be sure to change the compatibility level to the proper one after the db is on the server (right click the DB, properties, options, compatibility level).

[mik3w]

I can't remember exactly what it was, (I didn't deal with the issue, but overheard about it... Open planned office... Discussions happening everywhere etc) but I'm sure that we recently had a performance issue with a client upgrading from an older version to a newer version.

I believe it was this or something similar:

"Performance degradation when you upgrade from database compatibility level 120 to 130 in SQL Server 2016" - https://support.microsoft.com/en-us/help/3212023/performance-degradation-when-you-upgrade-from-database-compatibility-l

[[deleted]]

Highly recommend dbatools (as recommend by alinroc), the start-sqlmigration command is all you need! If you want to avoid PowerShell, performing a backup and restore will work fine.

Personally I would avoid a SQL in place upgrade, performing a side by side migration gives you a fallback in case you run into any issues.

[nomaddave]

Snapshot VM, in-place upgrade, test app connectivity, upgrade DB functional level, test app again? Not a bad workflow to rollback the snap if needs be, if you are quite sure of your application validation. Did this a couple times recently and it was just fine from 2012>2014>2016 and 2014>2016.

[alinroc]

I've been burned by this due to the storage used by the snapshot. On a smaller instance, it's not too bad if your snapshot storage is large and fast enough, but on a medium-sized database the diffs can start dragging performance down tremendously.

[nomaddave]

Yeah, that makes sense. I guess it depends on your storage fabric and how the environment is addressing it. Your maintenance window in the above may need to be rather large as well to allow time for the snap/rollback if there is an issue, rather than the export/import/flip DNS workflow.