Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evalua...replication.asp
Steve