I have OLTP central database sql standard 2005 that is currently working within LAN.

It is required that new  three remote branch sites will connect to the SQL server and do update/insert/delete and reporting.

The  four sites are connected with frame relay WAN.

The main site is 512 kbps  and has the database Server , and the other new remote sites are 256 kbps.

The size of database is 250 MB , and  the reporting in branch sites (huge reports) may cause high traffic over WAN and performance may suffer.

 I  need   to implement one of two solutions for reporting:

solution 1) using replication

the main site will be publisher and the other thre remote sites will be subscribers

OR solution 2) using log shipping

I have couple of questions:

what is the best stable solution?

what is the side effect in case of WAN  is down, which is better to re-synchronize the database?

In case of replication , what is the best replication to be used.?

please advice

Log shipping can't be used because whenever you restore the log, all users should be out of the db...it is not recommended...

Replication is the only solution if i am not mistaken...what type of replication depends on subscribers...

You can use transactional replication...or check peer to peer replication you the subscribers also modifying the data...

Mahmoud_H:

It is required that new  three remote branch sites will connect to the SQL server and do update/insert/delete and reporting.

IMO Snapshot replication and logshipping both are ruled out as you would required your subscribers to update/insert/delete records. The options that are viable according to me are:

1. Transaction Replication with updatable subscriber http://msdn.microsoft.com/en-us/library/ms151718(SQL.90).aspx

2. Merge Replication http://msdn.microsoft.com/en-us/library/ms172367(SQL.90).aspx

3. Peer-to-Peer Replication http://msdn.microsoft.com/en-us/library/ms151196.aspx

This is what my opinion is, lets wait for others reply.

 thank all for reply

The remote sites use 256 Kbps , so which replication is less overhead for WAN.

Is ther a reasonable speed for link  between  main and  remote sites

Hi Mahmoud,

Your only option is replication - nothing else (from the techniques delivered out of the box) will allow you to update at the remote branch sites.

Even if your branch offices were local I would recommend you to replicate to other servers for reporting - simply because OLTP and reporting activities don't mix well performance wise.

You have to choose between Transactional and Merge Replication (peer to peer is only available in Enterprise Edition):

- Both of them allow you to have bi-directional replication, i.e. data can be updated and replicated between Publisher and Subscribers.
- Both of them can "queue" replicated data in case your WAN is down

So which one to choose? Generally, Transactional replication is used in server to server replication and Merge Replication in "loosely coupled" scenarios (the typical example is Sales people with laptops).

General rules for choosing Transactional Replication:
- You want incremental changes to be propagated to Subscribers as they occur.

- You need transactions to adhere to ACID properties (merge replication does not replicate transactions; it replicates commands)

- Subscribers are reliably and/or frequently connected to the Publisher.

Merge Replication:

- Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
- Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
- You do not expect many conflicts when data is updated at multiple sites (because the data is filtered into partitions and then published to different Subscribers or because of the uses of your application). However, if conflicts do occur, violations of ACID properties are acceptable.

Another important thing is that with Transactional replication, you can only solve conflicts in 2 ways; either the Publisher always wins or the Subscriber always wins (there is a 3rd, reinitialization but that is usually not feasible). In Merge Replication you have a range of different Conflict resolvers you can choose from.

Depending on what your transactions look like, you might get less traffic with Merge Replication because you can choose column-level tracking. With column-level tracking, only the updated columns are replicated not the entire row. This is not going to help you if you have mainly deletes and inserts or typically update several or most columns.

HTH

 /Elisabeth

Say whether it is log shipping or replication the network bandwidth is most important factor for such environment on WAN especially. So you need to be careful in order to perform baseline of number of transactions that are happening within the databases that needs to be involved in this standby operations.

So in your case such operations by testing is very much helpful to perform between the sites and also the firewall tests as it is another overhead that might hurt the performance . 

Thank you  Elisabeth for your greet advice and details

Elisabeth Redei:

Depending on what your transactions look like, you might get less traffic with Merge Replication because you can choose column-level tracking. With column-level tracking, only the updated columns are replicated not the entire row. This is not going to help you if you have mainly deletes and inserts or typically update several or most columns.

satya:

the network bandwidth is most important factor for such environment on WAN especially. So you need to be careful in order to perform baseline of number of transactions that are happening within the databases that needs to be involved in this standby operations.

I agree with you  for bandwidth and firewall overhead

 
Again,

Thank you all  who shared their knowledge and experience