When you created your home lab last September you probably installed a version of SQL 2016. Well, SQL 2017 was released almost immediately afterward and so has been out a year now. Not to mention that SQL 2019 has been announced. Don’t you think it’s time to do an upgrade?

Your task is to perform two, yes two, upgrades. Don’t worry, even if you only have a single instance currently installed this isn’t a problem. You’ll do a side-by-side upgrade by installing a new instance and moving everything to it, then you’ll upgrade the original instance. Normally you’d only do one or the other, but this is practice.

First do a side by side upgrade.

1. Install a new SQL 2017 instance.
2. Check your old instance for any settings that are non-default. Review those settings, and any that need to be kept, copy to the new instance.
3. Back up any of your databases on the old instance that need to be kept. You will want to review each of the user databases and make sure that you actually want to keep them.
4. Restore the databases to the new instance.
5. Update their compatibility level.
6. Copy any logins from the old instance to the new instance. Again review them as you go to see if you need to keep them. If not then they get left behind.
7. While you’re on security, check the security in each of the system databases. If there are any permissions that need to be kept, copy them.
8. Copy any jobs from the old instance to the new one. And yes, again, review them to see which you actually want to keep.
9. You’ll also need to copy any operators, proxies, certificates, etc that you want to keep.
10. Test
11. Test again
12. Test one last time with the understanding that you’ll probably miss something no matter what you do. Do your best but don’t sweat it too much.
13. Oh, and I’m sure I’ve missed something too so anything you’ve added to your instance that is at the instance level and I didn’t mention needs to be moved as well.

Now that you are pretty comfortable that your new, upgraded instance is good to go, let’s do an:

Upgrade in place

1. Upgrade your existing instance to 2017.
2. Update the compatibility level of the user databases.
3. Test
4. Test again
5. Test one last time with the understanding that you’ll probably miss something no matter what you do. Do your best but don’t sweat it too much.

 
Things to think about

Obviously the upgrade in place is far easier right? So if that’s the case, can you think why side-by-side installs are done more frequently? To give you a direction to go in, think about what happens if the upgrade fails, or if during the testing you realize you have old code in place that is not compatible with the new version.

Also, upgrades are a great time to review your settings, security, jobs, etc. This is a good time to see if there are databases that can be archived, or even removed entirely. Users and logins that no longer exist or are no longer used. Settings that served their purpose at the time, but on this newer version of SQL they aren’t necessary. Etc.

In real life (not practice)

If this is a real upgrade I highly recommend running the Data Migration Assistant (DMA) on any database you plan on upgrading well ahead of time. This gives you (or your developers) time to correct what shows up. Heck, you may want some time to look into any new features recommended as well.

Last but not least, remember that this is practice, not reality. In reality, you need to make sure you are talking with your developers, your business, etc. Schedule your downtimes, make sure you have a rollback, make sure that your application is going to work correctly after the upgrade, and last but not least be prepared for the inevitable problems that occur after an upgrade. (i.e. make sure you have a full staff for a few days after the upgrade just in case)