SQL server replication advice.

Question

We have a scenario

One Main e-commerce website - currently attracting a lot of visitors.

Three sub "brand specific" sites which will hang off this site - each of these sites will potentiall have the same level of traffic over time.

The client requires order processing for each brand site to happen in one place (i.e. one backoffice).

What topology should we choose? We think that perhaps having a main sql server with both reads and writes from the backoffice, and replicate that data to "brand specific" sql server instances might work. Each brand specific site would have its own dedicated sql server for Frontoffice "reads". Any writes we perform would go back to the main database to keep stock concurrent

Any thoughts? Future scalability is a major factor.

Answer 1

Not a specific answer to your question but Youtube scaling is an interesting video about youtube scaling. Prehaps it will give you some ideas.

Answer 2

If you want to incorporate writes from the remote sites, then you need to use Merge Replication

This site may be of use to you: Replication Answers

Answer 3

Hi Paul,

Without having a more detailed understanding of how your application is to function it is difficult to provide you with clear direction.

Your proposed implementation of having a central server (Publisher) supporting reads and writes, with a number of additional site specific servers (subscribers) for reads only, is certainly plausible. This has the added benefit of giving you the flexibility to replicate only the tables that would be required for read queries i.e. your central server will likely manage data such as supplier information, billing etc. that may not need to be pushed to subscribers.

Your central server is likely going to be your sticking point if any, as all other servers will directing write activity back to it. The location of your distributed sites i.e. how far they are from the central server will also affect the transactional latency of your replicated environment.

If you wish to present all of your database data as read only at the distributed sites then you may wish to consider using Log Shipping for this. The disadvantage of this implementation if that your application needs to be aware that only read activity can be processed on the local server and all write activity needs to be routed to the central server.

I hope this helps but please feel free to pose additional questions.

Cheers, John