Hi Friends,
I took some time to choose a right title for this blog post. Over the years I have seen people using these terms, “Upgrade” & “Migration” interchangeably. Yes, I know, many of you would think that it is so ‘simple’ as these two have distinct meanings but I am sure, there are other lots that would agree, that it is indeed a little ‘confusing’ or sometimes mean the same thing. There goes my title – “Upgrade vs Migration – simple yet confusing” :).
So, let me explain Upgrade vs Migration in simple words.
‘Upgrade’ simply means upgrading the old version of a product to a new version. So suppose you want to move from SQL Server 2005 to 2008; we would call it an upgrade. We would say: “Upgrading” from SQL Server 2005 to SQL Server 2008.
Many of you would know, we have 2 options here: In-place upgrade and side-by-side upgrade. Now suppose, you want to do a side-by-side upgrade and you set up a new system and perform a SQL Server 2008 installation. You have to now move your data to this new system – and this is what we call as migration – “migrating” data from the old system to a new system. However, the next step or the overall thing is still an upgrade.
We also use the term migration, obviously, when we migrate from Oracle to SQL Server or vice versa, though I prefer the former one 🙂
I would suggest a different set of terms:
Upgrade: an in-place upgrade of the binaries and data structures (the MDF, NDF and LDF files are also upgraded when you move from one version of SQL Server to another)
Migration: migration of data from one instance of SQL Server to another (usually, but not necessary different versions of SQL Server). This is what you call a side-by-side upgrade.
Re-platforming: migration of data from one data platform (say, Oracle or MySQL) to another (say, SQL Server)
When going from one version of SQL Server to another, I prefer migration to an in-place upgrade. There is less risk and the ability to instantly fall back. While it is rare that the SQL Server in-place upgrade process fails, if it does the fall back usually requires a full restore of the server the database is running on AND a restore of the databases themselves. This can be stressful and time consuming, with some serious downtime. Even if everything goes well, there will still be downtime during the upgrade.
With a migration, once the new instance of SQL Server is up and running, a backup of the original databases can be restored (and upgraded), then tested. If all tests go well, a final restore from the source database to the target database is done and the new server assumes the identity or role of the original server (this can be done through MCSC, NLB, DNS or just renaming servers; it depends on your environment). If the upgrade doesn’t go well, leave the original server/database in place; with no perceived downtime from the users perspective.
With re-platforming, it’s generally very easy to move the data from one platform to another. What isn’t easy is migrating the functionality of stored procedures, functions, packages, etc. This is a process with significant risk and effort.