Replication or something else?

Question

Replication or something else?

Jared

SSC Guru

Points: 61793
More actions
October 27, 2011 at 11:14 am

#246952

Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
Does anyone have any other thoughts on another way to accomplish the task, or is this really the best way to accomplish it?
Thanks,
Jared
Jared
CE - Microsoft

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
Does anyone have any other thoughts on another way to accomplish the task, or is this really the best way to accomplish it?

Do Business Requirements ask to keep the four operational databases in sync or are they absolutely independent? The "depending on load" part of the scenario specification makes me think databases are supposed to be in sync.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jared SSC Guru Points: 61793 More actions · Answer 2

PaulB-TheOneAndOnly (10/27/2011)
jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
Does anyone have any other thoughts on another way to accomplish the task, or is this really the best way to accomplish it?
Do Business Requirements ask to keep the four operational databases in sync or are they absolutely independent? The "depending on load" part of the scenario specification makes me think databases are supposed to be in sync.

Depending on load is actually determined by the load on the application servers, not the SQL servers. So, the 4 servers are independent for this database.

Thanks,

Jared

Jared
CE - Microsoft

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

jared-709193 (10/27/2011)
PaulB-TheOneAndOnly (10/27/2011)
jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
Does anyone have any other thoughts on another way to accomplish the task, or is this really the best way to accomplish it?
Do Business Requirements ask to keep the four operational databases in sync or are they absolutely independent? The "depending on load" part of the scenario specification makes me think databases are supposed to be in sync.
Depending on load is actually determined by the load on the application servers, not the SQL servers. So, the 4 servers are independent for this database.

Well... if operational databases are independent then I think you may want to consider building four replication schemas, one for each one of the source databases.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jared SSC Guru Points: 61793 More actions · Answer 4

PaulB-TheOneAndOnly (10/27/2011)
jared-709193 (10/27/2011)
PaulB-TheOneAndOnly (10/27/2011)
jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
Does anyone have any other thoughts on another way to accomplish the task, or is this really the best way to accomplish it?
Do Business Requirements ask to keep the four operational databases in sync or are they absolutely independent? The "depending on load" part of the scenario specification makes me think databases are supposed to be in sync.
Depending on load is actually determined by the load on the application servers, not the SQL servers. So, the 4 servers are independent for this database.
Well... if operational databases are independent then I think you may want to consider building four replication schemas, one for each one of the source databases.

Even though the business requirement is for them all to be in 1 database on the reporting server?

Thanks,

Jared

Jared
CE - Microsoft

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.

Does the data at these locations need to be read/write? Real-time? How much data are we talking about?

jared-709193 (10/27/2011)
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.

2 Distributors? So you have two publishers?

Jared SSC Guru Points: 61793 More actions · Answer 6

MysteryJimbo (10/28/2011)
jared-709193 (10/27/2011)
Scenario:
Application may be routed to 1 of 4 servers depending on load and location: Colorado, Michigan, 2 Illinois. Data from each server needs to be replicated to 1 reporting server.
Does the data at these locations need to be read/write? Real-time? How much data are we talking about?
jared-709193 (10/27/2011)
Currently:
2 Distributors are set up (1 for 2 application data servers) for this replication. This is simply transactional replication. So 4 servers through 2 distributors replicate data to 1 reporting server.
2 Distributors? So you have two publishers?

The data at the 4 locations (4 publishers which should answer question 2) is read/write. However, it is really 1 insert and update per row. Fundamentally these are phone calls, so when I call someone it inserts 1 row with all of the information except the call end time. That gets updated at the end of the call. Approximately 100000 rows a day split between the 4 publishers. Kind of a 40,30,20,10 pct split.

Thanks,

Jared

Jared
CE - Microsoft