MERGE AND TRANSACTIONAL REPLICATION SUPPORT

We currently have a web application (using ASP for the front-end and SQL 2000
for the back-end dB). The site is accessed from several locations around the
world. Response is very slow for people in Europe when the server is located
in the US. We have now been tasked to deploy several servers at each
location and implement SQL replication )merge or trans?). I am not sure how
much that is done out in the industry and there is just not a lot of
documentation, either from MS or elsewhere, about how to do it. We have some
of the books that show you how to set up the servers but we are looking for
more detailed instructions and case studies on what the best practice are.
Before we get started we would like to learn about people who have actually
done this and find documentation to help us implement it. I have setup a lab
with two Windows 2003 servers and SQL in each. Thank you.
Edgar
saic_edgar@.yahoo.com
Paul,
Thank you for your suggestions including the books that will soon become
available. I plan to buy them as soon as they become available. I was
concerned why there is so little material on this subject when there are
hundreds of book when using Oracle for replication. I know MS is new at this
but we are hoping it will work well.
I am not sure I can answer your questions, but here is more details on what
we need.
We expect to have 4 servers located on 4 geographical areas. Each will
start with the same snapshot and each will be used to make updates. All
updates need to replicate to keep all servers in sync. Transactional may not
work since the WAN links connecting the servers may be too slow at times. I
have to do some reading on "the republisher replication model, or merge
replication
with alternative synchronization partners" you suggested to understand how
exactly it will work. My function in the team is as SA/DBA and I do not do
any programming. That will be done by 3 other SQL programmers. The ideal
solution would be to have people in each of the 4 locations use the
application in their local server, and periodically, maybe every two hours or
so, trigger a merge replication so that all servers are kept in sync. All
servers must be available at all times if possible.
We have been using SQL 200 for a long time but at a very low level so we
will need a lot of guidance in the are of getting replication going and
maintaining it, including conflict resolution. What methods of conflict
resolution do you suggest?
Thanks for all your help.
Edgar
"Edgar" wrote:

> We currently have a web application (using ASP for the front-end and SQL 2000
> for the back-end dB). The site is accessed from several locations around the
> world. Response is very slow for people in Europe when the server is located
> in the US. We have now been tasked to deploy several servers at each
> location and implement SQL replication )merge or trans?). I am not sure how
> much that is done out in the industry and there is just not a lot of
> documentation, either from MS or elsewhere, about how to do it. We have some
> of the books that show you how to set up the servers but we are looking for
> more detailed instructions and case studies on what the best practice are.
> Before we get started we would like to learn about people who have actually
> done this and find documentation to help us implement it. I have setup a lab
> with two Windows 2003 servers and SQL in each. Thank you.
> --
> Edgar
> saic_edgar@.yahoo.com
|||Edgar,
almost always the default has been suitable for me. This
is where the publisher wins against a subscriber and if 2
subscribers conflict, the first subscriber gets the
priority of the publisher. I've used global once where
the subscribers have priorities, but you only need one
person with a high priority who has a big gap before he
synchronizes for this to become a problem. From the other
ones I've looked at 'Subscriber Always Wins Conflict
Resolver' and custom stored procedure resolver. For the
main ones there's plenty of info in BOL. For the others
my impression from this newsgroup is that they have been
used only occasionally and in specialized cases.
Regards,
Paul Ibison

Memory Running Out

Hi-
We've got an ASP.Net web app that runs off a Microsoft SQL Server
2000 backend. After a few days the SQL server is completely out of
memory and crawls. It looks like there could be some connections that
aren't being closed or something. Is there a good way to figure out
where the problem is. Looking at the current activity in Enterprise
Manager there are a lot of threads sleeping and a few that are
runnable. Any ideas?

Thanksbig DWK (daveGoogle@.davewking.com) writes:
> We've got an ASP.Net web app that runs off a Microsoft SQL Server
> 2000 backend. After a few days the SQL server is completely out of
> memory and crawls. It looks like there could be some connections that
> aren't being closed or something. Is there a good way to figure out
> where the problem is. Looking at the current activity in Enterprise
> Manager there are a lot of threads sleeping and a few that are
> runnable. Any ideas?

Note that it's perfectly normal for SQL Server to grab as much memory
as possible, since it uses it for cache. So high memory consumption is
not a sympton of a problem in itself. But if you feel that you have
poor performance, then obviously you have something that needs fixing.

As for connections not being closed, again, this may be a non-issue.
Recall that ADO .Net uses connection pooling, and when the application
closes the connection, the connection hangs around for some 60 seconds.
If you use sp_who2, there is a LastBatch column, if there are idle
processes whose LastBatch is hours or even days ago, then there is
something that should be addressed. This could indeeed be due to failing
to close the connection in the ASP .Net code. You should always close
your commands and connections explicitly, and not rely on garbage
collection.

However, is SQL Server is "crawling" this may be due to problems with
poorly written queries and that sort of thing. Running the SQL Profiler
is a way to track down long-running queries. I often look at these sort
of problems with my own procedure aba_lockinfo, which gathers both lock
information and current statement in a snapshot. Look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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