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 |
|
Blade_316
Starting Member
5 Posts |
 Posted - 2007-07-24 : 12:18:31
|
| We have a system with multiple modules, for each module we have a separate database. Is it better to have multiple databases or have multiple schemas. Performance, advantages, disadvantages...When is too much too much, 10-25-50-100 Schemas???Course.dbo.table1Program.dbo.table1vs.NEWDB.Course.table1NEWDB.Program.table1Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-24 : 12:29:53
|
| If the data needs to be synchronised after a restoer then it should probably be in a single database.If not then it's up to you - often the choice is just on ease of administration.Nothing to stop you using a different schema even if it is in different databases then it's easy to move.Also you can use views to pretend the tables are in the same database when you've moved some out.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-24 : 12:36:27
|
one reason to use separate databases would be if you want to separate the load by having them on different servers. if everything is in one db, it would be more difficult to move stuff around.EDIT: note that if objects in your different dbs refer to each other, then it's a not-so-great idea to put them on different servers, as you'll have cross-server joins, etc, which are to be avoided.
elsasoft.org |
|
|
|
Blade_316
Starting Member
5 Posts |
Posted - 2007-07-25 : 10:56:49
|
| Alot of our DB's have joins between them, that is why I was debating whether or not to merge them into a single DB or keep them seperate. Will there be a performance gain, drop or no difference at all by merging them. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-25 : 12:19:06
|
| I think the most important thing to look at is how using multiple databases complicates your recovery process. Keeping multiple databases in sync is near impossible, whereas if all data was in a single database the recovery is easy and always consistent.-ec |
|
|
|
Blade_316
Starting Member
5 Posts |
Posted - 2007-07-25 : 12:58:44
|
| Yeah, that's pretty much what I figured. We are in the process of upgrading a small application that became very very big overnight and keeps on growing. We're in our first phase of reviewing key things to improve the system. This will probably fall into a second phase. The thing I disliked the most about multiple DB's was the restore/recovery process. So this would really easy up on these tasks.Thanks guys |
|
|
|
|
|
|
|
|
Multiple Database vs. Schemas