sql server replication: I currently have one SQL server running locally serving 15 people and
several MS Access, VB, and Web applications.

We just recently merged with another company 1100 miles away with 30 plus
users. They want to start using the same applications and have the same
response times we have locally.

I was thinking about putting a second SQL server in their location so the
applicaitons would have fast responses and have the servers server the exact
database. I would like the servers to stay in synch real time, thinking
that they would only have to send "Changed" data between them. Is this
possible? Is is easy? Any hurdles?

Other solutions?


Thanks,

Rog

Yes, it is possible. The best way of doing this however is using terminal
server or citrix.With these solutions your remote sites will run the app on
the central server and all that will travel back and forth are screen
refreshes.

However you can also use replication.

The problem is trying to understand your data flow.

Does the data only flow to the central server from the branch? Then use
transactional replication.

Is there anything in the data to allow you to tell which location owned the
data or where it originated from? If so your life is easy, it not you may
end up with primary key collisions or other conflicts. If there is something
about your data which allows you to tell where it comes from you can do
partitioning or filtering based on this key.

If not, you will probably have to implement some partitioning scheme
otherwise you might get two simultaneous orders with the same order id, and
it becomes hard to figure out which site it came from. Consider a case where
you get two order id's of 1. These orders will hit the branch locations and
be committed there, but when they are replicated to the central server you
will get a primary key collision. Also consider the case where you have two
simultaneous order id's of 1 on the remotes, but you have only one on the
central location. If you update the branch office for this order id with a
different phone number, this phone number will end up on the central
location.

If your data flows bi-directionally again you should try some partitioning
scheme. Merge replication or bi-directional transactional replication will
work here as well.

The problem with bi-directional transactional replication is that it doesn't
tolerate schema changes well.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
[quoted text, click to view]