Using Data compare to stream data from a SQL 2016 db to SQL 2012 DB

zippy

Hi guys, i'm after a bit of advice on if this is the best tool to achieve what i want.

We have moved a database from a SQL Server 2008 server to 2016.

A few days after the move, we were told that a department uses a full backup to restore the database to their reporting instance which is 2012. they can't upgrade to 2016 are on a physically separate location and domain and we dont want to replicate the data because of the complexity and increased surface area risk.

i've setup a job to stream data into a SQL Server 2012 database, but i don't like the solution as i have to script the table structure then use SSIS to put the data into the tables, and we've had the normal SSIS issues where it just breaks, the file wont open, etc. to be fair it's copying 89 tables and 4gb of data....

so...

i want to investigate the following:
As the 2016 database was created from a backup of the 2008 and it's structure and compatibility have not changed, i want to look at updating the last 2008 backup to have the current live data, but in a sql server 2012 instance. i can then just flip the data source fort he application to the sql server 2012 instance. basically it's a fancy way of saying i want o downgrade a database from 2016 to 2012. i don't want to script everything into a new db - there is no need, the structure is the same, three are dlls used, etc which could be problematic.

As the structure is exactly the same on 2016 as it was on 2008, i can restore the 2008 backup on 2012 without issue. i just need to then update the data in the 2012 instance to make it current..


i've setup a project with redgate data compare to compare the data in the 2012 (a week old) instance and 2016 instance (liveish). this has picked up the changes. when i attempt to deploy the changes i get warnings that tables has foreign keys to other undepolyed tables. when i try to deploy it fails with reference constraint errors.

the data in both 2012 and 2016 is constrained correctly, so there is no bad data and i dont want introduce any! i have set skip integrity checks... on the deployment behaviour already.

i'm not sure what i need to do to achieve this - or if this is the best tool for the job.

any advice would be greatfully received.

sam.blackburn

At first glance, it looks like SQL Data Compare isn't disabling as many foreign keys as it should be. We'd be interested in reproducing the issue, and fixing an underlying bug if possible.

Since this is a one-off migration, however, it might be quicker for you to just disable the foreign key(s) manually in SSMS (ALTER TABLE ... NOCHECK CONSTRAINT) so that your data deployment will succeed.

You can then use SQL Compare to ensure the foreign keys are put back in place. Once you're happy with the migration, I'd recommend running the integrity checks (ALTER TABLE ... WITH CHECK CHECK CONSTRAINT) to get the performance benefits of a trusted foreign key.

Hope this helps!

zippy

Hi Sam, thanks for the replay. I had a number of issues and worked with Alex Bartely under a support ticket.

i was using v12. I got a notification about v13.1 on Tuesday and this version has worked perfectly. I've been able to do what i needed.

thanks

james