2

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
  • ~100 views

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

xeraphim
  • 597
  • 2
  • 5
  • 11
  • Any of the databases in 80 (2000) compatibility mode? – Aaron Bertrand Oct 22 '14 at 13:27
  • Hey Aaron the compatibility_level for the DB on the 2008 server is 100 and for the DB on the 2005 server it's 90 (does this mean its in the SQL Server 2000 compatibility mode right?) – xeraphim Oct 22 '14 at 13:29
  • 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 http://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. http://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx – mskinner Oct 22 '14 at 13:33
  • 2
    90 is SQL Server 2005 compatability, and 100 is SQL Server 2008 compat. – Hannah Vernon Oct 22 '14 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... – Aaron Bertrand Oct 22 '14 at 13:38
  • Thanks for all the comments. Our DB-Team will execute the actual migration so they should know which upgradepath to use etc. we "only" have to make sure that the application will still run the same way as before. So basically we have to check each procedure / function / view individually to be sure that everything works fine? Or is there a more efficient way to do it? – xeraphim Oct 22 '14 at 14:03
  • 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 (http://msdn.microsoft.com/en-us/library/ms144262.aspx, http://msdn.microsoft.com/en-us/library/ms143179.aspx) – James Z Oct 22 '14 at 14:21
  • Also once you're on 2012 (or 2014, I agree with @JamesZ) check out this KB article. – Aaron Bertrand Oct 22 '14 at 15:18
  • 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. – Aaron Bertrand Oct 22 '14 at 15:22
  • This is true Aaron and we do this for every new feature we develop (we introduced EF to this project was well). But the application is ~10 years old and our predecessors loved to create stored procedures and no tests :P – xeraphim Oct 23 '14 at 07:17

0 Answers0