Any SQL 2005 Gurus' around?

Soldato
Joined
3 Apr 2007
Posts
7,286
Location
South of the Watford Gap!
We have 2 SQL 2005 servers with push transactional replication to keep a database on the secondary synced in the event of the primary going down. There are 2 main problems with the system:

1) Replication activity seems to swamp the primary server, I've had a look at the Agent profiles and they are all set to a PollingInterval of 10 seconds, changing this to say a minute does quieten the server down. Now the customer has a requirement which allows only half an hour of data loss in the event that a server goes down, would I be OK in changing this value to say 5 or even 10 minutes?

2) Due to the way the customer uses the database they may have inactivity of say 3-5 days, the current replications get deactivated and I've tracked it back to what's referred to as the retention period which is set to 72 hours. Now there is also a publication expires value which defaults to 14 days, would I be OK to change the retention period to this same 14 days?

Lastly, I've mentioned the PollingInterval above but is there a way to configure replication to say only replicate every half an hour as this would meet the requirements of the customer.
 
Thanks for the reply DJ XS

I've had a look at LogShipping but the customer demanded replication unfortunately.

I've found some tweaks for the Agent profiles which seem to help, namely:

set HistoryVerboseLevel to either 1 or 0 to minimise the logging of SQL Agent, errors will still be logged.
set MaxBcpThreads to 2 to make use of multi-processor environments
set PollingInterval from the default of 5 to 60 seconds to stop the various agents from drowning the server with read requests every 5 seconds.
set CommitBatchSize from 100 to 250 which again lengthens the time between Log reader commits

And lastly set the replication to half hour intervals rather then continous which seems to the default and again can drown the server.
 
Back
Top Bottom