Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmudway
Starting Member
1 Post |
 Posted - 2012-11-06 : 08:56:04
|
| Hi,I have a design question, which was a suggestion from someone and i am trying to follow up on...I currently have a single Database which can be highly active as it will be processing messages from a phone system, so it will have periods of being normal and also very busy.I have been thinking that i could divide my current Database into 2 Databases, to have one whos role would be to hold Historical data and the second to hold only Realtime data. The RealTime data thats no longer active would go into the History.What I wish to know is, the pros and cons? I still want both Databases to work together as they do currently, but the strategy would be that the Realtime Database will be always small enough to exist in Cache/RAM/etc and be as responsive as possible. And the Historical Database will grow as it needs.Thanks,Rich. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-06 : 16:17:07
|
| That is a big question and you haven't provided nearly enough details to make recommendations. Based on just what you've said I don't see any pros to the solution. Cons include: you'll need to create and manage a process that keeps your two DBs in the correct state - and that is not trivial. And that process itself can cause blocking, locking, and general contention issues.>>...the Realtime Database will be always small enough to exist in Cache/RAM/etc...I don't understand this statement. To what cache are you referring? And what specifically is loaded into this cache?Be One with the OptimizerTG |
 |
|
|
Trevinator
Starting Member
8 Posts |
Posted - 2012-11-29 : 18:03:52
|
| If you are not going to be any transactions on this database then the best thing for you to do would be to have two different databases. You are going to have all your realtime data in a database that all your tables are going to be MYISAM engine that is perfomcely the best (sorry i know that isnt a word but whatever). Then for keeping your history put all the tables into a merge engine that will keep record of all the tables that will also be easier for maintance as well. |
|
|
|
|
|
|
Use 2 Databases or 1?