Migrating to SQL Server 2017 / 2019

(for JH) When migrating to SQL Server 2017 or SQL Server 2019 the name of the game is “Risk Mitigation”. Avoid the temptation to bundle other improvements into the project. Business continuity is first, last, and everything in between.

Server Architecture should be duplicated Exactly in the new environment.

Server Rationalization – nope. Before the migration would be best, because if unexpected issues arise you are on familiar ground. But you have unused logins? databases? jobs? – double nope (Before or after, not during).

SQL Server Role based security – nope. That has been best practice for the last 20 years. This is not the time to make up for lack of database administration. 21 years will be fine.

High Availability – Avoiding downtime is important and HA helps with that. But as long as you have a mature backup system in place, this can wait until the migration is complete. Nope.

Backups – Are less clear cut. But even if the old system is a quirky high maintenance mill stone. It is still familiar, and you don’t want to add unfamiliar complexity at this time. This should be next on your list though.

Disaster Recovery – There are new toys, like clusterless AG’s but again – nope.

Compatibility Level – Should match the old system. A few months after the deed is done, you can quietly try switching less important databases, one at a time (database administration is a perpetual business).

SSIS Packages – Should be rationalized from Packages to Projects … after the migration. Oh and if a package fails after being moved and upgraded, change the environment NOT the package.

Encryption is much easier with modern versions of SQL Server. Schedule this for a few months down the line.

Cardinality Estimator – You may have picked up on a theme here 🙂 – nope. Set it to legacy for reliable performance, for now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s