sql server replication: Hello all,

I'm trying to figure out what type of replication I should use for this
scenario.

1. I've got a table of about 50,000 records that I want to replicate to
a remote database.
2. The database will need to be in a production state during the
transfer of data so that our customers can continue to use our web site.
3. The replication will only occur at scheduled times maybe once to
twice a week.
4. The replication will take place between 9pm and midnight as this is a
time when there are fewer people using our web site.
5. The data on the local server will always be the primary set of data,
meaning that the local copy of the table is the master copy and should
always overwrite the remote table.
6. I may also choose to only copy records marked as "live" which would
mean the data being copied would only be around 10,000 records.
7. The local office is connected to the internet with a T1 line so we
have a relatively fast connection to use for the replication.

Is this enough information to tell me what sort of replication would be
best? My original idea what to use snapshot replication, but I’m not
sure if this will cause the server to be unusable during the replication
process. Will the web site and server be operational during snapshot
replication? I noticed that merge replication required guid columns be
added to the table, this I don't want to do as it will increase the size
of the table. My thought about transactional replication is that I don’t
want the changes made on a continual bases, I only want all the changes
in one table made at once.

Given these thoughts what is the best solution?

Regards,

I would use transaciotnal. You can select to have the
snapshot made using the concurrency option so the tables
are not locked during the snapshot generation. The
distribution agent can be run whenever you want, so the
changes can be left to accumulate as you require.
Additionally, as you don't have updating subscribers,
there will be no changes made to your table schema.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[quoted text, click to view]

Paul,

Thank you for the answer. This sounds like what I need.

Do you have any tips on setting this up. I am sure I can find enough
information on the net to do the setup, but usually the docs and
articles don't say to much about problems and things to watch out for.

[quoted text, click to view]

Paul,

Where in the setup for transaction replication will I see the
"Concurrency Option"?

On hte snapshot tab, half way down there's the option
for 'Concurrent access during snapshot generation'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Mostly it's straightforward. In your case you want the
changes to accumulate for a while, so the extra things to
change are:
(a) on the publication's general tab the subscription
expiration date
(b) on the distributor properties both the transaction
retention and the history retention
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[quoted text, click to view]

Paul,

I'm setting this up and am not sure what to set the values of A and B
that you talk about to. What would be an appropriate setting if I want
to puch the subscription to the subscriber once a week?

It's really upto you. The longer you keep the
transactions the bigger the distribution database'll get
The distribution cleanup agent will only remove the
transactions once all subscribers have read them or it
reaches this cutoff point. If you can be sure the
subscribers will synchronize once a week, I'd set the
cutoff to 10 days to be safe. Remember that if they don't
synchronize within 10 days of their last synchronization
point they'll be forced to reinitialize.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[quoted text, click to view]

I have this all setup and working now as an immediate updating
subscriber. When I make changes on my publisher the get distributed to
the subscriber immediately. I did this just to make sure the setup was
working. Now that I know it is working how can I change it back to once
a day or once a week. I tried changing the subscription schedule, but
the remote database is still being updated as I make changes on the
local database.

Do I have to reinitialize the publication?

All you need to do is change the schedule of the
distribution agent. The changes will accumulate in
MSrepl_commands and MSrepl_transactions in the
distribution database and won't be removed by the
distribution cleanup agent as your increased retention
period will prevent this.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[quoted text, click to view]

Paul,

Thank you so much for all your help. I'm starting to get this working. I
am still having some problems and have started a new post. If you would,
please take a look.