2

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • When there's a lot of data involved, along with a lot of tables, SPs, functions, etc. Does the scripting take care of this ordering? Or would I need to go through and make sure the table creation and insertions of data are in the right places?
    – user1447679
    Jul 27, 2015 at 16:58
  • Inserting the data should go at the very end. I've never had a problem with the scripting personally but YMMV. Test it out. Running the schema script shouldn't take long even with hundreds of objects you are probably only talking a few minutes. If you run into an error fix it and try again. Once your schema is perfect then start adding in the data. Worst case disable all of the FKs and then load the data then enable the FKs.
    – Kenneth Fisher
    Jul 27, 2015 at 17:22
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.

Improve this answer
2
  • Regarding your remarks about downgrading, sometimes you have no control over the instance you will be scripting to unfortunately. I have had to schedule ETL processes to instances on domains that I had no control over (and weren't even in the same company). Sometimes replication is not possible politically for similar reasons. Sometimes downgrading is your only option.
    – Conor
    Jul 27, 2015 at 21:34
  • @Conor thats a very edge case. Once you decide to move on a higher version, then change of mind means poor planning. I can understand your situation and still I believe its an edge case. There are lot of things that can go wrong when you choose a fully functional system to downgrade. HTH.
    – Kin Shah
    Jul 27, 2015 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.