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 |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2006-01-06 : 12:05:53
|
| Hi all,We're migrating MSSQL to its own dedicated server (at last!) and I wanted to get some input on design considerations.Environment:- dedicated MSSQL machine built as database server- production accounting system database- reporting database replicated from accounting database (transactional repl.)- databases for other applications-> other applications use their own data tables and views-> other applications read from reporting database with replicated dataQuestions:Can better performance be achieved by having tables for various applications in a single database, or by creating a database for each application containing only its tables? Could there be any benefit to creating dedicated named instances for demanding applications (especially if they must read from the reporting database in the above instance)?Thanks for your thoughts,Daniel |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-06 : 13:22:33
|
| I would go with a single instance of SQL Server, unless you have significantly more than 2GB of memory on the box. If not, the individual instances of SQL Server will be fighting each other for memory.Multiple databases vs. a single database gets you into questions about recovery, security, and ease of access, more often than scalability or performance. Technically, you could have a database for every table, but I doubt anyone would want to administer the permissions, let alone the backup/recovery strategies. That's forgetting for the moment any consideration of referential integrity ;-). |
 |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-01-06 : 14:35:18
|
| We have 3GB memory on the box.Since you don't see any real performance difference between distinct databases for applications or one database with many tables, I guess the decision comes down to the most logical design for the business or application.Here's what we will probably do (have to discuss with involved admins and developers first, of course):- leave accounting application its own dedicated database- leave reporting database (replication target for accounting data) dedicated- group other applications' objects logically in one or more databases- use naming conventions to signal object associations- consider using dedicated database for complex applicationsThanks for the feedback,Daniel |
|
|
|
|
|
|
|
|
question on design for performance?