Potential issues migrating from SQL Server 2014 to 2012 by scripting tables and data?

Question

I have a very simple question.

What would be the typical "gotchas" involved with scripting all the data to migrate a 2014 SQL server database to 2012?

Is this a pretty solid process? Are there potential issues of the ordering of scripts being messed up based on FK relations and other rules?

Have there been times of "Oh man, didn't see that coming" some time later when you run into some type of conflict because of the migration that wasn't caught previously?

With business critical data, is this unwise to do? It's a cost/benefit situation of upgrading and licensing for a newer SQL.

Answer 1

You shouldn't have any problems scripting your database out and running it on the new database. There isn't any data for foreign keys to affect. After that you will have to be more careful to load the data in the correct order. Lookup tables first, then data tables etc.

That isn't a huge issue since you can get the order down in test before trying it in production. Where you may have your biggest problems is in any code/features that you are using that are new to 2014. Follow the link and review the new features and search for them. After that it's just testing, testing, testing. Try it out in test and see what happens. Document your changes, change them in production to remove any issues, and then start your move.

Answer 2

What would be the typical "gotchas" involved with scripting all the data to migrate a 2014 SQL server database to 2012?

A counter question would be why are you downgrading from 2014 to 2012 ?

Now lets address your question, if you are just downgrading from 2014 to 2012 with same version (standard --> standard) then there should not be any problem unless you are using something that is new in 2014 and is not present in 2012.

Downgrading an Enterprise edition to standard edition, you have to remove / disable all the enterprise features and then downgrade.

Downgrading is always a pain.. You have to script out the skeleton (schema, objects, etc) and then recreate it on 2012 and then use BCP OUT / BULK INSERT IN to get the data into sql server 2012. I have used the script that I wrote for a similar task.

Have there been times of "Oh man, didn't see that coming" some time later when you run into some type of conflict because of the migration that wasn't caught previously?

You have to thoroughly test out the entire process before you attempt to run it in PROD. The script that I have mentioned will assist you.

With business critical data, is this unwise to do? It's a cost/benefit situation of upgrading and licensing for a newer SQL.

I don't see a reason for downgrade. I would suggest you to be on the latest version of SQL Server. Its worth the investment. Since your data is "Business Critical", I would suggest to go with SQL Server 2014.

Also, what you can do is you can set up replication from sql server 2014 to sql server 2012 or just install another instance of sql server 2012 on the same machine (if you have enough capacity and resources).

Note: "migration" is misleading.. it should be downgrade. I use migration when moving from a lower version of sql server to higher version of sql server.