Multiple Databases vs a Few

I started work a new company recently and they provide a web based tool to
companies. The database model is in rough shape -- when a new client comes
on they simply create a new database for each client. Right now they have
roughly 400+ databases split over two database servers.
I am suggesting they bring that # down to a couple. I think managing 400
connections to a variety of databases might be a performance concern. I
would also think that in terms of Stored Procs it must be using more
resources -- instead of optimizing 1 it has to optimize the same 1, 400
times.
Im guessing on those above -- does anyone know the postive and negatives in
doing this, or know of any article discussing this topic.
ThanksBrian wrote:
> I started work a new company recently and they provide a web based
> tool to companies. The database model is in rough shape -- when a
> new client comes on they simply create a new database for each
> client. Right now they have roughly 400+ databases split over two
> database servers.
> I am suggesting they bring that # down to a couple. I think managing
> 400 connections to a variety of databases might be a performance
> concern. I would also think that in terms of Stored Procs it must
> be using more resources -- instead of optimizing 1 it has to optimize
> the same 1, 400 times.
> Im guessing on those above -- does anyone know the postive and
> negatives in doing this, or know of any article discussing this topic.
> Thanks
I don't necessarily think using separate databases is an architectural
problem. It does, however, lead to more involved backups and SP
management. OTOH, the databases are smaller, can be backed up more
quickly, and different databases offers a level of security that might
be more difficult to manage should all clients be combined into the same
tables in the same database. What happens when a client leaves? How
involved is the removal of data if they share tables? What happens if
one client's data is accidentally shown as being "owned" by another
client? Does that lead to potential legal concerns... You get the idea.
The architecture, it seems, may need to involve legal as well as IT.
Your comments about SP duplication, however, is a concern. Certainly, if
you could find a way to share all common procedures, it would help
maintenance. You could consider using a shared database for all (most)
stored procedure access. That should allow you to use a single set of
procs for all user databases. Instead of executing the procedure in the
current database, just prefix the shared db (shareddb.dbo.procname).
David Gugick
Imceda Software
www.imceda.com|||Brian
There are no reasons to create a database for each client. I think you are
going to build a new model of the database , only you do know a business
requrements.
Visit at www.sql-server_performance.com and you will find a really good
stuff there.
"Brian" <brian@.nospam.com> wrote in message
news:%23k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||It is an administrator's nightmare. However, if the DBs for each of your
clients has data related to that specific client and should not be mixed
with other clients (which is correct) then I guess, you will have to live
with it. If the DBs contain code and metadata for your web based client
application, then consolidating is the right approach.
"Brian" <brian@.nospam.com> wrote in message
news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||Have you considered using partitioned views to unify all this data in a
common DB? Using that method you may only need a single copy of much of your
server-side code.
I would have expected that the main argument for unifying the databases into
one would be based on the potential savings on development, reporting and
admin costs over the costs of those things for 100s of identical databases.
--
David Portas
SQL Server MVP
--|||There is little to no difference between the databases. A couple clients
have a few custom pieces but nothing that could not be solved in the data
model.
Aside from the admin nightmare (it exists now), is there any performance
hit. I guess this may be more is an IIS webserver question. Having to
manage 400 different connection strings.
And again the duplicate Stored Procs, and who knows what else.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:uwhvCspuEHA.2144@.tk2msftngp13.phx.gbl...
> It is an administrator's nightmare. However, if the DBs for each of your
> clients has data related to that specific client and should not be mixed
> with other clients (which is correct) then I guess, you will have to live
> with it. If the DBs contain code and metadata for your web based client
> application, then consolidating is the right approach.
>
>
> "Brian" <brian@.nospam.com> wrote in message
> news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
>> I started work a new company recently and they provide a web based tool
>> to
>> companies. The database model is in rough shape -- when a new client
> comes
>> on they simply create a new database for each client. Right now they
>> have
>> roughly 400+ databases split over two database servers.
>> I am suggesting they bring that # down to a couple. I think managing 400
>> connections to a variety of databases might be a performance concern. I
>> would also think that in terms of Stored Procs it must be using more
>> resources -- instead of optimizing 1 it has to optimize the same 1, 400
>> times.
>> Im guessing on those above -- does anyone know the postive and negatives
> in
>> doing this, or know of any article discussing this topic.
>> Thanks
>>
>|||Hi Brian,
I am a dba for multiple companies in a datacenter and one of our companies
has around 400 lawfirms and everything is in one database. The main thing
that you have to think about is security. In our case each lawfirm has a role
and many tables contain a field that specifies to which law firm the row
belongs (row level security). This is basically the idea but it gets more
complex when you have multiple groups with each law firm. This is based on
the requirements and something that you have to analyze). If it was me, I
would create one database with 2 clients and test the crap out of it... The
process might be painful but you have a lot to win as a DBA (amount of work
for you and the developers, $$$, more simplified disaster recovery
process...).
--
Sasan Saidi, MSc. in CS
"Brian" wrote:
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives in
> doing this, or know of any article discussing this topic.
> Thanks
>
>|||Personally I would go for the smaller the number of
databases the better.
Here are two reasons.
If you need a to make a change to the schema (i.e. insert
a new table), you will need to insert the new table into
every database table, and that goes for every schema
change and standard / generic database changes.
The second is general DB work, you will need to write a
lot of generic procedures that effect all the databases.
There however a really big downside to this which is
security. Assuming each customer db has there own users /
roles ect putting them all into one big database can be a
bit of a problem, as SQL Server as yet does not have
automatic row selections for specific users.
Here is an example.
You have three databases, each of them has
a 'PersonDetails' table. You mold them into one big table,
how do you stop the people in database 1 accessing info in
database 2.
It is possible to implement this, but requires a change to
your schema.
However my personal opinon still stands.
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
>--Original Message--
>I started work a new company recently and they provide a
web based tool to
>companies. The database model is in rough shape -- when
a new client comes
>on they simply create a new database for each client.
Right now they have
>roughly 400+ databases split over two database servers.
>I am suggesting they bring that # down to a couple. I
think managing 400
>connections to a variety of databases might be a
performance concern. I
>would also think that in terms of Stored Procs it must be
using more
>resources -- instead of optimizing 1 it has to optimize
the same 1, 400
>times.
>Im guessing on those above -- does anyone know the
postive and negatives in
>doing this, or know of any article discussing this topic.
>Thanks
>
>.
>|||Hi Brian,
I have noticed Peter and Sasan Saidi had given their suggestions on this
issue. I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!