Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Use 2 Databases or 1?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/06/2012 :  08:56:04  Show Profile  Reply with Quote

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.



Flowing Fount of Yak Knowledge

6065 Posts

Posted - 11/06/2012 :  16:17:07  Show Profile  Reply with Quote
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 Optimizer
Go to Top of Page

Starting Member

8 Posts

Posted - 11/29/2012 :  18:03:52  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000