Data replication architecture challenge

Server A is a SQL Server connected to a web server via 1433. It receives data from web applications

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

more ▼

asked Aug 20, 2010 at 05:36 AM in Default

avatar image

22 4 4 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Aug 20, 2010 at 05:44 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 20, 2010 at 05:36 AM

Seen: 753 times

Last Updated: Aug 20, 2010 at 05:49 AM

Copyright 2018 Redgate Software. Privacy Policy