We help IT Professionals succeed at work.

How to synchronize data between Staging and Production Sql Server Databases on the same server using SSIS Package?

26 Views
Last Modified: 2020-04-13
How to synchronize data effectively between two Sql Server Databases Staging Database and Production Database on the same server using SSIS Package?

Both Databases are copy to each other, having 12 tables and also have foreign keys?
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
"Both Databases are copy to each other" - so you mean you want to synchronize data from production to staging (which is fine in my opinion) but to sync it from staging to production...that could lead to disastrous results right? Meaning production data gets lost as it is replaced by staging data - right? I would never allow making DML changes to my production databases without a solid rollback script available plus to perform these deployments/changes controlled after a production database was taken and kept safe.

Assuming you still want to synchronize them regardless of all of the above - you could use SQL own Replication featur or CDC (Change Data Capture) can also be a solution but this way you need to build nearly everything on your own, or atabase change tracking. It is a built-in SQL Server function that does for you change tracking including inserts, updates and deletes. Everything else like sending and applying changes, solving conflicts etc. you will have to code yourself.

You can also use some tools like RedGate Data Compare, ApexSQL Data Diff, and dbForge Data Compare, which use the data compare method.or you can use SQL Database Studio, which automatically generates reusable SQL scripts.

Author

Commented:
Hi,

I have a Admin website which inserts new data/updates the existing and deletes the unnecessary data. This admin data inserts in the Staging Database.  Sql server job runs in the night which moves the data from staging database to production database. Currently this is done by Sql Server Replication. But we need to replace Sql server replication with SSIS Package.

I want SSIS Package moves the newly inserted data, new updates, new deletes from Staging Database to Production Database.

I know how to sync one staging table to production table. Please see the attached picture. How syncing can be done on multiple tables. I just need to sync the daily data.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
"I know how to sync one staging table to production table. Please see the attached picture. How syncing can be done on multiple tables. I just need to sync the daily data."

So if you know how to sync one table via the SSIS package all you need is to add one "Data Flow Task" per each table into the same package like you did for that one table already. You'll need to add source - destination, mapping, transformations etc for each of the 12 tables you need to sync.

Author

Commented:
I am having 12 tables:
1. Which option is good for validation and error handling:
--------Creating one package and having 12 data flow tasks for each table.
--------Creating 12 child packages, each package having one data flow task for one table. Master Package contains 12 child packages.

Problem is tables have relationships, foreign keys. Is it a preferred way to delete the foreign keys in the beginning and create foreign keys at the end.

I need to send an email if total count of source table does not match with total count on the destination table, Check if Max(Primary Key) on Source Table and Max(Primary Key) on the destination table matches, what's the best way to do the validation if syncing data is having no issues.
EachTableDataFlow.jpeg.crdownload
DataFlowTasks.jpeg.crdownload
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
I believe one package with 12 data flow tasks should be enough as you can control the order of execution and how many SSIS tasks run in paralel - I think default is 4. Considering this your options are:
- to put/process the child tables first then parent tables last so you don't have to drop all FK-eys at the begining and re-create them at the end
OR
- to have first task in the SSIS workflow a SQL task that will drop all FK-eys, process all 12 tables and add the FK-eys WITH CHECK in another SQL Execute task at the end then final task do the counts/compare


Ouh...and make sure to take Production database backup before this and also - make sure to test, test, test before running such sync against a production database.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Get access with a 7-day free trial.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.