SQL Server 2012 End of Support Migration Plan

Today is end of support for SQL Server 2012. May it rest in peace.

Migrating a SQL Server can be a lot of work. There are so many things to think about. It’s a pain.

Live view, upgrading SQL Server

Let me offer you a template of steps to get you started on your planning.

Here’s a quick overview of my process:

• Run Database Migration Assistant against the source SQL Server to get a report of incompatibilities and breaking changes in relation to my target version and edition.

• Fix the breaking changes issues, either on my own or with the help of Dev teams.

• Plan to use a DNS alias to ensure that the business and Devs don’t have to change the connection name after the upgrade.

• Use the migration as an opportunity to consolidate or remove old, unneeded logins from the SQL Server by excluding them from the migration plan.

• Determine if we’re going to try an online migration or not. This often depends on the size of the databases involved, which affects how fast they can be restored. And of course, this decision is affected by how many databases need to be moved and what the downtime requirements are for the customer and applications.

• For an online migration, decide between log shipping or transactional replication as a means of getting a warm copy of the database to the new target.

• Occasionally I’m upgrading a system in an Always On Availability Group. In that case, getting the databases to the new target is a matter of adding the new machine to the cluster and the AG as a secondary and letting AG seeding and synchronization do the migration of the databases for me.

• Discover what trace flags are enabled and which of them, if any, I need to enable on the target SQL Server.

• If it’s an online migration, use the DBATools PowerShell module to help me migrate everything outside of the database. This includes, Logins, SQL Agent jobs, credentials, everything that isn’t in the database!

• If it’s an offline migration, I can use DBATools to do everything for me, including restoring the databases.

• Test with Developers and internal application users after the migration.

• Post migration, turn on Query Store in the new databases.

• Post migration, at some point days or weeks later, update the database compatibility level to the most recent compatibility level.

I hope you find this template a great starting point and if you have questions, comments or concerns, let me know in the comments or hit me up on Twitter or LinkedIn.