storing parent child table on different databases but same server
i have a server with two hard disks i have two enormous tables with millions of transactions hourly customers table and transactions table was thinking what if i stored customer on database customers and transaction on trans database and placed each db on a drive but on same instance of SQL server will that improve the performance what i'm worried about is the reporting i need to join the two tables when generating reports joining two tables from different databases but on same server same instance i'm using sql server 2008 R2 Enterprise edition i need your opinions and feedback please
EDIT I totally agree with Thomas Rushton, about recovery being a good reason for keeping one database. I also want to mention that if you have a logical relation between customers and transactions, it could (would) be wise to also put constraints on that logical relation, so you don't mess up the consistency between customers and transactions. END EDIT Instead of putting them on two databases, I suggest you setup a new filegroup on a different disk than your current filegroup (if disk I/O is your bottleneck), and move one of the tables to the new filegroup. Adding row level compression will also speed up things a bit (and save you some disk space). You could also consider partitioning your transactions table to speed up some queries. The later is in no way a golden bullet to solve performance problems, but if your queries against the transactions table are normally within a given date-range, you will benefit from having the table partitioned on date. Remember that partitioning is an eneterprise feature. If you are using standard edition, that's not an option. Neither is row level compression an option on standard edition.
Another reason for keeping it in one database is recovery. In the event of a failure, and you do a restore of the system, at least you'll only have one database which will be in a transactionally-consistent state. If your data is stored in two databases, you can be pretty sure that you won't have that consistency.