Replication strategy | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Replication strategy

Hi, I’m fairly new to replication/warehousing so I’m seeking for help in determining which strategy I should choose for this busincess case: We have a production SQL database which supports a COTS product called McAfee ePolicy Orchestrator. Basically it "sniffs" all workstations on the WAN to check for anti-virus versions, updates them as required, logs some activities, etc.. The problem is when they run some reports off the ePO partition, the SQL Server performance takes a huge hit. Therefore, I’d like to divide the reporting from the live DB onto another server. The issue I have is that our clients want to keep 30 days worth of data on the live DB and have all historical data sitting on the "reporting" server. They want to see how many viruses have hit the department this current year in comparison with last year, etc.. So what would be the better strategy.. Should I opt for Analysis Services? DTS packages? Log Shipping? Well this last option is probably not a good one unless there’s a trick not to allow deletes on the reporting DB in order to keep the historical data. What would you suggest? Thanks in advance for your help!
Hi ya, this might be a question for Network Associates…? how big is your ePO database? our one here is 225MB, has been running for about 7 months with no data deletion, and haven’t had any issues with performance, although we’ve put our management databases (like SMS and ePO) onto its own database server…? Users should also be able to run the reports remotely so that it is only the query which runs on the server, and all of the other processing is done on the client… that may perhaps help? or are you saying that the SQL Server is havin gproblems processing the queries to be able to get the report data? Cheers
Hi Twan, Our ePO database is around 20GB. We have over 30,000 workstations on the WAN. We’re a fairly large government department. So I’m not surprised to see performance problems when ePO has to manage all these workstations and respond to reporting requests at the same time. I thought replicating the DB (and keep the historical data) onto another server would help with reporting activities and reduce the overhead on the live ePO database.
Hi ya,<br /><br />hmm ok, that is getting fairly large <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> <br /><br />1. have you thought about checking how well tuned the ePO database is? it may just be a matter of additional indexes?<br /><br />2. to replicate this, I’m guess will be a custom-written process… you’d have to write procs which will copy the data to a reporting database, and once that is done, then use the event deletion functions of ePO to kull the prod database<br /><br />Cheers<br />Twan