Very Computer

Publisher: WIN2k (sp2) SQL 2000 Standard (sp2) 4 processor 4GB RAM
Publisher Database: 5GB, largest table = 6 million rows
180 Subscribers: WIN2k (sp2) laptops MSDE 2000 (sp2)
28 Merge Publications: 120 Tables, 8 tables with static filters, 20
tables
with join filters, max joins per filter = 3
Network: About 500 separate VPN connections distributed throughout the
state, Mostly T1, some 512mb/s and 256mb/s
Average Sync times: 20 min to 3 hours depending time between synchs.
Average Merged Changes/synch: 20,000 to 250,000
Max time between synchs: 14 days
Synch Method: Custom VB app using the MSMerge object

The SQL Server is part of a Web site with a Web Server, an Application
Server, and the transactional SQL Server (OLTP). There is a data
warehouse that has transactional replication (one way) running with
the OLTP SQL Server as the publisher.

In production 13 days

We need to distribute the load on the OLTP database, primarily with
the Laptop Merge Replication.  We synchronize mostly at night, but
with network downtime, batch and backup jobs, snapshot jobs etc, we
are running out of time to work with. If more then 3 laptops
synchronize during business hours, the Web site becomes very
vulnerable to long lock wait times and deadlocks.

My basic strategy would be to deploy a new SQL Server solely for
Laptop Merge Replication.  I would have all the laptops synchronize to
this server.  This server would have the 28 filtered publications.
The OLTP server would then hold a single unfiltered Merger publication
with the new SQL Server as the sole subscriber.  I can't use two-way
transactional replication between the servers due to conflicts.

My question is, how much resources on the OLTP server will this single
subscription consume?  How often should I synchronize between the two
servers (after they synch the laptop, they go to the web site to print
checks)?  Is there a better way of distributing the merge replication
load then this?

OK, first off, you aren't running a data warehouse.  The fact that you are
processing transactions directly into it violates every design principle and
characteristic of a warehouse.

Now, merge does it's best work within hierarchies.  This allows you to not
only distribute the data, but also distribute the load.  The machine
everyone connects to right would remain the publisher.  Place a second
machine downstream from it as the subscriber.  Now, reconfigure all of your
laptops to point at this machine as their new publisher.  As your laptop
load increases, you can add a 2nd, 3rd, etc. machine at this level.  Now,
you have a latency issue on the data.  Your users should point at their
publisher for the reporting they are doing and not at your live website.
This mitigates any data latency.  Why?  Because if they immediately hit your
website after uploading, their data will most likely not be there yet.

--

Mike
SQL Server MVP
http://www.mssqlserver.com

First of all, the transactional replication to the data warehouse is
used as a lightweight method of creating a concurrent copy of the OLTP
database to the data warehouse server.  Once this copy is on this
server, the OLAP database (a different database on that server) can do
denormalization and build fact tables for Analysis Services.  Other then
the log reader, the OLTP resources are not consumed in processing the
OLAP system.

When I build the merge replication hierarchy, I assume I make the
downstream server subscription a single database without any static or
join filters.  The publications for the laptops would then partition the
data with the publications I currently have.  How often do I synchronize
the two servers?  I if run it continuously, how much resources and
locking will I put on the web site SQL Server?  If I synchronize once a
night (assuming latency is not a problem), will I have enough time at
night to leave room for batch jobs and backups?

Is there anyway of doing this without reinitializing the laptops?  If I

primary keys with clustered indexes.  How much of a benefit will this
give me?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

First of all, the transactional replication to the data warehouse is
used as a lightweight method of creating a concurrent copy of the OLTP
database to the data warehouse server. Once this copy is on this
server, the OLAP database (a different database on that server) can do
denormalization and build fact tables for Analysis Services. Other then
the log reader, the OLTP resources are not consumed in processing the
OLAP system.

When I build the merge replication hierarchy, I assume I make the
downstream server subscription a single database without any static or
join filters. The publications for the laptops would then partition the
data with the publications I currently have. How often do I synchronize
the two servers? I if run it continuously, how much resources and
locking will I put on the web site SQL Server? If I synchronize once a
night (assuming latency is not a problem), will I have enough time at
night to leave room for batch jobs and backups?

Is there anyway of doing this without reinitializing the laptops? If I

primary keys with clustered indexes. How much of a benefit will this
give me?

Yes.  You leave the database server backending your website as it is, but
drop your publication on it.  You create a publication on this server that
includes everything with no filtering.  You add a second machine downstream
from it that is the subscriber.  On this machine you recreate the
partitioned publication you are currently using.  The laptops then will
connect to this machine.  You have to reinitialize everything, because the
laptops now have a never publisher.  Once this second server is in though,
you can scale all you want.

I would try it first with continuous.  You can always throttle it back.  You
will probably be surpised.  You aren't killing your database server right
now during the subscriber uploads just because of replication.  You are
killing it because of all of the heavy filtering you are doing within that
publication.

--

Mike
SQL Server MVP
http://www.mssqlserver.com

Thank you very much.

On another note, I now have close to 3 million records in
MSmerge_contents now.  I have all the retention periods on all my
publications set to 14, but the record count continues to grow.  I do
have one publication on the database with the retention period set to
infinity, but this is on a publication in which all the articles are
stored procedure and functions, no data is in this publication.  Im
tempted to set this to 14 days as well, but this will download
procedures to the laptops on a 2 week basic and not when we what to
propagate the changes to the laptops.

The biggest problem we are having now is the duration of the snapshot
agents, specifically the bulk copy of MSmerge_contents.

SQL 2000 has the procedure sp_mergecleanupmetadata, but this requires
all the subscriptions to be reinitialized.  With close to 200 laptops,
this would not be reasonable.

Your article in mssqlserver.com has a script for sp__mergepurgemetadata.
The article seams to be written for SQL 7, but it seams pretty straight
forward basically deleting old records for MSmerge_contents and
MSmerge_history.  

It seams the sp__mergepurgemetadata will do what Im looking for, but is
there any risks running this SQL 2000?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nope.  The same process works in 7.0 and 2000.  You are simply purging data
based on a date range that you specify and control the execution on.

--

Mike
SQL Server MVP
http://www.mssqlserver.com