One database or many - which is better?

[Sailor]

In the past I've had multiple Access .mdb files for my various databases on a data-driven site. It made it easier when I would have to upload a new db after making changes.

I just switched my main db to SQL server, and I wonder if there's any performance or other reason to move all of my tables into one big single databases, or keep them as separate SQL Server db? Is one big db faster? Safer? It would certainly make connection strings simpler to manage.

thanks

 

[hafa]

In the past I've had multiple Access .mdb files for my various databases on a data-driven site. It made it easier when I would have to upload a new db after making changes.

I just switched my main db to SQL server, and I wonder if there's any performance or other reason to move all of my tables into one big single databases, or keep them as separate SQL Server db? Is one big db faster? Safer? It would certainly make connection strings simpler to manage.

thanks

Multiple databases means multiple connections, which cannot help performance. Given that MSSQL server handles several hundred databases simultaneously with no problems, one large database should work just fine and will, as you noted, make maintenance much easier.

 

[01jodo]

hi,

I don't agress with this, I think one little database will more quick than the big one, if you can use one database about 70%, another is 30%, why not use separatly.

 

[hafa]

hi,

I don't agress with this, I think one little database will more quick than the big one, if you can use one database about 70%, another is 30%, why not use separatly.

MSSQL is designed and optimized for high I/O; datastreams via http are far slower than internal processing provided by MSSQL (this is why we use stored procedures rather than submitting queries via http). Furthermore, proper relational design with appropriate indexing and keys will create an optimized environment which will easily out-perform any kind of distributed solution.

Load distribution for databases is not like load distribution over hardware devices. Each database has its own overhead;the more databases, the more load. A large database has the same overhead for basic services as a small one.

 

[Typ0]

hi,

I don't agress with this, I think one little database will more quick than the big one, if you can use one database about 70%, another is 30%, why not use separatly.

You really have to look at it on a table by table basis not the size of the db. If you have a table it should perform the same in any database (provided it's all put together right) because the db statistics are calculated on the table. And usually with the bigger db we are just talking about one with bigger tables. You move them to another db they are still the same tables. Even if there was a performance gain (and there are a lot of variables that may result in one) to us humans it will be negligible.

I use a simple rule, same security same database.