It’s Friday, and I’m out of town on vacation in historic Williamsburg, VA (if any burglars are reading this, I forgot to feed the 5 rabid Rottweilers that I’m fostering so feel free to stop by and visit them). What does that have to do with upgrades and migrations? Nothing. Just making conversation, but that does get me back on track to bring you these 5 tips for upgrades and migrations.
- Check compatibility level before upgrading: People often forget to set compatibility level to the new value after upgrading a database. You can get some behavior differences with different compatibility levels so it is important to make sure you are on the highest compatibility level available for your database prior to the upgrade. There’s a couple of reasons why.
First, if your database is using on an old compatibility level that is not supported on the version to which you’re upgrading, the compatibility level will be forced to the next closest compatibility level that is supported. Now if things aren’t working post-upgrade, how will you tell if it is because of the new compatibility level or because of something else related to the new version? It injects a certain amount of uncertainty into the process for which you cannot easily account. It muddles the remediation process you may need to go through.
Second, there was a ton of functionality and T-SQL syntax that was discontinued in SQL Server 2005, but it continued to function under compatibility level 80 (SQL Server 2000) in both SQL Server 2005 and SQL Server 2008/2008 R2. If you are upgrading from one of these 2 versions, it is critical that you first get off of compatibility level 80 so you can identify and remediate all of these discontinued features and syntax. SQL Server 2012 and above do not support compatibility level 80 and you will be forcefully upgraded to compatibility level 90 (or higher depending on the version) and none of those discontinued features or syntax will work anymore. You need to be able to run fully on the current version before upgrading to a higher version.
- Beware the ad hoc SQL: Data Migration Assistant (nee Upgrade Advisor) is great for a lot of checks. It can check your SQL code in the database or scripts, your schema, etc. What it can’t do is check the ad hoc SQL code that is being generated dynamically be clients. Nothing beats standing up a test server with the new version and running your actual real-world workloads against it.
- Log shipping is awesome: Whether you are merely migrating to new hardware or migrating for upgrade purposes, log shipping does it very well. Sure, it’s an old technology, but it doesn’t have the restrictions things like Availability Groups or database mirroring have. Log shipping is easily automated, and if need be, you don’t even have to set up log shipping. Just use the log backups that are already happening and restore them on the new server.
I previously blogged about when I rolled my own log shipping for databases backed up using Dell LiteSpeed here: T-SQL Tuesday #81 – Migrating Databases with Dell LiteSpeed. It shouldn’t require much work at all to modify this for native backups or some other 3rd party backup solution.
- Temporary single-node FCI: Have you ever needed to migrate a failover clustering instance (FCI) to a new OS and SQL version, but you didn’t have new hardware for the migration? In-place upgrade is not an option because of the new OS version. There is an option that doesn’t require new hardware, but it does add some risk into the process because you lose the ability to failover the FCI for some period of time. Now, you may have a log shipping secondary or something to help mitigate the risk, but there is risk nonetheless. This is the process I followed in the past when I had to do this:
- Evict the passive node from the existing cluster
- Build out the evicted node with the new OS, set up the Windows cluster (WSFC) on it, and create a new SQL Server cluster (FCI) on it
- Migrate from the old cluster to the new cluster
- Build out the old server with the new OS, join it to the new Windows cluster, and add a new node of the SQL Server cluster to it
- Compatibility levels revisited: Post upgrade, be sure to revisit compatibility levels. Some new functionality like query optimizations and the new cardinality estimator are dependent on compatibility level. Now more than ever, it is important to raise the compatibility level when upgrading to the latest versions to be sure you are taking advantage of all new functionality available to you. There may be cases where you need to lower it back down while you address issues that occur, but it the default action should be to raise it to the highest level until you have to change it to a lower one.