Hullo list, I'm new.



I set up replication on a production server on Monday, and it went badly
wrong, and I don't understand why ... Ideas?



The requirement :

Provide a near-real-time backup of a critical database, to enable near
continuity of the application in case of server failure.



The environment :

Production server : 2 processors PIII 700 with 1 Gb memory

Backup server, 2 processeurs PIII 1266 with 512 Mb memory

Both NT4 SP6a, and SS 7 SP2.



The method :

One-way Transactional replication (no « two phase commit ») with push
subscriptions - Distribution agent on subscriber.

The result :

The initial snapshot replication ran OK. Then, when the transactional
replication kicked in, no significant increase in CPU use, memory, IO etc.,
but severe degradation of application response times (number of connections
increases rapidly because web page transactions never finish, and eventually
time out)



Return to normal required not only stopping subscriptions, but removing the
publication. (after an hour)



The only metric that was significantly abnormal during the hour the
replication was in place, was "KeyLockAvgWaitTime" - generally under 50 ms,
went up to 8000 plus (over 8 seconds).



What is going on here??



Alistair Connor

Ext/Astek

DBA Sql Server

tél 598131



MS Sql Server LazyDBA home page