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 |
|
gnobber
Starting Member
11 Posts |
 Posted - 2008-06-06 : 02:47:05
|
| Hi everyone,Im having a hard time deciding what approach should I take. The scenario is this: I have developed various systems (inventory, HR, accounting, etc.). All this systems are (and should be) tightly integrated with one another. At present, for all these systems, i've used a single DB prefixing the tables with the systems name (eg. Inventory.Items).My question is: did I did the right (and practical) thing? Or should I create a DB for each system to organize them? The problem with multiple DBs is some system uses the other system's table(s). Example, if i created a separate DB for accounting, and a separated DB for inventory, and another for HR, how am I going to relate inventory and HR's accounts to the accounting DB's table? I want a single instance for each table; I don't want to create another account table for inventory or HR so I can enforce integrity. And if different DBs, is there a performance impact on this?Or is there another way? My concern is performance and manageability. Please help. Thanks! |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-06-06 : 04:49:23
|
| I'd say it depends. Are you facing any performance issues or are you likely to see them in the forseeable future? Moving parts of a system to a different database doesn't increase performance much by itself, unless the other database is on a different server but then you'll face some replication problems that you'd have to overcome. Naturally it's always good to create a scalable solution from the beginning (and it seems you have had this in mind) but an easier solution could be to create an additional filegroup for each different "system" (inventory, HR, accounting, etc.). Then you can place these on different disks and thus increase performance quite substantially but still keeping them all in the same logical database. You can also place indexes on one disk (filegroup), data files on another and log files on a third. There are many possibilities...but unless your different systems are able to work independently I for one would keep them all in the same database.--Lumbago |
 |
|
|
gnobber
Starting Member
11 Posts |
Posted - 2008-06-06 : 11:36:24
|
| Hi Lumbago,Thanks for the reply. At present, I dont see any performance issues at my setup. Yes, the system can work independently but they share a common database. Im gonna do some research about additional file groups since you mentioned it is an option. Thanks for the reply. At least I got an opinion that there's nothing wrong with what im doing. Thanks again! |
|
|
|
|
|
|
|
|
Single or multiple DB for different systems?