Sql-server – SQL Server 2000 to 2008 to 2012

migrationsql serversql-server-2005sql-server-2008

We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to know is – Is there anything I can do now to make it compatible to 2012 also at the same time. Atleast those changes that are backward compatible from 2012 to 2008 etc.. The reason, I want to do this, is to minimize any effort in future (3 to four years from now) when we decide to move from 2008 to 2012. Anything at all I can do or need to be aware of?

Best Answer

The best answer to this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions.

For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but on the way out in a future version) and it is finally gone in SQL Server 2012. So if you kept that syntax in 2008 it would work, but would require work after upgrading to SQL 2012 or greater or you would have a failure.

To get you started, check out these links that take you to the articles that describe what features are no longer supported in the listed version and what features are due to be removed in the future.. Then look for these in your code.. I would also say you may want to look at future compatibility beyond SQL Server 2012 if you are holding off 3+ years.. For instance TEXT fields will still work in SQL Server 2012, but they won't likely work in the version out after SQL Server 2012 according to the links below.

SQL Server 2012 Deprecated Feature List
SQL Server 2008R2 Deprecated Feature List

You should also make sure that you let your database run in SQL Server 2008 compatibility mode after your upgrade to 2008 and ensure it works well in 2008 and to allow you to upgrade it to 2012 - SQL Server 2000 Databases, or databases operating in SQL Server 2000 compatibility mode will not migrate to SQL Server 2012.

I also wrote an answer that talks extensively about upgrades and migrations and general preparedness steps.

Related Question