Lee Markum LLC

m
(He/Him) • You
I help SQL Server professionals make SQL Server more responsive, more highly available, and easier to manage which saves you time and makes your customers happier.
1mo • 1 month ago

I’ve upgraded SQL Server many, many times since 2008 when I got started in IT. 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 the PowerShell module 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.

If you're business uses a Microsoft database product, like SQL Server of any kind, I'd love to help you leverage that database product to its fullest extent.

A database health check is the starting point to see how healthy or sick your database system is. A health check will do these things:

• Identify poor configuration that is hurting database performance
• Make you aware of previously unknown changes to crucial SQL Server settings
• Tell you about the waits in SQL Server that are slowing down database performance
• Provide insight into indexes that are no longer helping your database
• Identify poor performing queries