We need to upgrade two SQL Servers for our projects to SQL Server 2012. Since we’re not db-admins or familiar with such migrations, I wanted to ask you if you could tell me what we have to be aware of and check before upgrading.

Some information about the servers:

Project 1:

  • SQL Server 2008
  • No procedures
  • No functions
  • No views

I think this should not really be a problem to upgrade to 2012 right? Is there anything else we have to make sure?

Project 2:

  • SQL Server 2005
  • ~700 procedures
  • ~50 functions

I think this will be more of a problem… is there any logic that won’t work on SQL Server 2012 that did on SQL Server 2005?

Is there a list of keywords which are not supported anymore?

Are there other problems that could happen during / after the migration?

Thanks in advance

  • 3
    When you say you are going to migrate, does this mean it will be a brand new server and that you will backup and restore the databases over to that server? Or does it mean you will run an upgrade? If running the upgrade, read through this msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx If you are just doing a restore to a new server, in general you should be fine but you would want to pay attention to the depracated features. technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx
    – mskinner
    Oct 22, 2014 at 13:33
  • 2
    90 is SQL Server 2005 compatability, and 100 is SQL Server 2008 compat.
    – Hannah Vernon
    Oct 22, 2014 at 13:37
  • 4
    I asked about 2000 compat mode because in that case there can be lots of trouble. Since neither of your databases are stuck in 2000 level you should be ok, but you should restore the backups on a test server first just to be sure. You can also run the upgrade advisor and best practices analyzer against each instance... Oct 22, 2014 at 13:38
  • 1
    Just wondering why to upgrade to 2012 now, wouldn't 2014 make more sense? Also, checking the breaking changes & discontinued features list might be a good idea (msdn.microsoft.com/en-us/library/ms144262.aspx, msdn.microsoft.com/en-us/library/ms143179.aspx)
    – James Z
    Oct 22, 2014 at 14:21
  • 1
    There is no way to test every view, function and procedure without running every view, function and procedure. This is why people spend a lot of time developing unit and regression tests for their applications - not just for testing upgrades but also for testing application changes or simpler changes to the underlying database. Oct 22, 2014 at 15:22