After this data is received it needs to be reviewed and evaluated for integrity and consistency. Server B serves as a central point for data review and validation before it is cleared to go onto other sources. Server B pulls data from Server A. There is a firewall between Server A and Server B. Server B can pull data from Server A. Server A cannot push data to Server B.
Server C is a business operational OLTP system which needs the data that has been approved by Server B. Server C pulls the data that has been approved by Server B, performs the necessary updates, then pushes changes back to Server A so that can now be called by a web page and displayed. Server B and C are separated by a firewall. Server C can pull data from Server B. Server B cannot push data to server C.
What replication technologies would you use to accomplish the above challenge? Is there a better way to design this?
Data Rows: A few thousand
Replication Interval: As fast as possible
Server B will process and approve data for transmission to server C, rules will be applied to data on Server B.
platform: SQL Server 2k5
Well, Replication or SSIS would be the main candidates. How do you want to control the data flow, does it all need to be shipped from A to B to C or is there a processing/filtering process that needs to be applied? What software versions are you using? How many rows of data? How often does it need to happen? What fail-safe contingencies need to be in place?
The process you describe is, in essence, sound.
answered Aug 20, 2010 at 05:44 AM