0

My boss tasked me with doing research on migrating sql server 2005 to 2014. my first question is, is it really as easy as restoring into an old backup? No thing needs to be changed in terms of processes that load to the databases or components that look at the databases?

I'm completely new to this, obviously. I use SQL server management studio almost every day, yet I am still not familiar with anything else besides running a few simple queries. I've looked at this site here that finds all permissions/access for all users in a database. I've also run sp_who2 and: SELECT * FROM Master..sysprocesses order by spid

just to get an idea of all the processes going on. How would I see the websites that are pulling from the databases? I know we have a DB loader, how can I see that process? I can't quite get a grasp of the scope of this project.

Community
  • 1
  • 1
p0tato
  • 71
  • 2
  • 10
  • Take a look at this link: http://sqlmag.com/sql-server-2014/migrating-sql-server-2014 – Mark Kram Mar 18 '16 at 19:57
  • Nice documentation on updating from 2005 to 2014 here as well; https://blogs.msdn.microsoft.com/ialonso/2015/04/17/sql-server-2005-upgrade-to-sql-server-2014-and-compatibility-levels/ – Karel-Jan Misseghers Jun 08 '17 at 12:25

1 Answers1

0

It can be as simple as backup and restore or an in-place upgrade, but there are subtle changes with each new edition that mean there's no way to be sure that it will be without thorough testing. There's hundreds of things that may catch you up caused by 10 years and 4 different revisions. Some might be as simple needing to fix SQL Users and SQL Login mapping, to changes to the SQL client causing incompatibility, to functions or methods that simply no longer work or work differently. It's impossible to tell without knowing your data and your database. The first step should be getting the documentation from your application vendor, assuming it's an application with support.

I strongly recommend that you start reading the MSDN doc on installing and upgrading SQL Server 2014, and you may need to read the 2008, 2008 R2, and 2012 doc as well to look for changes that might impact you. I'd strongly recommend setting up a testing environment to make sure that your application will even work.

Bottom line is that you should not assume that it will work without thorough investigation, planning, and testing.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • thanks. i will take a look at that doc. would I also have to worry about stored procedures, jobs, or views? would they stay intact? I have not set up a testing environment before... any tips? – p0tato Mar 18 '16 at 20:15