sql server replication: I have the following situation:

Clients connect to a SQL Server which is located elsewhere on a leased line.
Now when this line fails no more reading or writing is possible anymore. To
avoid this I thought it might be possible to put a SQL Server locally on the
site and implement replication.

Now the question:

- I am able to program the client so that it connects to the main server but
when this fails it will popup a msg and connect to the local server. I
guess it's probably pretty easy to read from this database (the local one).
But is it also possible to write to it and make it so that once the line
comes back online this server automatically saves the changes to the main
server?
- Is it possible to deny reading/writing to the local server as long as it
has a connection with the main server?


Thanks in advance,

Stijn Verrept.

Hello,

You can accomplish what you are requesting by using SQL Server Merge
Replication. Merge replication allows both the publsiher and the
subscriber to make updates and doesn't depend on constant connection
between the servers. Please read the BOL "Merge Replication - How Merge
Replicatin Works" . You might also want to look at "Queued Updating" and
use it with the Merge Replication. You will also need to talk to your
developers because an extra column is added to any tables that you
replicate using Merge Replication. The other part is you will need to talk
to the developers concerning failover. They will have to design their
application to read from whatever server is active so they will have an
issue with creating a way to point their application to the correct server.


thanks