SQL 2005 database synchroniztion........

Hello

I am using SQL 2005 server at head office and a 3 branch offices....
the WAN link is there in between each branch & head office. we are
using same database at each offices.

I want to synchronise each branch office database with head office
database daily. It should first compare the branch office and head
office database and then update the required field, tables at head
office database..

Can anyone help on this????????????

OS is MS windows 2003 server SP1

SQL 2005 standard edition..

WAN speed 512Kbps..

Thanks in advance...

The feature that you want to be using is called Replication. There are
several types of replication that can be performed, and this is
effectively capable of doing the "synchronisation" that you require.

HOWEVER: Implementing replication can be a little tricky, especially
because of conflict resolution (where two sources have provided data
for the same row in a table - which one is correct?). I would suggest
that the first step in getting this on the road is to verify with
someone who has done this before your table design / structure and data
flow so that you can be fairly certain there are no conflicts that
cannot be handled by normal replication implementation.

I would also suggest that you have a look and understand the different
types of replication. The type you choose will depend on your needs.
>From what I can tell probably a merge replication will suit, but still
- get this checked by someone who has done this before.

Once you have identified that the tables / data / data flow is okay for
replication and you have identified the replication methodology of
choice for your needs, you must draw up a plan for the implementation
of replication amongst your sites. Scheduling is one part, but also
network utilisation is a serious concern, as well as replication
failure. Recovery and contingency planning must also be taken into
account. And lastly, of course, making sure that your backups are
capable of actually restoring the servers to an operable state.

My suggestion here is to work to the principal that you should be able
to take a completely blank pc / server, load windows server, join the
domain (if necssary), install SQL server, and then have all the SQL
server set-up scripted for a full restore so that it simply can be run
by single command line instruction / windows icon. If you want to, you
could also use tools like sysprep to prepare an image of the server
with sql installed (before joining the domain), and use a tool like
"ghost" to save the image to a DVD for quick deployment. Keeping
restoration in mind, most people back up servers to tape, and maybe you
have done this for your servers, but for the purposes of an "automated"
restoration, I would also make a backup to an accessable network
location (protected area though) so that you dont have to go and fish
around for tapes, or potentially even be there at all. It may take
several tries to get this all operating correctly, but it is well worth
the effort, and the piece of mind you get from knowing that the server
could catch fire, and be replaced and operating withing a short time in
a completely automated way (except perhaps plugging the hardware into
the power socket) is its own reward.

This approach will give you a robust, replicated, and above all
reliable and safe system to work with.

I hope that this puts you on the right path. Although turning
replication on can seem such an easy thing, the practical application
can be somewhat more tricky. Start at the beginning, work step by step,
and use someone who has done this before to help you. If you follow
this I think you will be very happy.

I hope this helps

Cheers

The Frog.