I just accepted a position that has multiple versions of SQL Server, from what I was told during my interview, it ranges from versions 2000 - 2012, the main goal is to centralized these databases to the 2016 version so we can get rid of the Linked Servers and the users would just point to the 2016 instance.

All of these versions have identical databases, logins, etc, for the most part, but there are slight object differences that need to be evaluated. Most importantly, the data date on these versions are different, for instance, for SQL Server 2000, it contains data up to year 2005. For SQL Server 2005, it contains data up to 2007, and so on. The upgrade/migration experience I have are from SQL Server 2008 R2 to SQL Server 2012 which included Always On, but steps should be similar, and btw, this was a team effort. You cannot upgrade from SQL Server 2000 to SQL Server 2012 as it requires an intermediate step.

I would like to approach it this way as this requires

  • Upgrade SQL Server 2000 to SQL Server 2008 R2
  • Upgrade SQL Server 2005 to SQL Server 2008 R2

or this can be upgraded directly to SQL Server 2012

  • Upgrade SQL Server 2008 R2 to SQL Server 2012
  • Upgrade SQL Server 2012 to SQL Server 2016

There are a lot of documents out there but would you know of a good set of documents/whitepaper that includes the details step by step? From what I mentioned above, would you approach it differently?

Please let me know your thoughts as I know this is tedious work since the versions accumulated.

Also, this is a side-by-side upgrade, thank God!

  • 1
    See my answer at dba.stackexchange.com/a/166209/8783. Note that you can just backup and restore from 2005 to straight 2016. see dbatools link in my answer to help you automate the process.
    – Kin Shah
    May 2, 2018 at 19:19