I have a time and attendance/payroll system running off of a SQL 2000 database. The client has the application running at 2 seperate sites and at the moment these are seen as 2 seperate systems, each with their own independant database. They would now like to be able to manage and report on data from either site. We have looked at a number of options but cannot come up with a solution and as i am new to SQL replication I am not sure whether or not it would work?
Basically, we would need any data that is inserted, updated or deleted on either db to replicate onto the db at the other site. We would like to do this by changing as little code in our application as possible and do it all through SQL.
The problems i envisage are, does SQl replication know when a record is deleted on one DB, will it replicate it to the other ( We are not marking records as deleted but actually deleting), how does it handle new records created on either database as these would in theory conflict with each other?
Hopefully that made sense and any experts out there can tell me if I am on the right track and if SQL replication will work for me, and if so, any good reading material ?