Synchronized Versions

[Neil]

I'm running a SQL 7 db on a LAN in city A with other users accessing it from
another city (city B) over a WAN. Obviously the WAN users are getting much
slower access to the data. One thing I was wondering was if it was possible
to have two versions of the database, one on each server, and have them
automatically synchronize as users at each location make changes. Or, if
that's not possible, then at least have the city B users get any changes
that the city A users get as they are made.

Thanks for any input.

Neil

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

I'm running a SQL 7 db on a LAN in city A with other users accessing it
from another city (city B) over a WAN. Obviously the WAN users are
getting much slower access to the data. One thing I was wondering was if
it was possible to have two versions of the database, one on each
server, and have them automatically synchronize as users at each
location make changes. Or, if that's not possible, then at least have
the city B users get any changes that the city A users get as they are
made.

Since you included .replications, I leave it to the real experts for the
deep answer; I just give you the quick one.

Both options are possible, but double synchronize would require merge
replication, which I believe is more comples to set up, and may require
that you add guids to all table you need to replicate.

One-way replication by transactional replication is a simpler affair, but
still requires some consideration.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Neil]

Not sure what you mean by "Since you included .replications." Is there some
other way?

Re. one-way replication, if we were to go that route, even if simpler than
two-way, how complex is it?

Thanks,

Neil

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Neil (no****@nospam.net) writes:

I'm running a SQL 7 db on a LAN in city A with other users accessing it
from another city (city B) over a WAN. Obviously the WAN users are
getting much slower access to the data. One thing I was wondering was if
it was possible to have two versions of the database, one on each
server, and have them automatically synchronize as users at each
location make changes. Or, if that's not possible, then at least have
the city B users get any changes that the city A users get as they are
made.

Since you included .replications, I leave it to the real experts for the
deep answer; I just give you the quick one.

Both options are possible, but double synchronize would require merge
replication, which I believe is more comples to set up, and may require
that you add guids to all table you need to replicate.

One-way replication by transactional replication is a simpler affair, but
still requires some consideration.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

Not sure what you mean by "Since you included .replications." Is there
some other way?
You included the newsgroup microsoft.public.sqlserver.replication in
your post. This was what I referred to it. The people there knows this
a lot better than I do.
Re. one-way replication, if we were to go that route, even if simpler than
two-way, how complex is it?

Depends at little of how your database looks today. It was a looooong
time since I played with replication, and that was in SQL 6.5.

But there are some considerations for IDENTITY columns, also for some
constraints. Triggers that perform some form of cascaded updates could
also cause surprises.

But I believe at lot of this can be dealt with adding "NOT FOR REPLICATION"
in strategic places. You only have to find these places. :-)

One thing that definitely affects the complexity of the task is the
stability of your data model. If you are running a mature system where
the table definitions never changes, then this is a whole simpler,
because you don't need a strategy to deal with table changes.

Now, one thing to consider, is how the user on the remote end are going
to submit their updates. Not knowing your application, I don't have an
answer for this.

Setting up replication for a test should be a relatively easy affair.
There is a point-and-click GUI for this in Enterprise Manager. (At least
there was in 6.5, so I assume there is today as well!) Keep in mind
that there at least three parties involved in replication: Publisher,
Distributor and Subscriber. I think that in your case, it would probably
be OK to have the distribution on the same machine as the publisher,
although I think the usual recommendation is to have it on a separate
box.

By the way, if you have control over the client part of the application,
it is not impossible that rewriting the client can make significant
performance benefits for the people on the remote end. If the clients
sends lots of small SQL queries, rather than calling stored procedures
that returns all, then there is a lot of network traffic going on. Also,
if you can run with SET NOCOUNT ON, this can also improve performance.
The latter can actually be changed, by changing a server-wide configuration
option (so it would affect all applications). Many applications do not
care about the rowcounts for INSERT/UPDATE/DELETE, but some do, so I
cannot say for sure that you can do this trick.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Neil]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Neil (no****@nospam.net) writes:

Not sure what you mean by "Since you included .replications." Is there
some other way?
You included the newsgroup microsoft.public.sqlserver.replication in
your post. This was what I referred to it. The people there knows this
a lot better than I do.

Ah, I see. Thanks for clarifying. Thanks also for your notes below. They
were very helpful.

Neil

Re. one-way replication, if we were to go that route, even if simpler
than
two-way, how complex is it?

Depends at little of how your database looks today. It was a looooong
time since I played with replication, and that was in SQL 6.5.

But there are some considerations for IDENTITY columns, also for some
constraints. Triggers that perform some form of cascaded updates could
also cause surprises.

But I believe at lot of this can be dealt with adding "NOT FOR
REPLICATION"
in strategic places. You only have to find these places. :-)

One thing that definitely affects the complexity of the task is the
stability of your data model. If you are running a mature system where
the table definitions never changes, then this is a whole simpler,
because you don't need a strategy to deal with table changes.

Now, one thing to consider, is how the user on the remote end are going
to submit their updates. Not knowing your application, I don't have an
answer for this.

Setting up replication for a test should be a relatively easy affair.
There is a point-and-click GUI for this in Enterprise Manager. (At least
there was in 6.5, so I assume there is today as well!) Keep in mind
that there at least three parties involved in replication: Publisher,
Distributor and Subscriber. I think that in your case, it would probably
be OK to have the distribution on the same machine as the publisher,
although I think the usual recommendation is to have it on a separate
box.

By the way, if you have control over the client part of the application,
it is not impossible that rewriting the client can make significant
performance benefits for the people on the remote end. If the clients
sends lots of small SQL queries, rather than calling stored procedures
that returns all, then there is a lot of network traffic going on. Also,
if you can run with SET NOCOUNT ON, this can also improve performance.
The latter can actually be changed, by changing a server-wide
configuration
option (so it would affect all applications). Many applications do not
care about the rowcounts for INSERT/UPDATE/DELETE, but some do, so I
cannot say for sure that you can do this trick.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

I think that there are several third-party products for this. Look up
Red Gate, if I remember correctly. Never re-invent the wheel.