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.)
6 Comments. Leave new
What’s a guy (or gal) to do when their stuck supporting a legacy app that is only certified for SQL Server 2005? I can’t seem to get my company into the recent past, let alone the present or future.
Start > Run > Word.exe
File > Open > Gordon’s Resume.docx
It sounds like they’ll be out of support on both the vendor app and SQL Server in the near future. Depending on how critical that application and server are, that may change their tune about making a move.
This might be a great time to create one of those ‘3 options’ papers and present it to the application owner/management.
Option 1.
Do Nothing. Detail a bunch of the risks of running unsupported- malware, security exploits, no help in event of corruption or application bugs, etc. Maybe run up a cost estimate if that data was to get published? There’s certainly been enough data leaks lately to get a rough figure. Otherwise cost to the business per hour of downtime might work as well…
Option 2.
Get a hold of your nearest Microsoft Account manager and ask them to price up a custom support agreement for your SQL Server 2005 instance.
Option 3.
Contact the vendor and ask them the difficult question do they have a newer version that runs on a still supported platform?
If yes, then cost out an upgrade project to a supported SQL Server and application version.
If not, present mangement with an option for a competing product on a supported platform.
If they go ahead and choose option 1 then at least you’ve done your due diligence and perhaps Erik’s nuclear option is the way forward?
For option two also get a couple of different durations if you can. My guess is support costs will go up every year, so while year one might not be cost prohibitive the price might just steeply in the following years. My employer paid a one year support contract for both XP and server 2003, but was unwilling to pay for a second year on either one. We had thousands of PCs running XP with “critical” apps that would not upgrade, but the second year of support was still not worth the cost.
Thanks, Erik, for the info on DB ownership potentially changing with a restore. I either did not know it or it had flushed out of my personal cache, it was good for the refresher.
If you think that’s silly, you should see what happens when you detach a database…
https://support.microsoft.com/en-us/kb/922804
http://dba.stackexchange.com/questions/53756/access-denied-error-in-create-database-for-attach