November 30, 2011 at 8:12 am
Hi,
I'm currently at the conception stage of a new project and i'm struggling with how to attack this particular problem.
We have a number of third party applications which are all backed by SQL Server databases. Each application stores a separate version of employee data and the database structure of each database is different. This data is sporadically kept in sync by exporting CSV files from one database, importing it into the other and updating the data to match.
We have been asked to streamline this process.
I was thinking about maintaining a "master" database that holds the definitive employee data and somehow replicating the changes which take place in each individual application database on this master and pushing the changes out to each application database so that the entire batch remain in sync.
It seems to be very similar to P2P transactional replication but the fact that i am not dealing with exact copies of a database / database object would suggest i cannot use this technology.
That's a bout as far as i have got; any suggestions on how to proceed would be gratefully received.
I apologise if this is incoherent, please advise if any further info is needed and i will happily add it.
Thanks in advance.
November 30, 2011 at 8:16 am
What are the time constraints for keeping the data in synch. How stale can the data be in the dependant databases?
Have you thought about Replication of the employee table, then having a process on each DB by which it maintains that DB's version using that replicated table? I believe that would be doable. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2011 at 8:20 am
At this stage we have no limitations; i would envisage data could be out of sync until the end of the working day at which point the various applications databases could be brought into sync outside of working hours.
November 30, 2011 at 8:24 am
bumping this thread. I made an edit after your post and not sure if you'd be notified. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2011 at 8:26 am
Also, you could have an SSIS package set to handle the synching run nightly. Setting up linked servers and jobs to synch them. There are many possibile approaches. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2011 at 8:45 am
Thanks for the advice, a number of different approaches have been buzzing through my head and i wanted to see how others would proceed if faced with the same problem (for reassurance more than anything).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply