Ask not for whom the bell tolls
It tolls for SQL Server 2005. Which means in a few short months, you’ll need to get out of Dodge. I’m sure you all have your upgrade ducks in a row, you’ve studied the breaking changes, the upgrade path, and maybe even downloaded the latest upgrade advisor. Smarty pants you are! I’m proud of you.
But chances are, that database of yours has been around for a while. If you’re not regularly upgrading SQL Server, you may forget to do a few things after you get that fossil to its new museum.
Page, but verify
Turning this on is a good step towards catching corruption early. This will put SQL Server in the habit of giving all your data pages a checksum as it writes them to disk, that it will validate when it reads them back into memory. If something spooky happened to a page, it will ring the alarm. Follow the link for some scripts to figure out if it’s turned on, and turn it on if it’s not.
Mind your compatibility level
When going to 2014 (as of today, 2016’s RTM hasn’t been announced yet), you’ll have to decide whether or not the new cardinality estimator suits you. There’s not a cut and dry answer, you’ll have to test it on your workload. If you’d like some of the more modern SQL features added to your arsenal, you can bump yourself up to 2012-levels to get the majority of them.
Who owns this thing?
Most people don’t log in as sa. Right? Heh. NO SERIOUSLY. Okay, your Windows login is a sysadmin anyway, so it doesn’t matter. Cool. But when you restore a database, the owner becomes whomever restored it. It’s generally considered pretty smart to make sa the owner. This goes for your Agent jobs, too!
You’ve gotten so big!
Careful about how you’re growing out your files. The majority of SQL users aren’t standing around with calipers and a magnifying glass deciding how much to manually grow files by. That’s special. Chances are you’re relying on percent autogrowth for your data and log files. Problem is, the bigger your files get, the more they grow by. What’s 10% of 5TB? BASICALLY A RHINOCEROS!
Does anyone know what’s in here?
Updating stats is important enough on its own, but Microsoft has put it in the upgrade steps for both 2014 and 2016. Perhaps they’re being collected differently in newer versions? I haven’t seen much of a “why” on this, but it’s not something I’d want to get caught out there on. But I’m sure that you, studious as you are, are updating them regularly anyway.
Since you’ve got this whole new server
Now is a great time to check out our Download Pack! We have a setup guide, and all our scripts are in there too. You can really dodge a lot of the common mistakes and oversights people make when setting up a new server.
Thanks for reading!
Brent says: when Microsoft doesn’t support something, we don’t support it either. This means we won’t do our SQL Critical Care® on SQL 2005, and our First Responder Kit script will be SQL 2008+ only. (This makes our life easier when it comes to testing and support.)