Any SQL 2005 Gurus' around?

[IAmATeaf]

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.

 

[IAmATeaf]

Nobody able to help?

 

[DJ XS]

Hi there,

I hope this information helps as i've been looking at similar stuff recently.

Basically Replication kills your SQL box too much and will cause performance issues for the users.

Mirroring works pretty well but LogShipping is the way to go, you can push the transactions log every 5/10/15 minutes and is very stable with no performance problems.

If your primary SQL server goes down you can simply recover the secondary DB and allow the users/application to run live on it.

 

[IAmATeaf]

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.