What settings for a read database, and what settings for a write database?

Question

Hi,

I am implementing replication for a project I am developing, and would like to replicate changes in the Write database to the Read database.

While this isn't a problem, I want to tune one database for reading from, and the other to writing to, so they would have different settings.

Is there any resource/guide which will tell me what concepts to look into? I'm not looking for a how to guide (then again, at this level, these tasks are very involved to have guides to).

Thanks

Answer 1

Index your databases differently. You probably need different indexes (maybe fewer indexes) to suppor the process of writing to the Write database than you do with the read database. If an index is only used for reading, then leave it off the Write database.

I'm no expert on this, and my thinking might be fuzzy, but consider the hardware/memory/and even RAID configurations. I can't remember.... would one RAID configuration be more suited for writing and another for reading, or is that wrong...?

Answer 2

The most obvious difference will be the differing indexes required. Disk IO pattern will also be different but don't forget that the read database is also being written to by the replication procedure, you can't just optimise it completly for read. Other differences may also be evident in things like optimum memory configuration and the amount of CPU horespower the 2 servers require. Your first step will be to get some idea of the sort of workload each server will have to handle, and how much work. Although I haven't got any specific links, but the microsoft site does have several papers on sizing SQL server hardware. Once you know the workload the 2 servers will have to handle you should be able to use the same guide to size and get ideas for configuration of both of them.