We design websites for Realty companies. The websites are used only to display the information and all the websites share a common template. We have around 150 websites for different customers. Some third party data providers, provide us all the updates about each listing on the website on hourly basis. The updates for each customer happens during separate hours. On average we have 1000 listings per website. And on every hourly update 80% of the data is changed or updated.
Right now we have a single database in Sql server 2008, for all the customers(Designed initially to cater 10-20 websites). The tables in the database are shared by all. The problem is whenever an update happens, it slows down other customers websites also, that are not at all related to the update. Also deleting a customers data, slows down all the sites.
I am planning to remodel the database by creating a separate schema for each customer, but I am not sure if it is the best way to handle our problem. Having a separate database creates many problems for maintenance(Backups, mirroring etc). Can anyone suggest me a better way to handle this issue. I am not sure how it affects the performance, if I create a separate schema for each customer and isolate their tables form others. Or is there any better solution?
Best Answer
My personal preference would be for multiple databases as it allows you to see which databases use the most IO, and RAM, with a variety of DMVs - and it allows you to easily migrate the biggest databases off elsewhere down the line. Additionally, there's a security separation which is always reassuring to clients.
Brent Ozar covered this very issue in a recent blog post - definitely worth a read as he's very good: http://www.brentozar.com/archive/2011/06/how-design-multiclient-databases/