3 MS SQL 2000 Replication Hi all, I am wanting to hear from some more enlightened people and I thought that here would be a good place to ask. At work we are running into performance issues and are implementing several "fixes", but I would like to get the opinions of those that are more expreienced. Here is our production database setup. <pre> (domain controller 1) (DC 2) /\ /\ WEB 1 -----> SWITCH 1(100mb) -----| | ---------------| /\ \/ | | WEB 2---------| SWITCH 2(100mb) | | | | | | _________|______________|__________ | Pro|duction DB Clu|ster | | | | | | \/ \/ | | DB1 <-----> DB2 | | | | | |_________|______________|________| | | | __________| \|/ \|/ DISK ARRAY </pre> The problem we are having is reports they are sucking. So here are the changes we are making <pre> (domain controller 1) (DC 2) /\ /\ WEB 1 -----> SWITCH 1(100mb) -----| | ---------------| /\ \/ | | WEB 2---------| SWITCH 2(GIGABIT)======GBcables=|<-------------| |G |G | | |B |B | | |cable |cable | | _________|______________|__________ | | | Pro|duction DB Clu|ster | | | | | | | | | | \/ GBcable \/ | ____\/_______ ____\/_____ | DB1 <-----> DB2 | | | | | | | | | | Reports DB | |BATCH DB | |_________|______________|________| |____________| |__________| | possible | | | GBcables___| | \|/ \|/ | DISK ARRAY <====GB cables====| </pre> OK bear with me while I describe what we are thinking of doing and maybe someone more expreienced can give us a better way of doing it. Currently we are CPU/Disk bound on our Production cluster. Things are getting locked and reports are getting blocked. We have bought a new dell poweredge 2650 with dual 2.8ghz xeon processors that's the reports db server you see there. We are also going to upgrade to gigabit ethernet behind that second switch (it's going to be a giga switch too) The batch is not as good but it doesn't need to be. Lets work focus on that reports db. We are going to implement replication between the cluster and the reports db. It's going to be transactional with a real time subscription (NOT immediate updating nor queued updating...the reports will never need to update the prod database cluster). we have replication running on our test and dev setups and all is well so we are now looking for optimizing tips before we push this to production. Can anyone give us some tips and tricks to get the most out of this as well as some setup things that we may be missing or overlooking? Thanks!
