1. create the transactional pub via SSMS GUI

2. create the transactional sub (with the same push/pull that you want) via SSMS GUI

3. generate scripts for same

4. edit and prettify scripts...

in SQL200x products replication is FREE (unlike competing vendors)

exceptions

1. if doing Oracle-SQL replication you would need SQL2005 Enterprise Edition

2. SQL/Express and SQLCE can be subscribers (not publisher or distribution engines)

BTW,...

can't check everything? must check everything? bug-free software? hmm !

forgot to answer Berto's last question - yes you can replicate from SQL2005 to SQL2000

there are some gotchas if trying to...

the LogReader (LR) reads from the AppDb tranlog (.LDF) and writes to the distribution db

if these App team changes are still going in, the supposition must be some blocking and/or...

Berto, you have said that there is a query that can max out your CPU for extended period. I am presuming this is in minority and that all other queries...

Barry raises good question ...

simplistically there are these processes on the Distributor

1. LogReader [reads from PUB.srcdb tranlog, and writes to DIST.distribution]

2. Distribution Agent (DA) [reads from distribution, and writes to...

1. local disks are fast

2. repl does row-at-once (lotsa round trips)

3. networks are slow

4. people send huge # of rows in one transactions

5. some subs are way over-indexed (but reporting...

1. using INT IDENTITY ... gives 2 ** 32 potential values (it's a lot!)

2. having long network latencies can mean that small allocations can be eaten up too quickly and...

I agree replication is wrong and backup(PROD)-restore(TEST) is correct, but consider ...

1. the disk geometry on PROD (data=E:, tlog=F:, dumps=G:) may differ from TEST (data=tlog=D:)

2. the maintplan on PROD probably...

you should investigate what the problem is

LogReader [reads from pub db and writes to distribution db]

Distribution Agent (DA) [reads from distribution and applies to sub db]

from what you have said,...

1. most PROD db's will be Recovery=FULL, probably with Log-Shipping to Disaster Recovery (D/R)

2. a 100% reload (500,000 x DELETE plus 500,000 INSERT) will be expensive

3. transactional repl (or...

1. as you do I/U/D statements a db will incur fragmentation [akin to breathing]

2. replication is chronological replay of what happened at pub on each sub

-> hence you...

replication works great apart from user fiddling !

left alone, the replication Distribution Agent (DA) plods along happily and replays I/U/D statements [via synthetic sprocs] and all is wonderful.

But then a...

the replication Distribution Agent (DA) has to faithfully "replay" at the sub whatever happened on the pub. This means it has to do a BEGIN TRAN ... stuff ... COMMIT...

1. it IS necessary to have a PK for transactional/merge replication

2. it is NOT necessary to have a PK for Snapshot repl

as Lowell rightly said, the Distribution Agent (DA) needs...