SQL Homework – October 2018 – Time for an upgrade


October 1, 2018 by Kenneth Fisher

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)

One thought on “SQL Homework – October 2018 – Time for an upgrade

  1. […] configurations rarely if ever. That said, it does happen. And for new environments or even when you upgrade your environment you may need to change something. Heck, something as simple as adding a new application to an […]

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: