Upgrade Fiascos

We recently did an upgrade from SQL 2005 to SQL 2008R2 (yea, I know, better late than never) and I was reminded of the biggest risks in most such upgrades:

1. Security
2. Assumptions
3. More Security

Let's start at the top. Bear in mind our upgrade involved a new cluster and a new OS version.

Security

The plague of most upgrades, migrating security in SQL is never as easy as it appears it should be. Sometimes this is from the lack of a password vault. Sometimes it is from lack of application documentation. New users have to be created on the new system, sometimes SIDs have to be synced, and each existing login has to be understood at least enough to ensure that the right new security is being granted.

There is usually a healthy proliferation of SA and DBO accounts as a system ages, so this is a good time to re-examine those permissions and ensure they are set as conservatively as possible for each account.

Assumptions

The transaction log was 100 GB before, and it's set the same on the new box. But that's not always enough. For instance, not long after our upgrade, our identically-sized transaction log filled up.

How does a DBA know that the xlog will have the same requirements in a new version of SQL? What if reindexing takes more space? What if the downtime means catch-up work for the first hour before a xlog backup runs?

I recommend adding 50% space to the transaction log allocations when bringing a new server online. Yea, that seems like a lot, but auto-grow is painful and running out of space is moreso.

MAXDOP? All because unlimited or near-unlimited settings worked in a 4 CPU system doesn't mean it will still be good on a 48-core system. Merging parallel streams is a known point of difficulty in some types of queries.

Every assumption needs to be re-validated on a new system.

More Security

Synonyms? Linked Servers? SSRS Permissions? The list of easily-hidden dependencies goes on and on... it all needs to be checked and reverified. And cleaned, if possible, to use more AD Groups and less one-off permissions which are also usually prolific in older environments.

Everything Else

Of course there are other issues during upgrades, but I find that things like deprecated features are well documented (and everyone runs tests with profiler to monitor deprecated message warnings right?). The items I've mentioned here are more undocumented and are what derails an upgrade after it goes lives, if not properly considered.